Comment sauvegarder et restaurer des bases de données mysql avec mysqldump

Ce tutoriel explique comment sauvegarder et restaurer des bases de données MySQL ou MariaDB à partir de la ligne de commande à l'aide de l'utilitaire mysqldump.

Les fichiers de sauvegarde créés par l'utilitaire mysqldump sont essentiellement un ensemble d'instructions SQL qui peuvent être utilisées pour recréer la base de données d'origine. La commande mysqldump peut également générer des fichiers au format CSV et XML.

Vous pouvez également utiliser l'utilitaire mysqldump pour transférer votre base de données MySQL vers un autre serveur MySQL.

Syntaxe de la commande Mysqldump

Avant d'entrer dans la façon d'utiliser la commande mysqldump, commençons par revoir la syntaxe de base.

Les expressions de l'utilitaire mysqldump prennent la forme suivante:

mysqldump > file.sql

• options - Le fichier d'options mysqldump.sql - Le fichier de vidage (sauvegarde)

Pour utiliser la commande mysqldump, le serveur MySQL doit être accessible et en cours d'exécution.

Sauvegarder une seule base de données MySQL

Le cas d'utilisation le plus courant de l'outil mysqldump est de sauvegarder une seule base de données.

Par exemple, pour créer une sauvegarde de la base de données nommée database_name à l'aide de la root utilisateur et l'enregistrer dans un fichier nommé database_name.sql vous exécuterez la commande suivante:

mysqldump -u root -p database_name > database_name.sql

Vous serez invité à saisir le mot de passe root. Une fois l'authentification réussie, le processus de vidage démarre. Selon la taille de la base de données, le processus peut prendre un certain temps.

mysqldump database_name > database_name.sql

Sauvegarder plusieurs bases de données MySQL

Pour sauvegarder plusieurs bases de données MySQL avec une seule commande, vous devez utiliser l'option --database suivie de la liste des bases de données que vous souhaitez sauvegarder. Chaque nom de base de données doit être séparé par un espace.

mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql

La commande ci-dessus créera un fichier de vidage contenant les deux bases de données.

Sauvegarder toutes les bases de données MySQL

Utilisez l'option --all-databases pour sauvegarder toutes les bases de données MySQL:

mysqldump -u root -p --all-databases > all_databases.sql

Identique à l'exemple précédent, la commande ci-dessus créera un seul fichier de vidage contenant toutes les bases de données.

Sauvegardez toutes les bases de données MySQL dans des fichiers séparés

L'utilitaire mysqldump ne fournit pas d'option pour sauvegarder toutes les bases de données dans des fichiers séparés, mais nous y parvenons facilement avec une simple boucle bash FOR :

for DB in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $DB > "$DB.sql"; done

La commande ci-dessus créera un fichier de vidage distinct pour chaque base de données en utilisant le nom de la base de données comme nom de fichier.

Créer une sauvegarde de base de données MySQL compressée

Si la taille de la base de données est très grande, c'est une bonne idée de compresser la sortie. Pour ce faire, dirigez simplement la sortie vers l'utilitaire gzip et redirigez-la vers un fichier comme indiqué ci-dessous:

mysqldump database_name | gzip > database_name.sql.gz

Créer une sauvegarde avec horodatage

mysqldump database_name > database_name-$(date +%Y%m%d).sql

La commande ci-dessus créera un fichier au format suivant database_name-20180617.sql

Restauration d'un vidage MySQL

Vous pouvez restaurer un vidage MySQL à l'aide de l'outil mysql . La syntaxe générale de la commande est la suivante:

mysqld database_name < file.sql

Dans la plupart des cas, vous devrez créer une base de données dans laquelle importer. Si la base de données existe déjà, vous devez d'abord la supprimer.

Dans l'exemple suivant, la première commande créera une base de données nommée database_name puis elle y importera le vidage nom_base de database_name.sql :

mysql -u root -p -e "create database database_name"; mysql -u root -p database_name < database_name.sql

Restaurer une base de données MySQL unique à partir d'un vidage MySQL complet

mysql --one-database database_name < all_databases.sql

Exporter et importer une base de données MySQL en une seule commande

Au lieu de créer un fichier de vidage à partir d'une base de données, puis d'importer la sauvegarde dans une autre base de données MySQL, vous pouvez utiliser le one-liner suivant:

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

La commande ci-dessus dirigera la sortie vers un client mysql sur l'hôte distant et l'importera dans une base de données nommée remote_database_name . Avant d'exécuter la commande, assurez-vous que la base de données existe déjà sur le serveur distant.

Automatisez les sauvegardes avec Cron

L'automatisation du processus de sauvegarde des bases de données est aussi simple que la création d'un travail cron qui exécutera la commande mysqldump à l'heure spécifiée.

Pour configurer des sauvegardes automatisées d'une base de données MySQL à l'aide de cronjob, suivez les étapes ci-dessous:

1. Créez un fichier nommé .my.cnf dans votre répertoire personnel:

   sudo nano ~/.my.cnf

   Copiez et collez le texte suivant dans le fichier.my.cnf.

   user = dbuser password = dbpasswd

   N'oubliez pas de remplacer dbuser et dbpasswd par l'utilisateur de la base de données et le mot de passe de l'utilisateur.

   Limitez les autorisations du fichier d'informations d'identification afin que seul votre utilisateur y ait accès:

   chmod 600 ~/.my.cnf

   Créez un répertoire pour stocker les sauvegardes:

   mkdir ~/db_backups

   Ouvrez votre fichier utilisateur crontab:

   crontab -e

   Ajoutez le travail cron suivant qui créera une sauvegarde d'un nom de base de données mydb chaque jour à 3h du matin:

   0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +%Y%m%d).sql

   N'oubliez pas de remplacer le username d' username par votre nom d'utilisateur réel.

Vous pouvez également créer un autre cronjob pour supprimer toutes les sauvegardes de plus de 30 jours:

find /path/to/backups -type f -name "*.sql" -mtime +30 -delete

Bien sûr, vous devez ajuster la commande en fonction de votre emplacement de sauvegarde et des noms de fichiers. Pour en savoir plus sur la commande find, consultez notre guide Comment rechercher des fichiers sous Linux à l'aide du guide de ligne de commande.

Conclusion

Ce didacticiel ne couvre que les bases, mais il devrait être un bon début pour quiconque souhaite apprendre à créer et restaurer des bases de données MySQL à partir de la ligne de commande à l'aide de l'utilitaire mysqldump.

Vous pouvez également consulter le didacticiel sur la façon de réinitialiser un mot de passe racine MySQL au cas où vous l'auriez oublié.

sauvegarde mysql mariadb mysqldump