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 :

$ mysql -u root -p -e "CREATE USER 'dump'@'localhost' IDENTIFIED BY 'LEMOTDEPASSE';"
$ mysql -u root -p -e "GRANT SELECT , SHOW DATABASES , LOCK TABLES , SHOW VIEW ON * . * TO 'dump'@'localhost' IDENTIFIED BY 'LEMOTDEPASSE' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;"

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…

#!/bin/bash 

# Inspiré d'un script trouvé sur phpnews.fr (plus en ligne)
# Version 0.3 13/05/2013

# Script sous licence BEERWARE

set -eu

## Paramètres
USER='dump'
PASS='LEMOTDEPASSE' 
# Répertoire de stockage des sauvegardes
DATADIR="/var/backups/mysql"
# Répertoire de travail (création/compression)
DATATMP=$DATADIR
# Nom du dump
DATANAME="dump_$(date +%d.%m.%y@%Hh%M)"
# Compression
COMPRESSIONCMD="tar -czf" 
COMPRESSIONEXT=".tar.gz"
# Rétention / rotation des sauvegardes
RETENTION=30
# Exclure des bases
EXCLUSIONS='(information_schema|performance_schema)'
# Email pour les erreurs (0 pour désactiver
EMAIL=0
# Log d'erreur
exec 2> ${DATATMP}/error.log

## Début du script

ionice -c3 -p$ &>/dev/null
renice -n 19 -p $ &>/dev/null

function cleanup {
    if [ "`stat --format %s ${DATATMP}/error.log`" != "0" ] && [ "$EMAIL" != "0" ] ; then
        cat ${DATATMP}/error.log | mail -s "Backup MySQL $DATANAME - Log error" ${EMAIL}
    fi
}
trap cleanup EXIT

# On crée sur le disque un répertoire temporaire
mkdir -p ${DATATMP}/${DATANAME}

# On place dans un tableau le nom de toutes les bases de données du serveur 
databases="$(mysql -u $USER -p$PASS -Bse 'show databases' | grep -v -E $EXCLUSIONS)"

# Pour chacune des bases de données trouvées ... 
for database in ${databases[@]} 
do
    echo "dump : $database"
    mysqldump -u $USER -p$PASS --quick --add-locks --lock-tables --extended-insert $database  > ${DATATMP}/${DATANAME}/${database}.sql
done 

# On tar tous
cd ${DATATMP}
${COMPRESSIONCMD} ${DATANAME}${COMPRESSIONEXT} ${DATANAME}/
chmod 600 ${DATANAME}${COMPRESSIONEXT}

# On le déplace dans le répertoire
if [ "$DATATMP" != "$DATADIR" ] ; then
    mv ${DATANAME}${COMPRESSIONEXT} ${DATADIR}
fi

# Lien symbolique sur la dernier version
cd ${DATADIR}
set +eu
unlink last${COMPRESSIONEXT}
set -eu
ln ${DATANAME}${COMPRESSIONEXT} last${COMPRESSIONEXT}

# On supprime le répertoire temporaire 
rm -rf ${DATATMP}/${DATANAME}

echo "Suppression des vieux backup : "
find ${DATADIR} -name "*${COMPRESSIONEXT}" -mtime +${RETENTION} -print -exec rm {} \;

Et voici l’antidote (la restauration)

#!/bin/bash 

# Script sous licence BEERWARE

set -eu

## Paramètres mysql
USER='root'
PASS='xxxxxxxxxx' 
# Répertoire de stockage des sauvegardes (contient des fichier *.sql )
DATADIR="/tmp/dump_11.10.19@02h02"

## Début du script
ionice -c3 -p$$ &>/dev/null
renice -n 19 -p $$ &>/dev/null

dbfiles=`find ${DATADIR} -name "*.sql"`
for dbfile in $dbfiles; do
    db=`echo ${dbfile##*/} | cut -d'.' -f1`
    echo "Restauration de la base : $db avec le fichier $dbfile"
    mysql -u $USER -p$PASS $db < $dbfile
done 

23 réflexions au sujet de « 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 +%H`h`date +%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 :

    EXCLUSIONS='(information_schema|performance_schema)'

    Et modifier la ligne de dump par :

    mysqldump -u $USER -p$PASS --quick --add-locks --lock-tables --extended-insert $database > ${DATADIR}${DATANAME}/${database}.sql

    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 :

    unlink last.tar.gz

    par

    set +eu
    unlink last.tar.gz
    set -eu

    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 :

    tar -jcf ${DATANAME}.tar.bz2 $DATANAME/

    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 :

    #Log de sortie
    exec 1>> ${DATADIR}out.log
    #Log d'erreur
    exec 2>> ${DATADIR}error.log

    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 :

    # On fusionne le log d'erreur dans le log de sortie standard
    echo "" >> ${DATADIR}out.log
    echo "Log d'erreur :" >> ${DATADIR}out.log
    echo "" >> ${DATADIR}out.log
    more ${DATADIR}error.log >> ${DATADIR}out.log
    # Et on envoie le log par email
    more ${DATADIR}out.log | mail -s "Backup MySQL $DATANAME - Log" email@domain.tld

    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 :

    # Répertoire temporaire
    DATATMP="/mon_rep_temporaire/"

    Et on met à jour les lignes nécessaires :

    # On crée sur le disque un répertoire temporaire
    mkdir "$DATATMP$DATANAME"
    for database in ${databases[@]}
    do
                    mysqldump -u $USER -p$PASS --quick --add-locks --lock-tables --extended-insert $database  > ${DATATMP}${DATANAME}/${database}.sql
    done
    
    #On tar tous
    cd $DATADIR
    tar -jcf ${DATANAME}.tar.bz2 ${DATATMP}${DATANAME}/
    chmod 600 ${DATANAME}.tar.bz2
    #On supprime le répertoire temporaire
    rm -rf ${DATATMP}${DATANAME}

     

    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 ^^) :

      databases="$(mysql -u $USER -p$PASS -Bse 'show databases' | grep -v -E $EXCLUSIONS)"

      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 :

    ionice -c3 -p$$
    renice -n 19 -p $$

    juste après

    exec 2> ${DATADIR}error.log

    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 {} \;

  9. Merci pour ce script.

    Juste une petite proposition d’évolution : ajouter « –routines » dans la commande mysqldump, afin d’extraire aussi les fonctions utilisateurs.

  10. Bonjour

    Comment es tu sur que tes dump se sont bien déroulés avant suppression des plus anciens?

    Pour ma part, je fusionne toujours le log d’erreur et le log de sortie standard avec la commande : exec > >(tee $LOG_FILE) 2>&1

    1. Je reçois un email uniquement s’il y a eu erreur donc je ne souhait pas fusionner la sortie standard et d’erreur…
      Rien n’assure avant la suppression que le dump c’est bien passé, après avec 30 jours de rétention si tu ne réagit pas à la réception du mail d’erreur c’est que tu n’es plus de ce monde :-p

  11. Si on met set -eu en début de script, le script va se fermer en cas d’erreur c’est bien ça ?

    Si on choisit d’envoyer un mail de récap à la fin du script et que le celui est fermé, le mail ne partira pas ?

    cdt

Laisser un commentaire

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

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

En continuant à utiliser le site, vous acceptez l’utilisation des cookies (au chocolat) Plus d’informations

Les cookies sont utilisés à des fin de statistique de visite du blog sur une plateforme indépendante que j'héberge moi même. Les statistiques sot faites avec un logiciel libre. Aucune information n'est redistribué à google ou autre. Je suis seul autorisé à lire ces informations

Fermer