Sauvegarder des bases MySQL

Dans de précédents articles j’ai répondu brièvement aux questions de l’installation de MySQL 5 sur Mac OS X 10.5 et de la création d’un plist de démarrage launchd pour MySQL. J’ai aussi donné quelques ficelles pour trouver les points de blocage habituels au fonctionnement de MySQL.
Il me reste donc à aborder le problème des sauvegardes. Cet article s’adresse uniquement aux utilisateurs d’un serveur MySQL qui sont root/admin de leur serveur, si vous avez un serveur MySQL chez un hébergeur, alors vous pouvez passer votre chemin.

Le script que je propose ci-dessous doit être lancé en root, une fois par jour. Il fait une sauvegarde de chaque base de données qu’il trouve, et il conserve cette sauvegarde pendant 7 jours. En cas de pépin, il vous est donc possible de restaurer une ou plusieurs bases de données, en remontant de maximum 7 jours dans le passé.
Vous devez adapter absolument les lignes 2 à 5 pour indiquer les chemins suivants :

• mysqlroot : chemin du dossier contenant les bases de données sur le serveur
• monbkp : chemin du dossier contenant les sauvegardes des bases sur le serveur
• hotcopy : chemin de l’exécutable mysqlhotcopy sur le serveur
• mydump : chemin de l’exécutable mysqldump sur le serveur

Vous devez aussi remplacer LE_PASS_MYSQL par le mot de passe de root de MySQL. Attention, il n’y a pas d’espace entre -p et LE_PASS_MYSQL pour la commande mysqldump, mais il y a bien un espace entre le -p et LE_PASS_MYSQL pour la commande mysqlhotcopy.

Le script supporte un argument (”dump”). Si il est lancé avec cet argument, alors il utilisera mysqldump pour faire la sauvegarde. Vous obtenez alors un dump de chaque base, c’est à dire un fichier texte “plat” contenant les instructions SQL nécessaires à la reconstruction de la base de données. C’est la méthode que je recommande car le fichier obtenu peut être injecté dans presque n’importe quel serveur MySQL. Par ailleurs, le fichier obtenu est plus petit et plus facile à compresser.
Si le script est lancé sans l’argument “dump”, alors la méthode de sauvegarde utilisée est mysqlhotcopy. Ce programme duplique physiquement le répertoire de chaque base de données. L’avantage c’est que la sauvegarde est prête à l’emploi, il n’est pas nécessaire de la ré-injecter dans le serveur. L’inconvénient, c’est qu’il vous sera probablement impossible d’utiliser cette sauvegarde sur un autre serveur que le votre, et dans la même version de MySQL. Si vous souhaitez archiver vos sauvegardes de bases de données et pouvoir les restaurer quelques mois ou années plus tard, il ne faut pas utiliser mysqlhotcopy.

Déroulement du script :

1. création d’un dossier temporaire /tmp/mysql
2. pour chaque base de données, création d’un dump ou d’une “hotcopy”
3. archivage et compression de chaque dump/hotcopy (en .tgz)
4. suppression de la version non-compressée
5. déplacement de la version compressée vers le dossier de sauvegarde
6. suppression du dossier temporaire /tmp/mysql

#!/bin/sh
mysqlroot=/var/db/mysql
monbkp=/backup/MYSQL
hotcopy=/usr/local/bin/mysqlhotcopy
mydump=/usr/local/bin/mysqldump

madate=`date "+%Y-%m-%d"`
monjour=`date "+%w"`

echo "lancement du backup des bases de donnees MySQL..."
echo

mkdir -m 0777 /tmp/mysql

cd $mysqlroot
for directory in *
do
if [ $directory != "" ]; then
if [ -d "$mysqlroot/$directory" ]; then
  echo -n "backup de $directory : "
  case $* in
    dump)
    $mydump -u root -pLE_PASS_MYSQL --opt $directory > "/tmp/mysql/$directory"
    ;;
    *)
    $hotcopy -u root -p LE_PASS_MYSQL -q "$directory" /tmp/mysql
    ;;
  esac
  if [ $? = 0 ]; then
    tar -czf "$monbkp/$madate$directory.tgz" -C /tmp/mysql/ "$directory"
    if [ $? = 0 ]; then
      rm -f "$monbkp/$directory.${monjour}.tgz"
      rm -r "/tmp/mysql/$directory"
      mv "$monbkp/$madate$directory.tgz" "$monbkp/$directory.${monjour}.tgz"
      echo "ok"
    else
      echo "Erreur targz".
    fi
  else
    echo "Erreur export".
  fi
fi
fi
done

rm -fR /tmp/mysql/

Pour lancer ce script toutes les nuits, j’utilise une crontab (car mon mysqld est installé sur un serveur FreeBSD). Le script est enregistré dans /usr/local/bin/ sous le nom BKP_SQL.sh. Voilà la ligne en question :

30 5 * * * /usr/local/bin/BKP_SQL.sh

Et si je souhaite faire des dumps plutôt que des “hotcopy” :

30 5 * * * /usr/local/bin/BKP_SQL.sh dump

 
Note : ce script n’est pas du tout une référence de fiabilité et encore moins d’élégance, néanmoins, je l’ai créé en 2003 et depuis il tourne tous les jours. Je n’ai jamais eu de problème avec.

Note 2 : selon le système, votre environnement, … il vous faudra peut être indiquer le chemin complet pour l’application tar.