2. Rapide et simple

Une méthode rapide et simple pour sauvegarder vos bases MySQL est d'arrêter le service MySQL (mysqld) et de copier les données et les fichiers associés. L'arrêt de mysqld peut se faire depuis la ligne de commande en lançant lescript approprié :

# sous Debian GNU/Linux
/etc/init.d/mysql stop

# sous d'autres systèmes Unix
safe_mysqld stop

Depuis la version 4 de MySQL, la commande safe_mysqld peut-être renommé en mysqld_safe.

Une fois que MySQL a été arrêté, cela devient une chose facile de copier les fichiers MySQL contenant les données et les fichiers liés à MySQL. Ces fichiers sont situés à divers endroits sur le serveur. Pour déterminer où les fichiers ont été mis, jetez un oeil au fichier /etc/mysql/my.cnf sous Debian (l'emplacement peut varier selon les configurations). Utiliser un pager comme less ou more pour visualiser la valeur de la variable datadir qui vous indiquera le répertoire où sont stockées les données. Sur un système Debian de base, le répertoire en question est /var/lib/mysql. Avec cette information, vous pouvez copier tous les fichiers de ce répertoire dans un répertoire de sauvegarde en tapant la commande suivante :

/etc/init.d/mysql stop

cd /tmp/backup

cp -r /var/lib/mysql .

/etc/init.d/mysql start

tar cfz "mysql-backup-$(date +%d-%m-%y).tar.gz mysql

rm -rf mysql

La première ligne arrête mysqld, la deuxième nous place dans le répertoire /tmp/backup. Ensuite, une copie récursive (l'option -r) du répertoire contenant tous les fichiers MySQL est faite dans le répertoire courant. On relance le serveur MySQL, on compresse et archive le répertoire tout juste copié avec la date du jour et on efface le répertoire mysql. Tout ceci peut-être également placé dans un script shell pour être lancé soit manuellement soit à l'aide de crontab par exemple. Il peut être égalment utile de sauvegarder vos fichiers de configuration placés dans /etc/mysql/.

Le répertoire que nous venons de sauvegarder contient différents répertoires et fichiers. Un répertoire est créé pour chaque base de donnée et trois fichiers pour chaque table dans le répertoire de la base de donnée en question. Les noms des trois fichiers ont la même base, à savoir le nom de la table, avec une extension différente pour chaque fichier : .frm pour les définitions de table, .MYD pour les données et .MYI pour l'indexation des fichiers.

La restauration d'une base de données qui a été sauvegardé comme décrit ci-dessus est une chose qui se résume à décompresser et désarchiver le fichier sauvegardé précédemment. Ensuite, la base restaurée doit être copié dans le répertoire des données MySQL. Si vous voulez restaurer qu'une table, il vous suffit de copier les fichiers de la table dans le répertoire de la base de données approprié :

cd /tmp/backup

tar zxvf mysql-backup-25-11-04.tar.gz

/etc/init.d/mysql stop

cp -f mysql/base1/table1* /var/lib/mysql/base1

/etc/init.d/mysql start

La méthode de sauvegarde détaillé ci-dessus, surtout si elle a été exécuté via un script shell, sera très brève. Cette façon de faire est bonne pour les bases de données petites qui ne nécessitent pas un arrêt de la base durant de longues minutes. Mais si votre base a une taille conséquente et qu'elle est utilisée énormément alors un arrêt même de quelques minutes n'est pas envisageable. Dans ce cas une autre méthode doit être employée.

3. Utilisation de mysqldump

Si vous ne pouvez pas vous permettre d'arrêter MySQL pour faire une sauvegarde et que vos bases ne sont pas très grosses, une bonne alternative serait d'utiliser mysqldump. Cet outil, inclus dans MySQL, lis chaque base de donnée et table afin de construire un fichier texte contenant les données. Ce fichier est composé de déclarations SQL. En parcourant ce fichier, vous remarquerez de nombreuses déclarations "CREATE TABLE" ainsi qu'une multitude de "INSERT", une pour chaque ligne de donnée. Pour exporter toutes les bases de données de MySQL en utilisant mysqldump, il vous suffit de faire :

mysqldump -u root -p -x -e -A > /tmp/backup/bases.sql

Les options -u root -p indiquent à l'utilitaire qu'il faut executer la commande en tant qu'utilisateur root et qu'un mot de passe doit être demandé. Si l'extraction doit être faite via la crontab en incluant la commande dans un shell script, cette option peut-être changé en -pmypwd où mypwd est le mot de passe de root (notez qu'il n'y pas d'espace entre le -p et le mot de passe). L'option -x permet de verouiller toutes les tables avant de faire la sauvegarde. Le déverouillage des tables n'aura lieu qu'à la fin de la sauvegarde. Pour rassembler toutes les déclarations "INSERT" pour chaque table, ajouter l'option -e. Ceci réduira la taille du fichier généré et permettra une exécution plus rapide des restaurations futures. L'option -A spécifie que toutes les bases doivent être exportés. Enfin, la sortie standard est redirigée à l'aide du signe "supérieur à " vers le fichier /tmp/backup/bases.sql. En sauvegardant toutes les bases en une seule fois, l'extraction se présentera sous la forme d'un gros fichier. Cependant, il serait plus commode de faire des sauvegardes par base de données et ainsi sauvegarder certaines bases lorsque le système est moins actif. Pour exporter uniquement une base, faites comme ceci :

mysqldump -u root -p -x -e -B db1 > /tmp/backup/db1.sql

La différence significative est que l'option -A a été remplacé par -B et le nom de la base de donnée à exporter. Pour en exporter plusieurs, il suffit de les ajouter à la suite de l'option -B en les séparant par un espace.

Pour des bases encore plus importantes, une sauvegarde de MySQL peut-être fait sur les tables. Vous pourriez sauvegarder une base toutes les semaines et les tables tous les jours. Pour sauvegarder juste une table, veuillez lancer la commande suivante :

mysqldump -u root -p -x -e db1 table1 > /tmp/backup/db1_table1.sql

Astuce

Notez que l'option -B a été supprimé et non remplacé. Cet outil est suffisamment malin pour reconnaitre le fait que le premier nom indiqué est une base et que le second est le nom d'une table et non le nom d'une autre base de donnée. Pour sauvegarder plusieurs tables d'une base, ajouter juste les tables après le nom de la base de donnée en question séparé par des espaces (par exemple, base1 table1 table2).

La restauration d'un fichier généré à partir de mysqldump est faite à partir du client mysql qui va exécuter toutes les requêtes SQL du fichier. Cette restauration peut-être faite ainsi :

mysql -u root -pmypwd < /tmp/backup/base1_table1.sql

Encore une fois, l'utilisation de mysqldump n'est pas approprié ici car il ne fait que l'exportation de la base et non la restauration. A la place, on utilise le client mysql qui va lire le contenu du fichier sql afin d'exécuter les requêtes qu'il contient. Notez que je n'ai pas spécifié de base, cela sera fait lors de la restauration. Bien sûr, l'arrêt de MySQL devrait être fait avant de lancer une restauration.