Tutoriel MySQL mysqldump : Guide complet pour l’exportation, la sauvegarde et la restauration de bases de données

1. Introduction

MySQL est l’un des systèmes de gestion de bases de données relationnelles (SGBDR) les plus largement utilisés pour les services web et les applications d’entreprise. Les bases de données construites avec MySQL servent souvent de cœur aux opérations quotidiennes et aux services. Si des données sont perdues à cause de pannes système ou d’erreurs humaines, cela peut avoir un impact important sur la continuité des activités.

C’est à ce moment que les « sauvegardes » deviennent essentielles. MySQL fournit un outil en ligne de commande officiel appelé « mysqldump », qui permet d’exporter facilement le contenu d’une base de données et de l’enregistrer sous forme de fichiers de sauvegarde.

En exploitant cet outil, vous pouvez répondre à divers besoins opérationnels tels que la récupération après sinistre, la migration vers un autre environnement et le stockage d’archives périodiques.

Dans cet article, nous expliquerons comment exporter des bases de données MySQL à l’aide de « mysqldump », en couvrant tout, de l’utilisation de base aux configurations avancées. Même les débutants peuvent suivre, car nous fournissons des explications détaillées avec des exemples de commandes pratiques.

2. Utilisation de base de mysqldump

mysqldump est un outil en ligne de commande utilisé pour exporter le contenu d’une base de données MySQL dans un fichier SQL. La syntaxe de base est très simple, et les sauvegardes peuvent être créées avec seulement quelques lignes de commandes. Dans cette section, nous expliquons les méthodes d’exportation les plus couramment utilisées.

Exporter une base de données unique

Le cas d’utilisation le plus courant est l’exportation d’une base de données unique complète.

mysqldump -u username -p database_name > backup.sql

Après l’exécution, il vous sera demandé de saisir le mot de passe, et le contenu de la base de données spécifiée sera écrit dans un fichier nommé backup.sql.

Exporter plusieurs bases de données

Si vous souhaitez sauvegarder plusieurs bases de données en même temps, utilisez l’option --databases.

mysqldump -u username -p --databases db1 db2 db3 > multi_backup.sql

Dans ce format, une instruction CREATE DATABASE est incluse pour chaque base de données, ce qui rend la restauration plus fiable et pratique.

Exporter toutes les bases de données

Pour sauvegarder toutes les bases de données du serveur, utilisez l’option --all-databases.

mysqldump -u username -p --all-databases > all_backup.sql

Cette commande exporte toutes les bases de données présentes sur le serveur MySQL (y compris mysql, information_schema, performance_schema et d’autres).

Exemple : inclure la date dans le nom du fichier de sortie

Pour les sauvegardes planifiées, inclure la date dans le nom du fichier de sortie facilite la gestion. Voici un exemple utilisant un shell UNIX :

mysqldump -u username -p database_name > backup_$(date +%F).sql

Avec cette approche, un fichier tel que backup_2025-04-13.sql est généré automatiquement, rendant la gestion des sauvegardes plus organisée.

3. Variantes d’exportation

mysqldump peut faire plus que simplement exporter une base de données complète. Il prend également en charge des options d’exportation flexibles selon vos besoins. Dans cette section, nous présentons des techniques avancées telles que l’exportation de tables spécifiques, l’exportation uniquement du schéma ou des données, et l’exportation de données filtrées à l’aide de conditions.

Exporter des tables spécifiques

Si vous souhaitez sauvegarder uniquement des tables spécifiques au sein d’une base de données, vous pouvez spécifier les noms de tables explicitement.

mysqldump -u username -p database_name table1 table2 > selected_tables.sql

Exemple :

mysqldump -u root -p mydb users orders > users_orders.sql

Cette commande exporte uniquement les tables users et orders de la base de données mydb.

Exporter uniquement les données ou uniquement le schéma

mysqldump offre des options pour exporter uniquement les définitions du schéma ou uniquement les données.

• Exporter uniquement le schéma (structure) :

  mysqldump -u username -p --no-data database_name > schema_only.sql
  

• Exporter uniquement les données (exclure les instructions CREATE TABLE) :

  mysqldump -u username -p --no-create-info database_name > data_only.sql
  

Ces options sont utiles lorsqu’on partage uniquement le schéma entre les environnements de développement et de production, ou lors de l’importation de données incrémentielles.

Exportation de données avec conditions (–where)

Exportation de données avec conditions (–where)

Pour exporter uniquement un sous‑ensemble de données, utilisez l’option --where. Elle utilise la même syntaxe qu’une clause SQL WHERE.

mysqldump -u username -p database_name table_name --where="condition" > filtered_data.sql

Exemple :

mysqldump -u root -p mydb users --where="created_at >= '2025-01-01'" > users_2025.sql

Dans cet exemple, seuls les enregistrements créés en 2025 ou après dans la table users sont exportés.

4. Options fréquemment utilisées et leurs explications

Bien que mysqldump soit simple, combiner les options permet des sauvegardes plus sûres et plus efficaces. Dans cette section, nous expliquons les options couramment utilisées en environnements réels.

–single-transaction : Maintenir la cohérence des transactions

mysqldump -u username -p --single-transaction database_name > backup.sql

Cette option est efficace lorsqu’on utilise des moteurs de stockage supportant les transactions comme InnoDB. Elle exécute le processus de vidage dans le cadre d’une seule transaction, garantissant la cohérence pendant l’exportation sans appliquer de verrous de lecture. Cela est particulièrement utile lorsque vous devez maintenir les services en fonctionnement pendant la sauvegarde.

Note : Cette option n’a aucun effet sur les moteurs non transactionnels tels que MyISAM.

–quick : Réduire l’utilisation de la mémoire

mysqldump -u username -p --quick database_name > backup.sql

Avec cette option, mysqldump récupère les lignes une par une au lieu de charger toutes les données en mémoire d’un coup. Cela réduit la consommation de mémoire et améliore la stabilité lors de l’exportation de tables volumineuses.

–routines et –events : Inclure les procédures stockées et les événements

Par défaut, les procédures stockées et les événements ne sont pas inclus dans les exportations. Utilisez les options suivantes pour les inclure :

mysqldump -u username -p --routines --events database_name > backup_with_logic.sql

• --routines : Inclure les procédures stockées et les fonctions
• --events : Inclure les événements planifiés

Si la logique métier est largement implémentée au niveau de la base de données, n’oubliez pas ces options.

–add-drop-table : Utile pour écraser les tables

mysqldump -u username -p --add-drop-table database_name > backup.sql

Cette option ajoute une instruction DROP TABLE IF EXISTS avant chaque définition de table. Si des tables portant le même nom existent déjà dans l’environnement cible, elles seront supprimées avant d’être recréées.

–lock-tables : Efficace pour MyISAM

mysqldump -u username -p --lock-tables database_name > backup.sql

Bien que généralement inutile pour InnoDB, cette option verrouille les tables afin d’empêcher les écritures pendant l’exportation lorsqu’on utilise MyISAM. Elle est utile lorsque la cohérence est cruciale.

5. Comment importer un fichier d’exportation

Les fichiers SQL exportés avec mysqldump peuvent être restaurés (importés) à l’aide des méthodes d’importation standard de MySQL. Dans cette section, nous expliquons les bases de l’importation à partir d’un fichier de sauvegarde, des exemples de restauration pratiques et les précautions essentielles.

Commande d’importation de base

Un fichier SQL exporté peut être importé facilement à l’aide de la commande mysql. La syntaxe de base est la suivante :

mysql -u username -p database_name < backup.sql

Exemple :

mysql -u root -p mydb < backup.sql

Lorsque vous exécutez cette commande, les instructions SQL contenues dans backup.sql sont exécutées dans l’ordre, et la base de données est restaurée à son état d’origine.

Importation dans une nouvelle base de données

Comme un fichier de sauvegarde peut ne pas inclure d’instruction CREATE DATABASE, si vous souhaitez l’importer dans une base de données portant un nom différent, vous devez créer la nouvelle base au préalable.

Exemple : Créer une nouvelle base de données « mydb_restore » et l’importer

CREATE DATABASE mydb_restore;

mysql -u root -p mydb_restore < backup.sql

Remarque : le SQL exporté avec l’option --databases inclut des instructions CREATE DATABASE, il faut donc savoir que la procédure diffère dans ce cas.

Importation d’un fichier compressé (.gz)

Si votre fichier de sauvegarde est compressé avec gzip ou un outil similaire, vous pouvez l’importer directement tout en le décompressant :

gunzip < backup.sql.gz | mysql -u username -p database_name

Cette méthode vous permet de restaurer rapidement tout en économisant de l’espace disque.

Erreurs d’importation courantes et comment les corriger

Résumé : considérer l’importation comme faisant partie du processus de sauvegarde

Un fichier de sauvegarde créé avec mysqldump n’a pas de valeur si vous ne faites que le générer. La vraie valeur d’une sauvegarde réside dans le fait de pouvoir la restaurer quand vous en avez besoin. Pour cette raison, il est recommandé de tester régulièrement les importations et de confirmer que le fichier se charge correctement.

6. Conseils pratiques et précautions

Bien que mysqldump soit facile à utiliser, les bases de données volumineuses et les environnements de production nécessitent parfois une manipulation prudente et des stratégies supplémentaires. Dans cette section, nous présentons des techniques pratiques utiles et des précautions pour aider à prévenir les problèmes.

Compresser les bases de données volumineuses avec gzip

Parce que mysqldump génère des fichiers SQL en texte brut, ils peuvent devenir très volumineux. Pour les bases de données dépassant plusieurs gigaoctets, il est courant de combiner mysqldump avec gzip pour compresser la sortie.

mysqldump -u username -p database_name | gzip > backup.sql.gz

Cette méthode peut réduire considérablement l’utilisation du disque et diminue également la charge lors des transferts à distance.

Faire de la vérification d’exportation et d’importation une habitude

Une sauvegarde est inutile si vous ne pouvez pas l’importer quand c’est nécessaire. Nous recommandons des opérations comme les suivantes :

• Régulièrement restaurer les sauvegardes dans un environnement séparé pour les tester
• Vérifier l’intégrité du fichier avec md5sum ou sha256sum
• Conserver plusieurs générations de sauvegardes pour les bases de données critiques

Faire attention aux différences de version

Si la version de MySQL diffère entre la source d’exportation et la cible d’importation, les différences de syntaxe et de comportement interne peuvent empêcher le fichier SQL de s’exécuter correctement.

• Si possible, travailler sur la même version
• Si vous devez traverser les versions, contrôler le comportement avec des options (par ex., --set-gtid-purged=OFF )
• Avant et après les mises à jour, confirmez toujours la compatibilité de la définition du schéma

Utiliser cron et des scripts pour l’automatisation

Si vous souhaitez exécuter des sauvegardes automatiques quotidiennes ou hebdomadaires, l’utilisation de scripts shell et de cron rend la gestion efficace.

#!/bin/bash
DATE=$(date +%F)
mysqldump -u root -p[password] mydb | gzip > /backup/mydb_$DATE.sql.gz

En plaçant un script comme celui-ci sous /etc/cron.daily/, vous pouvez collecter les sauvegardes automatiquement chaque jour.

Note : Pour des raisons de sécurité, évitez d’écrire les mots de passe directement. Il est recommandé de les gérer en toute sécurité avec un fichier .my.cnf.

Considérez également la sécurité

Les fichiers de sauvegarde peuvent contenir des informations sensibles. Mettez en œuvre les mesures suivantes :

• Définir des restrictions d’accès appropriées sur l’emplacement de stockage
• Chiffrer les sauvegardes pour le stockage et le transfert (par ex., en utilisant GPG ou SFTP)
• Lors du stockage dans le cloud, envisagez les paramètres de sauvegarde automatiques et la gestion du cycle de vie

7. Questions fréquemment posées (FAQ)

Dans cette section, nous résumons les questions courantes et les problèmes fréquemment rencontrés lors de l’utilisation de mysqldump sous forme de Q&R.

Q1. Pourquoi obtiens‑je une erreur « Access denied » lors de l’exportation ?

R. L’utilisateur MySQL spécifié peut ne pas disposer des privilèges requis tels que « SELECT » ou « LOCK TABLES » sur la base de données cible. Vérifiez les permissions nécessaires et demandez à votre administrateur de les accorder si besoin. Si le verrouillage des tables échoue, l’utilisation de l’option --single-transaction peut aider dans certains cas.

Q2. La taille du fichier de sauvegarde est extrêmement grande. Existe‑t‑il un moyen de la réduire ?

R. Si vous avez de grandes tables ou un volume de données important, le fichier SQL peut atteindre plusieurs gigaoctets. Vous pouvez réduire la taille en utilisant les méthodes suivantes :

• Compresser avec gzip (exemple : mysqldump ... | gzip > backup.sql.gz )
• Utiliser --no-data ou --no-create-info pour n’exporter que ce dont vous avez besoin
• Utiliser l’option --where pour filtrer les données exportées (par ex., plages de dates spécifiques)

Q3. Comment puis‑je exporter uniquement des tables spécifiques ?

R. Listez les noms des tables à la fin de la commande pour n’exporter que les tables sélectionnées.

mysqldump -u root -p mydb users orders > selected.sql

Ceci est pratique lorsque vous souhaitez sauvegarder uniquement certaines tables au lieu de l’intégralité de la base de données.

Q4. Les procédures stockées et les événements ne sont pas inclus dans l’exportation. Pourquoi ?

A. Par défaut, mysqldump n’inclut pas les procédures stockées (routines) ni les événements planifiés. Pour les inclure, ajoutez les options suivantes :

--routines --events

Vérifiez également que l’utilisateur dispose des privilèges suffisants pour accéder à ces objets.

Q5. À quoi faut‑il faire attention lors du transfert d’un fichier de sauvegarde vers un autre serveur ?

A. Points clés à considérer :

• Encodage des caractères : Si l’encodage diffère entre les serveurs, cela peut entraîner du texte illisible. Spécifiez explicitement --default-character-set=utf8 si nécessaire.
• Différences de version : Assurez-vous de la compatibilité entre les versions MySQL sur la source et la cible.
• Transfert de fichiers sécurisé : Utilisez scp , rsync ou SFTP pour des transferts sécurisés.
• Vérifications d’intégrité des fichiers : Vérifiez l’intégrité après le transfert à l’aide de md5sum ou sha256sum .

Q6. Existe‑t‑il des différences entre les commandes Windows et Mac/Linux ?

A. La syntaxe de base des commandes est la même, mais des différences existent dans le comportement du shell, le traitement par lots et l’utilisation de la commande date. Par exemple, lors de la génération de noms de fichiers avec des dates, Windows peut utiliser PowerShell ou la variable %DATE%, tandis que Linux et macOS utilisent la commande date.

8. Conclusion

Dans cet article, nous avons couvert l’outil de sauvegarde et de migration MySQL « mysqldump », de l’utilisation de base aux techniques avancées et au dépannage.

Bien que mysqldump utilise une syntaxe simple, choisir les bonnes options et configurations de commande en fonction de votre objectif fait une différence significative dans la qualité de la sauvegarde et la fiabilité de la restauration.

✅ Points clés de cet article

• Syntaxe de base de mysqldump et trois méthodes d’exportation (une seule, multiples et toutes les bases de données)
• Variantes d’exportation flexibles telles que schéma‑seulement, données‑seulement et tables sélectionnées
• Options importantes pour une utilisation en production incluant --single-transaction et --routines
• Commandes de restauration de base et comment gérer les erreurs d’importation
• Conseils pratiques tels que la compression gzip et l’automatisation avec cron
• Connaissances utiles de dépannage fournies dans la section FAQ

🛡 Bonnes pratiques pour l’utilisation de mysqldump

1. Ne vous contentez pas de créer des sauvegardes — vérifiez qu’elles peuvent être restaurées
2. Préparez‑vous aux problèmes causés par les différences de version et l’encodage des caractères
3. Concevez les opérations de sauvegarde avec compression, automatisation et contrôle d’accès approprié
4. Incluez les procédures stockées et les événements pour correspondre à votre configuration de production

Un système de sauvegarde correctement conçu et exploité avec mysqldump permet une récupération rapide en cas de pannes inattendues et contribue à la fiabilité des opérations système. En particulier pour les systèmes d’entreprise et les sites WordPress où la perte de données peut être critique, comprendre et utiliser efficacement mysqldump est essentiel.

Utilisez ce guide comme référence pour élaborer la stratégie de sauvegarde optimale pour votre environnement.