MySQL / MariaDB : comment sauvegarder et restaurer une base de données avec Mysqldump ?
Sommaire
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).
Que ce soit avec MariaDB ou MySQL, vous pouvez utiliser l'outil mysqldump pour sauvegarder et restaurer les bases de données. Cet outil est disponible sur Linux et Windows.
II. Base de données : sauvegarde logique ou physique
Il existe deux types de sauvegarde pour les bases de données : les sauvegardes logiques et les sauvegardes physiques.
Une sauvegarde logique, réalisée par des outils comme mysqldump, consiste à exporter la structure et les données de la base sous forme de commandes SQL (CREATE TABLE, INSERT INTO, etc.). Ce format lisible et portable facilite la migration entre versions ou environnements différents, au prix d’un temps d’exécution souvent plus long, surtout pour les bases volumineuses.
À l’inverse, une sauvegarde physique copie directement les fichiers de données du moteur de stockage (InnoDB, MyISAM…), généralement à l’aide d’outils de sauvegarde spécialisés.
L'outil mysqldump reste adapté pour différents scénarios : export/migration, clonage d’environnements, sauvegarde applicative légère.
III. 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 <options>
Voyons comment utiliser mysqldump au travers de différents exemples....
Note : si vous avez besoin de lister les bases de données disponibles sur une instance MySQL ou MariaDB, connectez-vous en ligne de commande puis lancez la commande SQL "show databases;".
A. Sauvegarder une base de données
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éussit, la sauvegarde sera effectuée.
Voici un exemple complet :
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 bases de données
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
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é.
D. Comment créer des fichiers de sauvegardes indépendants ?
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
IV. 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
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
V. mysqldump : utiliser un fichier avec des identifiants
Nous venons de voir comment sauvegarder et restaurer une base de données MySQL / MariaDB avec mysqldump au travers de 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
VI. Conclusion
Pour sauvegarder une base de données sous Linux, mysqldump reste un allié fiable pour effectuer l'opération en ligne de commande. Vous pouvez aussi l'utiliser pour restaurer une base de données, comme nous venons de le voir. À l'aide d'une tâche planifiée (tâche cron), vous pouvez planifier la sauvegarde de la base de données.
FAQ
Quelle est la différence entre une sauvegarde logique et physique de MySQL ?
Une sauvegarde logique (mysqldump) génère un fichier SQL rejouable ; une sauvegarde physique copie les fichiers de données. La logique est flexible mais plus lente ; la physique est rapide et adaptée aux gros volumes.
Qu’est-ce que fait la commande mysqldump ?
mysqldump permet de sauvegarder une base MySQL ou MariaDB sous forme de fichier SQL. Ce fichier contient toutes les instructions nécessaires pour recréer les tables et les données, ce qui permet de restaurer la base à l’identique plus tard. La restauration peut être effectuée sur le serveur local ou un autre serveur grâce au fichier SQL.
Comment éviter de taper le mot de passe dans les scripts mysqldump ?
Placez l'identifiant et le mot de passe du compte dans un fichier ~/.my.cnf (avec les permissions 600). Lors de l'exécution de la commande mysqldump, supprimez -p car ce n'est plus nécessaire de préciser le mot de passe de façon interactive. Un exemple est donné dans ce tutoriel.
Comment automatiser les sauvegardes MySQL ?
Vous pouvez planifier mysqldump dans une tâche cron sous Linux, y compris en exécutant un script Bash. Ce sera utile pour planifier l'exécution d'une sauvegarde la nuit, par exemple à 2h du matin pour générer un dump dans un dossier spécifique.
Peut-on sauvegarder toutes les bases MySQL d’un serveur ?
Oui, l'outil mysqldump dispose de l’option --all-databases, que vous pouvez utiliser pour exporter l’ensemble des bases hébergées sur l'instance locale. Pratique pour des migrations complètes ou des sauvegardes serveur globales.
Très bonne article, mais dommage que la notion de –single-transaction –skip-lock-tables ne soit pas introduite 🙂
En tous les cas merci beaucoup de faire vivre ce site