Le guide ultime de mysqldump : sauvegarde, restauration et automatisation de base de données MySQL

mysqldump est un utilitaire en ligne de commande fourni avec MySQL et MariaDB qui génère des sauvegardes logiques en sérialisant les objets de base de données et les données sous forme d’une séquence d’instructions SQL. Le fichier dump résultant peut recréer une base de données identique sur n’importe quel serveur compatible, ce qui en fait l’outil standard du secteur pour les sauvegardes, les migrations entre serveurs, les mises à niveau de version et les workflows de reprise après sinistre.

Contrairement aux outils de sauvegarde physique tels que Percona XtraBackup ou MySQL Enterprise Backup, mysqldump opère au niveau de la couche SQL — il lit les données en direct via le protocole MySQL et écrit du SQL portable et lisible par l’homme. Cette portabilité est sa plus grande force et, à grande échelle, sa principale contrainte.

Ce que mysqldump fait réellement en coulisses

Lorsque vous invoquez mysqldump, le client se connecte au serveur MySQL, interroge le schéma d’information et le dictionnaire de données, et émet un flux d’instructions `CREATE DATABASE`, `CREATE TABLE`, `INSERT` et DDL vers la sortie standard. Vous redirigez ce flux vers un fichier, un pipe ou un utilitaire de compression.

Pour les tables InnoDB avec `–single-transaction`, mysqldump ouvre une transaction en lecture répétable avant de lire les données. Cela vous donne un instantané cohérent à un moment précis sans acquérir de verrous de lecture globaux — la base de données reste entièrement accessible en écriture pendant le dump. Pour les tables MyISAM, aucun mécanisme de ce type n’existe ; mysqldump se replie sur `FLUSH TABLES WITH READ LOCK`, qui bloque brièvement les écritures.

Comprendre cette distinction est essentiel avant de choisir mysqldump pour des charges de travail en production. Si votre schéma mélange des tables InnoDB et MyISAM, `–single-transaction` seul est insuffisant — vous aurez besoin de `–lock-all-tables` ou d’une fenêtre de maintenance.

Prérequis et privilèges requis

Avant d’exécuter toute commande de dump, vérifiez les points suivants :

• MySQL ou MariaDB est installé et accessible (socket local ou TCP/IP).
• L’utilisateur de sauvegarde dispose des privilèges minimaux requis :
• `SELECT` sur toutes les tables cibles
• `LOCK TABLES` (requis sauf si `–single-transaction` est utilisé exclusivement avec InnoDB)
• `SHOW VIEW` pour inclure les vues
• `TRIGGER` pour inclure les triggers
• `PROCESS` lors de l’utilisation de `–single-transaction` sur MySQL 8+
• `RELOAD` pour `FLUSH TABLES WITH READ LOCK`
• `REPLICATION CLIENT` si vous avez besoin des coordonnées du journal binaire pour la configuration de la réplication

Créez un utilisateur de sauvegarde dédié plutôt que d’exécuter les dumps en tant que root :

“`sql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPassword!';

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, PROCESS, RELOAD, REPLICATION CLIENT

ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

“`

Exécuter mysqldump en tant que root avec un mot de passe intégré dans la commande shell expose les identifiants dans les listes de processus et l’historique du shell — un risque de sécurité important sur tout système partagé ou multi-utilisateur.

Syntaxe de base

“`

mysqldump [OPTIONS] database_name [table1 table2 …] > backup_file.sql

“`

Référence complète des options

Options de connexion

Options de portée

Options de cohérence et de verrouillage

Options de sortie et de contenu

mysqldump vs. méthodes de sauvegarde alternatives

Le choix de la bonne stratégie de sauvegarde dépend de la taille de la base de données, des exigences RTO/RPO et de l’infrastructure. Voici comment mysqldump se compare aux alternatives les plus courantes :

Pour les bases de données de moins de 10–20 GB dans un environnement VPS Hosting, mysqldump reste la solution la plus pratique et la plus portable. Au-delà de ce seuil, les outils de sauvegarde physique offrent des fenêtres de sauvegarde et de restauration considérablement plus rapides.

Exemples d’utilisation pratiques

Exemple 1 : Sauvegarder une seule base de données

“`bash

mysqldump -u backup_user -p database_name > /backups/database_name_$(date +%F).sql

“`

La substitution `$(date +%F)` ajoute automatiquement la date ISO (par ex., `2025-07-15`) au nom de fichier, évitant ainsi les écrasements.

Exemple 2 : Sauvegarder plusieurs bases de données spécifiques

“`bash

mysqldump -u backup_user -p –databases app_db analytics_db > /backups/multi_db_backup.sql

“`

L’option `–databases` amène mysqldump à émettre des instructions `CREATE DATABASE` et `USE`, rendant le dump autonome pour la restauration.

Exemple 3 : Sauvegarder toutes les bases de données

“`bash

mysqldump -u backup_user -p –all-databases –events –routines –triggers

> /backups/full_server_$(date +%F).sql

“`

Incluez toujours `–events`, `–routines` et `–triggers` dans les dumps complets du serveur. Ces objets sont silencieusement omis sans options explicites.

Exemple 4 : Sauvegarde InnoDB cohérente (sûre pour la production)

“`bash

mysqldump -u backup_user -p

–single-transaction

–flush-logs

–source-data=2

–routines –triggers –events

database_name > /backups/database_name_$(date +%F).sql

“`

`–flush-logs` fait pivoter le journal binaire au début du dump. `–source-data=2` écrit le nom du fichier journal binaire actuel et la position en commentaire SQL, permettant la récupération à un instant précis en rejouant les binlogs suivants à partir de cette position.

Exemple 5 : Sauvegarde compressée avec gzip

“`bash

mysqldump -u backup_user -p database_name | gzip -9 > /backups/database_name_$(date +%F).sql.gz

“`

Pour les serveurs à CPU limité, remplacez par `pigz` (gzip parallèle) pour utiliser plusieurs cœurs :

“`bash

mysqldump -u backup_user -p database_name | pigz -9 > /backups/database_name_$(date +%F).sql.gz

“`

Exemple 6 : Sauvegarde du schéma uniquement (structure sans données)

“`bash

mysqldump -u backup_user -p –no-data database_name > /backups/schema_only.sql

“`

Utile pour versionner votre schéma dans Git ou déployer dans un environnement de staging sans copier les données de production.

Exemple 7 : Sauvegarde des données uniquement (sans schéma)

“`bash

mysqldump -u backup_user -p –no-create-info database_name > /backups/data_only.sql

“`

Utilisez ceci lorsque le schéma cible existe déjà et que vous avez seulement besoin de peupler ou d’actualiser les données.

Exemple 8 : Sauvegarder une seule table

“`bash

mysqldump -u backup_user -p database_name orders > /backups/orders_table_$(date +%F).sql

“`

Exemple 9 : Exporter un sous-ensemble filtré de lignes

“`bash

mysqldump -u backup_user -p database_name orders

–where="created_at >= '2025-01-01' AND status='completed'"

> /backups/orders_2025_completed.sql

“`

L’option `–where` est sous-utilisée mais extrêmement puissante pour les exports partiels, l’archivage de données et le débogage d’ensembles d’enregistrements spécifiques.

Exemple 10 : Exclure des tables spécifiques

“`bash

mysqldump -u backup_user -p database_name

–ignore-table=database_name.cache

–ignore-table=database_name.sessions

> /backups/database_name_no_cache.sql

“`

Exclure les tables volumineuses et éphémères (caches, stockages de sessions, tables de logs) peut réduire la taille et la durée du dump d’un ordre de grandeur.

Exemple 11 : Inclure les procédures stockées, fonctions et triggers

“`bash

mysqldump -u backup_user -p –routines –triggers –events database_name > /backups/full_backup.sql

“`

Exemple 12 : Sauvegarde d’une base de données distante

“`bash

mysqldump -u backup_user -p -h 192.168.1.100 -P 3306 database_name

“`

Lors de la sauvegarde d’un serveur distant, le trafic transite par le réseau non chiffré par défaut. Ajoutez les options `–ssl-ca`, `–ssl-cert` et `–ssl-key` ou tunnelisez via SSH :

“`bash

ssh user@remote-server "mysqldump -u backup_user -p database_name | gzip"

> /backups/remote_db_$(date +%F).sql.gz

“`

Restauration d’une sauvegarde mysqldump

Restaurer une seule base de données

“`bash

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Si la base de données cible n’existe pas encore, créez-la d’abord :

“`bash

mysql -u root -p -e "CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Restaurer toutes les bases de données depuis un dump complet du serveur

“`bash

mysql -u root -p < /backups/full_server_2025-07-15.sql

“`

Comme `–all-databases` intègre des instructions `CREATE DATABASE` et `USE`, aucun argument de base de données cible n’est nécessaire.

Restaurer depuis une sauvegarde compressée

“`bash

gunzip < /backups/database_name_2025-07-15.sql.gz | mysql -u root -p database_name

“`

Ou en utilisant la substitution de processus :

“`bash

mysql -u root -p database_name < <(gunzip -c /backups/database_name_2025-07-15.sql.gz)

“`

Restaurer une seule table depuis un dump complet de base de données

Il s’agit d’un scénario opérationnel courant que le fichier dump original rend non trivial. Utilisez `sed` ou `grep` pour extraire la section concernée :

“`bash

sed -n '/^– Table structure for table `orders`/,/^– Table structure for table `/p'

backup_file.sql | head -n -1 | mysql -u root -p database_name

“`

Alternativement, utilisez `mysql_extract_table.sh` ou importez dans une base de données temporaire et copiez la table :

“`bash

mysql -u root -p temp_restore < backup_file.sql

mysql -u root -p -e "INSERT INTO database_name.orders SELECT * FROM temp_restore.orders;"

“`

Récupération à un instant précis à l’aide des journaux binaires

Si votre dump a été effectué avec `–source-data=2` et que la journalisation binaire est activée, vous pouvez récupérer à n’importe quel point après le dump :

1. Identifiez la position du journal binaire dans le commentaire d’en-tête du fichier dump.
2. Restaurez le dump de base.
3. Appliquez les événements du journal binaire suivants jusqu’à l’horodatage souhaité :

“`bash

mysqlbinlog –start-position=154 –stop-datetime="2025-07-15 14:30:00"

/var/lib/mysql/binlog.000042 | mysql -u root -p database_name

“`

Automatisation des sauvegardes avec Cron

Tâche de sauvegarde quotidienne de base

Stockez les identifiants dans `~/.my.cnf` plutôt que de les intégrer dans les commandes cron :

“`ini

[mysqldump]

user=backup_user

password=StrongPassword!

“`

Définissez des permissions strictes :

“`bash

chmod 600 ~/.my.cnf

“`

Puis créez la tâche cron :

“`bash

crontab -e

“`

“`

Daily compressed backup at 02:00, retained for 30 days

0 2 * * * mysqldump –single-transaction –routines –triggers –events database_name

Delete backups older than 30 days

10 2 * * * find /backups/ -name "*.sql.gz" -mtime +30 -delete

“`

Script de sauvegarde de niveau production

Pour les Serveurs Dédiés hébergeant plusieurs bases de données, un script plus robuste gère la journalisation des erreurs, les vérifications d’espace disque et le déchargement distant :

“`bash

#!/bin/bash

BACKUP_DIR="/backups/mysql"

RETENTION_DAYS=30

LOG_FILE="/var/log/mysql_backup.log"

DATE=$(date +%F_%H-%M)

DATABASES=$(mysql –defaults-file=/etc/mysql/backup.cnf -e "SHOW DATABASES;"

mkdir -p "$BACKUP_DIR"

for DB in $DATABASES; do

OUTPUT="$BACKUP_DIR/${DB}_${DATE}.sql.gz"

mysqldump –defaults-file=/etc/mysql/backup.cnf

–single-transaction –routines –triggers –events

"$DB" | gzip -9 > "$OUTPUT"

if [ $? -eq 0 ]; then

echo "$(date): SUCCESS – $DB -> $OUTPUT" >> "$LOG_FILE"

else

echo "$(date): FAILURE – $DB" >> "$LOG_FILE"

fi

done

find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$RETENTION_DAYS" -delete

“`

Renforcement de la sécurité pour les opérations mysqldump

La gestion des identifiants est l’aspect le plus souvent négligé de la sécurité des sauvegardes. Ne transmettez jamais `-pYourPassword` directement en ligne de commande — il est visible dans la sortie `ps aux` et dans l’historique du shell. Utilisez plutôt l’une de ces approches :

• `~/.my.cnf` avec `chmod 600` (par utilisateur)
• `/etc/mysql/backup.cnf` avec `chmod 640`, appartenant à root, lisible par le groupe de sauvegarde
• Variable d’environnement `MYSQL_PWD` (visible dans `/proc`, à utiliser uniquement dans des conteneurs isolés)
• MySQL Vault ou HashiCorp Vault pour les environnements d’entreprise

Les permissions des fichiers de sauvegarde doivent être restrictives :

“`bash

chmod 640 /backups/database_name_2025-07-15.sql.gz

chown root:backup_group /backups/database_name_2025-07-15.sql.gz

“`

Chiffrement au repos : Pour les données sensibles, chiffrez les fichiers de sauvegarde avant de les stocker ou de les transférer :

“`bash

mysqldump –single-transaction database_name

> /backups/database_name_$(date +%F).sql.gz.enc

“`

Chiffrement du transport : Lors du dump depuis un serveur distant, utilisez toujours SSL/TLS ou un tunnel SSH. Dans un environnement VPS avec cPanel, l’interface de sauvegarde de cPanel gère cela automatiquement, mais les opérations mysqldump manuelles nécessitent des options SSL explicites.

Pièges courants et comment les éviter

Les incompatibilités de jeux de caractères sont la cause la plus fréquente de restaurations corrompues. Spécifiez toujours le jeu de caractères explicitement :

“`bash

mysqldump –default-character-set=utf8mb4 database_name > backup.sql

mysql –default-character-set=utf8mb4 database_name < backup.sql

“`

L’absence de `–column-statistics=0` provoque des échecs lorsqu’un client MySQL 8.0 dumpe depuis un serveur MySQL 5.7 ou MariaDB. Le client MySQL 8 tente de dumper des statistiques de colonnes que les serveurs plus anciens ne supportent pas :

“`bash

mysqldump –column-statistics=0 -u backup_user -p database_name > backup.sql

“`

Oublier `–routines`, `–triggers` et `–events` omet silencieusement des objets de base de données critiques. Ces options ne sont pas activées par défaut (sauf `–triggers`) et sont fréquemment oubliées dans les dumps ad hoc.

Les dumps de grandes tables provoquant des OOM : mysqldump met en mémoire tampon des ensembles de résultats entiers par défaut. Pour les très grandes tables, ajoutez `–quick` (activé par défaut dans la plupart des versions, mais vaut la peine d’être vérifié) pour diffuser les lignes une par une plutôt que de les mettre en mémoire tampon :

“`bash

mysqldump –quick –single-transaction database_name > backup.sql

“`

Restauration vers une version différente de MySQL : Les dumps de MySQL 8.0 peuvent contenir une syntaxe non supportée dans MySQL 5.7 (par ex., index fonctionnels, colonnes invisibles). Testez toujours les restaurations dans un environnement avec la même version avant de vous fier aux migrations entre versions.

Dérive de la valeur auto-increment : Si vous restaurez une table dans un schéma existant qui contient déjà des lignes, les instructions `INSERT` échoueront sur des conflits de clé primaire, sauf si vous incluez `–add-drop-table` ou tronquez manuellement la table cible au préalable.

Utilisation de mysqldump pour les migrations de bases de données

mysqldump est l’approche standard pour migrer des bases de données entre serveurs — par exemple, lors du déplacement d’un site WordPress depuis un Hébergement Web Mutualisé vers un VPS, ou lors d’une migration vers un environnement VPS avec Panneaux de Contrôle disposant de plus de ressources.

Le workflow de migration recommandé :

1. Dumper la base de données source avec toutes les options :

“`bash

mysqldump –single-transaction –routines –triggers –events

–default-character-set=utf8mb4 source_db | gzip > migration.sql.gz

“`

1. Transférer de manière sécurisée en utilisant rsync via SSH :

“`bash

rsync -avz -e ssh migration.sql.gz user@target-server:/tmp/

“`

1. Créer la base de données cible avec le jeu de caractères correspondant :

“`bash

mysql -u root -p -e "CREATE DATABASE target_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

“`

1. Restaurer et vérifier :

“`bash

gunzip < /tmp/migration.sql.gz | mysql -u root -p target_db

mysql -u root -p target_db -e "SHOW TABLES; SELECT COUNT(*) FROM critical_table;"

“`

1. Mettre à jour la configuration de l’application pour pointer vers le nouvel hôte de base de données.

Pour les applications qui dépendent également d’une infrastructure de messagerie, assurez-vous que les enregistrements DNS et les configurations d’Hébergement Email sont mis à jour en parallèle avec la migration de la base de données pour éviter toute interruption de service.

Vérification de l’intégrité des sauvegardes

Une sauvegarde qui n’a jamais été testée n’est pas une sauvegarde — c’est une hypothèse non vérifiée. Mettez en place une routine de vérification :

“`bash

#!/bin/bash

Restore backup to a test database and verify row counts

TEST_DB="backup_verify_$(date +%s)"

BACKUP_FILE="/backups/database_name_$(date +%F).sql.gz"

mysql -u root -p -e "CREATE DATABASE $TEST_DB;"

gunzip < "$BACKUP_FILE" | mysql -u root -p "$TEST_DB"

PROD_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM database_name.orders;")

TEST_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM $TEST_DB.orders;")

if [ "$PROD_COUNT" -eq "$TEST_COUNT" ]; then

echo "Backup verified: row counts match ($PROD_COUNT rows)"

else

echo "BACKUP VERIFICATION FAILED: prod=$PROD_COUNT, test=$TEST_COUNT"

fi

mysql -u root -p -e "DROP DATABASE $TEST_DB;"

“`

Exécutez ce script de vérification chaque semaine via cron et alertez en cas d’échec.

Matrice de décision : quand utiliser mysqldump

Liste de contrôle des points techniques essentiels

• Utilisez toujours `–single-transaction` pour les bases de données InnoDB uniquement afin d’éviter les verrous en écriture pendant la sauvegarde.
• Incluez toujours `–routines –triggers –events` dans tout dump destiné à être une sauvegarde complète.
• Stockez les identifiants dans `~/.my.cnf` ou `/etc/mysql/backup.cnf` avec `chmod 600/640` — jamais en ligne dans des scripts ou des commandes cron.
• Ajoutez `–column-statistics=0` lors de l’utilisation d’un client MySQL 8.0 contre un serveur MySQL 5.7 ou MariaDB.
• Spécifiez toujours `–default-character-set=utf8mb4` lors du dump et de la restauration pour éviter la corruption de l’encodage des caractères.
• Compressez toutes les sauvegardes avec gzip ou pigz ; chiffrez les dumps sensibles avec AES-256 avant le transfert hors site.
• Incluez `–flush-logs –source-data=2` dans les dumps de production pour permettre la récupération à un instant précis via les journaux binaires.
• Automatisez le nettoyage de la rétention avec `find … -mtime +N -delete` pour éviter l’épuisement du disque.
• Testez les restaurations régulièrement — vérifiez les comptages de lignes et contrôlez ponctuellement l’intégrité des données par rapport à la production.
• Pour les schémas à moteurs mixtes, utilisez `–lock-all-tables` plutôt que `–single-transaction` pour garantir la cohérence.

Foire aux questions

mysqldump verrouille-t-il les tables pendant la sauvegarde ?

Avec `–single-transaction` sur une base de données InnoDB pure, aucun verrou de table n’est acquis au-delà d’un bref flush initial. Les tables MyISAM nécessitent toujours un verrou en lecture (`LOCK TABLES`) car elles ne supportent pas les transactions. Les schémas à moteurs mixtes nécessitent `–lock-all-tables` pour un instantané cohérent, ce qui bloque les écritures pendant toute la durée du dump.

Comment sauvegarder uniquement le schéma de la base de données sans aucune donnée ?

Utilisez l’option `–no-data` : `mysqldump -u backup_user -p –no-data database_name > schema.sql`. Cela exporte toutes les instructions `CREATE TABLE`, `CREATE VIEW`, les procédures stockées et les triggers sans aucune instruction `INSERT`.

Pourquoi mon mysqldump échoue-t-il avec des erreurs de « statistiques de colonnes » ?

Cela se produit lorsqu’un client MySQL 8.0 se connecte à un serveur MySQL 5.7 ou MariaDB. Ajoutez `–column-statistics=0` à votre commande. Alternativement, mettez à jour le serveur vers MySQL 8.0 ou utilisez un binaire client correspondant à la version du serveur.

mysqldump peut-il effectuer des sauvegardes incrémentielles ?

Non. mysqldump produit toujours un dump logique complet de la portée spécifiée. La capacité de sauvegarde incrémentielle nécessite l’archivage des journaux binaires (`mysqlbinlog`) combiné à un mysqldump de base effectué avec `–flush-logs –source-data=2`. Les véritables sauvegardes physiques incrémentielles nécessitent Percona XtraBackup ou MySQL Enterprise Backup.

Quelle est la manière la plus sûre d’automatiser mysqldump sans exposer les mots de passe ?

Créez un utilisateur MySQL de sauvegarde dédié avec les privilèges minimaux requis, stockez ses identifiants dans une section `[mysqldump]` de `~/.my.cnf` ou dans un fichier d’options séparé avec `chmod 600`, et référencez-le avec `–defaults-file=/path/to/backup.cnf`. Cette approche maintient les identifiants hors des listes de processus, de l’historique du shell et des définitions de tâches cron.