Comment sauvegarder et restaurer des bases de données MySQL avec Mysqldump

Mysqldump est un utilitaire et complet pour sauvegarder et restaurer des bases de données MySQL ou MariaDB.
Il donne la possibilité d'exporter des bases de données au format SQL, CSV ou XML.
Dans le cas d'un fichier SQL, il s'agit d'un ensemble d'instructions SQL qui peuvent être utilisées pour recréer la base de données d'origine.

La sauvegarde de vos bases de données est importante cas d'un bogue logiciel ou une panne de disque dur peut être désastreux.
Mais on peut également utiliser l'utilitaire mysqldump pour transférer votre base de données MySQL vers un autre serveur MySQL.

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.

Comment sauvegarder et restaurer des bases de données MySQL avec Mysqldump

La syntaxe de Mysqldump

Voici la syntaxe la plus simple de mysqldump :

mysqldump [OPTIONS] base_de_donnees [tables]

ou encore :

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]


L'utilitaire fournit énormément d'options et paramètres, en voici les principales :

ParamètresDescription
-A, --all-databasesExporter toutes les bases de données. Ce sera la même chose que --databases
avec toutes les bases de données sélectionnées
-B, --databasesExporter plusieurs bases de données
--flush-privilegesÉmettre une instruction FLUSH PRIVILEGES après avoir vidé le mysql
base de données. Cette option doit être utilisée à chaque fois que le vidage
contient la base de données mysql et toute autre base de données qui
dépend des données de la base de données mysql pour
restaurer
--ignore-database=nameNe pas exporter la base de données spécifiée. Pour spécifier plus d'une base de données à ignorer, utilisez la directive plusieurs fois, une fois pour chaque base de données.
Ne prend effet que lorsqu'il est utilisé avec --all-databases | -A
--ignore-table-data=nameNe pas exporter les données de table spécifiées. Pour en préciser davantage qu'une table à ignorer, utilisez la directive multiple
fois, une fois pour chaque table. Chaque table doit être spécifiée avec les noms de base de données et de table
--ignore-table=nameNe pas exporter la table spécifiée. Pour en spécifier plusieurs table à ignorer, utilisez la directive plusieurs fois, une fois pour chaque table. Chaque table doit être spécifiée avec les deux noms de base de données et de table, par exemple, --ignore-table = base de données.table
-p, --password[=name]Mot de passe à utiliser lors de la connexion au serveur. Si le mot de passe est
pas donné c'est sollicité sur le tty
-P, --port=# Numéro de port à utiliser pour la connexion
-u, --user=nameUtilisateur pour la connexion si ce n'est pas l'utilisateur actuel
Les principaux paramètres de la commande MySQLDump

Comment sauvegarder des bases de données MySQL avec Mysqldump

Sauvegarder une seule base de données MySQL

Voici une utilisation classique de mysqldump pour exporter une base de données.

Par exemple, pour créer une sauvegarde de la base de données nommée nom_base de donnees à l'aide de la racine de l'utilisateur et l'enregistrer dans un fichier nommé nom_base_de_donnees.sql, exécutez la commande suivante:

mysqldump -u root -p nom_base_de_donnees > nom_base_de_donnes.sql

Il est possible de compresser le fichier de sauvegarde SQL avec gzip :

mysqldump -u root -p nom_base_de_donnees| gzip > nom_base_de_donnees.sql.gz

Enfin dernier exemple en créant un fichier avec la date du jour :

mysqldump -u root -p nom_base_de_donnees > nom_base_de_donnes-$(date +%d%m%Y).sql

Ainsi, si vous sauvegardez une base de données wordpress au 21/03/2021, on obtient un fichier d'export wordpress-20032021.sql

Sauvegarder plusieurs/toutes bases de données MySQL

Pour sauvegarder plusieurs bases de données, on utilise le paramètre --databases suivi des noms des bases de données

mysqldump -u root -p --databases nom_base_de_donnees1 nom_base_de_donnees1 > nom_base_de_donnes.sql

Voici comment sauvegarder l'intégralité des bases de données grâce à l'option --all-databases :

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

Cela créé un seul fichier SQL base_donnees_complet.sql d'export de toutes les bases de données.

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

L'utilitaire mysqldump ne donne pas la possibilité de sauvegarder toutes les bases de données par des fichiers séparés.
Toutefois, on peut faire cela avec la boucle For en listant les bases de données grâce à show databases.

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

Au final, on obtient un fichier sql avec le nom de la base de données.

Sauvegarder des tables spécifiques

Mysqldump donne aussi la possibilité de sauvegarder des tables spécifiques d'une base de données.
Voici la syntaxe à utiliser :

mysqldump -u root -p nom_base_donnees table1 table2 > base_donnees.sql

Par exemple pour exporter les tables phpbb_styles et phpbb_users de la base de données phpbb3 vers le fichier /tmp/phpbb.sql :

mysqldump -u root -p phpbb3 phpbb_styles phpbb_users > /tmp/phpbb.sql

Comment restaurer des bases de données MySQL avec Mysqldump

Restaurer une base de données depuis un fichier SQL

Voici comment restaurer une base de données depuis un fichier SQL créé par MySQLdump :

mysql -u root -p < base_donnees.sql

Par exemple pour restaurer une base de données depuis un fichier wordpress.sql :

mysql -u root -p < wordpress.sql

Restaurer une base de donnée provenant d'un Mysqldump complet

Si vous avez sauvegardé toutes vos bases de données à l'aide de l'option --all-databases et que vous souhaitez restaurer une seule base de données à partir d'un fichier de sauvegarde contenant plusieurs bases de données, utilisez l'option --one-database comme indiqué ci-dessous :

mysql --one-database nom_base_donnees < base_donnees_complet.sql

Automatiser les sauvegardes MysqlDump

Créer un cron Mysql

Il est possible d'automatiser un export et sauvegarde MySQL ou MariaDB avec cron.
Ce dernier permet de créer une tâche planifiée qui va s'exécuter périodiquement.
Il suffit alors de planifier l'exécution de mysqldump.
Pour modifier les programmations cron :

crontab -e

Puis par exemple pour automatiser une sauvegarde à 4H du matin, ajoutez l'entrée cron suivante :

0 4 * * * /usr/bin/mysqldump -u utilisateur_db nom_base_donnees > /home/nomutilisateur/sauvegarde_sql/bd-$(date +\%d\%m\%Y).sql

Adaptez la commande en modifiant

  • utilisateur_db par le nom d'utilisateur qui a accès à la base de donnée
  • nom_base_donnees par le nom de la base de données
  • Enfin l'emplacement de la sauvegarde /home/tuilisateur/sauvegarde_sql

Pour plus de détails sur l'utilisation de cron, suivez ce tutoriel complet :

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

find /chemin/sauvegarde -type f -name "*.sql" -mtime +30 -delete

Là aussi un tutoriel explicatif existe sur le site sur l'utilisation de la commande find :

Script de sauvegarde MySQL

Voici un exemple de script de sauvegarde MySQLDump qui envoie un mail en cas d'erreur.

#!/bin/bash
mail="[email protected]"
export RSYNC_PASSWORD=XXXX

function svg {
        if [[ ! -d ${rep}/${host} ]] ; then mkdir ${rep}/${host} ; fi
        cd ${rep}/${host}
        rm -f *
        db=$(echo "show databases;"|/usr/bin/mysql -h $host -u ${user} --password=${pass} -P ${port}|egrep -v "information_schema|Database|performance_schema")
        if [[ $? -ne 0 ]]
        then
            echo "Erreur mysqldump"
            echo "Erreur"|mail -s "`hostname` - Erreur sauvegarde MySQL" [email protected]
            exit 0
        fi
        for database in `echo "$db"`
        do
                echo Sauvegarde $database
                /usr/bin/mysqldump -h $host -u ${user} --password=${pass} -B $database -P ${port} > ${database}.sql
                /bin/gzip ${database}.sql
                sleep 2
        done
}

rep=/data/sauvegarde/sql/
user="XXX"
pass="XXX"
port="XXX"
host="XXXX"

svg

rsync -rlptD --stats --delete $rep [email protected]::sql
rsync -rlptD --stats --delete $rep [email protected]::sql

rm -f /tmp/rsync_mail.txt
rm -f /tmp/rsync.pid

A la fin d script, vous pouvez placer une commande rsync pour synchroniser les sauvegardes vers une machine distante*.

On peut aussi utiliser une commande de type SCP :

Transférer une base de données d'un serveur MySQL à un autre avec mysqldump

Voici la syntaxe pour transférer une base de données d'un serveur MySQL à l'autre avec mysqldump.

mysqldump --opt nom_base_donnees | mysql --host=adresse_ip -C nom_base_donnees

Tags: