Comment effectuer une récupération à un point dans le temps (PITR) dans MySQL pour remettre des bases de données corrompues ?

La récupération à un point dans le temps des données est un type de récupération des données de sauvegarde qui permet de remettre les modifications apportées aux données à un moment précis après une récupération complète des données de sauvegarde. Basée sur la journalisation binaire, elle permet de réinitialiser la base de données à un moment situé dans les plus brefs délais avant la survenue du problème ou de l'erreur. Pour illustrer cela, prenons un exemple :

« Si votre serveur MySQL a subi une panne système soudaine à 15 h mardi, ce qui a entraîné la corruption des tables de la base de données. Comme la panne s'est produite à 15 h, vous pouvez réinitialiser la base de données dans les plus brefs délais à un moment avant la panne. Si la journalisation binaire était activée, vous pouvez effectuer une récupération des données à un moment donné à un état antérieur à la panne. »

Toutefois, pour remettre la base de données avec PITR, vous avez besoin des fichiers journaux binaires. Ces fichiers ne sont créés qu'après une sauvegarde complète. Toutes les modifications apportées à la base de données après la sauvegarde complète sont enregistrées dans ces fichiers journaux binaires. Ces fichiers sont généralement inclus dans la dernière sauvegarde incrémentielle de la série de sauvegardes.

Dans cet article, nous allons découvrir différentes méthodes permettant d'effectuer une récupération ponctuelle des données afin de remettre des bases de données MySQL.

Conditions préalables à la récupération de données à un instant donné dans MySQL

Pour la récupération de données à un instant donné dans MySQL Server, veuillez noter les points suivants :

• Les fichiers journaux binaires sont y compris dans le fichier de sauvegarde. Cependant, si vous avez créé la sauvegarde à l'aide de l'option –use-tts, les fichiers journaux binaires ne seront pas générés.
• Assurez-vous que vous disposez d'un fichier de sauvegarde complet lisible. Le fichier journal binaire est souvent compris dans le fichier de sauvegarde incrémentielle et peut être utilisé pour la récupération de données à un instant donné.
• Veuillez-vous assurer que la journalisation binaire est activée.
• Veuillez-vous assurer que vous disposez de toutes les autorisations nécessaires pour remettre la sauvegarde. Vous pouvez exécuter l'instruction SHOW PRIVILEGES pour vérifier les autorisations.

Méthodes de récupération ponctuelle des données à partir de bases de données

Vous pouvez effectuer une récupération ponctuelle des données à l'aide des méthodes suivantes.

Méthode 1 : utilisation du fichier journal binaire (binlog)

Le fichier journal binaire est y compris dans votre dernier fichier de sauvegarde. Il contient les événements qui enregistrent les modifications apportées à la base de données. Il décrit les modifications apportées aux données des tables et les opérations de création de tables. Il contient les instructions SUPPRIMERque vous avez utilisées pour apporter des modifications. Il contient également la durée des instructions qui ont été exécutées pour mettre à jour les données dans la base de données.

Étapes à suivre pour effectuer une récupération de données à un instant donné à l'aide du fichier journal binaire :

Tout d'abord, assurez-vous que la journalisation binaire est activée sur votre serveur MySQL. Pour vérifier l'état de la journalisation binaire, vous pouvez exécuter la commande suivante :

mysql> SHOW VARIABLES LIKE 'log_bin';Copier le code

Si la commande affiche la valeur du fichier log_bin comme ON, cela signifie que la journalisation binaire est activée. Veuillez maintenant suivre ces étapes pour remettre les données à partir du journal binaire :

• Exécutez l'instruction suivante pour afficher la liste de tous les fichiers journaux binaires :

mysql> SHOW BINARY LOGS;Copier le code

• Pour connaître l'état des fichiers journaux binaires sur le serveur, exécutez la commande suivante :

mysql> SHOW BINARY LOG STATUS;Copier le code

• Pour déterminer le nom du fichier journal binaire actuel sur votre serveur MySQL, vous pouvez exécuter la commande suivante :

mysql> SHOW MASTER STATUS;Copier le code

• Étape suivante : utilisez l'utilitaire mysqlbinlog pour rejouer les événements du journal binaire. Assurez-vous de disposer de toutes les autorisations nécessaires, telles que BINLOG_ADMIN ou SUPER, REPLICATION_APPLIER, pour exécuter les événements du journal. Voici la commande permettant d'exécuter l'utilitaire mysqlbinlog :

mysqlbinlog [options] log_file Copier le code

Remarque : lorsque vous exécutez la commande ci-dessus, vous pouvez afficher les événements du fichier journal binaire au format texte.

• Vous pouvez vérifier les données dans les fichiers journaux binaires et sélectionner le contenu individuel à appliquer. Veuillez exécuter la commande suivante :

$> mysql -u root -p < tmpfileCopier le code

Vous pouvez également appliquer les événements directement à partir du fichier journal binaire dans la commande mysql à l'aide de la commande suivante :

$> mysqlbinlog binlog_files | mysql -u root -pCopier le code

• Étapes suivantes : Utilisez ensuite la commande suivante pour remettre la base de données MySQL à l'aide du fichier journal binaire :

mysqlbinlog | mysql -u -pCopier le code

• Pour remettre la base de données MySQL à l'aide de plusieurs fichiers journaux binaires, exécutez la commande suivante :

mysqlbinlog mysql_bin.000001 mysql_bin.000002 | mysql -u root -ppassword database_nameCopier le code

Méthode 2 : utilisation des positions d'événements

Vous pouvez également utiliser les positions d'événements dans le journal binaire pour effectuer une récupération de données à un instant donné dans MySQL. Pour ce faire, vous devez examiner attentivement le journal binaire afin de déterminer la position et l'événement exacts, puis les utiliser pour appliquer les événements du journal binaire. Pour vérifier les positions d'événements, vous pouvez utiliser l'utilitaire mysqlbinlog.

Étapes à suivre pour effectuer une récupération de données à un instant donné à l'aide des positions des événements

Vous trouverez ci-dessous les étapes à suivre pour utiliser les positions des événements dans le journal binaire pour la récupération de données à un instant donné :

• Tout d'abord, remettez la dernière sauvegarde complète et la série de sauvegardes.
• Utilisez la version suivante de l'utilitaire mysqlbinlog pour trouver la position de l'événement dans le journal binaire qui correspond au point dans le temps. Déterminez les positions de début et de fin à l'aide des options --start-position  et --stop-position  , respectivement. Pour ce faire, exécutez la commande suivante :

mysqlbinlog --start-position="binary-log-position-at-the-end-of-backup-remissions" \

         --stop-position="binary-log-position-corresponding-to-tR" \

         nom-du-fichier-journal-binaire | mysql -uadmin -p

Copier le code

• Ensuite, appliquez les événements du journal binaire au serveur, en commençant à cette position.

$> mysqlbinlog --start-position=1006 --stop-position=1868 /var/lib/mysql/bin.123456 \

         | mysql -uroot -p

Copier le code

Cette commande reflète toutes les modifications apportées à la base de données jusqu'au point spécifié dans le temps. Elle remet toutes les transactions entre la position de départ et la position finale. Elle remet votre base de données endommagée au moment où la base de données MySQL a subi une défaillance.

Limites de la récupération des données jusqu’à une limite de restauration dans le temps

La fonctionnalité de récupération des données à un point dans le tempsdans MySQL peut vous aider à remettre la base de données endommagée à un moment précis dans le passé. Cependant, cela nécessite que la fonctionnalité de journalisation binaire soit activée et qu'une sauvegarde récente lisible soit disponible. La récupération des données à un moment donné peut échouer si les fichiers journaux binaires sont endommagés.

Solution rapide pour remettre des bases de données endommagées

Si votre fichier de sauvegarde ou votre fichier de journalisation binaire est corrompu, ou si la journalisation binaire est désactivée, vous pouvez récupérer la base de données MySQL corrompue à l'aide d'outils spécialisés de récupération de données MySQL tels que Stellar® Repair for MySQL. Cet outil permet de récupérer rapidement les données des bases de données corrompues. Il peut réparer les bases de données corrompues créées dans les moteurs de stockage InnoDB et MyISAM. L'outil peut récupérer toutes les données de la base de données corrompue avec une précision absolue dans une nouvelle base de données. Vous pouvez également enregistrer le fichier de base de données réparé dans différents formats, tels que MariaDB, script SQL, CSV, XLS et HTML. Il permet la récupération de bases de données MySQL créées sur des systèmes Windows et Linux.

Conclusion

Nous avons expliqué ci-dessus deux méthodes pour réaliser la récupération d'une base de données MySQL endommagée à l'aide de Point-in Data Recovery. Cependant, pour réaliser la récupération de la base de données à l'aide de ces méthodes, un fichier journal binaire et une sauvegarde complète de la base de données sont nécessaires. Si votre fichier de sauvegarde est illisible, vous pouvez réparer la base de données MySQL endommagée à l'aide de Stellar Repair for MySQL . Cela vous permet de réparer et de remettre rapidement et efficacement la base de données MySQL endommagée avec une intégrité totale. Il vous permet également d'effectuer la récupération de données d'objets MySQL spécifiques tels que les tables de partition, les plages de tables, etc. L'outil prend en charge MySQL 8.0.40 et les versions antérieures.