Sauvegarde de bases MySQL via SVN

Il existe de nombreuses possibilités pour sauvegarder et archiver des bases de données, MySQL ou autres. En général, le protocole de sauvegarde dépend largement de l'objectif que l'on s'impose et des moyens dont on dispose.
La rétention des données sur le long terme pose bien sûr des problèmes de format et de support : vais-je pouvoir relire mes sauvegardes dans dix ans ? Elle pose aussi des problèmes de volume : puis-je me permettre d'archiver l'intégralité de mes bases une fois par jour pendant des années ?
Personne n'a de réponses absolues à toutes ces questions, car finalement tout est affaire de compromis. Dans la plupart des cas, j'utilise des scripts qui font un dump de mes bases de données, et qui archivent le résultat avec une rétention, en général, d'une semaine.
Le dump a cela de fantastique que c'est un format texte, il est donc lisible et modifiable par l'homme. Pas besoin de retrouver une version de MySQL compatible pour récupérer le contenu des bases archivées. Néanmoins il peut être assez volumineux suivant les options choisies, et le stockage à long terme peut vite devenir problématique. Dans le cadre de mon travail par exemple, le volume d'un dump pour un jour donné atteint 2,2 Go. Par contre, dans la plupart des bases de données, assez peu de données sont modifiées d'un jour sur l'autre. On pourrait économiser un maximum de place en n'enregistrant que la différence avec la veille. C'est là qu'intervient Subversion (SVN). Cet outil de versioning permet de ne stocker que la différence entre la version originale d'un fichier enregistrée initialement, et les versions ultérieures. Subversion est fourni de base avec Mac OS X, et il est disponible sur de très nombreux systèmes.

Pour certaines bases de données j'utilise normalement deux scripts. Le premier script crée le dump d'une base, et compare ce dump avec le précédent. Si ils sont différents, le nouveau dump est placé dans un répertoire sous le contrôle de SVN.
Le second script fait un commit du fichier, et plein d'autres choses que je ne détaillerai pas ici.
Pour faire plus simple, je présente ici un script unique qui s'acquitte de ces deux missions : dump d'une base de données, et injection dans SVN si nécessaire.

#!/bin/bash

################################################################################
# gestion du dump de la base de données
#
# nom de la DB
MA_DB=mabase
# dossier de travail :
MAISON=/var/root/svn
# nom et chemin du dépôt svn
NOM="backup_db"
REPOS="/var/subversion/${NOM}/trunk"
# répertoire de stockage du dump dans
# la copie de travail svn
MA_DB_DOCROOT=${MAISON}/${NOM}/${MA_DB}/
# nom du dump
DUMP_SQL=dump_${MA_DB}.sql
# login/mot de passe pour faire le dump
SQL_PASSWD=mon_pass
SQL_LOGIN=mon_log
# action : dump de la base MA_DB
cd /tmp/ || exit 1
/usr/bin/mysqldump -h localhost \
	--skip-dump-date \
	--skip-opt \
	--add-drop-table \
	--add-locks \
	--create-options \
	--disable-keys \
	--set-charset \
	-u ${SQL_LOGIN} -p${SQL_PASSWD} \
	--ignore-table=${MA_DB}.event_log ${MA_DB} > ${DUMP_SQL}

################################################################################
# gestion de l'enregistrement dans SVN
#
# chemin du binaire :
SVN=/usr/bin/svn

# umask de travail pour les documents sensibles :
umask='u=rwx,g=,o='

# si le dossier de destination n'existe pas on le crée :
[ ! -d $MAISON ] && umask $umask && mkdir $MAISON

# un peu d'environnement au cas où :
[ ! $HOME ] && HOME=/var/root

# on y va :
cd "$MAISON"
# si la copie de travail n'existe pas, on la crée.
# c'est "one shot".
if [ ! -d "${MAISON}/${NOM}" ]; then
	umask $umask
	mkdir "${MAISON}/${NOM}"
	echo "Checkout initial de ${NOM} !"
	$SVN co "file://${REPOS}" "${MAISON}/${NOM}"
fi
# on se place dans la copie de travail svn
cd "${NOM}"
umask $umask
# on l'update au cas où le dépôt svn aurait
# été mis à jour par un autre moyen
echo "Update de ${NOM}"
$SVN update
# copie conditionnelle du dump dans la copie de travail SVN
cd /tmp/
diff -q ${DUMP_SQL} ${MA_DB_DOCROOT}${DUMP_SQL} || \
	cp -f ${DUMP_SQL} ${MA_DB_DOCROOT}
# commit éventuel du dump de ${MA_DB}
$SVN commit -m "dump de ${MA_DB} du `date`" "${MA_DB_DOCROOT}${DUMP_SQL}"

Dans le détail, voici le déroulement du script :

1. Définition de variables.
2. Dump de la base de données $MA_DB dans le fichier /tmp/$DUMP_SQL.
3. Si le répertoire $MAISON n'existe pas, on le crée. Par défaut, c'est /var/root/svn, ce qui lui assure une certaine intimité mais oblige à lancer le script en root.
4. On se place dans $MAISON, et on crée le répertoire $NOM si il n'existe pas. Par défaut, c'est backup_db.
5. Si $NOM n'existait pas, c'est que le premier checkout du serveur SVN n'avait pas été fait, donc on le fait.
6. On se déplace dans $NOM, et on fait un update de la copie de travail.
7. Ensuite, si le dump /tmp/$DUMP_SQL est différent de celui qui est stocké dans la copie de travail ${MA_DB_DOCROOT}${DUMP_SQL} on replace ce dernier par le nouveau dump.
8. Pour finir on fait un commit du fichier de dump (si il n'a pas changé, le commit ne fait rien).

La partie du script qui effectue le dump de la base de données est importante, car le confort d'utilisation, et la taille du dépôt SVN en dépendent. Il convient notamment de faire bien attention à deux choses : chaque enregistrement dans la base doit avoir son propre INSERT dans le dump, et le fichier final ne doit pas contenir la date du dump.
Les INSERT groupés sont à éviter car ils forment des lignes excessivement longues, et que pour un seul caractère modifié dans une de ces lignes, SVN conserverait la ligne complète. En ayant un seul enregistrement par ligne on s'assure que les différences entre deux versions sont aussi petites et lisibles que possible, ce qui permet aussi de ralentir la prise de poids du dépôt SVN.
Si le fichier de dump contient sa propre date de création, on est alors sûr qu'il existera une différence entre le dump du jour et celui de la veille, même si rien ne change dans les données elles-même. Donc on aura un nouveau commit à chaque dump, sans savoir si il est vraiment significatif.
Un troisième point facultatif mais intéressant est la possibilité d'ignorer les tables qui changent beaucoup et qui ne sont pas significatives vis-à-vis de votre application. Dans le script ci-dessus, j'ai décidé d'ignorer la table nommée "event_log" : --ignore-table=${MA_DB}.event_log. Pour moi elle n'est pas du tout intéressante, et ferait gonfler artificiellement mon dépôt SVN tout en le remplissant de révisions non significatives.
À noter que ce script ne prend pas en charge la création du dépôt SVN, qui reste une tâche à la charge de l'administrateur. Il faut que le dépôt soit accessible localement (file://) par l'utilisateur qui lance le script de backup (ici root). D'autres combinaisons sont possibles, mais nécessitent des modifications assez importantes du script.

Une fois que tout cela fonctionne (via un automatisme comme cron, launchd, ...) il est très simple de restaurer une version arbitraire de la base de données. Il est aussi très facile de voir en une ligne de commande l'ensemble des différences entre deux versions du dump :

$ svn diff -rPREV dump_glpi.sql
Index: dump_glpi.sql
===================================================================
--- dump_glpi.sql	(revision 1774)
+++ dump_glpi.sql	(working copy)
@@ -9135,7 +9135,7 @@
 LOCK TABLES `glpi_users` WRITE;
 /*!40000 ALTER TABLE `glpi_users` DISABLE KEYS */;
 INSERT INTO `glpi_users` VALUES (2,'glpi0',...,'2009-07-15 10:44:57',...);
-INSERT INTO `glpi_users` VALUES (6,'user1',...,'2009-11-16 15:41:46'...,);
+INSERT INTO `glpi_users` VALUES (6,'user1',...,'2009-12-08 08:06:09'...,);
 INSERT INTO `glpi_users` VALUES (7,'user2',...,'2009-12-02 17:25:44'...,);
 INSERT INTO `glpi_users` VALUES (8,'user3',...,'0000-00-00 00:00:00'...,);
 INSERT INTO `glpi_users` VALUES (9,'user4',...,'0000-00-00 00:00:00'...,);

On a aussi l'assurance que les backups prennent bien moins de place que si on avait du tous les conserver intégralement.
Ainsi, j'ai une petite base de données (635 Ko), archivée dans subversion depuis juillet 2007 sous la forme de 443 révisions. Le poids total des révisions sur le serveur SVN est de 7,2 Mo, alors que le poids total d'une sauvegarde par jour pendant 900 jours représenterait environ 540 Mo. Même une excellente compression des dumps ne peut pas approcher le gain de place atteint par le stockage des modifications dans subversion.
Sur le long terme, et si votre base de données varie peu relativement à sa taille, la solution de l'archivage sous forme de révisions dans subversion est peut être la meilleure option. En tout cas, elle mérite qu'on s'y intéresse !