Rejouer les transactions grâce au mode log_bin et aux fichiers binlog

Par défaut, le mode log_bin est activé dans MySQL Server. Il entraîne la création de fichiers /var/lib/mysql/binlog.*, contenant l’historique de toutes les instructions SQL exécutées jusqu’à ce que ces fichiers soient purgés.

mysql> SHOW GLOBAL VARIABLES LIKE '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
+---------------------------------+-----------------------------+
5 rows in set (0,00 sec)

Dans les bonnes pratiques, il serait peut-être opportun d’indiquer un chemin différent de /var/lib/mysql pour ces fichiers extrêmement sensibles, afin qu’ils ne soient pas détruits à l’occasion de l’utilisation de Percona Xtrabackup. Il suffit alors de modifier les valeurs des variables log_bin_basename et log_bin_index.

Forcer l’écriture des transactions dans un fichier binlog

La création d’un fichier binlog peut se faire manuellement en exécutant l’instruction :

FLUSH LOGS;

Purger les fichiers binlog

Pour purger les fichiers binlog, vous disposez de la commande PURGE BINARY LOGS :

FLUSH LOGS;
PURGE BINARY LOGS BEFORE NOW();

Disposer d’une sauvegarde logique consistante

Afin de disposer d’une sauvegarde consistante de la base de données sakila, j’associe à la commande mysqldump les commutateurs suivants :

–single-transaction Interdit les opérations en écriture le temps de la sauvegarde
–databases Spécifie la base de données à sauvegarder
-F Équivalent de la commande FLUSH LOGS
-R Embarque les procédures et les fonctions
-E Embarque les events
–add-drop-database Ajoute l’instruction DROP et CREATE DATABASE en entête du script
–delete-master-logs Supprime les fichiers binlog
–flush-privileges Force l’écriture des modifications dans les tables de la base mysql

La 2e commande compresse le flux de sortie grâce à gzip ! Avec gzip, le taux de compression est de 1 pour 5, environ.

mysqldump --single-transaction --databases sakila -F -E -R --flush-privileges --add-drop-database --delete-master-logs -u user -pmot_de_passe > /home/sakila.sql
mysqldump --single-transaction --databases sakila -F -E -R --flush-privileges --add-drop-database --delete-master-logs -u user -pmot_de_passe |gzip -9 > /home/sakila.sql.gz

Rejouer les transactions grâce au mode log_bin et aux fichiers binlogCette méthode de sauvegarde est incompatible avec un fonctionnement en mode H24, du fait que les écritures sont bloquées le temps de la sauvegarde. Tournez-vous alors vers Xtrabackup ou alors vers la version MySQL Server Enterprise. Vous pouvez d’ailleurs combiner Xtrabackup et le mode log_bin pour les bases H24.

Restauration des données de la sauvegarde logique

Avant de rejouer les fichiers binlog, vous devez préalablement restaurer les données du dump, à partir de votre fichier sauvegardé. Avant toute chose, pensez à faire un FLUSH LOGS, pour disposer à coup sûr des dernières transactions dans vos fichiers binlog que vous récupérez après l’injection du dump.

mysql -u user -pmot_de_passe < sakila.sql
gzip -c -d sakila.sql.gz |mysql -u user -pmot_de_passe

Rejouer les fichiers binlog

Pour injecter les données des fichiers binlog dans la base sakila, je dois partir de la date et heure de fin de sauvegarde (–start-date-time), dont vous pouvez disposer grâce à ls -la –time-style=full-iso. Quant au paramètre –stop-date-time, il s’agit du point juste avant l’incident.

mysqlbinlog --database sakila --start-datetime='2018-12-27 16:44:57' --stop-datetime='2018-12-27 16:58:00' binlog.*|mysql -u user -pmot_de_passe

MySQL  / Formateur MySQL MySQL 

Commentaires

Pas encore de commentaire.

Laisser un commentaire

(requis)

(requis)