MySQL mysqldump –single-transaction expliqué : sauvegardes cohérentes sans verrouillage des tables

1. L’importance des sauvegardes pour maintenir la cohérence de la base de données

Les sauvegardes de bases de données sont une « assurance »

Dans le monde des affaires, la base de données peut être considérée comme le cœur des opérations. Les informations client, l’historique des transactions, les données d’inventaire — presque chaque processus métier dépend de la base de données. Si des données sont perdues à cause d’une défaillance du système ou d’une erreur humaine, cela peut entraîner des risques graves tels que des temps d’arrêt opérationnels et une perte de confiance.
Pour cette raison, les sauvegardes régulières constituent une « police d’assurance » opérationnelle essentielle.

Comment garantir la cohérence des données lors des sauvegardes ?

Cependant, l’un des principaux défis lors de la réalisation de sauvegardes est d’assurer la « cohérence » (intégrité des données). En particulier lorsque vous souhaitez sauvegarder un système sans l’arrêter pendant son fonctionnement, maintenir la cohérence devient crucial.

Par exemple, si quelqu’un met à jour une table pendant le processus de sauvegarde, des divergences peuvent apparaître entre les données exportées et l’état réel de la base de données.

Qu’est‑ce que mysqldump ? Et qu’est‑ce que –single-transaction ?

L’outil de sauvegarde le plus utilisé pour MySQL et MariaDB est mysqldump. Avec des opérations simples en ligne de commande, vous pouvez exporter une base de données entière ou des tables spécifiques dans un fichier SQL.

L’option utilisée pour maintenir la cohérence des sauvegardes de manière sûre et efficace dans mysqldump est le sujet de cet article : --single-transaction.

Cette option utilise des transactions (unités de travail) pour préserver un « instantané d’un point précis dans le temps » tout en permettant aux sauvegardes de se poursuivre sans bloquer les autres processus. C’est une fonctionnalité extrêmement utile.

Ce que vous apprendrez dans cet article

Dans cet article, nous expliquons en détail comment utiliser l’option mysqldump --single-transaction, les considérations importantes, et comment la combiner avec d’autres options d’un point de vue pratique.

• Ceux qui souhaitent comprendre les bases de mysqldump
• Ceux qui veulent comprendre correctement la signification de --single-transaction
• Ceux qui souhaitent mettre en œuvre des opérations de sauvegarde sécurisées dans des environnements réels

Nous parcourrons des exemples de commandes pratiques de manière claire et facile à comprendre. Assurez‑vous de lire jusqu’à la fin.

2. Utilisation de base de mysqldump

Qu’est‑ce que mysqldump ?

mysqldump est l’outil en ligne de commande standard pour sauvegarder les bases de données MySQL et MariaDB. Il exporte les structures de tables et les données au format SQL, et la restauration est aussi simple que d’exécuter le fichier SQL pour ramener la base de données à son état d’origine.

Il est à la fois pratique et puissant, et largement utilisé tant en développement qu’en production.

Syntaxe de base de mysqldump

La forme la plus simple de la commande mysqldump est la suivante :

mysqldump -u username -p database_name > output_file.sql

• -u : Nom d’utilisateur utilisé pour se connecter à MySQL
• -p : Invite à saisir le mot de passe (ne peut pas être omis)
• database_name : La base de données à sauvegarder
• > : Spécifie le fichier de sortie (redirection)

Après l’exécution de la commande, il vous sera demandé un mot de passe. En cas de succès, un fichier de sauvegarde au format SQL sera généré.

Sauvegarder uniquement des tables spécifiques

Si vous souhaitez sauvegarder des tables spécifiques plutôt que la base de données entière, indiquez les noms des tables comme illustré ci‑dessous :

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

Par exemple, pour sauvegarder uniquement les tables users et orders :

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

Sauvegarder plusieurs bases de données

L’option -B permet de sauvegarder plusieurs bases de données en une seule fois :

mysqldump -u username -p -B database1 database2 > multi_backup.sql

Cette méthode inclut les instructions CREATE DATABASE, ce qui la rend pratique pour la restauration.

Sauvegarder toutes les bases de données en une fois

Si vous avez besoin d’une sauvegarde complète du système, utilisez -A (ou --all-databases) :

mysqldump -u username -p -A > all_databases.sql

Cette commande exporte toutes les bases de données du serveur MySQL, y compris les bases de données système telles que mysql et information_schema, ce qui la rend adaptée aux migrations d’environnement.

Comment restaurer une sauvegarde

Un fichier SQL créé avec mysqldump peut être restauré en utilisant la commande suivante :

mysql -u username -p database_name < output_file.sql

Cela vous permet de restaurer les données de sauvegarde dans un nouvel environnement ou de récupérer après une corruption.

3. Détails de l’option –single-transaction

La clé pour maintenir la cohérence pendant les sauvegardes

Par défaut, la commande mysqldump exporte les tables une par une en séquence. En conséquence, si un autre utilisateur modifie des données pendant le processus de sauvegarde, un état « intermédiaire » peut être enregistré, ce qui peut rompre la cohérence des données lors de la restauration.

L’option qui résout ce problème est --single-transaction.

Comment fonctionne –single-transaction

Lorsque vous utilisez cette option, mysqldump exécute une instruction BEGIN au début du processus d’exportation pour démarrer une transaction. Cela crée un instantané de la base de données à ce moment‑là. Même si d’autres transactions effectuent des modifications pendant l’exportation, la sauvegarde peut se terminer sans être affectée.

En d’autres termes, cela vous permet de sauvegarder l’intégralité de la base de données telle qu’elle existait au moment exact où l’exportation a commencé—ainsi la cohérence est préservée.

Fonctionne uniquement avec InnoDB

Un point crucial à comprendre est que --single-transaction est efficace uniquement pour les tables utilisant le moteur de stockage InnoDB. InnoDB prend en charge les transactions, ce qui permet de créer et de maintenir des instantanés.

En revanche, si vous utilisez des moteurs de stockage non transactionnels tels que MyISAM ou MEMORY, --single-transaction ne garantira pas la cohérence attendue. Dans ces cas, vous devrez peut‑être recourir à des alternatives basées sur le verrouillage comme --lock-tables ou --lock-all-tables.

Différence avec les verrous de tables

Par défaut, mysqldump verrouille les tables pour maintenir la cohérence (car --lock-tables est activé automatiquement). Cependant, cette approche présente un inconvénient majeur : les autres utilisateurs ne peuvent pas mettre à jour les données—ce qui signifie que le service peut être effectivement interrompu.

Avec --single-transaction, vous pouvez sauvegarder sans verrous, ce qui signifie que vous pouvez effectuer des sauvegardes sans arrêter le service. Cela est extrêmement précieux dans les environnements de production.

Illustration (explication textuelle)

[Regular mysqldump]
Time passes → [Start dumping users table] → [Data changes mid-way] → [Start dumping orders table] → Inconsistency occurs

[Using --single-transaction]
Time passes → [Create snapshot with BEGIN] → [Dump users and orders from the same consistent point in time] → Safe backup completed

Important : Pas efficace contre les opérations DDL

--single-transaction est puissant contre les modifications de données (INSERT, UPDATE, DELETE), mais il n’est pas efficace contre les changements de schéma (DDL) tels que CREATE, DROP ou ALTER. Si un DDL est exécuté pendant une sauvegarde, des erreurs ou des incohérences peuvent survenir.

Pour cette raison, il est idéal de planifier les sauvegardes pendant une fenêtre de maintenance ou à un moment où les changements DDL peuvent être évités.

Résumé

--single-transaction est une méthode extrêmement efficace pour obtenir des sauvegardes cohérentes sans arrêter le service dans les environnements MySQL qui utilisent InnoDB. En comprenant son fonctionnement et en l’utilisant correctement, vous pouvez assurer une protection des données sûre et efficace.

4. Comment utiliser l’option –single-transaction

Exemple de commande de base

Commençons par la façon la plus simple d’utiliser --single-transaction :

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

Cette commande enregistre l’état de la base de données au moment où la transaction commence (pour les bases de données utilisant InnoDB). Un avantage majeur est qu’elle n’interfère pas avec les autres traitements pendant la sauvegarde, vous pouvez donc l’exécuter sans arrêter le service.

Sauvegarde de plusieurs tables

Vous pouvez également appliquer --single-transaction uniquement à des tables spécifiques :

mysqldump --single-transaction -u root -p shop_db users orders > users_orders.sql

Même en spécifiant des tables individuelles, mysqldump crée un instantané cohérent et exporte les données depuis le même point dans le temps.

Sauvegarde de plusieurs bases de données

Pour sauvegarder plusieurs bases de données, combinez‑le avec -B (ou --databases) :

mysqldump --single-transaction -u root -p -B db1 db2 > multi_db_backup.sql

Ce format inclut des instructions CREATE DATABASE pour chaque base de données, améliorant la commodité lors des restaurations.

Sauvegarde de toutes les bases de données

Si vous avez besoin d’une sauvegarde complète du serveur, utilisez‑le avec -A (ou --all-databases) :

mysqldump --single-transaction -u root -p -A > full_backup.sql

Cela exporte toutes les bases de données du serveur MySQL (y compris mysql, information_schema, performance_schema, etc.), ce qui le rend utile pour les migrations de serveur et les restaurations complètes.

Vérifications clés avant l’exécution

• Assurez‑vous que le moteur de stockage est InnoDB --single-transaction n’est efficace que pour InnoDB. Avec des moteurs non transactionnels comme MyISAM, il ne se comportera pas comme prévu.
• Ne le combinez pas avec --lock-tables Parce que --single-transaction et --lock-tables sont des comportements conflictuels, les combiner peut rompre les garanties de cohérence. Par mesure de sécurité, il est recommandé d’ajouter explicitement --skip-lock-tables .

Exemple recommandé commun (meilleure pratique)

mysqldump --single-transaction --quick --skip-lock-tables -u root -p production_db > backup.sql

Cette configuration possède les caractéristiques suivantes :

• --quick : Réduit l’utilisation de la mémoire en écrivant les lignes au fur et à mesure qu’elles sont lues, plutôt qu’en chargeant tout en mémoire d’un coup
• --skip-lock-tables : Évite explicitement les verrous automatiques pour garantir un comportement sûr

Exemple d’automatisation avec un script shell

Dans les opérations réelles, il est courant de script les sauvegardes régulières et de les exécuter automatiquement avec cron, etc.

#!/bin/bash
DATE=$(date +%F)
mysqldump --single-transaction --quick --skip-lock-tables -u root -pYourPassword production_db > /backups/production_$DATE.sql

Remarque : Il est recommandé de gérer les mots de passe à l’aide de variables d’environnement ou de fichiers de configuration.

5. Considérations importantes lors de l’utilisation de –single-transaction

Pas d’effet sur les moteurs non transactionnels (ex. : MyISAM)

Cette option n’est efficace que pour les moteurs de stockage transactionnels (principalement InnoDB). Des moteurs comme MyISAM et MEMORY ne supportent pas les transactions, donc spécifier --single-transaction ne garantit pas la cohérence.

Exemple :

• La table users utilise InnoDB
• La table logs utilise MyISAM

Dans un tel environnement mixte, la table users reste cohérente, mais la table logs peut être affectée par des opérations concurrentes pendant la sauvegarde.

Mesures correctives :

• Standardisez sur InnoDB autant que possible.
• Si MyISAM ou d’autres moteurs sont mélangés, envisagez d’utiliser --lock-all-tables .

Pas efficace contre les opérations DDL (modifications de schéma)

Bien que --single-transaction fonctionne bien contre les opérations de données (SELECT, INSERT, UPDATE, DELETE), il ne protège pas contre les opérations DDL (CREATE, DROP, ALTER, etc.).

Si les définitions de tables changent pendant un dump, les risques incluent :

• Une table est supprimée en cours de dump → une erreur se produit
• La définition d’une table change pendant le dump → incohérence du schéma

Mesures correctives :

• Établissez une règle pour éviter les DDL pendant les sauvegardes.
• Effectuez les sauvegardes pendant une fenêtre de maintenance autant que possible.

Ne pas combiner avec --lock-tables

Par défaut, mysqldump active --lock-tables, mais ce comportement entre en conflit avec --single-transaction. Les verrous de tables sont appliqués avant le début de la transaction, ce qui peut rompre les garanties de cohérence.

Par conséquent, lors de l’utilisation de --single-transaction, il est recommandé d’ajouter explicitement --skip-lock-tables.

mysqldump --single-transaction --quick --skip-lock-tables -u root -p dbname > backup.sql

La cohérence n’est garantie qu’au point de départ

--single-transaction capture un instantané de la base de données au moment où la transaction commence. Toute modification effectuée par la suite n’est naturellement pas incluse dans la sauvegarde.

Cela évite les conflits de verrouillage pendant la sauvegarde, mais il est important de comprendre qu’il représente un instantané à un point spécifique dans le temps.

Utilisez –quick pour les grands ensembles de données

Lors de la sauvegarde de grands ensembles de données, mysqldump peut tenter de charger des tables entières en mémoire par défaut, ce qui peut potentiellement causer un épuisement de la mémoire ou un swapping.

Dans de tels cas, combinez-le avec --quick, qui lit et sort les lignes une par une, réduisant significativement l’utilisation de la mémoire.

mysqldump --single-transaction --quick --skip-lock-tables -u root -p dbname > backup.sql

Résumé

--single-transaction est une option puissante pour obtenir des sauvegardes cohérentes sans arrêter le service. Cependant, une utilisation appropriée nécessite de comprendre ses limitations. Dans les environnements de production, considérez attentivement les moteurs de stockage, l’activité DDL et le timing lors de la conception de votre stratégie de sauvegarde.

6. Combinaison de –single-transaction avec d’autres options

–quick : Le meilleur partenaire pour réduire l’utilisation de la mémoire

mysqldump --single-transaction --quick -u root -p dbname > backup.sql

Lorsque vous ajoutez --quick, mysqldump ne charge pas toutes les données en mémoire d’un coup. Au lieu de cela, il lit et sort les lignes une à une. Cela est particulièrement efficace pour les grandes tables et réduit significativement la consommation de mémoire.

Avantages :

• Utilisation de mémoire réduite pendant la sauvegarde
• Empêche le swapping et la dégradation des performances
• Améliore la stabilité dans les environnements de grandes données

Recommandation : Si vous utilisez --single-transaction, il est presque toujours une meilleure pratique de l’utiliser avec --quick.

–skip-lock-tables : Éviter explicitement les verrous automatiques

mysqldump tente d’activer --lock-tables par défaut, mais cela entre en conflit avec --single-transaction. Pour éviter les conflits, spécifiez explicitement --skip-lock-tables.

mysqldump --single-transaction --quick --skip-lock-tables -u root -p dbname > backup.sql

Avantages :

• Clarifie l’intention de la commande
• Empêche les erreurs ou avertissements causés par les conflits d’options

–master-data : Idéal pour les configurations de réplication

Dans les environnements de réplication MySQL, --master-data est couramment utilisé pour assurer une synchronisation précise du côté réplica.

mysqldump --single-transaction --quick --master-data=2 -u root -p dbname > repl_backup.sql

Lors de la spécification de --master-data=2, le nom du fichier de journal binaire actuel et la position sont enregistrés dans le fichier de sauvegarde sous forme de lignes commentées. Cela vous permet de synchroniser les réplicas en utilisant ces informations.

Important :

• --master-data doit également être utilisé principalement avec InnoDB.
• Il est parfois combiné avec --flush-logs pour faire pivoter les journaux binaires.

–set-gtid-purged=OFF : Lorsque vous voulez désactiver GTID

Dans les environnements utilisant GTID (Global Transaction ID), mysqldump peut inclure automatiquement les informations GTID. Dans certains cas, vous pourriez vouloir désactiver ce comportement.

mysqldump --single-transaction --quick --set-gtid-purged=OFF -u root -p dbname > no_gtid.sql

Cas d’utilisation :

• Sauvegardes temporaires en dehors des configurations de réplication
• Transfert de données vers un environnement différent

Exemple complet (options combinées)

mysqldump   --single-transaction   --quick   --skip-lock-tables   --master-data=2   --set-gtid-purged=OFF   -u root -p production_db > production_backup.sql

En combinant plusieurs options, vous pouvez construire un script de sauvegarde pratique qui aborde la cohérence, l’efficacité de la mémoire, la compatibilité de la réplication et la gestion GTID.

Résumé

Bien que --single-transaction soit puissant par lui-même, le combiner avec d’autres options permet une stratégie de sauvegarde optimale adaptée à votre environnement et à vos objectifs. En particulier, l’associer à --quick et --skip-lock-tables est presque essentiel, et dans les environnements de réplication, vous devriez envisager d’utiliser --master-data également.

Pour exploiter pleinement mysqldump, la sélection des options en fonction de votre objectif est essentielle.

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

Ici nous résumons les questions pratiques courantes concernant mysqldump --single-transaction ainsi que leurs réponses. Utilisez cette section comme référence pour prévenir les problèmes opérationnels et concevoir une stratégie de sauvegarde fiable.

Q1. Dans quelles situations l’option --single-transaction est-elle appropriée ?

A1.
C’est idéal lorsqu’on utilise le moteur de stockage InnoDB et que l’on souhaite obtenir une sauvegarde cohérente sans arrêter le service. C’est particulièrement précieux dans les environnements de production tels que les sites de commerce électronique ou les systèmes de réservation où les utilisateurs accèdent constamment au système.

Q2. Puis-je utiliser --single-transaction si des tables MyISAM sont incluses ?

A2.
Oui, vous pouvez l’utiliser, mais la cohérence des données pour les tables MyISAM n’est pas garantie. Étant donné que MyISAM ne prend pas en charge les transactions, les mises à jour pendant la sauvegarde peuvent entraîner des incohérences. Si des tables MyISAM sont incluses, envisagez d’utiliser --lock-all-tables à la place.

Q3. Que se passe-t-il si j’utilise --single-transaction et --lock-tables ensemble ?

A3.
Ces options sont en conflit, et mysqldump désactivera automatiquement l’une d’elles. Cependant, pour éviter un comportement inattendu ou des avertissements, il est plus sûr de spécifier explicitement --skip-lock-tables.

Q4. Que se passe-t-il si des opérations DDL telles que CREATE TABLE ou ALTER TABLE surviennent pendant le dump ?

A4.
--single-transaction ne protège pas contre les opérations DDL. Si les définitions de tables changent pendant le dump, il existe un risque de échec de la sauvegarde ou de résultats incohérents. Idéalement, planifiez les sauvegardes pendant les fenêtres de maintenance ou à des moments où les opérations DDL peuvent être évitées.

Q5. Existe-t-il des options recommandées à utiliser avec --single-transaction ?

A5.
Oui, combiner les options suivantes améliore la sécurité et l’efficacité :

• --quick : Réduit l’utilisation de la mémoire et permet des dumps stables
• --skip-lock-tables : Évite explicitement les conflits de verrouillage de tables
• --master-data=2 : Prend en charge les sauvegardes compatibles avec la réplication
• --set-gtid-purged=OFF : Offre de la flexibilité dans les environnements sans GTID

Q6. Comment puis‑je réduire le temps de sauvegarde ?

A6.
Les mesures suivantes sont efficaces :

• Utilisez l’option --quick pour réduire la charge mémoire et améliorer la vitesse
• Sauvegardez uniquement des tables spécifiques (sauvegardes partielles)
• Archivez ou supprimez les données inutiles à l’avance pour réduire la taille du jeu de données
• Exportez les fichiers de sauvegarde vers un SSD ou un stockage à haute vitesse

Q7. Comment restaurer une sauvegarde créée avec --single-transaction ?

A7.
Vous pouvez la restaurer comme un fichier mysqldump normal en utilisant la commande suivante :

mysql -u username -p database_name < backup.sql

Si la sauvegarde inclut le journal binaire ou des informations GTID, une configuration supplémentaire peut être nécessaire avant la restauration (par exemple, en utilisant la commande CHANGE MASTER TO).

8. Conclusion

mysqldump --single-transaction est une méthode puissante pour obtenir des sauvegardes cohérentes sans arrêter le service dans les environnements MySQL et MariaDB. Dans cet article, nous avons détaillé son mécanisme, son utilisation, les considérations importantes, les combinaisons d’options et les questions fréquemment posées.

Points clés

• --single-transaction exploite la fonctionnalité de transaction d’InnoDB pour fournir des sauvegardes cohérentes basées sur des instantanés.
• Il vous permet d’éviter les verrous de tables et de dumper les données en toute sécurité même en environnements de production.
• Cependant, il ne prend pas en charge les moteurs non transactionnels comme MyISAM ou les opérations DDL, il est donc essentiel de bien comprendre votre environnement.
• Le combiner avec des options telles que --quick, --skip-lock-tables et --master-data améliore à la fois la qualité et l’efficacité.
• La section FAQ répond aux préoccupations pratiques fréquemment rencontrées dans les opérations réelles.

Construisez une stratégie de sauvegarde sûre et efficace

Dans les opérations commerciales et de service, la perte de données affecte directement la crédibilité. En utilisant correctement mysqldump et --single-transaction, vous pouvez mettre en place une stratégie de sauvegarde qui équilibre fiabilité et praticité.

L’essentiel est d’aller au-delà du simple « avoir des sauvegardes » et de viser des sauvegardes cohérentes et fiables sur lesquelles vous pouvez compter en cas de défaillance. Appliquez les connaissances de cet article pour renforcer vos opérations quotidiennes.