Utilisation de MySQLDump

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.

27 commentaires pour “Utilisation de MySQLDump

  1. SQL Structured query language, langage de requête structurée
    J aime bien traduire les acronymes…

  2. Ah oui, mais si tu commences à traduire tous les acronymes que j’ai utilisé sur ce blog, tu n’as pas fini ! 😉
    Les informaticiens aiment bien les acronymes…

  3. Je comprends, anyway I am very proud of what you have achieved. Congratulations !?

  4. J’utilise personnelement ce micro-script pour faire 1 fichier / db : https://github.com/Nono-m0le/DB_Backup

    en utilisant la boucle :
    sqlarg="-h $host -u $user -p${pass}"

    for table in `mysql $sqlarg -e 'show databases;' | tail -n +3`
    do
    mysqldump $sqlarg $table | gzip -9 > ${backup_dir}${table}.sql.gz
    done

  5. @Nono : Merci, mais ça ne fonctionne pas pour n’importe quel utilisateur.
    Le root verra les tables « mysql », « performance_schema » et « information_schema » ; alors qu’un autre utilisateur pourrait ne voir que la base « information_schema », par exemple (ou aucune des trois). Ce qui fait que ton “tail -n +3” ne serait pas utilisable tel quel, à moins de savoir quel utilisateur va servir à se connecter à MySQL, et que tu n’en changes jamais.

  6. Oui, oui, bien sûr. Ma remarque était juste pour dire que mon script était plus “agnostique” quant à l’identité et aux droits de l’utilisateur qui fait la sauvegarde 🙂

  7. Bonjour merci pour cet article. J’ai une petite question.
    Comment faire pour restorer une base de donnée en ligne de commande en précisant qu’il s’agit d’un update ?
    Par avance merci pour réponse.

    Cordialement,
    Laurent

  8. @Laurent : Quand tu restaures une base de données, tu la remets dans l’état dans lequel elle était au moment de la sauvegarde. Tu ne peux pas mettre à jour une base de données en la restaurant. Ça n’a pas de sens.

  9. Pour les utilisateurs d’un CMS, une option est d’aller chercher les informations dans le fichier de conf, exemple avec un WordPress :

    WP_CONFIG=$CDIR/shared/wp-config.php
    DB_NAME=$(grep DB_NAME $WP_CONFIG | cut -d \’ -f 4)
    DB_USER=$(grep DB_USER $WP_CONFIG | cut -d \’ -f 4)
    DB_PASSWORD=$(grep DB_PASSWORD $WP_CONFIG | cut -d \’ -f 4)
    DB_HOST=$(grep DB_HOST $WP_CONFIG | cut -d \’ -f 4)
    mkdir -p $CDIR/archives
    MYSQL_PWD=$DB_PASSWORD mysqldump -u $DB_USER –single-transaction –skip-lock-tables $DB_NAME -h $DB_HOST > $CDIR/archives/$CURRENT_DATE-$DB_NAME.sql

  10. Merci pour ces infos intéressantes!
    Pour le coup j’utilisais –all-databases et je suis aujourd’hui dans la situation de récupérer en urgence une des base de ce gros dump. Y a t il une méthode pour scinder ce fichier directement et récupérer uniquement ce qui concerne la base que je cherche, ou dois-je obligatoirement l’injecter sur une nouvelle bdd et re-exporter seulement ce qui m’intéresse ?
    Merci beaucoup!

  11. @max : Tu peux découper le fichier en faisant de la recherche de portions de textes. Suivant la taille de la base, ça peut être infiniment plus rapide que d’injecter le fichier complet puis de réexporter uniquement la base qui t’intéresse, puis de la réinjecter ailleurs. Mais c’est artisanal.

  12. Hello hello,
    pour commencer merci pour ton site, il m’a appris un tas de trucs.
    Sans chercher la petite bête et particulierement pour avoir ton avis, en surfant sur le site de percona ils évoquent le fait que mysqldump –single-transaction pourrait potentiellement réserver de mauvaises surprises dans des cas bien particulier et recommande l’usage de mysqldump –lock-all-tables.
    T’en penses quoi ?
    l’article en question => https://www.percona.com/blog/2012/03/23/best-kept-mysqldump-secret/

  13. @aZer : L’article est intéressant. Mais il faut bien le lire, et ne pas prendre ce conseil pour argent comptant.

    Ce qu’ils expliquent, c’est qu’avec l’option –single-transaction on peut potentiellement ne pas sauvegarder des données si une altération de table a lieue pendant que la table est sauvegardée. Je ne sais pas pour toi, mais moi je fais des sauvegardes infiniment plus souvent que des altérations !

    La solution qui est proposée (utiliser l’option –lock-all-tables) n’est à employer que dans le cas très précis où on a besoin de sauvegarder pendant qu’une altération a lieue.

    Il faut bien comprendre que cette option (–lock-all-table) empêche d’écrire la moindre donnée pendant toute la durée de la sauvegarde. Ça peut être très problématique.
    L’option –single-transaction, quant à elle, permet de sauvegarder les données dans un état stable, sans empêcher que des écritures soient faites pendant ce temps.

    Donc, à moins d’avoir besoin de faire des altérations de tables en même temps que tes sauvegardes, pas de soucis 🙂

  14. Merci pour cet article, je l’utilise assez régulièrement depuis quelques années 😉

  15. Merci ! 🙂
    Eh, j’avoue que parfois j’écris des articles pour retrouver l’info plus facilement 😉

  16. Bonjour Amaury
    très bon article
    je suis en charge de la gestion des sauvegardes de base de données et je souhaites effectuer des sauvegardes par tables mais comment garantir la cohérence des données de cette façon

    D’avance merci de ton retour

  17. @asgardi : C’est effectivement une problématique complexe à résoudre.

    Ce que je ferais, c’est utiliser mysqldump, mais en redirigeant sa sortie vers un programme que j’écrirais, dont le rôle serait d’écrire ce que lui envoies mysqldump dans des fichiers. Il faudrait que ce programme reconnaisse quand il commence à recevoir des instructions concernant une nouvelle table, pour écrire le contenu dans un nouveau fichier.

    Ça ferait un truc du genre :
    MYSQL_PWD=$DB_PASS mysqldump -u $DB_USER --single-transaction --skip-lock-tables $DB_NAME -h $DB_HOST | mon_programme_d_export /var/archives/$OUTDIR/$DB_NAME

    Il suffit que le programme lise son entrée standard ligne-à-ligne, pour détecter chaque table.

  18. Bonjour, super intéressant l’article et le script!

    Grâce à lui j’ai réussi à diriger mes copies de sauvegarde sur une clé USB sur le Raspberry!(j’ai juste remplacé le chemin par /media/pi/nomdelaclé et remplacé les : de l’heure par un – (sinon le dossier ne pouvait pas être crée…))

    Y a-t-il un moyen d’automatiser la suppression des sauvegardes du genre après une semaine on supprime les plus vieilles afin de ne pas saturer le disque de sauvegarde?

    Merci

  19. @Mangin-voirin : Tu peux regarder du côté de mon script Arkiv (https://github.com/Amaury/Arkiv), qui fait de la sauvegarde de fichiers et de bases de données (MySQL/PostgreSQL), sur le disque local et sur Amazon S3 et Amazon Glacier. Il y a un système de purges qui fonctionne bien.
    Tu peux soit utiliser le script, soit t’inspirer de son code source.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Notifiez-moi des commentaires à venir via email. Vous pouvez aussi vous abonner sans commenter.