Script de sauvegarde Mysql par base « mysql_dump.sh »

EDIT – 13/05/2013 : v0.3 du script avec prise en compte des suggestions d’améliorations de David M + Trap

EDIT – 06/12/2012 : v0.2 du script avec prise en compte des commentaires de l’article

Un énième script de sauvegarde à plat de bases Mysql sur internet. Celui-ci crée un fichier texte (.sql) par base et compresse le tout ensuite.

J’utilise ce script depuis plus de 3 ans, ça tourne bien et surtout ça dépanne bien !

Attention : ce script est à coupler avec un système de sauvegarde complet et distant…

Préparation

Il faut créer un utilisateur Mysql (appelé dump) avec des droits restreints en lecture sur toutes les bases :

Le script

Copier le contenu du script dans un fichier mysql_dump.sh puis faite un chmod +x mysql_dump.sh afin de le rendre exécutable. Ajouter ensuite ce script dans vos tâches crons pour qu’il s’exécute toutes les nuits (par exemple).

Note : les remarques sont les bienvenus…

17 thoughts on “Script de sauvegarde Mysql par base « mysql_dump.sh »”

  1. Bonjour,

    Merci pour ce script. une autre solution est l’utilisation de Backup Manager pour sauvegarder les bases de données. C’est un script complet de sauvegarde qui se configure assez simplement.

    J’ai écrit ce guide pour installer et configurer Backup Manager:

    http://howto.biapy.com/fr/debian-gnu-linux/systeme/logiciels/installer-et-configurer-backup-manager-sur-debian

    La procédure de configuration de la sauvegarde de MySQL est disponible dans le guide :

    http://howto.biapy.com/fr/debian-gnu-linux/serveurs/bases-de-donnees/installer-et-configurer-mysql-sur-debian

    Si cela peut aider quelqu’un :p.

    Bonne continuation.

  2. Bonjour,

    Quelques remarques a propos de script de sauvegarde :
    la ligne :
    databases=( $(mysql -u $user -p »$pass » -e « show databases » | grep -v Database) )
    peut-être remplacée par :
    databases= »$(mysql -u $user -h $host -p$pass -Bse ‘show databases’) »

    Le tar : pas besoin du v (on a pas besoin de la verbosité car elle n’est, a priori, pas récupérée.

    Ensuite il peut-être une bonne idée de rajouter un check des bases, avant de les sauvegarder.

    Ce qui donne au final, par exemple :

    for db in $BASES
    do
    #On lance un check et une analyse pour chaque base de donnée
    $MYSQLCHECK -u $user -h $host -p$pass -c -a $db
    # On lance un mysqldump pour chaque base de donnée
    $MYSQLDUMP -u $user -h $host -p$pass $OPTIONS $db -R > $TEMPORAIRE »/ »$MACHINE »-« $db »-« $DATE ».sql »;
    done

    A++

    1. Bon sauf que ma stratégie d’un utilisateur restreint ne va pas fonctionner avec mysqlcheck :

      mysqlcheck: Got error: 1142: INSERT command denied to user ‘dumpusr’@’localhost’ for table ‘XXXXXX’ when executing ‘ANALYZE TABLE … ‘

  3. J’utilise à peu de chose près la même technique, sauf qu’en plus je sépare la sauvegarde de la structure et la sauvegarde des données (un fichier par table)

    Ça m’arrive plus souvent de devoir restaurer juste les données d’une table que de restaurer toute une base et sur une grosse base de données, il n’est pas forcément pratique d’avoir à rechercher une table dans un très gros fichier.

    Mon script : https://github.com/leblanc-simon/OpenInstallServer/blob/master/utils/backup/mysqlbackup.sh

  4. Salut,

    Tu peux utiliser un tel système si :

    – tu as une fenêtre de maintenance qui te permet le lock de tes tables pour toutes opérations d’écriture
    – tu n’as pas besoin que tes dbs soient intègrents (imagines que tu inserts des données qui sont liées dans ton application dans 2 dbs différentes, les données sauvegardées pourraient donc être incorrectes)
    – tu n’as pas besoin du backup pour remonter un slave (meme si tu sauvegardes la position du binlog, lequel prendras-tu ?)
    – la vitesse de restauration n’est pas importante, car en effet, importer un fichier dump peut prendre très longtemps surtout lors de la création des indexes

    Je te conseille si tu as tes tables en InnoDB, d’utiliser XtraBackup (http://www.percona.com/software/percona-xtrabackup)

    voilà mes 2 centimes 😉

  5. Première chose, lorsqu’on écrit un script système, c’est une bonne idée de le durcir en ajoutant au début:

    set -eu

    De cette façon on s’évite beaucoup de problèmes; le script s’arrête en erreur si une commande retourne une erreur (e) ou si une variable est vide (u). Il peut être nécessaire de désactiver le « e » à certains endroits.

    Par exemple si pour une raison ou une autre ton dirname est vide, ça t’évitera d’exécuter « rm -rf / »

    Ensuite tu as mis

    # Nom du dump
    dirname= »dump_date +%d.date +%m.date +%y@date +%Hhdate +%M »

    Il vaut mieux éviter d’utiliser le nom d’une commande en nom de variable, ça ne pose pas de problème technique mais ça crée de la confusion, ça réduit la lisibilité et la maintenabilité.

    Tu n’es pas obligé d’appeler 5 fois la commande date, une seule suffit:

    dirname= »dump_ »$(date ‘+%d.%m.%y@%Hh%M’)

    Tu fais un traitement spécial pour « information_schema », pourquoi ne pas faire un grep -v au niveau de la ligne 23, qui simplifierait la boucle ?

    Je ne comprends pas pourquoi du place le mysqldump dans un $(…); tu essayes d’exécuter le texte produit par le résultat de la commande ???

  6. Quelques suggestions d’amélioration :

    1) Ajouter un paramètre :

    Et modifier la ligne de dump par :

    Cela permet d’exclure une ou plusieurs bases de la liste (pour mon cas, je veux enlever performance_schema en plus de information_schema)

    2) Remplacer :

    par

    En effet, last.tar.gz n’existe pas à la première exécution, et ça fait planter le script. En retirant temporairement les flags e et u, on évite cela.

    3) Compte tenu de la puissance des serveurs aujourd’hui, on peut aussi compresser en bz2. Ca consomme un poil plus de proc, mais la compression est meilleure. 😉

    La commande :

    Et bien sûr, renommer toutes les extensions .tar.gz du script en .tar.bz2

    4) Ajouter les lignes suivantes à la fin des paramètres :

    Ca permet de revenir dessus en cas de besoin.

    Si on veut, on peut aussi faire un envoi des logs par email. Dans ce cas, utilisez « > » plutôt que « >> », pour n’envoyer que le dernier log à chaque fois. Pour l’envoi :

    5) Utiliser un répertoire temporaire pour l’export en .sql, plutôt que le répertoire de destination final. Pourquoi ? Cas d’un SAN, par exemple. Le répertoire de stockage final est sur un disque distant, et donc par conséquent, moins performant.

    Ainsi, on fait le travail qui demande une grande vitesse d’écriture en local, et seulement au moment de la compression, on enregistre sur le répertoire final.

    Pour se faire :

    Et on met à jour les lignes nécessaires :

     

    Voilà pour les suggestions 😉

    1. Ooops, je viens de voir une erreur sur mon commentaire précédent…

      Ca n’est pas la ligne de dump, qu’il faut modifier, pour utiliser la variable d’exclusion, mais la ligne qui récupère les noms des bases (copier/coller trop rapide ^^) :

      Voilà ! 🙂

      1. Bonjour David M.,

        Merci pour toutes tes suggestions, j’ai modifié l’article (et donc le script) en conséquence….

        David M. (aussi…)

  7. Une petite dernière pour la route :
    6) Dans le cas où vous créez un fichier de log (n°5 dans mon commentaire précédent), pour vraiment intégrer tous les messages dans les logs, déplacez les lignes :

    juste après

    Voilà ! 🙂

  8. Tiens, je vois que ca bouge un peu ici.

    Perso, j’ai simplifié au maximum pour obtenir 1 fichier compréssé par table existante (dans « show databases ») :

    #!/bin/bash
    backup_dir=/path/
    user="user"
    pass="passwd"
    host="host"

    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
    zip -r ${backup_dir}date +'%Y%m%d-%H%M'_mysql.zip ${backup_dir}*.sql.gz
    rm -f ${backup_dir}*.sql.gz
    find ${backup_dir}*.zip -mtime +10 -exec rm {} \;

Laisser un commentaire

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