Maîtrisez complètement les trois journaux principaux de MySQL : binlog, redo log et undo log

Cet article vous apporte des connaissances pertinentes sur les journaux MySQL. Ce sur quoi nous devons nous concentrer est le journal binaire (binlog) et le journal des transactions (y compris le journal de rétablissement et le journal d'annulation, j'espère que cela sera utile à tout le monde).

1. Binlog

Binlog est utilisé pour enregistrer les informations sur les opérations d'écriture (hors requêtes) effectuées par la base de données et est enregistrée sous forme binaire sur le disque. Binlog est le journal logique de MySQL et est enregistré par la couche serveur. Les bases de données Mysql utilisant n'importe quel moteur de stockage enregistreront les journaux binlog.

• Journal logique : peut être simplement compris comme une instruction SQL ;
• Journal physique : les données dans MySQL sont stockées dans la page de données et le journal physique enregistre les modifications sur la page de données ; insérez le morceau de code ici

Binlog ; est écrit en ajoutant. Vous pouvez définir la taille de chaque fichier binlog via le paramètre max_binlog_size Lorsque la taille du fichier atteint une valeur donnée, un nouveau fichier sera généré pour enregistrer le journal.

Scénarios d'utilisation de Binlog
Projet Dans les applications pratiques, il existe deux principaux scénarios d'utilisation de binlog, à savoir la réplication maître-esclave et la récupération de données.

• Réplication maître-esclave : activez le binlog du côté maître, puis envoyez le binlog à chaque côté esclave. Le côté esclave relit le binlog pour obtenir la cohérence des données maître-esclave.
• Récupération de données : récupérez des données à l'aide de l'outil mysqlbinlog.

Principe de synchronisation maître-esclave MySQL

• Thread de vidage du journal binaire du nœud maître
  Lorsque le nœud esclave se connecte au nœud maître, le nœud maître créera un thread de vidage de journal pour envoyer le contenu du journal binaire. Lors des opérations de lecture dans le binlog, ce thread verrouillera le binlog sur le nœud maître. Une fois la lecture terminée, le verrou sera libéré avant même d'être envoyé au nœud esclave
• Thread d'E/S du nœud esclave
  Lorsque le Nœud esclave Après avoir exécuté la commande start slave sur le nœud, le nœud esclave créera un thread d'E/S pour se connecter au nœud maître et demandera le binlog mis à jour dans la bibliothèque maître. Une fois que le thread d'E/S a reçu la mise à jour du processus de vidage du journal binaire du nœud maître, il l'enregistre dans le journal de relais local ;
• Thread SQL du nœud esclave
  Le thread SQL est responsable de la lecture du contenu du journal de relais et de son analyse en opérations spécifiques. et les exécuter, garantissant finalement la cohérence des données maître-esclave ;
  Principe de synchronisation maître-esclave de la base de données MySQL

Contenu du binlog
Comme mentionné ci-dessus, binlog est un journal logique, qui peut être simplement compris comme une instruction SQL, mais en fait, cela inclut également l'exécution de la logique inverse de l'instruction SQL. delete correspond à la suppression elle-même et la mise à jour d'insertion inverse contient des informations sur les lignes de données avant et après l'exécution de la mise à jour correspondante, l'insertion contient sa propre insertion et les informations de suppression correspondantes.

format binlog
Il existe trois formats de binlog, à savoir instruction, ligne et mixte. Avant MySQL 5.7.7, l'instruction était utilisée par défaut, et après MySQL 5.7.7, la ligne était utilisée par défaut. Le format du journal peut être modifié via binlog-format dans le fichier de configuration my.ini.
(1) Déclaration : Réplication basée sur les instructions (SBR), chaque instruction SQL qui modifie les données sera enregistrée dans le binlog.

• Avantages : pas besoin d'enregistrer spécifiquement les modifications dans une certaine ligne, ce qui permet d'économiser de l'espace, de réduire les E/S et d'améliorer les performances.
• Inconvénients : lors de l'exécution d'opérations telles que sysdate() ou sleep(), cela peut entraîner une incohérence entre le maître ; et données esclaves ;

(2)row : réplication basée sur les lignes (RBR), qui n'enregistre pas les informations liées au contexte de l'instruction SQL, mais enregistre les détails de l'enregistrement qui a été modifié.

• Avantages : Les détails de la modification de chaque ligne d'enregistrements sont enregistrés de manière très détaillée, il n'y aura donc aucune situation où les données ne peuvent pas être copiées correctement
• Inconvénients : Puisque les détails de la modification de chaque enregistrement sont enregistrés ; de manière très détaillée, cela générera un grand nombre de contenus de journal. Supposons qu'il existe une instruction de mise à jour et que de nombreux enregistrements sont modifiés. Chaque enregistrement modifié sera enregistré dans le journal binaire. En particulier, pour le fonctionnement de alter table, en raison de changements dans la structure de la table, chaque ligne d'enregistrement changera, entraînant une augmentation soudaine du volume du journal

(3) mixte : selon ce qui précède, l'instruction et la ligne ; Chacun a ses propres avantages et inconvénients, c'est pourquoi la version mixte est apparue, mélangeant les deux. Dans des circonstances normales, le format d'instruction est utilisé pour l'enregistrement. Lorsque l'instruction ne peut pas être résolue, passez au format de ligne pour l'enregistrement.
En particulier, comme mentionné ci-dessus, la nouvelle version (après MySQL 5.7.7) utilise le format de ligne par défaut. La ligne ici a également été optimisée en conséquence. Lors de l'opération de modification de table, le format d'instruction est utilisé pour l'enregistrement. les autres opérations utilisent toujours le format de ligne.

Minutage de vidage du binlog

Pour le moteur de stockage InnoDB, le binlog ne sera enregistré que lorsque la transaction est soumise. À ce moment, l'enregistrement est toujours dans la mémoire. MySQL contrôle le timing de vidage du binlog via sync_binlog, et le. la plage de valeurs est 0-N :

• 0 : Il n'est pas forcé de vider le disque, et le système décidera quand l'écrire sur le disque ;
• 1 : Le journal binaire doit être écrit sur le disque après chaque soumission ;
• N : Toutes les N transactions ; , le binlog sera écrit sur le disque. Binlog est écrit sur le disque ;

Comme le montre ce qui précède, le paramètre le plus sûr pour sync_binlog est 1, qui est également la valeur par défaut pour les versions MySQL après 5.7.7. Toutefois, la définition d'une valeur plus élevée peut améliorer les performances de la base de données. Par conséquent, dans des situations réelles, vous pouvez également augmenter la valeur de manière appropriée et sacrifier un certain degré de cohérence pour obtenir de meilleures performances.

Taille physique du fichier binlog

Dans le fichier de configuration my.ini, la taille du binlog peut être configurée via max_binlog_size. Lorsque le volume du journal dépasse la taille du fichier binlog, le système régénérera un nouveau fichier pour continuer à enregistrer le fichier. Que dois-je faire lorsqu'une transaction est relativement importante, ou lorsqu'il y a de plus en plus de logs et que l'espace physique qu'elle occupe est trop grand ? MySQL fournit un mécanisme de suppression automatique, qui peut être résolu en configurant le paramètre expire_logs_days dans le fichier de configuration my.ini. L'unité est en jours. Lorsque ce paramètre est à 0, cela signifie qu'il ne sera jamais supprimé ; lorsqu'il est à N, cela signifie qu'il sera automatiquement supprimé après le Nième jour.

2. redo log

redolog est le système de journalisation propriétaire du moteur InnoDB. Il est principalement utilisé pour assurer la durabilité des transactions et des fonctions anti-collision. Redolog est un journal physique qui enregistre les modifications spécifiques sur la page de données après l'exécution de l'instruction SQL.
Nous savons tous que lorsque MySQL est en cours d'exécution, les données seront chargées du disque vers la mémoire. Lorsqu'une instruction SQL est exécutée pour modifier les données, le contenu modifié n'est en fait que temporairement enregistré dans la mémoire. Si l'alimentation est coupée ou si d'autres circonstances se produisent à ce moment-là, ces modifications seront perdues. Par conséquent, après avoir modifié les données, MySQL recherchera des opportunités pour vider ces enregistrements mémoire sur le disque. Mais il existe un problème de performances, principalement sous deux aspects :

InnoDB interagit avec le disque en unités de données de pages, et une transaction ne peut modifier que quelques octets sur une page si le renvoi de pages de données complètes sur le disque est un problème. gaspillage de ressources ;

Une transaction peut impliquer plusieurs pages de données. Ces pages de données ne sont que logiquement continues mais pas physiquement continues. Les performances de l'utilisation d'E/S aléatoires sont trop faibles.

Par conséquent, MySQL a conçu Redolog pour enregistrer les modifications spécifiques apportées ; la page de données par la transaction, puis videz le redolog sur le disque. Vous avez peut-être des doutes. À l’origine, je voulais réduire io. Cela n’ajouterait-il pas un autre io ? Les concepteurs d'InnoDB en ont tenu compte dès le début de la conception. Les fichiers Redolog sont généralement relativement petits et le processus de retour sur disque est une E/S séquentielle, qui offre de meilleures performances qu'une E/S aléatoire.

Concept de base du redo log
redo log se compose de deux parties, l'une est le tampon de journalisation dans la mémoire et l'autre est le fichier de journalisation sur le disque. Chaque fois que l'enregistrement de données est modifié, ces modifications seront d'abord écrites dans le tampon de journalisation, puis attendront l'opportunité appropriée pour vider les modifications de la mémoire dans le fichier de journalisation. Cette technologie consistant à écrire d'abord des journaux, puis à écrire sur le disque, est la technologie WAL (Write-Ahead Logging). Il convient de noter que le redolog est renvoyé sur le disque avant la page de données. Les modifications apportées à l'index clusterisé, à l'index secondaire et à la page d'annulation doivent toutes être enregistrées dans le redolog.

Dans les systèmes d'exploitation informatiques, les données du tampon dans l'espace utilisateur ne peuvent généralement pas être écrites directement sur le disque et doivent passer par le tampon d'espace du noyau du système d'exploitation (OS Buffer). Par conséquent, l'écriture du tampon de journalisation dans le fichier de journalisation l'écrit d'abord dans le tampon du système d'exploitation, puis le vide dans le fichier de journalisation via l'appel système fsync(). Le processus est le suivant :

Mysql en prend en charge trois. types de tampon de journalisation Le moment d'écriture du fichier de journalisation peut être configuré via le paramètre innodb_flush_log_at_trx_commit La signification de chaque valeur de paramètre est la suivante :

Valeur du paramètre	signifiant
0 (retardé). écriture)	La restauration ne sera pas validée lorsque la transaction est soumise. Le journal dans le tampon du journal est écrit dans le tampon du système d'exploitation. Au lieu de cela, le journal est écrit dans le tampon du système d'exploitation toutes les secondes et fsync() est appelé pour l'écrire. le fichier de journalisation. En d'autres termes, lorsqu'il est défini sur 0, les données sont écrites sur le disque (environ) toutes les secondes. Lorsque le système tombe en panne, 1 seconde de données sera perdue.
1 (écriture en temps réel, brossage en temps réel)	Chaque fois qu'une transaction est soumise, le journal dans le tampon de journalisation sera écrit dans le tampon du système d'exploitation et fsync() sera appelé pour le vider le fichier de journalisation. Cette méthode ne perdra aucune donnée même si le système tombe en panne, mais comme chaque soumission est écrite sur le disque, les performances d'E/S sont médiocres.
2 (écriture en temps réel, brossage différé)	Chaque soumission est uniquement écrite dans le tampon du système d'exploitation, puis fsync() est appelée toutes les secondes pour écrire le journal dans le tampon du système d'exploitation dans le fichier de journalisation.

redo log format d'enregistrement
redolog adopte un format d'écriture cyclique de taille fixe Lorsque le redolog est plein, il sera réécrit depuis le début. Pourquoi est-il conçu ainsi ?
L'objectif principal du redo log est de réduire le besoin de vidage des pages de données. Redolog enregistre les modifications sur la page de données, mais lorsque la page de données est également renvoyée sur le disque, ces enregistrements deviennent inutiles. Par conséquent, lorsque MySQL détermine que le redolog précédent a expiré, les nouvelles données écraseront les données non valides. Alors comment juger si cela doit être couvert ?

L'image ci-dessus est un diagramme schématique du fichier de journalisation redo. pos représente le numéro de séquence du journal LSN (numéro de séquence du journal) actuellement enregistré par redolog. Lorsque la page de données a été renvoyée sur le disque, le LSN dans le fichier de journalisation sera mis à jour, indiquant que les données avant que ce LSN n'ait été écrite sur le disque. Ce LSN est le point de contrôle. La partie entre le point de contrôle et le point de contrôle est la partie de rechange de redolog, qui est utilisée pour enregistrer de nouveaux enregistrements ; la partie entre le point de contrôle et le point de contrôle est la partie modifiée de la page de données que redolog a enregistrée, mais la page de données ne l'a pas fait. été renvoyé sur le disque à ce moment-là. Lorsque la position d'écriture rattrape le point de contrôle, elle poussera d'abord le point de contrôle vers l'avant, quittera la position, puis enregistrera un nouveau journal.

Lors du démarrage d'innodb, peu importe qu'il ait été arrêté normalement ou anormalement la dernière fois, l'opération de récupération sera toujours effectuée. Lors de la récupération, le LSN dans la page de données sera vérifié en premier. Si ce LSN est plus petit que le LSN dans le redolog, c'est-à-dire la position d'écriture, cela signifie que les opérations inachevées sur la page de données sont enregistrées dans le redolog, puis il commencera à partir du point de contrôle le plus proche, commencera à synchroniser les données.

Est-il possible que le LSN dans la page de données soit supérieur au LSN dans le redolog ? La réponse est bien sûr possible. Lorsque cela se produit, la partie au-delà du redolog ne sera pas refaite, car cela signifie en soi que ce qui a été fait n'a pas besoin d'être refait.
La différence entre le journal redo et le binlog

	redo log	binlog
Taille du fichier	La taille du journal redo est fixe.	Binlog peut définir la taille de chaque fichier binlog via le paramètre de configuration max_binlog_size.
Méthode d'implémentation	redo log est implémenté par la couche moteur InnoDB, et tous les moteurs ne l'ont pas.	Binlog est implémenté par la couche serveur.Tous les moteurs peuvent utiliser les journaux binlog
Méthode d'enregistrement	redo log est enregistré dans une méthode d'écriture en boucle, il reviendra au début pour écrire les journaux. en boucle.	binlog est enregistré en ajoutant. Lorsque la taille du fichier est supérieure à la valeur donnée, les journaux suivants seront enregistrés dans de nouveaux fichiers
Scénarios applicables	redo log convient à la récupération en cas de crash (sans crash)	binlog Convient à la réplication maître-esclave et à la récupération de données

Cela ressort de la différence entre binlog et redo log : le journal binlog est uniquement utilisé pour l'archivage, et s'appuyer uniquement sur binlog n'a pas de fonctionnalités de sécurité en cas de crash. Mais seul le journal redo ne fonctionnera pas, car le journal redo est unique à InnoDB et les enregistrements du journal seront écrasés après avoir été écrits sur le disque. Par conséquent, le binlog et le redo log doivent être enregistrés en même temps pour garantir que les données ne seront pas perdues lors de l'arrêt et du redémarrage de la base de données.
Soumission en deux étapes
Ce qui précède présente brièvement le redolog et le binlog lors de la modification des données, ils enregistreront ces modifications, mais l'un est un journal physique et l'autre est un journal logique. Alors, comment ont-ils effectué le processus de modification ?

Supposons qu'il y ait une instruction de mise à jour à exécuter maintenant, mise à jour à partir de l'ensemble table_name c=c+1 où id=2, le processus d'exécution est le suivant :

• Localisez d'abord l'enregistrement id=2 ;
• L'exécuteur obtient le moteur aux données de ligne, ajoutez 1 à cette valeur pour obtenir une nouvelle ligne de données, puis appelez l'interface du moteur pour écrire cette nouvelle ligne de données
• Le moteur met à jour cette nouvelle ligne de données dans la mémoire et l'enregistre ; opération de mise à jour dans redolog , à ce moment, redolog est en état de préparation. Ensuite, l'exécuteur est informé que l'exécution est terminée et que la transaction peut être soumise à tout moment ;
• L'exécuteur génère le binlog de cette opération et écrit le binlog sur le disque
• L'exécuteur appelle l'interface de transaction de validation du moteur, et le moteur modifie le journal redo qui vient d'être écrit dans l'état de validation et la mise à jour est terminée

Le diagramme schématique est le suivant :

Ce processus de division de l'écriture redolog en deux étapes, préparation et validation, est appelé deux ; soumission en phase.

Redolog et binlog peuvent être utilisés pour représenter le statut de validation d'une transaction, et la validation en deux phases consiste à maintenir les deux états logiquement cohérents. Si vous n'utilisez pas de validation en deux phases, mais écrivez l'une d'abord, puis l'autre, cela peut poser des problèmes.

À l'heure actuelle, la mise à jour est toujours utilisée à titre d'exemple. Supposons que l'identifiant actuel = 2 et qu'il y ait un champ c = 0. Analysez respectivement les situations suivantes :
Écrivez d'abord redolog, puis écrivez binlog
Supposons que redolog soit écrit en premier lorsque redolog est terminé mais que binlog n'est pas terminé, MySQL va Une exception soudaine provoque un redémarrage. Étant donné que le redolog a été écrit auparavant, les enregistrements modifiés existent toujours après le redémarrage du système, donc la valeur de c dans cette ligne après la récupération est 1. Cependant, en raison du redémarrage du système, cet enregistrement n'existe pas dans le binlog. Lors d'une sauvegarde ultérieure du journal, cette instruction n'existe pas dans le journal binaire enregistré. Ensuite, vous constaterez que si vous devez utiliser ce binlog pour restaurer la bibliothèque temporaire, car le binlog de cette instruction est perdu, la bibliothèque temporaire ne sera pas mise à jour cette fois. La valeur de c dans la ligne restaurée est 0, ce qui est. la même chose que la valeur de la bibliothèque d'origine différente.
Écrivez d'abord binlog, puis redolog
Si vous écrivez d'abord binlog, puis redémarrez le système lors de l'écriture de redolog. Après le redémarrage, il n'y a aucun enregistrement de modification de c dans le redolog, et la valeur de c est toujours 0 pour le moment. Mais le journal "Changer c de 0 à 1" a été enregistré dans le binlog. Par conséquent, lorsque binlog est utilisé pour restaurer ultérieurement, une transaction supplémentaire sera générée. La valeur de c dans la ligne restaurée est 1, ce qui est différent de la valeur dans la base de données d'origine.

Donc, pour résumer, si vous écrivez d'abord un certain journal puis écrivez un autre journal, l'état de la base de données sera incohérent avec l'état de la bibliothèque restaurée à l'aide de binlog.

3. annuler le journal

undolog est principalement utilisé pour enregistrer l'état avant qu'un certain enregistrement de ligne ne soit modifié et enregistre les données avant la modification. Dans ce cas, lorsque la transaction est annulée, les enregistrements peuvent être restaurés tels qu'ils étaient avant le démarrage de la transaction via undolog. L'atomicité et la durabilité des transactions sont également obtenues par undolog. Le journal d'annulation enregistre principalement les modifications logiques des données. Par exemple, une instruction INSERT correspond à un journal d'annulation DELETE. Pour chaque instruction UPDATE, elle correspond à un journal d'annulation UPDATE opposé, de sorte que lorsqu'une erreur se produit, il peut être lancé. retour à avant l'état des données. Dans le même temps, lors de la récupération des données, il est utilisé conjointement avec binlog et redolog pour garantir l'exactitude de la récupération des données.

Le processus fonctionnel d'undolog est le suivant :

• Écrivez la version pré-modifiée dans le journal d'annulation avant le début de la transaction ;
• Démarrez la modification et enregistrez les données modifiées dans la mémoire ;
• Undolog est conservé ; sur le disque ;
• vider la page de données sur le disque ;
• soumission des transactions

Il convient de noter que, comme redolog, l'annulation doit être renvoyée sur le disque avant la page de données. Lors de la récupération des données, si l'annulation de la connexion est terminée, la transaction peut être annulée en fonction de l'annulation de la connexion.

Dans une transaction, la même donnée peut être modifiée plusieurs fois, donc les enregistrements avant chaque modification doivent-ils être enregistrés dans l'undolog ? Dans ce cas, la quantité de journaux d'annulation sera trop importante et la redolog entrera en jeu à ce moment-là. Dans une transaction, si le même enregistrement est modifié, undolog enregistrera uniquement l'enregistrement d'origine avant le début de la transaction. Lorsque cet enregistrement est à nouveau modifié, redolog enregistrera les modifications ultérieures. Pendant la récupération des données, redolog termine la restauration et undolog termine la restauration. Les deux se coordonnent pour terminer la récupération des données. Le processus est le suivant :

Une autre fonction est la chaîne de contrôle multi-version MVCC. Veuillez vous référer à cet article pour ce
Principe d'implémentation de MySQL MVCC

binlog, redolog et undolog sont les trois journaux les plus importants dans MySQL. récupération des données, les trois parties se coordonnent et coopèrent pour garantir l'exactitude de la récupération des données.

Apprentissage recommandé : Tutoriel vidéo mysql

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!