Dans le travail de maintenance quotidien, des opérations d'exportation de données sont souvent nécessaires, et mysqldump est un outil fréquemment utilisé pour exporter les données. Il dispose de nombreux paramètres fonctionnels intégrés, et cet article présentera quelques opérations couramment utilisées.
Qu'est-ce que mysqldump ?
mysqldump est un programme client MySQL utilisé pour extraire des bases de données. Il génère principalement une série d'instructions SQL qui peuvent être encapsulées dans un fichier. Ce fichier contient toutes les commandes SQL nécessaires pour reconstruire votre base de données, telles que CREATE DATABASE, CREATE TABLE, INSERT, etc. Il peut être utilisé pour une migration ou une récupération rapide et légère de la base de données. C'est une méthode permettant de réaliser des sauvegardes logiques dans les bases de données MySQL.
Comment exporter une base de données MySQL à l'aide de mysqldump ?
1. Exporter toutes les bases de données
Cette commande exporte toutes les bases de données, y compris les bases de données système.
mysqldump -uroot -proot --all-databases > /tmp/all.sql
2. Exporter toutes les données des bases de données db1 et db2
mysqldump -uroot -proot --databases db1 db2 > /tmp/user.sql
Exporte plusieurs bases de données. Tous les paramètres suivants après l'option sont considérés comme des noms de bases de données.
Ajoutez une instruction DROP DATABASE avant de créer chaque base de données.
mysqldump -uroot -p --databases test --add-drop-database
3. Exporter les tables a1 et a2 depuis db1
Remarque : l'exportation de tables spécifiées ne peut être effectuée que pour une seule base de données, et le contenu exporté diffère de celui d'une exportation de base de données. Le texte exporté pour des tables spécifiées n'inclut pas d'instructions de création de la base de données ; il contient uniquement les instructions DROP TABLE, CREATE TABLE et INSERT.
mysqldump -uroot -proot --databases db1 --tables a1 a2 > /tmp/db1.sql
Remplacer le paramètre --databases et spécifier les tables à exporter.
mysqldump -uroot -p --host=localhost --databases test --tables test
Exporter tous les tablespaces.
mysqldump -uroot -p --databases test --all-tablespaces
Exclure toutes les informations sur les tablespaces de l'exportation.
mysqldump -uroot -p --databases test --no-tablespaces
Ajoutez une instruction DROP TABLE avant de créer chaque table (activé par défaut, utilisez --skip-add-drop-table pour désactiver).
mysqldump -uroot -p --databases test
mysqldump -uroot -p --databases test --skip-add-drop-table
4. Export conditionnel : exporter les enregistrements avec id=1 depuis la table a1 dans db1
L'exportation conditionnelle ne peut être effectuée que sur une seule table.
mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1' > /tmp/a1.sql
N'exporter que les enregistrements sélectionnés par la condition WHERE donnée. Si la condition contient des interpréteurs de commandes, des espaces ou des caractères spéciaux, elle doit être placée entre guillemets.
mysqldump -uroot -p --host=localhost --databases db1 --where="user='root'"
5. Exporter uniquement les structures de table, sans données
mysqldump -uroot -proot --no-data --databases db1 > /tmp/db1.sql
Exporter uniquement les données, sans ajouter d'instructions CREATE DATABASE.
mysqldump -uroot -p --host=localhost --databases db1 --no-create-db
Exporter uniquement les données, sans ajouter les instructions CREATE TABLE.
mysqldump -uroot -p --host=localhost --databases db1 --no-create-info
Exclure des tables spécifiques. Pour ignorer plusieurs tables, répétez l'option pour chaque table. Chaque table doit indiquer à la fois le nom de la base de données et celui de la table, par exemple : --ignore-table=database.table1 --ignore-table=database.table2 ...
mysqldump -uroot -p --host=localhost --databases db1 --ignore-table=mysql.user
Utiliser le mode d'insertion différé (INSERT DELAYED) lors de l'exportation des données.
mysqldump -uroot -p --databases db1 --delayed-insert
Utilisez des instructions INSERT avec plusieurs clauses VALUES. Cela réduit la taille du fichier d'exportation et accélère l'importation. Activé par défaut ; utilisez --skip-extended-insert pour désactiver.
mysqldump -uroot -p --databases db1
mysqldump -uroot -p --databases db1 --skip-extended-insert
6. Exporter et importer des données entre serveurs
mysqldump --host=h1 -uroot -proot --databases db1 | mysql --host=h2 -uroot -proot db2
Cela signifie exporter toutes les données de db1 sur le serveur h1 et les importer dans db2 sur h2. La base de données db2 doit exister, sinon une erreur se produira.
Utilisez un pipe nommé pour vous connecter à MySQL.
mysqldump -uroot -p --host=localhost --all-databases --pipe
mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp
Activer la compression pour la communication entre le client et le serveur.
mysqldump -uroot -p --all-databases --compress
7. Ajouter la position du binlog et le nom de fichier de la base de données principale au fichier d'exportation
Cette option, lorsqu'elle est exécutée sur un serveur esclave, équivaut à exécuter SHOW SLAVE STATUS.
Le régler sur 1 génère une commande CHANGE MASTER dans le fichier de données.
Le régler sur 2 ajoute des commentaires avant CHANGE MASTER.
Cette option active --lock-all-tables sauf si --single-transaction est spécifié.
Après l'exécution, --lock-tables est automatiquement désactivé. La valeur par défaut de --dump-slave est 1.
mysqldump -uroot -proot --dump-slave=1 --database db1 > /tmp/db1.sql
mysqldump -uroot -proot --dump-slave=2 --database db1 > /tmp/db1.sql
Exporter les événements.
mysqldump -uroot -p --all-databases --events
8. Vider les journaux avant l'exportation
Remarque : Si vous exportez plusieurs bases de données (--databases ou --all-databases), les journaux sont vidés pour chaque base de données. Si vous utilisez --lock-all-tables ou --master-data, les journaux sont vidés une seule fois, avec verrouillage de toutes les tables. Pour exporter et vider les journaux simultanément, utilisez --lock-all-tables ou --master-data conjointement avec --flush-logs.
mysqldump -uroot -p --all-databases --flush-logs
9. Assurer une cohérence de l'état lors de l'exportation
Avant d'exporter les données, cette option exécute une instruction SQL BEGIN. BEGIN ne bloque aucune application et garantit la cohérence de la base de données pendant l'exportation. Elle s'applique uniquement aux moteurs de stockage multi-versions (par exemple, InnoDB). Cette option est mutuellement exclusive avec --lock-tables, car LOCK TABLES valide implicitement toute transaction en attente. Pour exporter de grandes tables, utilisez-la avec l'option --quick.
mysqldump -uroot -p --host=localhost --all-databases --single-transaction
10. Exporter les procédures stockées et les fonctions
mysqldump -uroot -p --host=localhost --all-databases --routines
Une autre méthode : utiliser SELECT...INTO OUTFILE pour exporter des données MySQL
SELECT...INTO OUTFILE est une syntaxe MySQL utilisée pour exporter les résultats d'une requête dans un fichier. Dans MySQL, vous pouvez utiliser cette instruction pour exporter facilement des données vers un fichier texte.
Utilisation de base :
SELECT column1, column2, ...
INTO OUTFILE 'file_path'
FROM your_table
WHERE your_conditions;
Exemple :
SELECT id, name, email
INTO OUTFILE '/tmp/user_data.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM users;
Dans l'instruction SQL ci-dessus, nous avons sélectionné les colonnes id, nom et email de la table users et écrit les résultats dans le fichier /tmp/user_data.csv. FIELDS TERMINATED BY ',' précise le séparateur de colonne (virgule), et LINES TERMINATED BY '\n' précise le séparateur de ligne (saut de ligne).
Remarques importantes :
L'exécution de SELECT...INTO OUTFILE nécessite des autorisations appropriées, et le répertoire du fichier de sortie doit être un emplacement sur lequel le serveur MySQL peut écrire.
Dans l'exemple suivant, nous exportons les données de la table runoob_tbl vers le fichier /tmp/runoob.txt :
mysql> SELECT * FROM runoob_tbl
-> INTO OUTFILE '/tmp/runoob.txt';
Vous pouvez spécifier le format de sortie à l'aide des options de commande. L'exemple suivant exporte les données au format CSV :
mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';
Propriétés de SELECT ... INTO OUTFILE :
LOAD DATA INFILE est l'opération inverse de SELECT ... INTO OUTFILE. Pour écrire des données de la base de données dans un fichier, utilisez SELECT ... INTO OUTFILE, et pour relire le fichier dans la base de données, utilisez LOAD DATA INFILE.
Une instruction SELECT sous la forme SELECT...INTO OUTFILE 'file_name' écrit les lignes sélectionnées dans un fichier. Étant donné que le fichier est créé sur l'hôte du serveur, vous devez disposer du privilège FILE pour utiliser cette syntaxe.
Le fichier de sortie ne doit pas déjà exister, afin d'éviter la falsification des données.
Vous avez besoin d'un compte disposant d'un accès de connexion au serveur pour récupérer le fichier. Sinon, SELECT ... INTO OUTFILE ne fonctionnera pas.
Sous les systèmes UNIX, une fois le fichier créé, il est lisible et ses autorisations appartiennent au serveur MySQL. Cela signifie que, bien que vous puissiez lire le fichier, vous ne pourriez pas être en mesure de le supprimer.
Protection ultime pour votre écosystème de bases de données
Dans une ère où la résilience des données est essentielle, le choix d'une solution de sauvegarde dotée d'une évolutivité mondiale et d'une agilité multi-cloud est crucial. Vinchin Backup & Recovery se distingue par sa protection MySQL robuste, conçue spécifiquement pour les entreprises internationales :
✅ Stratégies complètes de sauvegarde
Prend en charge les stratégies de sauvegarde à chaud, de sauvegarde complète, de sauvegarde incrémentielle et de sauvegarde du journal des transactions ;
✅ Prise en charge universelle des bases de données
Unifiez la protection de MySQL, Oracle, SQL Server et d'autres bases de données dans des environnements hybrides (physique/cloud/VM).
Simplifiez la gestion avec une console unique pour les politiques de sauvegarde multi-bases de données.
✅ Renforcement anti-ransomware
Stockage de données de sauvegarde chiffré + fonction anti-logiciel rançonneur pour résister aux attaques malveillantes.
✅ Support mondial, efficacité locale
Assistance technique 24h/24 et 7j/7 et licences optimisées en coûts, idéal pour les PME et les équipes distribuées.
Pour créer des tâches de sauvegarde de base de données MySQL avec Vinchin, veuillez vous rendre sur la page Physical Backup > Database Backup > Backup :
1. Sélectionnez les bases de données qui doivent être sauvegardées.
2. Sélectionnez un nœud de sauvegarde sur lequel les données de sauvegarde doivent être traitées et stockées.
3. Configurez les stratégies de sauvegarde selon vos besoins.
4. Vérifiez et confirmez les paramètres.
Vinchin est une solution de confiance pour les institutions financières et les infrastructures informatiques multinationales dans plus de 60 pays. Cliquez sur le bouton ci-dessous pour tester la version d'essai gratuite de 60 jours de Vinchin et découvrir une solution de sauvegarde des données efficace et fiable !
Conclusion
Dans cet article, nous avons exploré diverses options de mysqldump pour exporter efficacement les données d'une base de données MySQL, allant des exports complets de bases de données à des exports plus précis, tels que des tables individuelles ou des enregistrements spécifiques. Comprendre ces commandes et leurs fonctionnalités permet d'optimiser les processus de sauvegarde, de migration et de récupération des bases de données.
