Des situations imprévues telles que des pannes du système d'exploitation, des coupures de courant, des défaillances du système de fichiers ou des pannes matérielles peuvent toutes entraîner un mauvais fonctionnement de la base de données que nous utilisons, provoquant ainsi des incohérences dans les données. Pour garantir la sécurité de la base de données, il est nécessaire de la sauvegarder régulièrement. Les sauvegardes de bases de données peuvent être classées en sauvegardes complètes, sauvegardes de journaux, sauvegardes incrémentielles et sauvegardes de fichiers. Pour une grande base de données, effectuer fréquemment des sauvegardes complètes peut prendre trop de temps, et ces sauvegardes complètes dupliquent souvent des données inchangées, ce qui entraîne un gaspillage des ressources. La stratégie de sauvegarde de base de données la plus couramment utilisée aujourd'hui consiste à effectuer des sauvegardes incrémentielles plus fréquentes à partir d'une sauvegarde complète. Par exemple, nous pouvons effectuer une sauvegarde complète une fois par semaine pendant les périodes de faible utilisation, puis réaliser une sauvegarde incrémentielle chaque jour, en capturant ainsi les opérations susceptibles d'avoir modifié la base de données au cours de cette période.
Introduction à la sauvegarde incrémentielle
La sauvegarde incrémentielle consiste à sauvegarder uniquement les fichiers ajoutés ou modifiés depuis la dernière sauvegarde complète ou la précédente sauvegarde incrémentielle. Cela signifie que la première sauvegarde incrémentielle couvre les fichiers ajoutés ou modifiés après la sauvegarde complète ; la deuxième sauvegarde incrémentielle couvre les fichiers ajoutés ou modifiés après la première sauvegarde incrémentielle, et ainsi de suite. L'avantage le plus significatif de cette méthode de sauvegarde est qu'elle ne contient aucune donnée dupliquée, ce qui entraîne une taille de sauvegarde plus réduite et un temps de sauvegarde plus court. Toutefois, la restauration des données à partir de sauvegardes incrémentielles est plus complexe. Vous devez posséder la dernière sauvegarde complète et toutes les sauvegardes incrémentielles (la perte ou l'endommagement de l'une d'elles peut entraîner un échec de la restauration), et celles-ci doivent être restaurées dans l'ordre chronologique, en commençant par la sauvegarde complète puis chaque sauvegarde incrémentielle, ce qui prolonge considérablement le temps de récupération.
Par exemple, si nous avons une base de données contenant 20 Go de données et que 10 Mo supplémentaires sont ajoutés chaque jour, effectuer une sauvegarde complète chaque jour imposerait une charge importante au serveur. Au lieu de cela, nous pouvons sauvegarder uniquement les nouvelles données ajoutées afin de réduire la charge du serveur.
Introduction au journal des transactions (Binlog)
MySQL ne fournit pas de méthode directe de sauvegarde incrémentielle, mais elle peut réaliser des sauvegardes incrémentielles indirectement à travers les journaux binaires de MySQL.
Le binlog est activé par l'option log-bin dans le fichier de configuration. Le serveur MySQL créera deux nouveaux fichiers, « XXX-bin.001 » et « xxx-bin.index », dans le répertoire racine des données. Si aucun nom de fichier n'est spécifié dans l'option de configuration, MySQL utilisera le nom d'hôte pour nommer ces deux fichiers. Le fichier « .index » contient une liste de tous les fichiers journaux. MySQL enregistre toutes les modifications apportées au contenu et à la structure de la base de données dans les fichiers « XXX-bin.n », mais n'enregistre pas les instructions « SELECT » et « UPDATE » qui ne mettent effectivement pas à jour les données.
Lorsque la base de données MySQL s'arrête ou redémarre, le serveur écrit les journaux dans le fichier journal suivant. MySQL génère un nouveau fichier binlog au redémarrage, avec un numéro de fichier incrémenté. En outre, si un fichier journal dépasse la limite définie par la variable système « max_binlog_size », un nouveau fichier journal est également créé.
La sauvegarde incrémentielle s'appuie sur le mécanisme de binlog de MySQL. Chaque opération d'insertion, de mise à jour, de suppression et de requête dans la base de données est enregistrée dans le binlog. En effectuant d'abord une sauvegarde complète tout en vidant le binlog, toutes les opérations ultérieures seront enregistrées dans de nouveaux fichiers binlog. Pour la sauvegarde incrémentielle, nous devons simplement sauvegarder les nouveaux fichiers binlog générés, ce qui permet une sauvegarde parfaite de la base de données en croissance continue.
L'importance des journaux binaires pour les sauvegardes est la suivante :
Les journaux binaires stockent toutes les opérations qui mettent à jour ou pourraient mettre à jour les données.
Les journaux binaires commencent à enregistrer après le démarrage du serveur MySQL et créent un nouveau fichier journal lorsque la taille de fichier définie est atteinte ou lorsque la commande « flush logs » est exécutée.
En exécutant périodiquement la commande « flush logs », en créant une nouvelle séquence de fichiers binaires et en stockant ces fichiers en toute sécurité, une sauvegarde incrémentielle sur une période donnée est effectuée.
Comment effectuer une sauvegarde incrémentielle MySQL ?
La sauvegarde incrémentielle MySQL peut être réalisée à l'aide des journaux MySQL. Les journaux MySQL comprennent trois types : les journaux binaires, les journaux de transactions et les journaux d'erreurs. Parmi ceux-ci, les journaux binaires sont les principaux journaux de MySQL, contenant toutes les opérations de modification des données au format binaire stockées sur le disque. En sauvegardant les journaux binaires, une sauvegarde incrémentielle MySQL peut être mise en œuvre.
Étapes pour la sauvegarde incrémentielle MySQL :
1. Activer le journal binaire MySQL :
Définissez les paramètres suivants dans le fichier de configuration MySQL (« my.cnf ») :
[mysql]
log-bin=mysql-bin
server-id=1
Le paramètre « log-bin » spécifie le préfixe du fichier journal binaire, et le paramètre « server-id » identifie de manière unique l'instance MySQL.
2. Créer un utilisateur de sauvegarde :
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'your_password';
GRANT REPLICATION CLIENT, REPLICATION SLAVE, PROCESS, SUPER, RELOAD ON *.* TO 'backup'@'localhost';
Ici, « backup » est le nom d'utilisateur. L'utilisateur doit disposer des autorisations suivantes : « REPLICATION CLIENT », « REPLICATION SLAVE », « PROCESS », « SUPER » et « RELOAD ».
3. Effectuez une sauvegarde complète :
Utilisez la commande « mysqldump » pour sauvegarder l'intégralité de la base de données MySQL dans un fichier :
mysqldump -u root -P 3306 -h 127.0.0.1 -p123456 ks_flask > ks_flask.sql
Ici, « localhost » est l'hôte MySQL, « root » est le nom d'utilisateur et « ks_flask » est le nom de la base de données.
4. Enregistrer la position actuelle du journal binaire :
Après avoir effectué la première sauvegarde complète, enregistrez le nom actuel du fichier de journal binaire et sa position pour les sauvegardes incrémentielles futures.
SHOW MASTER STATUS;
Cette commande renvoie le fichier journal binaire actuel et sa position.
5. Effectuer une sauvegarde incrémentielle :
Utilisez la commande « mysqlbinlog » avec les paramètres « --start-datetime », « --stop-datetime » et « --start-position » pour spécifier la plage horaire et la position de la sauvegarde précédente.
mysqlbinlog --start-datetime='2023-10-23 00:00:00'
--stop-datetime='2023-10-23 23:59:59'
--start-position=[previous position]
mysql-bin.000001 > incr_backup_2023-10-23.sql
Ici, « [précédente position] » est la position du journal binaire enregistrée lors de la dernière sauvegarde.
6. Enregistrer la nouvelle position du journal binaire :
Après avoir exécuté la sauvegarde incrémentielle, utilisez « SHOW MASTER STATUS » pour enregistrer le nouveau fichier de journal binaire et la position pour la prochaine sauvegarde incrémentielle.
SHOW MASTER STATUS;
7. Effectuez une sauvegarde incrémentielle régulièrement :
Effectuez des sauvegardes incrémentielles quotidiennement selon les besoins, en veillant à inclure les modifications de la veille. Des scripts d'automatisation peuvent être utilisés pour enregistrer et mettre à jour périodiquement les positions des journaux binaires.
8. Restaurer les données de sauvegarde :
Fusionner et restaurer les sauvegardes complètes et incrémentielles dans MySQL :
mysql -hlocalhost -uroot -p your_database < ks_flask.sql
mysql -hlocalhost -uroot -p your_database < incr_backup_2023-10-23.sql
S'il existe plusieurs sauvegardes incrémentielles, restaurez-les dans l'ordre chronologique.
Comment récupérer à partir d'une sauvegarde incrémentielle MySQL ?
Il existe trois méthodes courantes pour la récupération incrémentielle : récupération générale, récupération basée sur la position et récupération à un instant précis.
1. Récupération générale
Restaure tout le contenu des journaux binaires sauvegardés.
mysqlbinlog [--no-defaults] incremental backup file | mysql -u username -p password
2. Récupération basée sur la position
Lorsqu'un administrateur de base de données effectue des opérations, des actions erronées et correctes peuvent survenir en même temps. La récupération basée sur la position permet une restauration plus précise.
Récupération à une position spécifique
mysqlbinlog --stop-position='operation ID' binary log | mysql -u username -p password
Récupération à partir d'une position spécifique
mysqlbinlog --start-position='operation ID' binary log | mysql -u username -p password
3. Récupération à un instant donné
Ignore un point temporel spécifique erroné pour récupérer les données. Il existe trois méthodes :
1) Restauration à partir du début du journal jusqu'à un point précis dans le temps
mysqlbinlog [--no-defaults] --stop-datetime='YYYY-MM-DD HH:MM:SS' binary log | mysql -u username -p password
2) Récupération à partir d'un point temporel spécifique jusqu'à la fin du journal
mysqlbinlog [--no-defaults] --start-datetime='YYYY-MM-DD HH:MM:SS' binary log | mysql -u username -p password
3) Récupération d'un instant à un autre
mysqlbinlog [--no-defaults] --start-datetime='YYYY-MM-DD HH:MM:SS' --stop-datetime='YYYY-MM-DD HH:MM:SS' binary log | mysql -u username -p password
Une méthode plus simple de créer des sauvegardes incrémentielles MySQL
Comme vous pouvez le constater, l'ensemble du processus de sauvegarde et de restauration incrémentielle MySQL à l'aide des journaux binaires est complexe et chronophage. Étant donné qu'il s'agit d'opérations en ligne de commande, vous n'aurez aucune possibilité d'annuler si vous commettez une erreur pendant les processus de sauvegarde ou de restauration.
Heureusement, le puissant logiciel de sauvegarde MySQL Vinchin Backup & Recovery propose une interface conviviale pour simplifier les opérations.
Vinchin Backup & Recovery offre 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 afin d'assurer la disponibilité durable des données. Elle propose également des stratégies de sauvegarde flexibles pour Oracle DB, SQL Server, SQL Server, PostgreSQL, Postgres Pro et MariaDB, permettant la sauvegarde automatique, la sauvegarde différentielle et la sauvegarde du journal des transactions.
Pour créer des tâches de sauvegarde incrémentielle de base de données MySQL, veuillez vous rendre sur la page Physical Backup > Database Backup > Backup (Veuillez noter que la première sauvegarde doit être une sauvegarde complète) :
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.
Ici, vous pouvez choisir une sauvegarde incrémentielle planifiée.
4. Vérifiez et confirmez les paramètres.
Cliquez sur le bouton ci-dessous pour essayer la version d'essai gratuite de 60 jours de Vinchin et découvrir une solution efficace et fiable de sauvegarde et de récupération des données !
FAQ sur la sauvegarde incrémentielle MySQL
1. Que se passe-t-il si un fichier de journal binaire est supprimé avant la restauration d'une sauvegarde incrémentielle ?
Si des fichiers de journal binaire requis sont manquants, la récupération peut être incomplète, entraînant une perte de données. Veillez toujours à conserver les journaux binaires jusqu'à ce qu'ils ne soient plus nécessaires.
2. Pendant combien de temps dois-je conserver les journaux binaires MySQL pour les sauvegardes incrémentielles ?
Cela dépend de votre politique de sauvegarde. En général, les journaux doivent être conservés jusqu'à ce qu'une nouvelle sauvegarde complète soit terminée et vérifiée.
Conclusion
Il est recommandé d'actualiser les journaux binaires de sauvegarde incrémentielle quotidiennement. Cela garantit une fréquence de sauvegarde de la base de données suffisamment élevée pour minimiser le risque de perte de données. En actualisant les sauvegardes incrémentielles chaque jour, les modifications apportées à la base de données peuvent être gérées plus efficacement, permettant ainsi de la restaurer dans son état le plus récent lorsque nécessaire.
