Importer et Exporter des Bases de Données MySQL en Utilisant la Ligne de Commande

Maîtriser les opérations d’importation et d’exportation de bases de données MySQL depuis la ligne de commande est une compétence incontournable pour tout administrateur de base de données ou ingénieur backend. L’utilitaire `mysqldump` exporte une base de données dans un fichier `.sql` portable contenant toutes les instructions DDL et DML nécessaires pour reconstruire entièrement le schéma et les données, tandis que la commande client `mysql` effectue l’opération inverse — en diffusant ce fichier vers une instance MySQL en cours d’exécution.

Ce guide couvre tous les scénarios pratiques : exports de base de données unique, dumps multi-bases, sauvegardes de structure uniquement, transferts compressés, gestion des jeux de caractères et workflows d’importation sécurisés — y compris les cas limites qui provoquent une corruption silencieuse des données ou des échecs de restauration en environnements de production.

Prérequis

Avant d’exécuter toute commande dans ce guide, vérifiez les points suivants :

• MySQL Server (5.7, 8.0 ou 8.4) est installé et le processus `mysqld` est en cours d’exécution
• Les binaires `mysqldump` et `mysql` se trouvent dans votre `PATH` système (confirmez avec `which mysqldump`)
• Vous disposez d’un compte MySQL avec au minimum les privilèges `SELECT`, `LOCK TABLES`, `SHOW VIEW` et `TRIGGER` pour l’export ; `CREATE`, `INSERT`, `ALTER` et `DROP` pour l’import
• Un espace disque suffisant existe sur la destination — un dump compressé peut s’étendre de 5 à 10 fois lors de l’importation
• Vous disposez d’un accès shell au serveur (terminal local, SSH ou un environnement VPS Hosting géré)

Exportation de bases de données avec mysqldump

`mysqldump` est l’outil de sauvegarde logique canonique fourni avec MySQL. Il sérialise les objets de base de données en un script SQL lisible par l’homme. Contrairement aux outils de sauvegarde physique tels que Percona XtraBackup, `mysqldump` est indépendant du moteur de stockage et fonctionne avec toutes les versions de MySQL et même les forks MariaDB.

1. Exporter une seule base de données

“`bash

mysqldump -u [username] -p [database_name] > [filename].sql

“`

Détail des paramètres :

• `-u [username]` — Compte MySQL utilisé pour la connexion
• `-p` — Déclenche une invite de mot de passe interactive (ne transmettez jamais le mot de passe en ligne comme `-p[password]` sur des systèmes partagés ; il est visible dans la sortie `ps aux`)
• `[database_name]` — Schéma cible à exporter
• `> [filename].sql` — Redirige la sortie standard vers le fichier de sortie

Exemple :

“`bash

mysqldump -u root -p mydatabase > mydatabase_backup.sql

“`

Cela produit un fichier contenant des instructions `CREATE TABLE`, `INSERT` et `ALTER TABLE` suffisantes pour recréer `mydatabase` de zéro.

Cas limite critique : Par défaut, `mysqldump` acquiert un verrou de lecture global (`FLUSH TABLES WITH READ LOCK`) au début du dump. Sur les serveurs InnoDB à fort trafic, utilisez plutôt `–single-transaction` pour prendre un instantané cohérent sans bloquer les écritures :

“`bash

mysqldump -u root -p –single-transaction mydatabase > mydatabase_backup.sql

“`

`–single-transaction` ne fonctionne de manière fiable qu’avec les tables InnoDB. Si votre base de données contient des tables MyISAM, le verrou est inévitable.

2. Exporter plusieurs bases de données

“`bash

mysqldump -u [username] -p –databases db1 db2 > multiple_databases_backup.sql

“`

L’indicateur `–databases` indique à `mysqldump` d’inclure les instructions `CREATE DATABASE` et `USE` dans la sortie, rendant le dump autonome. Sans cet indicateur, ces instructions sont omises et le dump suppose qu’une base de données cible est déjà sélectionnée au moment de l’importation.

Exemple :

“`bash

mysqldump -u root -p –databases db1 db2 > multiple_databases_backup.sql

“`

3. Exporter toutes les bases de données

“`bash

mysqldump -u root -p –all-databases > all_databases_backup.sql

“`

Cela exporte chaque schéma auquel l’utilisateur connecté peut accéder, y compris les bases de données système `mysql`, `information_schema` et `performance_schema`. Évitez d’importer des bases de données système entre des versions majeures de MySQL — le schéma de la table des privilèges a changé significativement entre MySQL 5.7 et 8.0, et l’importation de l’ancien schéma `mysql` dans une nouvelle instance 8.0 corrompra l’authentification.

Pour exclure les schémas système :

“`bash

mysqldump -u root -p –all-databases

–ignore-table=mysql.user

–ignore-table=mysql.db

> all_user_databases_backup.sql

“`

4. Exporter uniquement la structure des tables (sans données)

“`bash

mysqldump -u root -p –no-data mydatabase > structure_only.sql

“`

Cela est très utile pour le contrôle de version des schémas, les revues de code ou la mise en place d’un environnement de staging vide. La sortie contient uniquement les instructions DDL `CREATE TABLE`, `CREATE VIEW`, `CREATE PROCEDURE` et similaires — aucune ligne `INSERT`.

5. Exporter des tables spécifiques

“`bash

mysqldump -u root -p mydatabase table1 table2 > tables_backup.sql

“`

Notez que lors de l’exportation de tables spécifiques de cette façon, `mysqldump` n’inclut pas les instructions `CREATE DATABASE` ou `USE`. Vous devez vous assurer que la base de données cible existe avant l’importation.

6. Exporter avec les procédures stockées, les triggers et les événements

Par défaut, `mysqldump` inclut les triggers mais omet les procédures stockées, les fonctions et les événements planifiés. Pour une sauvegarde complète de l’application :

“`bash

mysqldump -u root -p

–routines

–triggers

–events

–single-transaction

mydatabase > full_backup.sql

“`

Oublier `–routines` est l’une des causes les plus fréquentes d’échec de restauration d’application — le schéma et les données sont présents mais la logique métier est manquante.

7. Export compressé

Redirigez la sortie directement via `gzip` pour réduire la taille du fichier de 60 à 80 % :

“`bash

mysqldump -u root -p mydatabase | gzip > mydatabase_backup.sql.gz

“`

Pour une compression maximale sur les grandes bases de données (au détriment du temps CPU) :

“`bash

mysqldump -u root -p mydatabase | gzip -9 > mydatabase_backup.sql.gz

“`

Importation de bases de données avec le client mysql

Le client en ligne de commande `mysql` accepte un fichier SQL via la redirection stdin et exécute chaque instruction séquentiellement contre le serveur cible.

1. Importer dans une base de données existante

“`bash

mysql -u [username] -p [database_name] < [filename].sql

“`

Exemple :

“`bash

mysql -u root -p mydatabase < mydatabase_backup.sql

“`

Important : Si le fichier `.sql` a été exporté avec `–databases` ou `–all-databases`, il contient déjà des directives `CREATE DATABASE` et `USE`. Dans ce cas, ne spécifiez pas de nom de base de données sur la ligne de commande — cela crée un conflit :

“`bash

mysql -u root -p < all_databases_backup.sql

“`

2. Importer dans une nouvelle base de données

La base de données cible doit exister avant de pouvoir y importer des données. MySQL ne la créera pas automatiquement à partir d’un dump de tables brut.

Étape 1 — Créer la base de données :

“`bash

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

“`

Étape 2 — Importer le dump :

“`bash

mysql -u root -p newdatabase < mydatabase_backup.sql

“`

Spécifiez toujours explicitement le jeu de caractères et la collation lors de la création de la base de données. Se fier aux valeurs par défaut du serveur est une source fréquente de discordances d’encodage, notamment lors de migrations entre serveurs avec des configurations `character_set_server` différentes.

3. Importer un dump compressé

“`bash

gunzip < mydatabase_backup.sql.gz | mysql -u root -p mydatabase

“`

Ou en utilisant `zcat` (équivalent sur la plupart des distributions Linux) :

“`bash

zcat mydatabase_backup.sql.gz | mysql -u root -p mydatabase

“`

4. Importer avec suivi de la progression

Pour les grands dumps, le client `mysql` standard ne fournit aucun retour. Utilisez `pv` (pipe viewer) pour afficher une barre de progression en temps réel :

“`bash

pv mydatabase_backup.sql | mysql -u root -p mydatabase

“`

Installez `pv` avec `apt install pv` ou `yum install pv`. Sur un Serveur Dédié gérant des bases de données de production de plusieurs gigaoctets, cette visibilité est opérationnellement critique.

mysqldump vs. méthodes de sauvegarde alternatives

Pour les environnements exécutant plusieurs bases de données de production sur un VPS avec cPanel géré, `mysqldump` reste l’option la plus universellement supportée en raison de sa compatibilité et de sa simplicité.

Configuration avancée et cas limites

Gestion correcte des jeux de caractères

Les discordances de jeux de caractères sont responsables d’une part disproportionnée des importations corrompues. L’approche la plus sûre est d’être explicite à chaque étape :

“`bash

mysqldump -u root -p

–default-character-set=utf8mb4

mydatabase > mydatabase_backup.sql

“`

“`bash

mysql -u root -p

–default-character-set=utf8mb4

mydatabase < mydatabase_backup.sql

“`

Remarque : `utf8` dans MySQL est un sous-ensemble de 3 octets qui ne peut pas stocker les caractères Unicode de 4 octets (emojis, certains idéogrammes CJK). Utilisez toujours `utf8mb4` pour les nouvelles bases de données.

Accélération des grandes importations

Par défaut, MySQL effectue un commit complet après chaque instruction `INSERT` dans le dump. Pour les grands ensembles de données, cela est catastrophiquement lent. Ajoutez ce qui suit au début de votre session d’importation :

“`bash

mysql -u root -p mydatabase <<EOF

SET foreign_key_checks = 0;

SET unique_checks = 0;

SET autocommit = 0;

SOURCE /path/to/mydatabase_backup.sql;

COMMIT;

SET foreign_key_checks = 1;

SET unique_checks = 1;

EOF

“`

Alternativement, exportez avec `–extended-insert` (activé par défaut) et `–disable-keys` pour regrouper les insertions et différer la reconstruction des index jusqu’après le chargement des données.

Automatisation des sauvegardes avec Cron

Une entrée de sauvegarde automatisée de niveau production dans `/etc/cron.d/mysql-backup` :

“`bash

0 2 * * * root mysqldump -u backup_user -p'StrongPass'

–single-transaction –routines –triggers –events

mydatabase | gzip > /backups/mydatabase_$(date +%F).sql.gz

“`

Utilisez un utilisateur MySQL dédié avec les privilèges minimaux requis plutôt que `root`. Créez-le avec :

“`sql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPass';

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

“`

Sécurisation des identifiants avec .my.cnf

Transmettre des mots de passe sur la ligne de commande les expose dans l’historique du shell et les listes de processus. Stockez les identifiants dans `~/.my.cnf` :

“`ini

[client]

user=root

password=YourSecurePassword

“`

Définissez immédiatement les permissions :

“`bash

chmod 600 ~/.my.cnf

“`

Avec cela en place, toutes les commandes `mysqldump` et `mysql` récupèrent automatiquement les identifiants sans les indicateurs `-u` et `-p`.

Export de base de données distante

Pour dumper une base de données depuis un serveur MySQL distant :

“`bash

mysqldump -h remote.server.com -P 3306 -u remoteuser -p remotedatabase > remote_backup.sql

“`

Assurez-vous que l’instance MySQL distante autorise les connexions depuis votre adresse IP et que le port 3306 est ouvert dans le pare-feu. Pour les transferts chiffrés, tunnelisez via SSH :

“`bash

ssh -L 3307:127.0.0.1:3306 user@remote.server.com -N &

mysqldump -h 127.0.0.1 -P 3307 -u remoteuser -p remotedatabase > remote_backup.sql

“`

Matrice de décision pratique

Points techniques clés à retenir

• Utilisez toujours `–single-transaction` pour les bases de données InnoDB afin d’éviter de bloquer les écritures de l’application pendant l’export
• Spécifiez toujours `utf8mb4` explicitement — ne vous fiez jamais aux hypothèses par défaut du serveur concernant le jeu de caractères
• Incluez `–routines`, `–triggers` et `–events` dans chaque sauvegarde complète d’application, sinon vous risquez de perdre la logique métier
• N’importez jamais les tables de schéma système (`mysql.*`) entre des versions majeures de MySQL
• Stockez les identifiants dans `~/.my.cnf` avec `chmod 600` — ne transmettez jamais les mots de passe comme arguments en ligne
• Pour les bases de données dépassant 10 Go, évaluez `mysqlpump` ou les utilitaires de dump parallèle de MySQL Shell, car `mysqldump` deviendra un goulot d’étranglement
• Vérifiez chaque sauvegarde en effectuant une restauration de test dans un environnement isolé avant de vous y fier pour la reprise après sinistre
• Lors de l’hébergement de plusieurs bases de données clients, isolez les environnements en utilisant des Panneaux de contrôle VPS séparés pour éviter les accès inter-locataires lors des opérations de restauration
• Associez votre stratégie de sauvegarde de base de données à un Certificat SSL valide sur toute couche d’application web se connectant à MySQL pour éviter l’interception des identifiants en transit

FAQ

Quelle est la différence entre mysqldump et mysqlpump ?

`mysqldump` est mono-thread et produit un seul fichier SQL — fiable et universellement compatible. `mysqlpump`, introduit dans MySQL 5.7.8, prend en charge l’export parallèle de plusieurs bases de données et tables simultanément, réduisant considérablement le temps de dump sur les serveurs multi-cœurs. Cependant, `mysqlpump` présente des problèmes connus avec les sauvegardes cohérentes de bases de données à moteurs mixtes et est moins adapté aux migrations inter-versions.

Puis-je importer un dump MySQL 5.7 dans MySQL 8.0 ?

Oui, avec des réserves. Les données utilisateur et les schémas d’application s’importent proprement. Cependant, n’importez jamais directement la base de données système `mysql` — le plugin d’authentification est passé de `mysql_native_password` à `caching_sha2_password` dans la version 8.0, et l’importation des anciennes tables de privilèges cassera l’authentification. Recréez les utilisateurs manuellement en utilisant les instructions `CREATE USER` et `GRANT`.

Pourquoi mon importation échoue-t-elle avec « ERROR 1005: Can’t create table » en raison de contraintes de clés étrangères ?

Cela se produit lorsque les tables sont importées dans un ordre qui viole les dépendances de clés étrangères. La solution consiste à faire précéder votre session d’importation de `SET foreign_key_checks = 0;` et à ajouter `SET foreign_key_checks = 1;` après la fin. Alternativement, exportez avec `–single-transaction` qui préserve l’intégrité référentielle dans le fichier dump lui-même.

Comment exporter uniquement les données sans les instructions CREATE TABLE ?

Utilisez l’indicateur `–no-create-info` : `mysqldump -u root -p –no-create-info mydatabase > data_only.sql`. Cela est utile lorsque vous devez recharger des données dans un schéma existant sans modifier sa structure.

Quelle est la méthode la plus sûre pour transférer un dump MySQL entre deux serveurs distants ?

Redirigez le dump directement via SSH sans écrire de fichier intermédiaire sur le disque : `mysqldump -u root -p sourcedb | ssh user@destination.server "mysql -u root -p targetdb"`. C’est à la fois plus rapide et plus sécurisé que de copier un fichier `.sql` en texte clair, notamment lorsque vous opérez sur un environnement d’Hébergement Web Mutualisé où les quotas de disque sont une contrainte.