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.

5 comments

  1. oui pas un modéle de fiabilité, il manque des tests sur les répertoires et des sorties :)

    tsss !

  2. C'est peut-être hors sujet, mais l'utilisation des snapshots LVM peut être également une idée séduisante.
    Maintenant, je ne sais pas si c'est utilisable sur OS X.

  3. Mac OS X ne connait pas LVM, donc c'est mort de ce côté là. Comme je l'ai écris ici (http://www.patpro.net/blog/index.php/2010/01/04/1288-le-fonctionnement-des-snapshots/ ) Mac OS X ne dispose actuellement d'aucun FS supportant les snapshots, puisque ZFS nous est passé sous le nez.

    Par ailleurs, les snapshots sont sévèrement limités. Ils n'existent que sur le volume qui est "snapshoté". Si tu veux remonter ton serveur de DB suite à la mort du disque dur, tu ne peux pas repartir des snapshots, car ils sont morts en même temps que le disque.

    Si tu cherches une méthode avancée et intelligente de sauvegarde pour MySQL, regarde plutôt par là : http://www.patpro.net/blog/index.php/2010/01/11/1347-sauvegarde-de-bases-mysql-via-svn/

  4. ah merci pour la méthode qui utilise le versioning, c'est effectivement pas bête.
    Je vais peut-être la creuser ;
    Cependant, le hic, comme tu l'as précisé : c'est plutôt indiqué pour les petites bases de données.
    Dans mon cas, il s'agit de "grosses" bases (parfois plus de 15 Go).
    Et les écritures par minutes peuvent être supérieures à 1000.
    Je me demande si ta méthode ne va pas prendre trop de temps.
    A tester :)

  5. Bon, 15Go c'est énorme, effectivement, mais ce qui compte le plus, c'est le pourcentage de lignes qui changent d'une version à l'autre.
    En versionnant, tu sauvegardes une seule fois 15 Go. Si ton delta entre deux versions est de 0,1% alors tu es gagnant, car tu peux sauvegarder ta base avec l'équivalent d'une compression x1000. Le nombre d'écritures n'est pas représentatif du nombre de différences entre deux versions de la base. Et si en plus tu stockes ton repository sur un filesystem compressé comme ZFS, tu ajoutes l'équivalent d'un coup de Gzip dessus :)
    N'oublie pas le fait que tu peux aussi laisser de côté les tables qui ne sont pas intéressantes (par exemple les logs de certaines appli, dont la valeur est nulle, mais qui prennent une place folle).

Laisser un commentaire

Votre adresse de messagerie 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.