Raisons et solutions du retard de synchronisation maître-esclave MySQL

Les principes de base maître-esclave de MySQL, les principales formes et le principe de délai de synchronisation maître-esclave (séparation lecture-écriture) conduisent à des problèmes et solutions d'incohérence des données maître-esclave

1. La différence entre les bases de données maître et esclave

La base de données esclave (Slave) est une sauvegarde de la base de données maître (Master) change. , la base de données esclave doit être mise à jour. Ces cycles de mise à jour du logiciel de base de données peuvent être conçus. Il s’agit d’un moyen d’améliorer la sécurité des informations. Les serveurs de base de données maître et esclave ne se trouvent pas au même emplacement géographique, la base de données peut donc être sauvegardée en cas d'accident.

(1) Division du travail maître-esclave

Le Maître est responsable de la charge des opérations d'écriture, ce qui signifie que toutes les opérations d'écriture sont effectuées sur le Maître, tandis que les opérations de lecture sont Allouer à l'esclave. Cela peut grandement améliorer l’efficacité de la lecture. Dans les applications Internet générales, après quelques enquêtes de données, il est conclu que le rapport lecture/écriture est d'environ 10:1, ce qui signifie qu'un grand nombre d'opérations de données sont concentrées sur les opérations de lecture, c'est pourquoi nous avons plusieurs raisons d'esclaves. Mais pourquoi séparer la lecture et l’écriture ? Le personnel de R&D familier avec la base de données sait tous que les opérations d'écriture impliquent des problèmes de verrouillage, qu'il s'agisse de verrous de lignes, de verrous de tables ou de verrous de blocs, qui réduisent relativement l'efficacité d'exécution du système. Notre séparation consiste à concentrer les opérations d'écriture sur un nœud, tandis que les opérations de lecture sont effectuées sur N autres nœuds. Cela améliore efficacement l'efficacité de lecture et garantit la haute disponibilité du système.

(2) Processus de base
1) La synchronisation maître-esclave de Mysql signifie que lorsque les données changent dans le maître (bibliothèque principale), elles seront synchronisées avec l'esclave (bibliothèque esclave ) en temps réel .
2) La réplication maître-esclave peut augmenter horizontalement la capacité de charge, la tolérance aux pannes, la haute disponibilité et la sauvegarde des données de la base de données.

3) Qu'il s'agisse de supprimer, mettre à jour, insérer ou créer des fonctions ou des procédures stockées, elles sont toutes sur le maître Lorsque le maître a des opérations, l'esclave recevra rapidement ces opérations et effectuera la synchronisation.

(3) Objectif et conditions
1), objectif de réplication maître-esclave MySQL
●Reprise après sinistre en temps réel, utilisée pour le basculement
●Séparation de la lecture et de l'écriture, fourniture de services de requête
●Sauvegarde pour éviter d'affecter l'activité
2), conditions nécessaires au déploiement maître-esclave :
●Activer les journaux binlog dans le base de données principale (définissez le paramètre log-bin)
●L'identifiant du serveur maître-esclave est différent
●Le serveur esclave peut se connecter à la base de données maître

2. La granularité, le principe et la forme de la synchronisation maître-esclave :

(1), trois granularités principales de mise en œuvre
La synchronisation maître-esclave détaillée a principalement trois formes : déclaration, ligne, mixte
　1), déclaration : oui Écrivez les instructions SQL pour les opérations de base de données dans le binlog
　2), ligne : Écrivez les modifications de chaque élément de données dans le binlog.
3), mixte : un mélange d'énoncé et de rang. Mysql décide quand écrire le binlog au format instruction et quand écrire le binlog au format ligne.

(2), grands principes de mise en œuvre, opérations spécifiques, schéma schématique

1), fonctionnement sur la machine maître :
Quand les données sur le maître changent, les changements d'événement seront écrits dans le journal bin dans l'ordre. Lorsque l'esclave est connecté au maître, la machine maître démarre le thread de vidage du journal binaire pour l'esclave. Lorsque le journal binaire du maître change, le thread de vidage du journal binaire en informera l'esclave et enverra le contenu du journal binaire correspondant à l'esclave.
2), opérer sur la machine esclave :

Lorsque la synchronisation maître-esclave est activée, deux threads seront créés sur l'esclave : le thread IO. Ce thread est connecté à la machine maître et le thread de vidage du journal binaire sur la machine maître enverra le contenu du journal binaire au thread IO. Après avoir reçu le contenu du journal binaire, le thread d'E/S écrit le contenu dans le journal de relais local ; Ce thread lit le journal ralay écrit par le thread d'E/S. Et selon le journal du relais. Et effectuez les opérations correspondantes sur la base de données esclave en fonction du contenu du journal de relais.

3) Le diagramme schématique de la réplication maître-esclave MySQL est le suivant :

La base de données esclave génère deux threads, une E/S thread et un thread SQL ;
Le thread d'E/S demande le journal binlog de la bibliothèque principale et écrit le journal binlog obtenu dans le fichier journal de relais (journal de relais)
La bibliothèque principale générera un thread de vidage de journal ; fournir à l'esclave Le thread d'E/S de la bibliothèque transmet binlog
Le thread SQL lira le journal dans le fichier journal du relais et l'analysera en opérations spécifiques pour obtenir des opérations maître-esclave cohérentes et des données finales cohérentes

(2), forme maître-esclave

La réplication maître-esclave MySQL est flexible

● Un maître et un esclave
● La réplication maître-maître
● Un maître et plusieurs esclaves --- expansion Performances de lecture du système, car la lecture est lue à partir de la bibliothèque esclave
● Plusieurs maîtres et un esclave --- pris en charge depuis 5.7

● Réplication en cascade ---

3. Problèmes, causes et solutions tels que le retard dans la synchronisation maître-esclave :

(1), problème de retard dans MySQL synchronisation esclave de la base de données

1) Paramètres associés :

Exécutez d'abord show slave satus sur le serveur, vous pouvez voir de nombreux paramètres synchronisés :

Master_Log_File : Le nom du fichier journal binaire du serveur maître actuellement lu par le thread d'E/S dans SLAVE
Read_Master_Log_Pos : Dans le journal binaire actuel du serveur maître, la position que le thread d'E/S dans SLAVE a lu
Relay_Log_File : Le nom du fichier journal de relais que le thread SQL est en train de lire et d'exécuter
Relay_Log_Pos : La position dans le journal de relais actuel que le thread SQL a lu et exécuté
Relay_Master_Log_File : Par le thread SQL Le nom du fichier journal binaire contenant les événements les plus récents
SLAVE_IO_RUNNING : Si le thread d'E/S est démarré et connecté avec succès au serveur principal
SLAVE_SQL_Running : Si le thread SQL est activé
Seconds_Behind_master : L'intervalle de temps entre le thread SQL du serveur et le thread d'E/S du serveur esclave, en secondes.
Un délai de synchronisation de la bibliothèque esclave se produit ● Le paramètre d'affichage de l'état de l'esclave Show Seconds_Behind_Master n'est pas 0, cette valeur peut être très grande
● Le paramètre d'affichage de l'état de l'esclave Show Relay_Master_Log_File et Master_Log_File montrent que le numéro du journal bin est très différent, indiquant que le bin-log n'est pas synchronisé dans le temps sur la base de données esclave, donc le bin-log récemment exécuté et le bin-log lu par le thread IO actuel sont très différents
● Il existe un grand nombre de mysql -relay-log logs dans le répertoire de données de la base de données esclave mysql. , le journal sera automatiquement supprimé par le système une fois la synchronisation terminée. Il existe un grand nombre de journaux, indiquant que le délai de synchronisation maître-esclave est très important. 🎜>

(2), problème de délai de synchronisation de l'esclave de la base de données MySQL

1), principe du délai de synchronisation maître-esclave de la base de données MySQL mysql master- principe de synchronisation esclave : la bibliothèque principale écrit le binlog de manière séquentielle pour les opérations d'écriture, et le thread unique de la bibliothèque esclave se rend dans la bibliothèque principale pour lire et écrire séquentiellement "Opération binlog", récupérer le binlog de la bibliothèque et l'exécuter localement (écrit de manière aléatoire ) pour garantir que les données maître-esclave sont logiquement cohérentes. La réplication maître-esclave de MySQL est une opération monothread. La bibliothèque principale génère un journal binaire pour tous les DDL et DML. Le journal binaire est écrit de manière séquentielle, il est donc très efficace. Le thread Slave_IO_Running de l'esclave accède à la bibliothèque principale pour obtenir le journal. , ce qui est plus efficace. Étape suivante, question Ici, le thread Slave_SQL_Running de l'esclave implémente les opérations DDL et DML de la bibliothèque principale sur l'esclave. Les opérations d'E/S de DML et DDL sont aléatoires, non séquentielles, et le coût est beaucoup plus élevé. D'autres requêtes sur l'esclave peuvent également provoquer un conflit de verrouillage. Puisque Slave_SQL_Running est également monothread, l'exécution d'une carte DDL principale prendra 10 minutes. Ensuite, tous les DDL suivants attendront que ce DDL soit exécuté avant de continuer, ce qui entraîne des retards. Certains amis demanderont : « Le même DDL sur la bibliothèque principale doit également être exécuté pendant 10 minutes. Pourquoi l'esclave est-il retardé ? » La réponse est que le maître peut s'exécuter simultanément, mais pas le thread Slave_SQL_Running.

2) Comment se produit le délai de synchronisation maître-esclave dans la base de données MySQL ? Lorsque la concurrence TPS de la bibliothèque principale est élevée et que le nombre de DDL générés dépasse ce qu'un thread SQL de l'esclave peut supporter, des retards se produiront. Bien sûr, il peut également y avoir des attentes de verrouillage causées par les grandes requêtes de l'esclave. Raison principale : la base de données est soumise à trop de pression pour lire et écrire en entreprise, la charge de calcul du processeur est lourde, la charge de la carte réseau est lourde et les E/S aléatoires du disque dur sont trop élevées. Raisons secondaires : l'impact sur les performances de. lecture et écriture du journal binaire et délai de transmission réseau.

(3), Solution de retard pour la synchronisation esclave de la base de données MySql

1), Aspects d'architecture

1. La mise en œuvre de la couche de persistance métier adopte une architecture de sous-base de données, et le service mysql peut être étendu en parallèle pour répartir la pression.

2. Séparez la lecture et l'écriture dans une seule bibliothèque, un maître et plusieurs esclaves, le maître écrit et les esclaves lisent, pour répartir la pression. De cette façon, la pression de la bibliothèque esclave est supérieure à celle de la bibliothèque principale, protégeant ainsi la bibliothèque principale.

3. L'infrastructure de service ajoute une couche de cache Memcache ou Redis entre l'entreprise et MySQL. Réduisez la pression de lecture de MySQL.

4. MySQL pour différentes entreprises est physiquement placé sur différentes machines pour répartir la pression.

5. Utilisez un meilleur équipement matériel que la bibliothèque principale car le résumé esclave de MySQL a moins de pression et le délai deviendra naturellement plus petit.

2) En termes de matériel

1. Utilisez un bon serveur, par exemple, 4u a des performances nettement meilleures que 2u, et 2u a des performances nettement meilleures que 1u. .

2. Utilisez un SSD, une baie de disques ou un san pour le stockage afin d'améliorer les performances d'écriture aléatoire.

3. Le maître et l'esclave sont garantis sous le même switch et dans un environnement 10G.

En résumé, si le matériel est solide, le délai deviendra naturellement plus petit. En bref, la solution pour minimiser la latence est l’argent et le temps.

3), l'accélération de la synchronisation maître-esclave mysql

1. Sync_binlog est défini sur 0 du côté esclave

2. -updates slave Les mises à jour que le serveur reçoit du maître ne sont pas enregistrées dans son journal binaire.

3. Désactivez directement le binlog côté esclave

4 Côté esclave, si le moteur de stockage utilisé est innodb, innodb_flush_log_at_trx_commit =2

4. ), à partir du système de fichiers Optimisation des propres attributs

Le côté maître modifie l'attribut etime des fichiers dans les systèmes de fichiers Linux et Unix. Étant donné que le système d'exploitation réécrit l'heure de l'opération de lecture sur le disque à chaque fois qu'un fichier est lu, cela n'est pas possible pour les fichiers de base de données fréquents. opérations de lecture. Si nécessaire, cela ne fera qu'augmenter la charge sur le système de disque et affecter les performances d'E/S. Vous pouvez organiser le système d'exploitation pour écrire des informations atime en définissant l'attribut mount du système de fichiers. L'opération sous Linux est la suivante : ouvrez /etc/fstab, ajoutez le paramètre noatime /dev/sdb1 /data reiserfs noatime 1 2, puis remontez le fichier. système de fichiers #mount -oremount /data

5), la bibliothèque principale d'ajustement des paramètres de synchronisation est écrite, qui a une sécurité des données plus élevée, telle que sync_binlog=1, innodb_flush_log_at_trx_commit = 1 et d'autres paramètres sont nécessaires. L'esclave n'a pas besoin d'une sécurité de données aussi élevée. Vous pouvez définir sync_binlog sur 0 ou désactiver binlog peut également être défini sur 0 pour améliorer l'efficacité d'exécution de SQL

1. fournit un paramètre sync_binlog pour Le journal binaire de la base de données de contrôle est vidé sur le disque. Par défaut, sync_binlog=0, ce qui signifie que MySQL ne contrôle pas l'actualisation du binlog et que le système de fichiers lui-même contrôle l'actualisation de son cache. Les performances à l’heure actuelle sont les meilleures, mais le risque est également le plus élevé. Une fois le système en panne, toutes les informations binlog contenues dans binlog_cache seront perdues.

Si sync_binlog>0, cela signifie que chaque transaction sync_binlog est soumise, MySQL appelle l'opération d'actualisation du système de fichiers pour vider le cache. Le paramètre le plus sûr est sync_binlog=1, ce qui signifie que chaque fois qu'une transaction est soumise, MySQL videra le binlog. Il s'agit du paramètre le plus sûr mais présente la plus grande perte de performances. Dans ce cas, le système peut perdre les données d'une transaction uniquement si le système d'exploitation de l'hôte sur lequel se trouve la base de données est endommagé ou perd soudainement de l'alimentation. Cependant, bien que binlog soit une IO séquentielle, définir sync_binlog=1 et que plusieurs transactions soient soumises en même temps affectera également grandement les performances de MySQL et IO. Bien que cela puisse être atténué grâce à des correctifs de validation de groupe, une fréquence de rafraîchissement trop élevée aura également un impact important sur les E/S.

Pour les systèmes avec des transactions simultanées élevées, l'écart de performances d'écriture entre les systèmes avec "sync_binlog" défini sur 0 et défini sur 1 peut atteindre 5 fois ou plus. Par conséquent, le sync_binlog défini par de nombreux administrateurs de base de données MySQL n'est pas le 1 le plus sûr, mais 2 ou 0. Cela sacrifie une certaine cohérence pour obtenir une concurrence et des performances plus élevées. Par défaut, le binlog n'est pas synchronisé avec le disque dur à chaque fois qu'il est écrit. Ainsi, si le système d'exploitation ou la machine (pas seulement le serveur MySQL) plante, il est possible que la dernière instruction du binlog soit perdue. Pour éviter cela, vous pouvez utiliser la variable globale sync_binlog (1 est la valeur la plus sûre, mais aussi la plus lente) pour synchroniser le binlog avec le disque dur après chaque N écritures du binlog. Même si sync_binlog est défini sur 1, lorsqu'un crash se produit, il peut y avoir des incohérences entre le contenu de la table et le contenu du binlog.

2. innodb_flush_log_at_trx_commit (cela fonctionne très bien) Vous vous plaignez du fait qu'Innodb est 100 fois plus lent que MyISAM ? Alors vous avez probablement oublié d'ajuster cette valeur. La valeur par défaut de 1 signifie que chaque validation de transaction ou instruction en dehors de la transaction doit écrire le journal sur le disque dur (vidage), ce qui prend beaucoup de temps. Surtout lorsque vous utilisez un cache sauvegardé par batterie. Le définir sur 2 convient à de nombreuses applications, en particulier celles transférées depuis les tables MyISAM. Cela signifie ne pas écrire sur le disque dur mais écrire dans le cache système. Les journaux sont toujours vidés sur le disque toutes les secondes, vous ne perdrez donc généralement pas plus de 1 à 2 secondes de mises à jour. Le définir sur 0 sera plus rapide, mais la sécurité est médiocre. Même si MySQL raccroche, les données de transaction peuvent être perdues. La valeur 2 n'entraînera une perte de données que lorsque l'ensemble du système d'exploitation se bloquera.

3. La commande ls(1) peut être utilisée pour lister les atime, ctime et mtime du fichier.

heure d'accès au fichier atime fichier ctime qui change lors de la lecture ou de l'exécution du fichier créez l'heure fichier mtime qui change à mesure que le contenu de l'inode change lors de l'écriture du fichier, du changement de propriétaire, des autorisations ou des paramètres de lien, l'heure modifiée change comme le contenu du fichier change lors de l'écriture du fichier ls -lc filename liste les ctimels du fichier -lu filename liste les atimels du fichier -l filename liste le mtimestat du fichier filename liste atime, mtime, ctimeatime ne sont pas nécessairement dans Le fichier est modifié après l'accès car : lors de l'utilisation du système de fichiers ext3, si le paramètre noatime est utilisé lors du montage, les informations atime ne seront pas mises à jour. Ces trois horodatages sont placés dans l'inode. Si mtime et atime sont modifiés, l'inode changera définitivement. Puisque l'inode est modifié, le ctime sera également modifié. La raison pour laquelle noatime est utilisé dans l'option de montage est que le fichier. le système ne veut pas le faire. Trop de modifications pour améliorer les performances de lecture

(4), synchronisation de la base de données MySql, autres problèmes et solutions à partir de la base de données.

1) Problèmes avec la réplication maître-esclave MySQL : ● Après la panne de la base de données principale, des données peuvent être perdues ● La base de données esclave n'a qu'un seul thread SQL, la base de données principale est soumis à une forte pression d'écriture et la réplication est susceptible d'être retardée 2). Solutions : ● Réplication semi-synchrone --- résoudre le problème de la perte de données ● Réplication parallèle --- résoudre le problème du délai de réplication de la base de données

3), réplication semi-synchrone mysql semi-sync (réplication semi-synchrone) réplication semi-synchrone : ● 5.5 est intégré à mysql, existe sous forme de plug-in, et nécessite à installer séparément ● Assurez-vous que le binlog est au moins 1 après la soumission de la transaction Transfert vers une bibliothèque esclave ● Il n'y a aucune garantie que la bibliothèque esclave a fini d'appliquer le binlog de cette transaction ● Les performances seront réduites dans une certaine mesure, et le temps de réponse sera plus long. ● Une anomalie du réseau ou un temps d'arrêt de la bibliothèque esclave entraînera le blocage de la bibliothèque principale jusqu'à l'expiration du délai ou la récupération de la bibliothèque esclave. 4) , Réplication maître-esclave - Comparaison du principe de réplication asynchrone, de réplication semi-synchrone et de réplication parallèle. principes

a. Principe de réplication asynchrone :

b. Principe de réplication semi-synchrone :

Après l'écriture de la bibliothèque principale. le binlog, la transaction doit renvoyer un message accepté de la bibliothèque avant de le renvoyer au client ; 5.5 est intégré à mysql et existe sous la forme d'un plug-in, qui doit être installé séparément pour assurer la transaction Après soumission, le journal binaire est transféré vers au moins une bibliothèque esclave. Il n'y a aucune garantie que les performances du journal binaire de l'application de bibliothèque esclave lors de l'exécution de cette transaction seront réduites dans une certaine mesure en raison d'anomalies du réseau ou que la bibliothèque esclave est en panne et que la bibliothèque maître est en panne. bloqué jusqu'à ce que le délai soit écoulé ou que la bibliothèque esclave soit récupérée

c. Réplication parallèle Réplication parallèle MySQL ● Nouveau dans Community Edition 5.6 ● Parallèle fait référence au binlog d'application multi-thread de la bibliothèque ● Binlog est appliqué en parallèle au niveau de la bibliothèque, et les modifications de données dans la même bibliothèque sont toujours en série (la réplication parallèle dans la version 5.7 est basée sur le groupe Transaction) set set global slave_parallel_workers=10 ; définit le nombre de threads SQL sur 10

Principe : application binlog multithread à partir de la bibliothèque. Un nouveau binlog d'application parallèle au niveau de la bibliothèque est ajouté dans la communauté 5.6. Les modifications de données dans la même bibliothèque sont toujours en série. La version 5.7 de la réplication parallèle est basée sur des groupes de transactions

. Pour plus d'articles techniques liés à MySQL, veuillez visiter la colonne

Tutoriel MySQL pour apprendre !

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!