Rejouer les transactions grâce au mode log_bin et aux fichiers binlog
27 décembre 2018
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
Cette 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