Explication détaillée des problèmes liés au journal binaire MySQL

Source de cet article :
(Conserver la source n'est pas le droit à l'œuvre originale. Mon humble travail est loin de là. Il s'agit simplement de faire un lien vers le texte original, car certaines erreurs qui peut exister sera corrigé plus tard. Ou supplément, rien d'autre)

Aujourd'hui, j'ai accidentellement découvert une "Saison 1 : Défiez le concours d'optimisation des performances SQL lent de Xuanxuan" organisé par la communauté Yunqi sur le serveur de test Lors de l'exécution de son script de test pour écrire des données, le message d'erreur est le suivant :
La transaction multi-instructions nécessitait plus de 'max_binlog_cache_size' octets de stockage, augmentez cette variable mysqld et réessayez

Il indique que l'espace max_binlog_cache_size est insuffisant car le journal binaire est activé. Le paramètre par défaut n'était pas d'avoir de gros lots d'opérations transactionnelles auparavant. Ce problème n'a pas été rencontré cette fois. a été rencontré au début et a échoué.
Après avoir modifié la taille de binlog_cache_size, le problème a été résolu.

Étant donné que le moteur innodb par défaut est utilisé, le journal binaire est activé
Pour les opérations transactionnelles, le journal binaire doit être écrit lorsque la transaction est terminée avant que la transaction ne soit soumise. , Les opérations d'écriture effectuées sont mises en cache jusqu'à ce que la transaction entière soit terminée, et le processus mysqld écrit la transaction entière dans le journal binaire.
Lorsqu'une chose démarre, l'espace de contenu sera alloué en fonction de la valeur spécifiée par la variable système binlog_cache_size. Si l'espace de cache binlog_cache_size spécifié n'est pas suffisant, l'opération transactionnelle exécutée sera annulée et un message d'échec sera affiché. .

Au fait, résumons le journal binaire et les informations sur ses paramètres associés

Qu’est-ce que le journal binaire ?
Utilisé pour enregistrer les opérations d'écriture (ajouts, suppressions, modifications, mais hors requêtes) dans la base de données MySQL, ce qui équivaut au fichier journal des transactions en mode de récupération complète dans sqlserver.
Quel est le rôle du journal binaire ?
1. Utilisé pour la réplication. Lorsque la réplication maître-esclave est configurée, le serveur maître enverra le journal binaire qu'il génère au côté esclave. Le côté esclave utilisera les informations de ce journal binaire pour le refaire localement afin d'atteindre le maître. -synchronisation esclave
2. Récupération utilisateur, MySQL peut utiliser des journaux binaires pour effectuer des opérations de récupération basées sur des points temporels ou des ID de transaction basés sur une sauvegarde complète et une sauvegarde différentielle. Le principe est similaire à la restauration du journal de la réplication maître-esclave.

Informations sur les paramètres associés du journal binaire

1, activer le journal binaire
Pour activer le journal binaire, vous devez spécifier un chemin d'accès au paramètre log-bin. Par exemple : log_bin=/var/lib/mysql/mysql-bin
Après avoir démarré le journal binaire, un fichier log_bin_index qui gère le journal binaire sera automatiquement généré. L'option log_bin est également affichée comme étant activée, ce qui signifie que le journal binaire est activé.

　

2. Le format du fichier journal binaire

Le format du journal binaire est contrôlé par le paramètre binlog_format. le journal binaire a trois modes, basé sur le mode Statement (instruction), basé sur les lignes (ligne) et mixte (mixte) qui combine les deux premiers
La fonction binaire basée sur les instructions présente certaines lacunes (à mon avis), telles comme l'utilisation actuelle de la même instruction de mise à jour L'opération de mise à jour immédiate du temps obtient également l'heure actuelle sur le serveur maître et le serveur esclave. Les résultats obtenus par la réplication maître-esclave sont différents.
Le mode de journalisation binaire basé sur les lignes résout certaines des lacunes du mode basé sur les instructions, mais dans certains cas, un grand nombre de journaux seront générés. Par exemple, une opération de mise à jour met à jour 1 million de lignes de données. journal binaire basé sur les lignes, le résultat sera de 1 million de journaux
Basé sur le mode hybride, combinant les avantages des deux méthodes ci-dessus.
Peut être défini dans le fichier de configuration : binlog_format = MIXED

3, synchronisation de l'enregistrement du journal binaire

Enregistrement du journal binaire Elle peut être synchrone, c'est-à-dire que la transaction est écrite dans le journal binaire après sa soumission, ou elle peut être asynchrone, où le cache disque du système d'exploitation détermine quand écrire sur le disque.
Il est contrôlé par le paramètre sync_binlog= n. Si sync_binlog = 1 est défini, cela indique le niveau de sécurité d'écriture le plus élevé (mais il n'y a aucune garantie qu'aucun journal de transactions ne soit perdu). Mode, mais cela a un certain impact sur les performances.
Personnellement, je pense que s'il s'agit d'un moteur transactionnel, c'est pour assurer la sécurité des choses. Il n'y a aucune raison de ne pas mettre sync_binlog à 1.

On dit que définir sync_binlog sur 1 entraînera également potentiellement la perte d'un journal de transactions, mais je n'ai pas compris pourquoi il sera perdu, car comme il s'agit d'un moteur transactionnel, il existe également une couche de journaux d'annulation ou de rétablissement. pour le sauvegarder ?

Pensez-y plus tard, du fait de l'existence de logs de rétablissement et d'annulation, la cohérence des choses peut être garantie sur le serveur maître. Il se pourrait que lors de la réplication maître-esclave, une chose qui pourrait être perdue puisse être perdue. ne pas être livré au serveur esclave.

　

4, la taille d'un seul fichier du journal binaire

La taille du journal binaire est la limite maximale de un seul fichier journal, dans des circonstances normales. La limite maximale de taille de fichier définie ne sera pas dépassée. Après avoir dépassé la limite maximale définie, un roulement de journal se produira, c'est-à-dire qu'un fichier journal binaire sera régénéré.
max_binlog_size = 100M
L'unité de 104857600 indiquée ici est l'octet, soit 104857600/1024/1024 = 100M

　

5 , Nettoyage du journal binaire

Une fois le journal binaire lancé, un nouveau fichier sera généré pour stocker le journal. Le fichier journal sera automatiquement supprimé après son expiration, sinon un flux constant de fichiers journaux sera généré <.> Par exemple, vous pouvez définir le délai d'expiration sur 2. La valeur configurable est : expire_logs_days = 2. Les journaux binaires de plus de deux jours seront automatiquement supprimés.
Vous pouvez afficher le nombre actuel de fichiers journaux binaires par la commande show master logs

 

6, défilement des fichiers journaux binaires

1) Dans des circonstances normales, une fois l'enregistrement plein, il défilera automatiquement et le nom du suffixe sera +1

2) Une fois le service MySQL redémarré, il défilera automatiquement, que le fichier journal soit ou non est plein en fonction de la capacité maximale spécifiée
3) Défilement manuel, Exécutez la commande flush logs Après avoir exécuté les journaux de vidage comme suit, un fichier journal binaire est régénéré

　

. 4) Supprimez manuellement le journal binaire

Vous pouvez utiliser la commande

purger les journaux binaires pour que fileName supprime le fichier avant le nom de fichier spécifié

　

Vous pouvez le supprimer via la commande purger les journaux binaires avant le '2017-03-10 10:10:00' Fichiers avant l'heure spécifiée

　

Supprimer le binaire de purge des journaux spécifié logs avant date_sub (now (), intervalle de 7 jours);

Le maître Xiaoxiang purge les journaux principaux avant date_sub (now (), intervalle de 7 jours), cela devrait-il être un effet (mots-clés binaires et maîtres) ?

7. Lier (ou exclure) des bases de données pour les journaux binaires

Vous pouvez définir certaines bases de données pour activer les journaux binaires, ou certaines bases de données pour ne pas activer les journaux binaires

# binlog_do_db : utilisé lors de la configuration du maître-esclave ;
# binlog-ignore-db : définit quelle base de données n'enregistre pas les journaux
Il est défini dans MySQL5.7.18 (configuré dans my.cnf), mais lors de l'interrogation, cela semble inutile ?

　

8. Cache des journaux binaires et configuration de la taille du cache

La taille de binlog_cache_size, mentionnée au début Problème, quand une chose démarre, l'espace de contenu sera alloué en fonction de la valeur spécifiée par la variable système binlog_cache_size. Si l'espace de cache binlog_cache_size spécifié n'est pas suffisant, une erreur sera signalée et la chose sera annulée

L'unité du L'enregistrement présenté ici est également en octets, divisé par Après les deux 1024, c'est la capacité en Mo. Le 20971520/1024/1024 équivaut ici à 20 Mo.
S'il y a des opérations transactionnelles volumineuses, comme lors des tests, le cache doit être défini sur une taille relativement grande, sinon l'instruction ne peut pas être exécutée avec succès

　

La différence entre max_binlog_cache_size et binlog_cache_size est que le premier est un cache au niveau de l'instance et le second est un cache au niveau de la session. Si la quantité de concurrence est importante, vous devez envisager de définir max_binlog_cache_size légèrement plus grand.

La valeur par défaut max_binlog_cache_size est de 4 Go, et la valeur maximale est également de 4 Go. Le paramètre ici pour les tests est de 100 Mo (104857600/1024.0/1024.0)

.

La taille de mémoire maximale définie par max_binlog_cache_size est de 4 Go. Si le contenu du serveur est volumineux, par exemple 128 Go ou plus, cela ne fait pas de mal de définir max_binlog_cache_size au maximum par défaut, car cela est nécessaire pour garantir une écriture simultanée réussie.
Quant au binlog_cache_size au niveau de la session, vous pouvez l'ajuster en fonction de la situation commerciale. Personnellement, je pense que le définir légèrement plus grand n'est pas un gros problème. Après tout, en plus des opérations OLTP normales, il y a certaines données. l'extraction ou la fusion de données telles que les tâches planifiées. De telles opérations peuvent générer une grande quantité de journaux.
On dit que vous pouvez juger si binlog_cache_size doit être ajusté en regardant binlog_cache_disk_use et binlog_cache_use.
Mais ce paramètre n'a pas été trouvé dans MySQL5.7.18

9, d'autres paramètres du journal binaire

max_binlog_stmt_cache_size est pour les déclarations non transactionnelles, les paramètres non transactionnels ne sont pas concernés pour le moment
Je me souviens d'une fois, j'ai vu un maître dire que l'avantage du moteur innodb ne réside pas seulement dans le support transactionnel par rapport au moteur myisam non transactionnel. , lisez L'écart de performances devient de plus en plus petit, c'est pourquoi MySQL définit innodb comme moteur par défaut.
Abandonner myisam et aller sur innodb est la bonne voie.
binlog_checksum est utilisé comme somme de contrôle maître-esclave pour la réplication. Je n'ai pas encore étudié ce paramètre, donc je ne le laisserai pas seul
Pour plus de détails, veuillez vous référer à l'article de Xiangxiangshen.

Résumé :

Le journal binaire MySQL n'est pas seulement utilisé pour la fonctionnalité (réplication maître-esclave), mais aussi pour la sécurité (journal binaire ) et les opérations transactionnelles lorsque le journal binaire est activé, il peut donc être considéré comme une configuration essentielle pour un environnement de production.
Dans le même temps, ses différents paramètres affecteront certaines opérations, c'est pourquoi les paramètres du journal binaire doivent faire l'objet d'une attention particulière pour garantir que la fonctionnalité et la disponibilité de la base de données sont garanties lors de leur utilisation.

Référence :

"Smear MySQL"

Et divers flip books, documents en ligne

Prise l’action peut changer vos schémas de pensée et votre peur.

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!