Il existe plusieurs moyens pour faire des sauvegardes de bases de données. Je ne vais pas parler ici de l’utilisation des logs binaires ni de la mise en place de réplication pour effectuer les sauvegardes sur un serveur esclave ; je vais me concentrer sur l’outil principal de sauvegarde lorsqu’on utilise MySQL : mysqldump

Je pense que tout le monde (en tout cas, une majorité des gens qui lisent cet article) sait à quoi sert cet outil. Il sert à écrire un fichier contenant toutes les directives SQL permettant de recréer une base de données à l’identique de l’état dans lequel elle se trouvait au moment de la sauvegarde.
Je ne vais pas m’étaler sur les aspects théoriques, mais plutôt vous expliquer comment je l’utilise.

Je me sers de mysqldump depuis une bonne quinzaine d’années, il me semble. À l’époque, l’utilisation par défaut (sans passer d’option particulière en ligne de commande) était assez pénible, surtout quand on voulait restaurer les données ; le code SQL généré contenait une commande INSERT pour chaque ligne de chaque table. Quand on injectait le fichier, le serveur mettait un temps infini pour exécuter toutes ces insertions et recalculer les index au fur et à mesure.
Ma vie a changé quand j’ai découvert l’option “–extended-insert”, qui regroupe plusieurs insertions dans une seule commande INSERT.

Les options à utiliser

Aujourd’hui, l’option “–opt” est activée par défaut sur les versions modernes de mysqldump. Cette option se contente d’activer par défaut les options suivantes :

• –add-drop-table : Ajoute des directives pour effacer les tables avant de les recréer. Utile pour réinjecter un fichier en écrasant les anciennes versions des tables qui pourraient déjà traîner dans la base.
• –add-locks : Ajoute des commandes pour verrouiller les tables pendant leur écriture ; il est alors impossible de lire ou d’écrire dedans en même temps que les données sont injectées, ce qui évite de ralentir l’injection.
• –create-options : Ajoute les commandes de création des tables. On ne saurait s’en passer.
• –disable-keys : Cette option accélère l’injection des données en faisant en sorte que les index sont créés qu’une fois que toutes les lignes sont injectées. Sauf que cela ne fonctionne qu’avec les index non uniques des tables MyISAM. Et comme vous êtes censés utiliser le moteur InnoDB à la place du MyISAM, on s’en moque un peu…
• –extended-insert : Je viens d’en parler, c’est juste essentiel.
• –lock-tables : Cette option a pour but de garantir l’intégrité des données qui sont sauvegardées, en verrouillant les tables durant la lecture. Je vais revenir sur ce point, car il est problématique.
• –quick : Par défaut, quand mysqldump s’occupe d’une table, il tente de récupérer toutes les données en mémoire, avant d’en écrire le contenu. Sauf que si vous avez de très grosses tables, cela ne tiendra pas en RAM. L’option “–quick” empêche de tout charger en mémoire.
• –set-charset : Demande l’ajout de la directive SET NAMES avec l’indication de l’encodage de caractères utilisés. Ça ne mange pas de pain.

Du coup, toutes ces options peuvent sembler utiles (et c’est sûrement pour ça qu’elles ont été regroupées sous l’option unique “–opt”, et que cette option est activée par défaut). Sauf qu’il y a un problème lorsqu’on exécute mysqldump en production avec ces options : Je vous ai dit que l’option “–lock-tables” verrouille les tables au moment où on les lit.

Encore une fois, la volonté derrière cela est assez simple. Si des écritures (ajouts ou modifications de données) ont lieu pendant la sauvegarde de la base de données, le fichier qui est généré n’a alors plus aucune cohérence. Vous pouvez avoir récupéré des données dont le début reflète un état différent de la fin, parce que les données ont bougé entretemps… Vous imaginez le bordel.
Sauf que si vous utilisez cette option sur votre serveur de production (et sans avoir mis en place une réplication qui vous permettrait de faire les sauvegardes sur un serveur esclave), cela veut dire que pendant toute la durée de l’exécution de mysqldump, votre application risque de vouloir manipuler les données… sans succès. Le pire, c’est que ces verrous sont posés par base ; donc si vous avez des données avec des liaisons qui se font entre des tables qui sont dans des bases différentes, cela ne sera pas suffisant de toute façon.

La solution est de désactiver cette option avec l’option “–skip-lock-tables”, mais de faire en sorte que la sauvegarde se fasse au sein d’une transaction. Non seulement la base de données continue à fonctionner normalement pendant la durée de la sauvegarde, mais l’intégrité des données récupérées est garantie par le fait d’être dans une transaction. Pour cela, il suffit d’ajouter en plus l’option “–single-transaction”.

Quelles bases sauvegarder

Quand on sauvegarde ses bases de données, on peut être tenté d’utiliser l’option “–all-databases”, qui effectue la sauvegarde de toutes les bases présentes sur le serveur. Je l’ai fait pendant des années, et c’est une fausse bonne idée.

Quand on utilise cette option, c’est souvent parce qu’on passe par l’utilisateur root (celui de MySQL, pas celui du système d’exploitation), ce qui permet de récupérer en même temps les informations des utilisateurs et de leurs droits, stockés dans la base ”mysql”. Il paraît séduisant de pouvoir restaurer toutes ces données d’un seul coup, sans avoir besoin de les recréer à la main.
Sauf que le format des tables utilisées par MySQL peut changer d’une version à l’autre. Il n’y a aucune garantie de stabilité à ce niveau, et vous pourriez donc mettre votre serveur en vrac après avoir écrasé le contenu de cette table et ne plus pouvoir le redémarrer − simplement parce que la version sur laquelle vous tentez de le faire est plus récente ou plus ancienne que celle sur laquelle la sauvegarde a été effectuée…

En plus de cela, mon expérience personnelle me pousse à créer des fichiers séparés pour chaque base de données. Avoir un seul gros fichier contenant toutes les tables de toutes les bases peut sembler plus pratique (un seul fichier à manipuler), mais c’est la plupart du temps une erreur. Ce fichier unique peut être énorme, ce qui complique son utilisation ; sa décompression prend beaucoup de temps, alors que lorsque vous avez un fichier par base il devient possible de mettre en place des stratégies intelligentes (paralléliser les décompressions, les répartir sur plusieurs machines, décompresser une base pendant qu’une autre est en cours d’injection, etc.).
Sans oublier qu’il ne faut pas sous-estimer les fois où vous aurez juste besoin de récupérer un petit ensemble de données. Il sera alors plus rapide de ne restaurer qu’une seule base, plutôt que de perdre du temps à tout restaurer avant d’aller chercher les données qui vous intéressent.

Triggers et autres joyeusetés

Par défaut, mysqldump sauvegarde les triggers, donc pas besoin d’ajouter l’option “-triggers”. Par contre, si vous utilisez les procédures stockées et les événements, il vous faut ajouter les options respectives “-routines” et “-events”.

Un peu de sécurité

La plupart du temps, on utilise mysqldump dans un script qui s’exécute automatiquement par crontab. On est alors tenté de passer le mot de passe de l’utilisateur en ligne de commande, comme ceci :

mysqldump -u utilisateur -pmot_de_passe base > base.sql

Sauf que si vous faites ça, vous aurez un petit message d’alerte :

[Warning] Using a password on the command line interface can be insecure.

Eh oui, rien qu’en regardant la liste des processus, il est possible de voir le mot de passe. Et même en se disant que le serveur est sécurisé, on n’est jamais trop prudent, d’autant que la solution est d’une simplicité enfantine.
Il suffit de passer le mot de passe de la manière suivante :

MYSQL_PWD="mot_de_passe" mysqldump -u utilisateur base > base.sql

Script d’exécution

Voici le petit script shell que j’utilise pour sauvegarder toutes les bases d’un utilisateur donné. Ce qu’il fait est assez simple et se décompose en plusieurs étapes :

• Il crée une arborescence sous /var/archives, avec un sous-répertoire nommé suivant la date d’exécution et un sous-sous-répertoire nommé suivant l’heure.
• Puis il récupère la liste des bases auxquelles l’utilisateur a accès.
• Il boucle sur cette liste pour dumper chaque base dans un fichier.
• Il reboucle sur la liste des bases pour compresser les fichiers générés.
• Enfin il recopie le tout sur Amazon S3 pour archivage.

#!/bin/sh

# configuration de l'utilisateur MySQL et de son mot de passe
DB_USER="utilisateur"
DB_PASS="mot_de_passe"
# configuration de la machine hébergeant le serveur MySQL
DB_HOST="localhost"
# configuration du bucket Amazon S3
AWS_BUCKET="mon_bucket"

# sous-chemin de destination
OUTDIR=`date +%Y-%m-%d/%H:%M:%S`
# création de l'arborescence
mkdir -p /var/archives/$OUTDIR
# récupération de la liste des bases
DATABASES=`MYSQL_PWD=$DB_PASS mysql -u $DB_USER -e "SHOW DATABASES;" | tr -d "| " | grep -v -e Database -e _schema -e mysql`
# boucle sur les bases pour les dumper
for DB_NAME in $DATABASES; do
    MYSQL_PWD=$DB_PASS mysqldump -u $DB_USER --single-transaction --skip-lock-tables $DB_NAME -h $DB_HOST > /var/archives/$OUTDIR/$DB_NAME.sql
done
# boucle sur les bases pour compresser les fichiers
for DB_NAME in $DATABASES; do
    gzip /var/archives/$OUTDIR/$DB_NAME.sql
done
# archivage sur Amazon S3
aws s3 sync /var/archives/$OUTDIR s3://$AWS_BUCKET/$OUTDIR

Au cas où l’utilisateur est le root, on retire la base “mysql” de la liste des bases à traiter, ainsi que toutes celles dont le nom se termine par “_schema”, afin de ne pas prendre les bases “information_schema” et “performance_schema”.
La base “information_schema” est gérée en mémoire par MySQL ; elle est recréée à chaque redémarrage du serveur (il n’y a même pas de fichiers ni de répertoire sur disque y correspondant). La base “performance_schema” contient des indicateurs de performance qui ne sont pas non plus utiles de sauvegarder.

Il y a deux boucles séparées − l’une pour le dump et l’autre pour la compression − alors qu’il aurait été possible de mettre ces deux actions à la suite dans la même boucle. Mais alors, il aurait fallu attendre qu’un dump soit compressé avant de passer à la base suivante. Il ne faut pas oublier que si une transaction est ouverte par mysqldump pour garantir l’intégrité des données, cela n’est valable que pour chaque dump de base. Il n’y a pas de garantie concernant l’intégrité entre plusieurs bases. Je préfère donc réduire au maximum le temps de lecture sur le MySQL, pour ensuite compresser les fichiers une fois que tout a été dumpé.
Évidemment, cela implique qu’il faut avoir assez de place sur le disque dur pour stocker tous les fichiers non compressés, mais c’est une contrainte pas très impactante au final.

Pour l’archivage sur Amazon S3, j’utilise l’outil en ligne de commande proposé par Amazon. Il peut être installé de plusieurs manières différentes (et notamment via les dépôts standard sous Ubuntu). Sa configuration n’est pas l’objet de cet article, j’en parlerai sûrement de nouveau prochainement ; en attendant, vous pouvez regarder la documentation officielle.

InnoDB

J’ai parlé plus haut des moteurs de stockage MyISAM et InnoDB. Pour résumer, MyISAM c’est vieux et c’est nul, utilisez InnoDB pour créer toutes vos tables. Mais je vais détailler un peu.

MyISAM est le moteur « historique » de MySQL et en a longtemps été le moteur par défaut. Il a pour avantage d’être simple et donc rapide ; il supporte aussi l’indexation full-text.
InnoDB est un moteur plus récent (quand même disponible depuis la version 4 de MySQL) et est le moteur par défaut depuis MySQL 5.5.5. Ses avantages sont nombreux, depuis le support des transactions et des clés étrangères, jusqu’à une plus grande robustesse (une fois écrites, les données ont moins de chance d’être corrompues), en passant par le fait qu’une écriture ou une modification de données va verrouiller uniquement la ligne concernée et non pas la table en entier (ce qui est le cas avec MyISAM).

Pour l’argument de l’indexation full-text, je pense que plus personne n’utilise MySQL pour cela de nos jours. On aura tendance à privilégier Sphinx, Solr ou ElasticSearch.