Le guide ultime sur mysqldump – Un programme de sauvegarde de base de données

mysqldump est un utilitaire de ligne de commande fourni par MySQL qui vous permet de créer des sauvegardes logiques des bases de données MySQL. Cet outil est largement utilisé pour créer des sauvegardes de bases de données, exporter des bases de données vers d’autres serveurs et migrer des bases de données entre des versions de MySQL ou des systèmes différents. C’est un moyen simple, puissant et flexible de sauvegarder et de restaurer des bases de données, ce qui en fait un outil essentiel pour tout administrateur de bases de données.

Ce guide ultime couvre tout ce que vous devez savoir sur mysqldump, y compris l’installation, la syntaxe, les exemples d’utilisation et les meilleures pratiques.

Qu’est-ce que mysqldump ?

• mysqldump crée une sauvegarde logique d’une base de données MySQL, ce qui signifie qu’il produit les données et la structure sous forme de commandes SQL.
• Ces commandes SQL peuvent ensuite être utilisées pour recréer la base de données, ce qui rend mysqldump idéal pour les sauvegardes, les migrations et le transfert de données.
• Il prend en charge l’exportation de bases de données entières, de tables individuelles ou de données spécifiques avec diverses options de personnalisation.

Syntaxe de base de mysqldump

La syntaxe de base de la commande mysqldump est la suivante :

• [OPTIONS] : Indicateurs optionnels qui modifient le comportement de mysqldump (par exemple, spécifier l’utilisateur, le mot de passe, l’hôte, etc.)
• nom_de_la_base_de_données : Le nom de la base de données que vous souhaitez sauvegarder.
• [tables]: (Facultatif) Tables spécifiques à sauvegarder. En cas d’omission, la base de données entière est sauvegardée.
• > backup_file.sql : Redirige la sortie vers un fichier (backup_file.sql), qui contiendra les commandes SQL pour recréer la base de données.

Conditions préalables

• MySQL ou MariaDB installé sur le serveur ou le client.
• Un compte d’utilisateur avec des privilèges suffisants (autorisations SELECT et LOCK) sur la base de données à sauvegarder.
• Accès à la ligne de commande (terminal) sous Linux/macOS ou à l’invite de commande sous Windows.

Options couramment utilisées avec mysqldump

• -u ou –user : Spécifie le nom d’utilisateur MySQL.
• -p ou –password : Demande le mot de passe de l’utilisateur.
• -h ou –host : Spécifie l’hôte (par exemple, localhost ou adresse IP).
• -P ou –port : Indique le numéro de port du serveur MySQL.
• –databases : Permet de sauvegarder plusieurs bases de données.
• –all-databases : Sauvegarde toutes les bases de données du serveur.
• –no-data : Exporte uniquement la structure de la base de données (pas de données).
• –single-transaction : Prend un instantané cohérent de la base de données, utile pour les tables InnoDB.
• –add-drop-table : Ajoute des instructions DROP TABLE avant les instructions CREATE TABLE, ce qui garantit que les tables sont supprimées et recréées lors de la restauration.
• –routines : Inclut les procédures stockées et les fonctions dans la sauvegarde.
• –triggers : Inclut les déclencheurs dans la sauvegarde.
• –Compresser : Compresse les données envoyées entre le client et le serveur MySQL si cette fonction est prise en charge.

Exemples de base

Exemple 1 : Sauvegarde d’une seule base de données

• Demande le mot de passe de l’utilisateur root.
• Le contenu de nom_de_base de données est déchargé dans fichier_de_sauvegarde.sql.

Exemple 2 : Sauvegarde de plusieurs bases de données

• Utilisez l’option –databases suivie des noms des bases de données.
• La base de données 1 et la base de données 2 sont transférées dans le fichier multiple_databases_backup.sql.

Exemple 3 : Sauvegarde de toutes les bases de données

• Sauvegarde toutes les bases de données sur le serveur MySQL.
• Inclut les instructions CREATE DATABASE et USE, ce qui facilite la restauration de toutes les bases de données.

Exemple 4 : Sauvegarde d’une table spécifique

• Extrait la structure et les données d’une table spécifique (nom_de_la_table) de nom_de_la_base_de_données dans table_backup.sql.

Exemple 5 : Sauvegarder uniquement la structure de la base de données

• L’option –no-data exclut les données et n’exporte que les structures des tables.

Exemples avancés

Exemple 6 : Utilisation de –single-transaction pour les grandes bases de données

• L’option –single-transaction est idéale pour les tables InnoDB et assure une sauvegarde cohérente sans verrouiller les tables.
• Elle est recommandée pour les bases de données volumineuses car elle minimise le temps de verrouillage des tables.

Exemple 7 : Sauvegarde compressée

• Utilise gzip pour compresser la sortie de mysqldump à la volée.
• Il en résulte un fichier de sauvegarde plus petit (backup_file.sql.gz), ce qui permet d’économiser de l’espace disque.

Exemple 8 : inclusion de procédures stockées, de fonctions et de déclencheurs

• L’option –routines inclut les procédures stockées et les fonctions.
• L’option –triggers inclut les triggers dans la sauvegarde.

Exemple 9 : Exclusion de tables spécifiques

• L’option –ignore-table exclut une table spécifique de la sauvegarde.
• Vous pouvez répéter cette option pour chaque table que vous souhaitez exclure.

Restauration d’une sauvegarde mysqldump

Pour restaurer une sauvegarde créée avec mysqldump, vous utilisez la commande mysql :

Exemple 10 : Restauration d’une seule base de données

• Demande le mot de passe root.
• Restaure la base de données à partir du fichier backup_file.sql.

Exemple 11 : Restauration de plusieurs bases de données ou de toutes les bases de données

• Cette commande peut être utilisée pour restaurer plusieurs bases de données ou toutes les bases de données si all_databases_backup.sql a été créé en utilisant les options –databases ou –all-databases.

Exemple 12 : Restauration à partir d’une sauvegarde compressée

• Utilise gunzip pour décompresser le fichier de sauvegarde à la volée et envoie la sortie à la commande mysql.

Meilleures pratiques pour l’utilisation de mysqldump

1. Utilisez –single-transaction pour InnoDB :
   • Cette option garantit une sauvegarde cohérente sans verrouiller les tables de la base de données, ce qui est particulièrement important pour les bases de données volumineuses.
2. Planifier des sauvegardes régulières :
   • Utilisez les tâches cron sous Linux pour automatiser le processus de sauvegarde. Par exemple, créez une tâche de sauvegarde quotidienne :
     0 2 * * * * mysqldump -u root -pVotreMotDePasse nom_de_base > /path/to/backup/nom_de_base_$(date \%F).sql
   • Ce job crée une sauvegarde tous les jours à 2 heures du matin.
3. Conserver les sauvegardes hors site :
   • Stockez les sauvegardes sur un autre serveur ou sur un espace de stockage en nuage comme AWS S3 ou Google Drive pour garantir la récupération des données en cas de défaillance matérielle.
4. Vérifier les sauvegardes :
   • Vérifiez régulièrement que vos sauvegardes peuvent être restaurées correctement en les testant dans un environnement distinct.
5. Utilisez la compression pour les grandes bases de données :
   • Utilisez gzip ou bzip2 pour compresser les fichiers de sauvegarde et économiser de l’espace disque.
6. Considérations de sécurité :
   • Évitez de spécifier des mots de passe directement dans la ligne de commande, car ils peuvent être visibles par d’autres utilisateurs. Utilisez plutôt un fichier de configuration ou demandez le mot de passe.
   • Protégez les fichiers de sauvegarde par des autorisations appropriées afin d’empêcher tout accès non autorisé.

Conclusion

mysqldump est un outil polyvalent et puissant pour gérer les sauvegardes des bases de données MySQL. En comprenant sa syntaxe et ses options, vous pouvez créer des sauvegardes sur mesure qui répondent à vos besoins spécifiques, qu’il s’agisse de traiter des bases de données volumineuses, de migrer des données ou de mettre en place des sauvegardes automatisées. Suivez les meilleures pratiques pour vous assurer que vos données sont toujours sûres, sécurisées et restaurables en cas de besoin.