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…

Table of Contents

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 » »

Biapy dit :

5 décembre 2012 à 16 h 30 min

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.

Répondre
Alexandre LUCAZEAU dit :

5 décembre 2012 à 18 h 05 min

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++

Répondre
1. David dit :
  
  6 décembre 2012 à 7 h 37 min
  
  @Alexandre LUCAZEAU : Yes, merci !
  
  Je vais tester ça dans mon script et je mettrais à jour le post !
  
  Répondre
2. David dit :
  
  6 décembre 2012 à 10 h 50 min
  
  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 … ‘
  
  Répondre
Simon dit :

5 décembre 2012 à 18 h 15 min

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

Répondre
Block dit :

5 décembre 2012 à 18 h 22 min

Ce serait pas mieux d’utiliser mysqlhotcopy[1] ? (jamais essayé). Sinon pour les backups de (petites) bdd, j’utilise backupninja.

[1] https://dev.mysql.com/doc/refman/5.0/fr/mysqlhotcopy.html

Répondre
1. David dit :
  
  6 décembre 2012 à 7 h 42 min
  
  @Block : mysqlhotcopy ne fonctionne qu’avec les tables de type MyISAM et ISAM.. donc adieu InnoDB…
  
  Répondre
lefred dit :

5 décembre 2012 à 22 h 34 min

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 😉

Répondre
Nono dit :

6 décembre 2012 à 9 h 04 min

Je vais jetter un coup d’oeil à ca 🙂

J’utilisais ma propre sauce pour le moment : http://blog.m0le.net/2012/02/17/le-petit-script-de-backup-de-nono/

Répondre
skc dit :

6 décembre 2012 à 9 h 31 min

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 ???

Répondre
1. David dit :
  
  6 décembre 2012 à 9 h 59 min
  
  Merci pour tes retours, je les prends en compte, je tests et je modifie le script !
  
  Répondre
zatmania dit :

6 décembre 2012 à 13 h 52 min

Bonjour,

comme le dit lefred, j’utilise xtrabackup en prod chez nous. Il permet de faire les sauvegardes à chaud. J’ai aussi posté un petit howto sur mon blog : http://gotofreedom.org/success-story/sauvegarde-des-bases-mysqlmariadb-avec-xtrabackup/ qui explique sa mise en oeuvre

zat

Répondre
David M. dit :

2 mai 2013 à 5 h 08 min
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 😉
Répondre
1. David M. dit :
  
  2 mai 2013 à 15 h 06 min
  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à ! 🙂
  Répondre
  1. David dit :
    
    14 mai 2013 à 7 h 27 min
    
    Bonjour David M.,
    
    Merci pour toutes tes suggestions, j’ai modifié l’article (et donc le script) en conséquence….
    
    David M. (aussi…)
    
    Répondre
David M. dit :

2 mai 2013 à 12 h 54 min
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à ! 🙂
Répondre
nono dit :

28 mars 2017 à 14 h 35 min

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

Répondre
Francescu dit :

26 février 2018 à 14 h 55 min

Merci pour ce script.

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

Répondre
1. David dit :
  
  27 février 2018 à 0 h 28 min
  
  Merci pour cette contribution !
  
  Répondre
tuxmika dit :

5 août 2020 à 15 h 06 min

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

Répondre
1. David dit :
  
  5 août 2020 à 15 h 09 min
  
  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
  
  Répondre
tuxmika dit :

5 août 2020 à 15 h 41 min

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

Répondre
1. David dit :
  
  5 août 2020 à 15 h 43 min
  
  Effectivement avec le set s’il y a erreur le script va se fermé
  L’email sera quand même envoyé car il est mis en TRAP
  
  Répondre

Laisser un commentaire Annuler la réponse

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

Préparation

Le script

Articles relatif

23 réflexions au sujet de « Script de sauvegarde Mysql par base « mysql_dump.sh » »

Laisser un commentaire Annuler la réponse