Pour assurer la gestion des bases de données MySQL, divers outils et techniques de sauvegarde ont été développés, chacun ayant ses propres forces et cas d'utilisation. Cet article explore les fonctionnalités clés, les mécanismes et les considérations associés à ces outils populaires de sauvegarde et de gestion MySQL, vous aidant ainsi à prendre des décisions éclairées pour vos opérations de base de données.
1. Sauvegarde logique : mysqldump
Dans MySQL, mysqldump est l'un des outils les plus couramment utilisés pour exporter des bases de données sous forme de fichiers SQL portables. Il permet de sauvegarder des tables individuelles, des bases de données uniques ou multiples, et propose un ensemble riche d'options répondant à divers besoins de sauvegarde. Son mécanisme de sauvegarde consiste à se connecter à la base de données MySQL via un protocole, interroger les données à sauvegarder, puis convertir ces données en instructions « INSERT » correspondantes. Lorsque nous devons restaurer les données, l'exécution de ces instructions « INSERT » permettra de récupérer les données concernées.
Le processus de sauvegarde mysqldump est approximativement le suivant :
Appliquer des verrouillages en lecture à toutes les tables d'une base de données spécifique ;
Bouclez et sauvegardez les données du tableau ;
Libérer le verrou de lecture ;
Répétez les trois étapes ci-dessus ;
Sauvegarde terminée.
À partir du processus ci-dessus, il est clair que lors de la sauvegarde par mysqldump, quand on sauvegarde une base de données spécifique, toutes les tables de cette base seront en mode lecture seule, empêchant toute modification jusqu'à ce que la sauvegarde soit terminée. Cela est généralement inacceptable pour les environnements en ligne. Si l'option « --master-data » ou « --dump-slave » est spécifiée, un verrouillage global en lecture (« FLUSH TABLES WITH READ LOCK ») sera appliqué au début de la sauvegarde et restera valide jusqu'à la fin. Une alternative consiste à effectuer la sauvegarde sur une base de données répliquée afin d'éviter d'affecter les opérations en ligne.
Un autre grand avantage de l'utilisation de mysqldump est qu'étant donné qu'il génère des requêtes SQL, il prend en charge la migration ou la restauration des données entre différentes plateformes et versions, ce qui n'est pas possible avec les sauvegardes physiques. Cependant, puisque mysqldump produit des requêtes SQL, une extrême prudence est nécessaire lors de son utilisation afin d'éviter des problèmes graves. Les problèmes courants lors de l'utilisation de mysqldump incluent :
Déplacer uniquement une partie des données vers une nouvelle instance mais supprimer accidentellement les données d'origine ;
En raison des problèmes de fuseaux horaires, les tables restaurées et les données temporelles peuvent être incorrectes ;
Après l'importation des données de sauvegarde dans la base de données principale, la base de données secondaire ne se synchronise pas ;
En raison de problèmes de jeu de caractères, les données restaurées apparaissent corrompues.
Par conséquent, lors de l'utilisation de mysqldump, il est essentiel de comprendre la fonction de chaque option, vérifier quelles opérations sont incluses dans le fichier SQL exporté, et évaluer l'impact sur les données existantes.
2. Mydumper
Le principe de Mydumper est similaire à celui de mysqldump, mais sa principale différence réside dans l'introduction de sauvegardes multithreadées, où chaque thread de sauvegarde traite une partie des tables. La granularité de concurrence peut même atteindre le niveau des lignes, permettant ainsi des sauvegardes multithreadées.
Fonctionnalités clés de Mydumper :
Léger, écrit en C
La vitesse d'exécution est 10 fois plus rapide que mysqldump
Copies cohérentes des tables transactionnelles et non transactionnelles (pour les versions 0.2.2 et ultérieures)
Compression rapide des fichiers
Prise en charge de l'exportation des binlogs
Restauration multi-threadée (pour les versions 0.2.1 et supérieures)
Fonctionne en tant que démon pour les captures instantanées programmées et les journaux binaires en continu (pour les versions 0.5.0 et ultérieures)
Open-source (GNU GPLv3)
Étant donné que Mydumper utilise des exports multi-threadés, il ne peut garantir le même ordre d'exportation que mysqldump, ce qui peut provoquer des incohérences de données pour les fonctionnalités dépendant du temps telles que les routines et les événements. Il est recommandé d'exporter et d'importer la base de données système MySQL séparément des autres bases de données.
Les fonctions d'extraction et de fusion de bases de données de Mydumper reposent sur des délimiteurs, donc les noms de bases de données ne devraient pas contenir de points (« . »), et les noms de tables ne devraient pas contenir de tirets (« - »).
3. mysqlbackup
mysqlbackup, abrégé en MEB, est un outil de sauvegarde développé par Oracle pour la version entreprise de MySQL. Cet outil propose des fonctionnalités avancées de sauvegarde MySQL, notamment des sauvegardes à chaud, des sauvegardes incrémentielles, des sauvegardes sélectives de données et la restauration des données.
En utilisant le traitement parallèle, mysqlbackup effectue des sauvegardes parallèles au niveau des blocs des données de la base de données, améliorant ainsi l'efficacité et la rapidité des sauvegardes.
MEB prend en charge trois types de sauvegardes de bases de données MySQL : Complète, Différentielle et Incrémentielle. Il prend également en charge le stockage compressé de ces sauvegardes. Après avoir effectué une sauvegarde, trois types de fichiers sont générés : ibdata, ibd et ibz. Parmi eux :
« ibdata » contient les données de l'espace de table système InnoDB ;
« Ibd » contient les fichiers de données réels ;
« Ibz » contient des fichiers de données compressés.
De plus, MEB génère des fichiers tels que « backup-mysql.cnf » (une sauvegarde de la configuration MySQL au moment de la sauvegarde) et des fichiers de sauvegarde incrémentielle comme « ibbackup_ibd_files » et « ibbackup_logfile », qui stockent les données et les journaux incrémentiels. MySQL a également introduit un mécanisme séparé de sauvegarde des journaux de redo afin d'améliorer l'intégrité des données.
4. Sauvegarde physique : Percona XtraBackup
Percona XtraBackup est un outil de sauvegarde à chaud pour MySQL développé par Percona, conçu pour les sauvegardes physiques basées sur le mécanisme de récupération après panne d'InnoDB. Son principe de fonctionnement de base est le suivant :
1. Au démarrage, un processus de copie des journaux de reprise (redo logs) est créé pour capturer et enregistrer le numéro de séquence actuel du journal (LSN) et copier en continu les journaux de reprise modifiés à partir de ce point.
2. Un thread est initié pour copier « ibdata1 », les espaces de tables undo et tous les fichiers « ibd ».
3. Une fois la copie du fichier « ibd » terminée, elle déclenche un « FLUSH TABLES WITH READ LOCK » (ou applique un verrou de sauvegarde).
4. Il sauvegarde ensuite les fichiers de données non-InnoDB (.frm, .MRG, .MYD, .MYI, etc.).
5. Il sauvegarde également les informations relatives à la base de données répliquée et aux journaux binaires.
6. Enfin, il vide les journaux, copie les derniers journaux de réexécution (redo logs), arrête le thread de copie des journaux, et libère les verrous globaux tout en enregistrant les métadonnées de sauvegarde pour terminer le processus.
Lors de la restauration, Percona XtraBackup applique les journaux de réexécution copiés, valide les transactions terminées et annule les transactions non validées afin de restaurer la base de données dans un état cohérent.
Étant donné que Percona XtraBackup produit des fichiers physiques, il évite de nombreux problèmes associés aux sauvegardes logiques telles que mysqldump lors de la restauration ou de la migration de bases de données. Toutefois, différents paramètres de sauvegarde peuvent avoir un impact variable sur la base de données.
XtraBackup présente l'avantage d'impacter minimalement la base de données et permet une récupération rapide, ce qui en fait le choix privilégié pour les sauvegardes courantes. En revanche, mysqldump est plus flexible mais nécessite une manipulation prudente afin d'éviter d'altérer les données existantes.
5. phpMyAdmin
phpMyAdmin est un outil de gestion de bases de données MySQL basé sur PHP, conçu comme une application web fonctionnant sur un hébergeur web. Il permet aux administrateurs de gérer les bases de données MySQL via une interface web, offrant un moyen pratique d'entrer des syntaxes SQL complexes, particulièrement utile pour l'importation et l'exportation de grandes quantités de données.
phpMyAdmin propose une interface graphique de base de données similaire à Microsoft Access et SQL Server, permettant aux utilisateurs d'effectuer diverses opérations MySQL telles que la création de bases de données, de tables, et la génération de fichiers scripts pour les bases de données MySQL.
Un des principaux avantages de phpMyAdmin est que, comme d'autres applications PHP, il fonctionne sur un serveur web. Cependant, les utilisateurs peuvent accéder aux pages HTML générées depuis n'importe où, ce qui permet la gestion à distance des bases de données MySQL. Cela facilite la création, la modification et la suppression des bases de données et des tables. De plus, phpMyAdmin peut générer une syntaxe PHP couramment utilisée, assurant ainsi la correction des requêtes SQL lors du développement d'applications web.
6. MySQL Workbench
MySQL Workbench est un outil graphique officiel gratuit permettant de gérer les bases de données MySQL, offrant une interface conviviale pour la conception, le développement et l'administration des bases de données. Il permet aux utilisateurs de créer et modifier visuellement les structures des bases de données à l'aide de diagrammes ER, de générer des scripts SQL et de synchroniser les modifications avec la base de données. L'éditeur SQL intégré prend en charge la coloration syntaxique, la saisie semi-automatique et l'exécution des requêtes avec des résultats visualisés. De plus, il propose des outils pour l'importation/exportation des données, la gestion des autorisations des utilisateurs, la surveillance des performances et la migration des bases de données. Compatible avec Windows, macOS et Linux, il s'adresse aussi bien aux débutants qu'aux développeurs expérimentés, simplifiant les tâches liées aux bases de données sans nécessiter une connaissance approfondie des commandes en ligne.
7. Vinchin Backup & Recovery
Vinchin Backup & Recovery propose une solution plus automatisée, efficace et sécurisée, simplifiant les processus de sauvegarde MySQL grâce à une gestion centralisée, une récupération rapide, des sauvegardes incrémentielles et des stratégies de stockage flexibles pour garantir la disponibilité à long terme des données. Et il offre également des stratégies de sauvegarde flexibles pour Oracle DB, SQL Server, SQL Server, PostgreSQL, Postgres Pro et MariaDB.
Ses fonctionnalités de stockage optimisées, telles que la déduplication et la compression des données, réduisent considérablement les besoins en espace de stockage des fichiers de sauvegarde et diminuent le temps de sauvegarde. En même temps, ses fonctions de chiffrement et de sécurité lors de la transmission assurent la sécurité des données sauvegardées, ce qui le rend adapté aux entreprises ayant besoin de protéger strictement leurs données.
Pour créer des travaux de sauvegarde de base de données MySQL, veuillez vous rendre sur la page Physical Backup > Database Backup > Backup :
1. Sélectionnez les bases de données à sauvegarder.
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.
Cliquez sur le bouton ci-dessous pour profiter de la version d'essai gratuite de Vinchin pendant 60 jours et découvrir une solution de sauvegarde et de récupération de données efficace et fiable !
Outils de sauvegarde MySQL FAQ
1. Quelle est la différence entre les sauvegardes logiques et physiques ?
Les sauvegardes logiques stockent les données sous forme d'instructions SQL, tandis que les sauvegardes physiques copient les fichiers bruts de la base de données.
2. Qu'est-ce que la restauration à un instant précis (PITR) dans MySQL ?
Il permet de restaurer une base de données à un moment précis en utilisant les journaux binaires (binlog).
Conclusion
Le choix de l'outil de sauvegarde approprié pour votre base de données MySQL est essentiel pour garantir la sécurité des données et l'efficacité opérationnelle. Comprendre les forces et les limites de chaque outil vous permet d'adapter votre stratégie de sauvegarde aux besoins spécifiques, assurant ainsi une protection solide des données et une récupération sans problème dans n'importe quelle situation.
