Comment sauvegarder et restaurer une base de données avec Mysqldump ?

I. Présentation

Dans ce tutoriel, nous allons voir comment sauvegarder et restaurer une base de données MySQL (ou plusieurs bases de données MySQL) à partir de l’utilitaire mysqldump. Il s’agit d’un utilitaire livré avec MySQL / MariaDB et qui s’utilise en ligne de commande.

Cet utilitaire est intéressant pour réaliser des sauvegardes d’une ou plusieurs bases de données d’une instance MySQL (ou MariaDB), mais également pour transférer une base de données d’un serveur MySQL à un autre (lors d’une migration, par exemple).

Pour cette démonstration, j’utilise MariaDB 10.5 sur un serveur Debian 11, mais cela fonctionne aussi avec les autres versions et d’autres distributions. Sachez également que mysqldump fonctionne sur Windows.

II. Sauvegarder une base de données avec mysqldump

Comme je le disais en introduction, mysqldump est inclus nativement avec MySQL et MariaDB, donc pour l’utiliser il suffit de l’appeler dans la console. À la suite du nom, il faudra préciser les options que l’on souhaite utiliser, et là, il y a du choix. Toutes les options sont listées dans la documentation officielle (lien).

mysqldump

Voyons comment utiliser mysqldump au travers différents exemples….

A. Sauvegarder une BDD spécifique avec mysqldump

Partons du principe que l’on souhaite sauvegarder une base de données spécifique, identifiée par son nom, à l’aide de mysqldump. Dans cet exemple, je vais sauvegarder la base de données nommée « wordpress » de mon serveur MySQL, au sein d’un fichier SQL qui sera stocké dans « /home/flo », ce qui donne :

mysqldump -u root -p wordpress > /home/flo/wordpress.sql

Il faudra indiquer le mot de passe « root » afin de pouvoir réaliser la sauvegarde. On peut voir que l’on précise le fichier de sortie grâce au caractère « > » suivi du chemin vers le fichier. Dans le cas où l’authentification réussie, la sauvegarde sera effectuée. Si vous souhaitez que le nom soit daté avec la date du jour et que cela soit dynamique, c’est possible :

mysqldump -u root -p wordpress > /home/flo/wordpress-$(date +%Y%m%d).sql

Cela va donner le fichier suivant pour le 9 mai 2022 (le format est adaptable en jouant sur la structure de la commande date ci-dessus) :

wordpress-20220509.sql

Avec cette syntaxe, il sera nécessaire de créer la base de données avant de pouvoir réimporter les données (dans le cas où l’on restaure la BDD sur un autre serveur, ce sera indispensable). Si l’on souhaite que la base de données soit recréée automatiquement, il faut ajouter l’option « –databases » comme ceci :

mysqldump -u root -p --databases wordpress > /home/flo/wordpress-$(date +%Y%m%d).sql

Dans le même esprit, on peut supprimer la base de données existante pour qu’elle soit recréée lors de la restauration via l’option « –add-drop-dabatase » qui va permettre d’ajouter une requête « DROP DATABASE IF EXISTS » dans le dump SQL. Cette option doit être utilisée conjointement avec « –databases« .

mysqldump -u root -p --databases wordpress --add-drop-dabatase > /home/flo/wordpress-$(date +%Y%m%d).sql

B. Sauvegarder plusieurs BDD spécifiques avec mysqldump

Dans le même principe, on peut sauvegarder non pas une, mais plusieurs bases de données spécifiques de notre instance MySQL. Pour cela, ce n’est pas utile d’exécuter plusieurs fois la commande mysqldump en modifiant le nom. Par exemple, on peut sauvegarder les bases de données « wordpress » et « moodle » dans le même fichier de sortie « wordpress-et-moodle.sql » de cette façon :

mysqldump -u root -p --databases wordpress moodle > /home/flo/wordpress-et-moodle.sql

Facile, n’est-ce pas ? Grâce à cette commande, on obtient un dump de nos deux bases de données dans un même fichier SQL.

C. Sauvegarder toutes les bases de données avec mysqldump

Il est possible d’aller encore plus loin : sauvegarder toutes les bases de données de notre instance MySQL en une seule fois ! Pour réaliser ce tour de magie, il suffit d’utiliser le paramètre « –all-databases » comme ceci :

mysqldump -u root -p --all-databases > /home/flo/all-databases.sql

Toujours sur le même principe, le mot de passe sera demandé, puis le dump sera effectué.

Je peux comprendre que ce ne soit pas très pratique d’avoir toutes les informations des bases de données dans le même dump (même si cela n’empêche pas de restaurer une seule base de données). Grâce à un petit script bash et une boucle for, on peut variabiliser le nom des bases de données et exécuter un mysqldump sur chaque base avec un fichier de sortie différent. En fait, on peut utiliser la commande ci-dessous pour récupérer le nom de toutes les bases de données de notre instance : une liste que l’on peut exploiter dans notre boucle for !

mysql -e 'show databases' -s --skip-column-names

Ensuite, voici un script Bash (à améliorer à votre convenance) pour réaliser la sauvegarde de chaque base de données dans le répertoire « /home/flo » au sein d’un fichier SQL indépendant :

#!/bin/bash
for database in $(mysql -e 'show databases' -s --skip-column-names); do
   echo "Sauvegarde de $database";
   mysqldump -u root -p $database > "/home/flo/$database.sql";
done

Pour ma part, ce fichier s’appelle « mysqldump-loop.sh » donc avant de l’exécuter, il faut lui ajouter les droits d’exécution :

cd /home/flo
chmod +x mysqldump-loop.sh

Puis, on l’exécute pour déclencher une sauvegarde :

./mysqldump-loop.sh

III. Restaurer une base de données avec mysqldump

Nous venons de voir différentes manières de sauvegarder une base de données avec mysqldump. Désormais, nous allons faire l’opération inverse afin de restaurer une base de données via mysqldump. Pour restaurer la base de données sur un serveur MySQL différent du serveur source, vous pouvez copier le fichier SQL au travers du réseau via SCP, à condition qu’un accès SSH vers le serveur distant soit possible.

A. Restaurer une seule base de données avec mysqldump

Tout d’abord, nous allons voir qu’il est possible de restaurer la base de données « wordpress » que l’on a sauvegardée précédemment. Pour restaurer la base de données, nous allons utiliser le fichier « wordpress.sql » qui est un dump uniquement de cette BDD.

mysql -u root -p wordpress < wordpress.sql

Il est possible que cette commande vous indique que la base de données n’existe pas (tout dépend comment est fait le dump). Dans ce cas, il faudra créer la base de données avant de pouvoir importer les données.

Sans ouvrir une console MySQL interactive, on peut exécuter une requête sur l’instance pour créer la base de données en amont :

mysql -u root -p -e "CREATE DATABASE wordpress";

Une fois que c’est fait, la requête précédente permettant d’importer les données du fichier « wordpress.sql » peut-être exécutée de nouveau :

mysql -u root -p wordpress < wordpress.sql

Bien sûr, cette opération sera plus ou moins longue en fonction de la taille du fichier SQL et des performances de votre serveur.

B. Restaurer une base de données à partir d’un dump complet

Maintenant, imaginons que l’on souhaite restaurer la base de données « wordpress » à partir du dump complet nommé « all-databases » et que l’on a créé précédemment. Il sera nécessaire de préciser le nom de la base de données à restaurer, comme ceci :

mysql -u root -p --one-database wordpress < all-databases.sql

IV. Conclusion

Nous venons de voir comment sauvegarder et restaurer une base de données MySQL / MariaDB avec mysqldump au travers différents exemples. Pour aller plus loin, notamment dans la gestion des identifiants pour ne pas avoir à préciser le login et le mot de passe à chaque fois, il est possible d’utiliser un fichier de configuration MySQL.

Le fichier de configuration peut-être créé dans le répertoire « home » de votre utilisateur Linux :

nano ~/.my.cnf

Puis, il contiendra un couple identifiant et mot de passe :

(client)
user = adm-wordpress
password = MotDePasseAdmBddWordPress

Ce fichier étant sensible, on va le sécuriser en ajustant les droits :

chmod 600 ~/.my.cnf

De cette façon, lorsque vous utilisez mysqldump avec cet utilisateur, le fichier « my.cnf » du répertoire « home » sera chargé automatiquement pour l’authentification, simplement en précisant le nom d’utilisateur. L’option « -u » est bien présente, mais plus l’option « -p » correspondante au mot de passe ».

mysqldump -u adm-wordpress --databases wordpress > /home/flo/wordpress-$(date +%Y%m%d).sql

Voilà, cette astuce fait office de conclusion pour cet article ! Si vous connaissez d’autres options et astuces intéressantes pour bien utiliser mysqldump, n’hésitez pas à laisser un commentaire sur cet article ! 🙂