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 !

4 comments

  1. Super, j'avais jamais pensé à ça ! (pas eu non plus l'occasion de devoir y penser ^^)
    J'adhère à l'idée, voilà un bon moyen de tenir des backups intelligentes d'un BDD :)

  2. Bonjour,

    excellent post : bravo pour la riche idée de "un insert par ligne".
    par contre, 3 remarques :
    - pas la peine de comparer la dernière version sur SVN et la dump : SVN sait faire ça sans problème : écrasez votre fichier et faites le commit direct. Si le fichier est modifé, il serait commité, sinon rien ne se passera
    - inutile de paramétriser le chemin du binaire svn : il est dans le "path" de l'utilisateur qui lancera les commande.
    - la partie umask n'est pas nécessaire non plus
    - utiliser git est plus facile, en particulier l'installation du serveur qui est beaucoup plus facile (quelle poisse l'install d'un serveur svn) car inexistante : pas besoin d'admin, just git init git add git commit : l'autoroute vers le bonheur...

  3. Bonjour JP, et merci pour tes commentaires pleins d'intérêt !
    - Je sais qu'il est inutile de faire un diff avant de pousser le fichier dans le dépôt SVN. Néanmoins, c'est une étape que je fais systématiquement dans mes scripts, qu'il y ai ou non SVN en bout de chaîne. Car au final, mon fichier se retrouve toujours sur une machine distante, et tout ce que je peux éviter comme trafic réseau inutile est bienvenu.
    - Pour ce qui est du PATH de svn, ton affirmation est fausse, cela dépend grandement de la plateforme.
    - l'umask est inutile ici, c'est vrai. C'est un héritage d'un autre script.
    - git ? ouais bof :) Le seul intérêt de git c'est de pouvoir faire ses commits sans serveur. Moi j'utilise svn justement pour que mon commit ne soit pas sur la même machine que la base de donnée. Par ailleurs, installer un serveur svn n'est pas compliqué.

    Enfin, c'est cool de proposer une version git, les développeurs préfèrent git, et si ça peut leur donner l'idée de sauvegarder proprement leur travail, c'est déjà ça de gagné !

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.