Tutoriel : sauvegarder quotidiennement ses bases de données MySQL sur un serveur dédié ou privé

Vous souhaitez sauvegarder systématiquement les bases de données de votre serveur Web et les récupérer sur une machine Windows ?  Je vous propose une solution simple, fiable et rapide pour récupérer un fichier SQL gzippé de chaque base, comportant la date du jour, sous la forme db.NomDeLaBase.2009-11-17.sql.gz.

J’ai longtemps galéré pour sauvegarder systématiquement les bases de données de mon serveur Web Linux (Debian) et les récupérer sur ma machine Windows. Aucune solution clé en main ne me permettait d’obtenir ce que je souhaitais : un fichier SQL gzippé de chaque base, comportant la date du jour, sous la forme db.NomDeLaBase.2009-11-17.sql.gz. Cette granularité de sauvegarde est pratique : elle permet de retrouver dans un dossier toutes les versions successives d’une même base, pour la réinjecter en cas de problème sur le serveur. Pour ceux qui recherchent une solution simple, fiable et rapide, voici en 5 étapes comment procéder :

  • identifier les bases à sauvegarder et créer un fichier gzippé sur le serveur distant
  • créer une tâche Cron qui exécute cette sauvegarde à intervalle régulier
  • rapatrier via FTP les fichiers zippés dans Windows
  • créer une tâche planifiée qui rapatrie les fichiers à intervalle régulier
  • vérifier régulièrement que les fichiers sont bien sauvés !

Pré-requis

Ce tutoriel nécessite :

  • un serveur Web Linux sur lequel vous disposez d’un accès SSH et/ou la possibilité de créer des scripts Cron (via Webmin par ex.) et d’exécuter mysqldump (une version restreinte est envisagable sur un serveur mutualisé)
  • un accès root à la base de données

Le script de sélection des bases et de sauvegarde

Commençons par nous connecter en SSH afin de créer le script Shell qui s’exécutera chaque jour sur le serveur. On le créera dans un dossier et avec un nom de fichier de notre choix (dans mon cas /home/dbbackup.sh) avec un éditeur de texte, par ex. Nano :

On ouvre le fichier en écriture :
nano /home/dbbackup.sh

Contenu du fichier
#!/bin/sh

# Hôte (laisser localhost car mysqldump ne fonctionnera pas sur un serveur distant)
HOSTNAME='localhost'

# Login root
USERNAME='root'

# Mot de passe root SQL (pas le root Linux !)
PASSWORD='1234abcdefgh7890'

# Chemin de sauvegarde
BackupFolder='/home/web/mondomaine.net/ftp/dbbackup/'

# Préfixe du nom du fichier de sauvegarde (avant le nom de la base)
PREFIXE=db.

# Suffixe du nom du fichier de sauvegarde (après le nom de la base)
SUFFIXE=.`date +%Y-%m-%d`.sql.gz

# Requête MySQL qui renvoie la liste des bases disponibles
for i in $(echo 'SHOW DATABASES;' | mysql --user $USERNAME -p$PASSWORD -h $HOSTNAME | grep -v '^Database$' ); do
# Exportation de chaque base
mysqldump --user $USERNAME -p$PASSWORD -h $HOSTNAME --opt $i | gzip -9c >$BackupFolder/$PREFIXE$i$SUFFIXE;
done;

// On sort CTRL+X : on enregistre les modifications (O pour oui) puis ENTREE

Dans notre scénario (réel), je souhaite assurer une sauvegarde intégrale de mes bases MySQL, sans devoir intervenir à chaque création de base de données (au risque d’en oublier). Je veux tout sauver, y compris les bases de privilèges.

Pour ce faire, j’utilise mon accès root MySQL pour me connecter à la base locale, afin d’exécuter une requête SQL qui renvoie l’ensemble des bases de données présente. Je dois donc renseigner mes codes de connexion MySQL dans le script.

J’indique ensuite le chemin local du dossier où je stockerai temporairement les fichiers de sauvegarde avant le rapatriement. Ce dossier devra être accessible via FTP (idéalement en créant un accès FTP sur mesure).

La sauvegarde en tant que telle sera exécutée grâce à mysqldump, utilitaire livré avec mysql dans ses versions récentes. Parce qu’il est intégré au système de fichiers en natif, il est extraordinairement plus rapide et économe en ressources que n’importe quel script de sauvegarde SQL.

N’oubliez pas de rendre ce fichier exécutable :

chmod +x /home/dbbackup.sh

Créer le dossier de sauvegarde

Le dossier de sauvegarde sera idéalement situé dans un dossier non accessible via le Web, mais uniquement en FTP. Si le dossier est accessible via un navigateur Web (ce qui est mon cas), il y a un risque de sécurité ENORME à laisser le contenu des bases de données en accès libre. Il faut donc placer un .htaccess interdisant tout accès à ce dossier :

// Contenu du fichier .htaccess
deny from all

Tester la sauvegarde

Votre script est prêt à tester : appelez-le par ./dbbackup.sh si vous êtes dans le répertoire /home (ou son chemin complet /home/dbbackup.sh). Il vous suffit ensuite de vérifier que le répertoire de sauvegarde contient bien les fichiers sql.gz datés du jour.

Créer la tâche Cron

Une fois le script validé, il ne vous reste qu’à lancer son exécution à une heure appropriée de la journée. Dans mon cas, j’exécute le backup à 12h30 chaque jour. Je sais, ce n’est pas courant de faire ça en pleine journée, mais j’ai une bonne raison : puisque la tâche planifiée de rapatriement des fichiers s’exécute sur un ordinateur portable XP, celui-ci doit être allumé au moment où les fichiers sont sauvegardés. La pause déjeuner paraît donc le moment idéal pour effectuer cette sauvegarde.

// On appelle le fichier de crontab
crontab -e

// On ajoute la ligne suivante :
30 12 * * * /home/web/dbbackup.sh > /home/web/dbbackup.log #Sauvegarde des bases de données

// On enregistre CTRL+O, on quitte CTRL+X

// On recharge Cron (sur Debian)
/etc/init.d/cron reload

Rapatrier les données sur Windows

Une fois les fichiers gz créés, encore faut-il les récupérer car ils ne sont pas à l’abri d’un plantage du serveur. Si vous disposez d’un autre serveur Linux, il est possible de créer une nouvelle tâche Cron sur ce serveur pour récupérer le contenu du dossier ainsi créé, mais ce n’est pas mon cas. Je dois donc me débrouiller avec un poste Windows XP. Je vais donc :

  • créer un répertoire pour accueillir mes sauvegardes ;
  • écrire un fichier .bat qui lance une série d’actions FTP ;
  • écrire le fichier de commandes FTP en question ;
  • créer la tâche planifiée qui déclenchera le rapatriement chaque jour.

Créer le répertoire

Selon votre degré de paranoïa, vous pourrez planquer votre dossier plus ou moins loin dans l’arborescence de fichiers, le cacher ou préciser les droits en lecture/écriture de manière draconienne (sur XP Pro ou Vista). Je me suis contenté de créer un dossier à la racine qui s’appelle c:\[A archiver].

Le fichier dbbackup.bat

J’ai créé un nouveau fichier texte dans c:\, que j’ai renommé dbbackup.bat. Pour l’éditer, il ne faut pas le double-cliquer mais faire un clic droit > modifier. Ce fichier ne contient qu’une ligne qui appelle le client FTP en ligne de commande et lui indique d’utiliser un fichier de commandes texte :

ftp -s:c:\dbbackupftp.txt -i -n

Le fichier de commandes FTP

Ce fichier situé dans c:\ s’appelle dbbackupftp.txt. Il s’agit d’un simple fichier texte qui contient les informations de connexion au FTP de votre serveur. A vous de remplacer les mots entre slashes par vos propres codes :

open <<HOTE>>
user <<LOGIN>> <<MDP>>
binary
hash
lcd "<<DOSSIER_LOCAL>>"
cd <<DOSSIER_DISTANT>>
mget *.sql.gz
mdelete *.sql.gz
close
quit

Explications :

  • HOTE : nom d’hôte ou adresse IP du serveur distant (ex. vds1999.sivit.org)
  • LOGIN : username du FTP
  • MDP : mot de passe du FTP
  • DOSSIER_LOCAL : chemin d’accès du dossier local (ex. c:\[A archiver]). Les guillemets sont importantes s’il y a des espaces dans le chemin
  • DOSSIER_DISTANT : dossier où sont stockés les fichiers sql.gz, relatif à la racine du FTP (ex. dbbackup)
  • Vous noterez que la commande mdelete est appelée après mget pour effacer les fichiers du serveur distant après récupération. Si ce n’était pas le cas, tout l’historique resterait sur le serveur distant et nous devrions chaque jour retélécharger l’intégralité des fichiers.

Testez votre script après avoir ouvert une ligne de commande (Démarrer > Exécuter > Tapez Cmd > ENTREE > Tapez c:\dbbackup.bat > ENTREE), pour éviter que la fenêtre se ferme après le script. Vous pourrez ainsi vérifier que la connexion est correcte et, si vous aviez des fichiers sql.gz dans le dossier distant, assister au premier téléchargement de fichiers distants – et donc à leur disparition du dossier distant ! Champag… enfin presque.

Une tâche planifiée côté Windows

Afin de récupérer les fichiers créés chaque midi, on créera une tâche planifiée dans Windows. Allez dans le Menu démarrer > Programmes > Accessoires > Outils systèmes > Tâches planifiées > Création d’une tâche planifiée > Suivant > Cliquez sur parcourir puis trouvez c:\dbbackup.bat > Choisissez la périodicité « tous les jours » > Suivant > Heure de début : 13h30 (on laisse une heure au serveur pour finir sa copie, ce qui devrait être large) > Suivant > Entrez vos noms d’utilisateur (ou ceux d’un administrateur si l’accès au dossier a été restreint) > Suivant > Terminer

Champagne !



C’est terminé ! Tous les jours à 13h30, vous récupérerez une sauvegarde de l’ensemble des bases de données sur votre serveur. Vérifiez régulièrement que la sauvegarde s’est bien exécutée en regardant dans votre dossier c:\[A archiver] que les fichiers les plus récents sont bien là. C’est la première chose qu’on fait le matin en arrivant au boulot (avant de lancer la machine à café, même).

Et si vous n’avez qu’un serveur mutualisé ?

Sans accès SSH, pas de Cron, pas de mysqldump. Mais tout n’est pas perdu : avec le script BackupDB installé sur votre serveur, vous pouvez lancer la sauvegarde d’un fichier de base de données simplement en appelant son URL depuis un logiciel comme cUrl sur votre machine Windows. Dans ce batch on devra :

  • lister à la main les bases de données à sauvegarder
  • boucler sur ces base pour exécuter la requête curl

Exemple de script c:\lancerDbbackup.bat :

@ECHO off
SETLOCAL
set base1=lepotlatch
set base2=lecapharnaum
set base3=teratoorg

::Passer le nombre de bases en troisième argument après IN
FOR /L %%n IN (1,1,3) DO (call :loop %%n)
goto :s_next_bit

:loop
set _base_name=base%1

CALL SET _base_name=%%%_base_name%%%
echo base %_base_name%
::Remplacer l'url monsite.org par celle de votre script backupdb.php
CALL "C:\Program Files\curl\curl.exe" "http://www.monsite.org/backupdb.php?StartBackup=standard&nohtml=1&onlyDB=%_base_name%"
echo OK Pour cette fois

goto :eof

:s_next_bit
:: continue below

J’espère que ma démonstration est explicite sans être trop lourde. Je suis preneur de toute suggestion me permettant d’améliorer mon système.

Outil de référencement professionnel - essai gratuit Ce contenu a été publié dans Développement PHP, avec comme mot(s)-clé(s) , , , , , , , . Vous pouvez le mettre en favoris avec ce permalien.

2 réponses à Tutoriel : sauvegarder quotidiennement ses bases de données MySQL sur un serveur dédié ou privé

  1. fred Wolf dit :

    Merci pour le script, qui fonctionne parfaitement pour moi

    Fred

  2. Matthieu dit :

    Sympa l’article, tout automatiser , c’est quand même mieux …

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *