Journal La sauvegarde MySQL

Posté par  . Licence CC By‑SA.
Étiquettes :
22
28
juin
2012

Cher journal,

J'ai enfin trouvé une manière de sauvegarder et de restaurer une base MySQL de plusieurs Go qui soit efficace dans les deux sens.

Pour les bases

  • Sauvegarde:

    mysqldump --single-transaction --quick -B $db | gzip > $db.sql.gz

  • Restauration

    zcat $db.sql.gz

Pour les bases > 1 Go, une approche innovante est préférable, surtout dans le cas d'innodb (où il ne faut pas oublier d'activer innodb_file_per_table)

  • Sauvegarde (que vous pouvez éventuellement compresser par la suite): mysqldump --quick --skip-extended-insert --tab=/tmp/$db -B $db

Concernant la restauration, c'est là où on va rencontrer des problèmes. Les clés étrangères vont provoquer des
ERROR 1005 (HY000) at line 23: Can't create table '$db.$table' (errno: 150)
et des
mysqlimport: Error: 1452, Cannot add or update a child row: a foreign key constraint fails ($db.$table, CONSTRAINT FK444FE365DB877774 FOREIGN KEY (fk_$table_id) REFERENCES $table ($field) ON DELETE CASCADE), when using table: $table

Le plus simple est d'utiliser ce script, qui va désactiver les vérifications de clé durant l'import. Et non, malheureusement, la demande d'ajout de cette fonctionnalité directement dans la cli datant de 2006 n'a toujours pas été implémenté.

Il faut faire soi même son script, qui peut ressembler à ça:

#!/bin/bash

echo "SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;"
for f in `ls -1 *.sql`; do echo "source '`pwd`/$f;'" done;
echo "COMMIT;"

for f in `ls -1 *.txt`; do
        table=${f/.txt/}
        echo "load data infile '`pwd`/$f' INTO TABLE $table;"
done;
echo "SET FOREIGN_KEY_CHECKS = 1;"
echo "COMMIT;"

  • La restauration passe alors sans erreur et attaque directement les performances brutes du stockage sous-jacent:

    bash sql.sh | mysql

Sur une base de 10 Go, on passe de 5h en restauration à environ 30 minutes, et de 5/10 Mo/s à 50/80 Mo/s, d'après iotop.

L'autre gros avantage de cette méthode est d'être en capacité de pouvoir restaurer seulement une table sans avoir à réinjecter tout le dump dans une base à côté.

  • # Codage

    Posté par  (site web personnel) . Évalué à 8.

    Attention au codage des données. mysqldump > fichier puis mysql < fichier ça va corrompre des données parce que :

    • mysqldump écrit des données en UTF-8 par défaut ;
    • mysql lit des données en Latin-1 par défaut (ou l'inverse).

    • [^] # Re: Codage

      Posté par  (Mastodon) . Évalué à 3.

      Ça peut dépendre aussi des valeurs de test my.cnf, .my.cnf, $LC* $LANG et des encodages choisis dans la base, mais ça fait longtemps que j’ai pas eu de problèmes, et si tu en as pour le dump/restore, tu vas en avoir ailleurs à coup sûr…

  • # C'est la routine.

    Posté par  . Évalué à 5.

    Experience recente ne pas oublier de rajouter "--routines" ;)

    • [^] # Re: C'est la routine.

      Posté par  . Évalué à 0.

      Ce n'est pas nécessaire si on sauvegarde la table "mysql", normalement.

  • # innodb

    Posté par  (Mastodon) . Évalué à 1.

    Sur une base innodb en cours d’utilisation, il est aussi judicieux d’utiliser --single-transaction au dump, pour garantir l’intégrité sans pénaliser les autres accès par un lock ☺

  • # Xtrabackup et Xtrabackup-manager

    Posté par  . Évalué à 2. Dernière modification le 29 juin 2012 à 12:04.

    Une solution alternative est xtrabackup.

    Cet outil est capable de sauvegarder directement en copiant les fichiers de la base de donnée, sans arrêter le serveur. (Il fait ça en surveillant le journal, etc.)

    Il est aussi capable de faire des backups incrémentaux.

    Du coup c'est beaucoup mieux que des dumps sql:

    • sauvegardes plus rapides
    • restaurations beaucoup plus rapides
    • taille des sauvegardes
    • la DB n'est jamais verrouillée
    • les données sont consistantes

    Pour planifier des backups, gérer les backups incrémentaux, gérer les backups de serveurs distants, etc il y a xtrabackup-manager, qui est un frontend CLI à xtrabackup.

    • [^] # Re: Xtrabackup et Xtrabackup-manager

      Posté par  (site web personnel) . Évalué à 1.

      les données sont consistantes

      Ou pas. Je dirais que ça marche globalement bien mais il ne faut pas oublier de faire un test du jeu de backup. Je eu quelques problèmes sur les bases > à 20 GO en particulier lorsque l'innodb_file_per_table n'est pas activé. L'heure est également extrêmement importante si on stream sur le réseau.

  • # pas de Q

    Posté par  (site web personnel) . Évalué à 3.

    Pour l'export des structures ne pas oublier -Q pour l'abruti qui aurait nommé sa table UPDATE ou SELECT.

    • [^] # Re: pas de Q

      Posté par  . Évalué à 6.

      il ne faut pas prendre les utilisateurs pour des cons
      mais il ne faut pas oublier qu'ils le sont.

  • # LV Snap

    Posté par  (site web personnel) . Évalué à 4.

    Pourquoi ne pas mettre ta DB sur un LVM ?

    Tu lock ta DB
    Tu créer un snapshot
    Tu delock ta DB
    Tu backup ton snapshot

    Is it a Bird? Is it a Plane?? No, it's Super Poil !!!

    • [^] # Re: LV Snap

      Posté par  . Évalué à 3.

      Le temps (peut-être court, mais il existe quand même) entre le verrouillage et le déverrouillage de la DB ne risque-t-il pas de gêner les écritures ? Ou bien ces dernières peuvent-elles être mises "en pause", et seront faites lorsque la Db sera déverrouillée ?
      Mais dans ce cas, le système se retrouve avec des données "vieilles" ou pas à jour (je parle de systèmes où la péremption des données est extrêmement rapide, type la Bourse).

      • [^] # Re: LV Snap

        Posté par  (site web personnel) . Évalué à 1.

        Pour moi mysqldump lock également les tables (en tout cas il a besoin des permissions)
        La création du snapshot est extrêmement courte (inférieure à 1s)

        Is it a Bird? Is it a Plane?? No, it's Super Poil !!!

        • [^] # Re: LV Snap

          Posté par  . Évalué à 0.

          Ça peut être intéressant, mais le problème est surtout sur la restauration, beaucoup plus que sur la sauvegarde. La commande mysqldump mets moins de 2 minutes sur une base de 10 Go, c'est tout à fait acceptable.

          J'avoue que j'ai toujours un peu peur de la restauration d'une base au format binaire. Ça peut donner l'impression de marcher, mais je ne sais pas jusqu'à quel point on peut avoir confiance en elle. Si le snapshot se fait au mauvais moment, on est susceptible d'avoir un sympathique "table marked as crash" lors de la restauration, et d'avoir à faire un REPAIR TABLE qui peut prendre beaucoup de temps (!).

          Enfin, en ayant un fichier par table, on gagne la souplesse de pouvoir éventuellement restaurer que la table fautive (si l'applicatif le permet). Sur de gros volumes, ça peut se révéler très utile.

Suivre le flux des commentaires

Note : les commentaires appartiennent à celles et ceux qui les ont postés. Nous n’en sommes pas responsables.