Les administrateurs de base de données et les administrateurs système doivent effectuer des sauvegardes de leur base de données MySQL, et l’utilitaire mysqldump est l’outil le plus couramment utilisé pour convertir une base de données entière ou un seul objet en fichier texte. Une fois que l’utilitaire mysqldump a créé un fichier de sauvegarde, il peut être utilisé pour restaurer les données sur un serveur ou les migrer vers une autre base de données.
Qu’est-ce que mysqldump ?
L’outil mysqldump est un utilitaire de ligne de commande fourni avec MySQLpour l’exportation d’instructions SQL pouvant reconstruire une base de données ou un sous-ensemble d’objets dans une base de données. Il peut être utilisé pour créer une sauvegarde des objets et des données de la base de données, ou pour transférer une base de données d’un serveur à un autre. Les administrateurs peuvent utiliser mysqldump pour exporter des données dans un format spécifique tel que XML et CSV.
Comment utiliser mysqldump pour sauvegarder une seule table
L’utilitaire mysqldump peut être utilisé pour exporter uniquement des tables spécifiques dans une base de données. Par exemple, supposons que vous ne souhaitiez exporter qu’une seule table client à partir d’une base de données d’e-commerce. Les données du tableau client peuvent ensuite être utilisées pour envoyer des e-mails marketing. L’utilitaire mysqldump exportera la table client et ses données dans un fichier texte.
Note : Pour exécuter n'importe quelle commande dans mysqldump, le nom d'utilisateur utilisé pour accéder à la base de données doit disposer des autorisations appropriées pour accéder aux objets. Sans accès, la commande n’exporte aucun schéma, objet ou donnée.
Vous devez spécifier le nom de la base de données et le nom de la table pour pouvoir exporter les objets vers un fichier texte. Si vous souhaitez vérifier que vous avez le bon nom de table, assurez-vous d’abord que vous êtes authentifié dans la base de données avec laquelle vous souhaitez travailler. Ensuite, exécutez la commande SQL suivante dans la ligne de commande MySQL ou dans l’application Workbench :
SHOW TABLES;
Après avoir trouvé le bon nom de table, vous pouvez l’exporter dans un fichier texte. La commande suivante permet d'exporter la table Client de la base de données myDB vers le fichier customers.sql :
mysqldump -u username -p password myDB Customer > customers.sql
Les termes « nom d’utilisateur » et « mot de passe » doivent être échangés avec votre propre nom d’utilisateur et mot de passe. Vous pouvez exporter plusieurs tables en séparant chaque table par un espace. Les tableaux Client et Commande sont exportés vers customers_orders.sql :
mysqldump -u username -p password myDB Customer Order > customers_orders.sql
Après avoir exporté les instructions SQL dans un fichier, vous pouvez ouvrir le fichier SQL avec un éditeur de texte standard ou l’ouvrir avec MySQL Workbench. MySQL Workbench est une installation distincte de la base de données MySQL, mais vous pouvez la télécharger gratuitement depuis le site Web MySQL. Il est préférable de l’ouvrir avec Workbench, car vous pouvez exécuter les instructions et importer des données sur le serveur de base de données MySQL cible.
Exemples de mysqldump
L’utilitaire mysqldump est principalement utilisé pour les sauvegardes ou le transfert de données vers un autre serveur de base de données. Que vous effectuiez une sauvegarde de votre base de données ou que vous souhaitiez transférer des données vers un autre serveur, l’utilitaire mysqldump fonctionne de la même manière. Une sauvegarde mysqldump est couramment utilisée pour créer un environnement de test à partir de la base de données de production.
Pour utiliser une base de données de production afin de créer un environnement de test, vous pouvez effectuer une sauvegarde complète de votre base de données à l’aide de l’utilitaire mysqldump. La commande suivante effectue une sauvegarde de l’ensemble de votre base de données appelée myDB et l’exporte vers un fichier nommé myDB.sql :
mysqldump -u username -p password -databases myDB > myDB.sql
Maintenant, connectez-vous à votre serveur de test. MySQL doit également être installé pour que vous puissiez importer les données exportées. Notez que la commande d’exportation utilise le caractère « > » pour déplacer les données d’une base de données vers un fichier SQL. Le processus d’importation utilise le caractère « < ». La commande suivante importe toutes les données du fichier myDB.sql dans un environnement de test avec la même base de données appelée myDB :
mysqldump -u username -p password -databases myDB < myDB.sql
Avec les deux commandes ci-dessus, vous répliquez les données de la base de données myDB originale en production vers votre environnement de test. L’environnement de test permet aux développeurs et aux responsables de l’assurance qualité de travailler avec des données réelles et une structure de base de données qui imite les problèmes des clients ou des employés pour le dépannage.
Utilisation de base de mysqldump
Le fichier .sql créé pendant un mysqldump est un fichier texte simple. Vous pouvez l’ouvrir dans n’importe quel éditeur de texte, mais il est préférable de l’ouvrir avec MySQL Workbench. Les commandes de codes couleur et les types de données Workbench facilitent la lecture des commandes SQL.
Vous pouvez ouvrir le fichier .sql dans Workbench à l’aide du menu Fichier. Cliquez sur « Ouvrir le script SQL » dans la boîte de dialogue et choisissez le fichier .sql que vous souhaitez lire. Une fois le fichier .sql ouvert, vous pouvez le lire et l’exécuter avec l’élément de menu « Exécuter le script SQL », si vous choisissez d’exécuter les commandes. Workbench s’exécute sur Windows et Linux, vous pouvez donc exécuter un script de cette manière sur les deux systèmes d’exploitation.
Vous pouvez également exécuter un fichier mysqldump sous Windows et Linux à l’aide de l’utilitaire mysqldump. La commande suivante exécute les instructions SQL sur une base de données MySQL sous Windows et Linux à partir de l'utilitaire mysqldump :
mysqldump -u username -p password -databases myDB < myDB.sql
La commande ci-dessus importe les données du fichier myDB.sql mysqldump et exécute les instructions SQL sur le serveur de base de données local. La commande importe les données dans la base de données myDB.
Si aucune donnée n’est présente dans la base de données ou dans l’une des tables de la base de données, les structures de la base de données sont toujours créées et la base de données est ajoutée au schéma. Les structures de table sont disponibles pour vous permettre d’y ajouter des données à l’avenir.
Utilisation avancée de mysqldump
Une grande base de données peut contenir des tables contenant des téraoctets de données. Un trop grand nombre d’exportations pourrait épuiser les ressources de stockage, mais vous pouvez compresser chaque fichier pour réduire l’espace de stockage nécessaire aux exportations de données MySQL. La compression d’un fichier permet d’économiser plusieurs gigaoctets d’espace de stockage nécessaires pour les fichiers très volumineux. La commande mysqldump suivante exporte la base de données myDB et la compresse à l’aide de l’utilitaire gzip vers un fichier nommé myDB.sql.gz :
mysqldump -u username -p password -databases myDB | gzip myDB.sql.gz
Les sauvegardes contiennent souvent des données sensibles. Il est donc nécessaire de les chiffrer pour assurer la conformité et la protection des données. Vous avez besoin d’un outil tiers pour chiffrer les données. Dans cet exemple, nous utiliserons le chiffrement pour chiffrer le fichier myDB.sql. Vous avez besoin d’une clé (similaire à une phrase secrète) pour chiffrer un fichier, mais vous devez vous souvenir de cette clé pour le déchiffrer. L’utilitaire de chiffrement vous invite à saisir une clé lorsque vous chiffrez le fichier, ou vous pouvez stocker une clé dans un fichier sécurisé où elle peut être réutilisée pour chiffrer et déchiffrer un fichier .sql.
Dans l’exemple suivant, le fichier .sql exporté est chiffré à l’aide d’une clé stockée dans le fichier nommé mykey.key :
mysqldump -u username -p password -databases myDB | ccrypt -k mykey.key > myDB.sql.cpt
Notez que les données sont exportées vers un fichier .cpt. Ce fichier .cpt permet de stocker toutes les données chiffrées. Vous avez besoin de la même clé pour déchiffrer le fichier. Vous pouvez le déchiffrer à l’aide de la virgule suivante :
cdecrypt -k mykey.key myDB.sql.cpt
La plupart des bases de données contiennent plus de tables et données. Les déclencheurs et les procédures doivent également être sauvegardés. Par défaut, l’utilitaire mysqldump exporte les déclencheurs avec une exportation standard, mais les administrateurs peuvent indiquer explicitement à la commande de les exporter. Les procédures ne sont pas exportées par défaut, les administrateurs doivent donc les exporter explicitement. La commande suivante exporte les déclencheurs et les procédures ainsi que d’autres objets et données de base de données :
mysqldump -u nom d’utilisateur -p mot de passe -bases de données myDB –triggers=true –routines=true > myDB.sql
Bonnes pratiques pour mysqldump
Chaque fois que vous devez utiliser l’utilitaire mysqldump, vous pouvez l’utiliser librement sans interrompre la productivité. Elle est particulièrement utile lorsque vous devez effectuer des sauvegardes ou des exportations fréquentes de votre base de données et de ses données. Les sauvegardes doivent être effectuées souvent afin que les données puissent être récupérées en cas de défaillance de la base de données, de logiciel malveillant ou de corruption des données.
La fréquence des sauvegardes dépend de la quantité de données que vous ajoutez à une base de données chaque jour et de la fréquence des modifications apportées à la base de données. En général, la fréquence à laquelle vous utilisez la commande mysqldump dépend de la quantité de données pouvant être perdues sans avoir d’impact négatif sur les revenus et la continuité des opérations. Vous n’avez pas toujours besoin d’une sauvegarde complète, mais certaines entreprises utilisent l’utilitaire mysqldump une fois par semaine et d’autres l’utilisent une fois par jour. Déterminez la fréquence des sauvegardes complètes nécessaires à votre entreprise et utilisez des outils d’automatisation pour exécuter mysqldump à un intervalle défini.
Conclusion
La gestion d’une base de données est un travail à temps plein, mais l’utilitaire mysqldump rend la gestion beaucoup plus efficace. Vous pouvez l’utiliser pour des sauvegardes complètes de vos données ou les utiliser pour transférer des données entre serveurs. Il est compatible avec Windows ou Linux, ce qui vous permet de l’utiliser sur n’importe quel environnement hébergeant votre serveur MySQL.
