Script de sauvegarde MySQL

Je sais que beaucoup de personnes on leur petit script maison de sauvegarde MySQL mais personnellement, il m'est déjà arrivé de devoir en mettre un en place très rapidement et à chaque fois, il faut se replonger dans la documentation MySQL pour les options. :-(

Ce script est très simple mais il permet malgré tout d'effectuer la sauvegarde de chaque schéma d'une instance MySQL dans un fichier horodaté séparé et compressé avec bzip2 (c'est avec cet algorithme que j'obtiens le meilleur taux de compression sur mes sauvegardes MySQL) . Il ne reste plus qu'à le paramétrer sous crontab (toutes les nuits par exemple) et c'est parti !

#!/bin/bash
# Script de sauvegarde MySQL v5.0
MYSQL_LOGIN=backup_login
MYSQL_PASSWORD=backup_password
BACKUP_DUMP_ROOT=/backup/mysql
echo "# Sauvegarde des Bases MySql"
echo "# DUMP=${BACKUP_DUMP_ROOT}"
timestamp="$(date +%Y%m%d-%H%M%S)"
prefix=`hostname`
# Sauvegarde de chaque Base dans un fichier
for database in `echo "show databases" | mysql -u${MYSQL_LOGIN} -p${MYSQL_PASSWORD} -N`
do
BACKUP_FILE="${BACKUP_DUMP_ROOT}/${prefix}-mysql-backup-${timestamp}-${database}.sql"
echo "#$(date +%Y%m%d-%H%M%S)# DEBUT Sauvegarde de [${database}] in [${BACKUP_FILE}]"
mysqldump --quick --add-locks --extended-insert --lock-tables --allow-keywords --default-character-set=latin1 -c -u${MYSQL_LOGIN} -p${MYSQL_PASSWORD} ${database} | bzip2 -9 > ${BACKUP_FILE}.bz
retour=$?
if [ "$retour" -eq "0" ]
then
echo "#$(date +%Y%m%d-%H%M%S)# La sauvegarde s'est correctement deroulee"
else
echo "#$(date +%Y%m%d-%H%M%S)# La sauvegarde s'est terminee en ERREUR"
fi
echo "#$(date +%Y%m%d-%H%M%S)# FIN Sauvegarde de [${database}] in [${BACKUP_FILE}]"
done
exit $retour

Voici la liste des options utilisées dans le script :

• -q | --quick

ne garde pas en buffer les requêtes mais écrit immediatement sur la sortie

• --add-locks

ajoute dans le fichier de sauvegarde une instruction LOCK TABLES avant et UNLOCK TABLES après pour accélérer les insertions dans MySQL

• -e | --extended-insert

utilise la nouvelle syntaxe INSERT multi-ligne (plus courtes et plus efficaces)

• --lock-tables

Verrouille toutes les tables avant de commencer l'export. Les tables sont verrouillées avec READ LOCAL pour permettre des insertions concurrentes sur les tables MyISAM

• --allow-keywords

Permet la création de colonnes ayant des noms de mots réservés. Cela fonctionne en préfixant chaque nom de colonne avec le nom de la table.

• --default-character-set=latin1

le charset par defaut (si ce paramètre n'est pas spécifié alors utf8 est utilisé)

• -c | --complete-insert

insertion complete avec les noms de colonnes

Pour plus d'options de paramétrage de mysqldump, n'hésitez pas à aller consulter la documentation MySQL en ligne et à partager vos trouvailles ou optimisations.