Blog

Comment archiver une table MySQL (pt-archiver)

Écrit le 19 08 2013 par Kévin MET _

Voici la problématique, vous avez un super MySQL qui tourne aux petits oignons mais qui avalent des Go de données chaque jours. Vous avez beau avoir des disques de plusieurs To, cela ne va pas pouvoir durer trop longtemps et vous devez archiver le contenu d'une ou plusieurs tables en fonction d'une clause WHERE qui vous est propre. Par exemple si vous devez archiver les données qui ont été insérés durant la dernière année :


WHERE date BEETWEEN '2012-08-20' AND '2013-08-20'

Pour faire cela, il existe un outil super simple à utiliser et très pratique. Il s'agit de pt-archiver qui va vous permettre en une ligne de commande (que vous pouvez par exemple ajouter dans un cron) d'archiver vos tables.

Attention, un élément important à retenir avec cet outil est que par défaut, il va supprimer les données qu'il archive. Si vous souhaiter voir ce qu'il va effacer avant de procéder à l’effacement il faut utiliser l'option --dry-run qui va afficher les requêtes SQL qu'il aurait jouer. Vous pouvez aussi utiliser l'option --no-delete qui permet de ne pas supprimer les données importées mais d'effectuer l'archive.

Ce logiciel utilise la close WHERE que vous lui avez indiqué pour construire les requêtes SQL permettant d'extraire les données, mais il ne fait pas tout d'un bloc. Il avance par paquet en utilisant des index (comme celui de la primary key par exemple) en retenant la valeur de la dernière PK correspondante à la dernière ligne qui matchait votre WHERE. Cela lui permet de ne pas être trop gourmand même si votre WHERE ne peut pas utiliser d'index. Cela permet également de ne pas locker la table trop longtemps afin qu'elle reste utilisable.

Pour installer ce soft, il faut ajouter les dépôt percona à votre distribution. Pour une Debian, ça se passe comme ça :


# echo -e "deb http://repo.percona.com/apt wheezy main\ndeb-src http://repo.percona.com/apt wheezy main" >> /etc/apt/sources.list
# apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
# aptitude update
# aptitude install percona-toolkit

Et voici un exemple d'utilisation. Imaginons que l'on souhaite archiver la table plop dans la base yop qui contient le champs date. Ce champs est sous la forme 'AAAA-MM-JJ' et on veut sauver les données qui y ont été insérées il y a un an. Le serveur sur lequel on bosse est en local et on veut déplacer cette archive sur le serveur backup.host.fr dans une base yop avec un schéma identique.


# pt-archiver --source h=localhost,D=yop,t=plop --dest h=backup.host.fr,D=yop,t=plop --where "date='2012-08-19'" --user root --ask-pass

Les paramètres --source et --dest utilisent la syntaxe DSN et on ajoute également l'option --ask-pass pour que la commande nous demande le password.

On peut aussi sauver notre archive directement dans un fichier. Pour cela on utilise l'option --file qui peut utiliser quelques arguments sympa :

  • %d : Jour du mois, numérique (01..31)
  • %H : Heure (00..23)
  • %i : Minutes, numérique (00..59)
  • %m : Mois, numérique (01..12)
  • %s : Secondes (00..59)
  • %Y : Années, numérique, four digits
  • %D : Nom de la base
  • %t : Nom de la table

Pour sauver le même exemple que précédemment dans un fichier, il faudrait donc faire :


# pt-archiver --source h=localhost,D=yop,t=plop  --file '/var/log/archive/%Y-%m-%d-%D.%t' --where "date='2012-08-19'" --user root --ask-pass

Il existe également deux options bien pratiques :

  • --progress : Indique la progression toutes les X colonnes
  • --statistics : Collecte et indique les statistiques à la fin de la tache

Et sinon pour ceux qui ont des quantités de données faramineuses, il y sqoop qui permet d'extraire des données d'une base MySQL et de les exporter vers un cluster Hadoop. J'en parlerai surement dans un prochain article.

♥ Partage sur tes réseaux sociaux ♥
Kévin MET
Kévin MET

Auteur de ce blog et gérant de la société MNT-TECH, je publie sur ce blog lorsque le temps me le permet et lorsqu'un sujet qui me parait intéressant n'a pas encore été abordé en français. Toutes les informations techniques présentes sur cette page peuvent être réutilisées moyennant le fait de citer la source.