Résoudre le problème du délai de synchronisation de la base de données maître-esclave dans MySQL

Récemment, je faisais un test de synchronisation de base de données maître-esclave MySQL et j'ai trouvé quelques problèmes, parmi lesquels le problème du délai de synchronisation maître-esclave en fait partie. Le contenu suivant est quelques explications trouvées sur Internet, enregistrées pour votre propre apprentissage. ;

La synchronisation maître-esclave de MySQL est une architecture très mature. Les avantages sont : ① Le travail de requête peut être effectué sur le serveur esclave (c'est-à-dire ce que nous appelons souvent la fonction de lecture), ce qui réduit la pression sur le serveur. le serveur maître ; ② La sauvegarde est effectuée sur le serveur maître esclave pour éviter la période de sauvegarde. Affecte le service du serveur principal ; ③ En cas de problème avec le serveur principal, vous pouvez passer au serveur esclave.

Je pense que tout le monde est très conscient de ces avantages et adoptera également cette solution dans le déploiement de projets. Cependant, la synchronisation maître-esclave de MySQL a toujours eu le problème du retard de la base de données esclave, alors pourquoi ce problème se produit-il ? Comment résoudre ce problème ?

1. Principe de délai de synchronisation maître-esclave de la base de données MySQL.

2. Comment se produit le délai de synchronisation maître-esclave dans la base de données MySQL ?

3. Solution de délai de synchronisation maître-esclave de la base de données MySQL.

1. Principe de délai de synchronisation maître-esclave de la base de données MySQL.

Réponse : Lorsque nous parlons du principe du délai de synchronisation maître-esclave dans la base de données MySQL, nous devons commencer par le principe de la réplication maître-esclave de la base de données MySQL. La réplication maître-esclave de Mysql est une opération monothread, et la base de données principale génère tous les Binlog DDL et DML, le binlog est écrit de manière séquentielle, il est donc très efficace. Le thread Slave_IO_Running de l'esclave va à la bibliothèque principale pour obtenir le journal, ce qui est très efficace. Le thread Slave_SQL_Running 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 ?

Réponse : lorsque la concurrence TPS de la bibliothèque principale est élevée, le nombre de DDL générés dépasse la plage qu'un thread SQL de l'esclave peut supporter, puis un retard se produit, et bien sûr, il peut y avoir des instructions de requête volumineuses avec l'esclave. Une attente de verrouillage se produit.

3. Solution de délai de synchronisation maître-esclave de la base de données MySQL

Réponse : La solution la plus simple pour réduire le délai de synchronisation esclave est d'optimiser l'architecture et d'essayer de faire exécuter rapidement le DDL de la base de données principale. Il y a aussi le fait que la bibliothèque principale est écrite avec une sécurité de données élevée, telle que sync_binlog=1, innodb_flush_log_at_trx_commit = 1 et d'autres paramètres. Cependant, 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. innodb_flushlog peut également être défini sur 0 pour améliorer l'efficacité de l'exécution SQL. L'autre consiste à utiliser un meilleur périphérique matériel que la bibliothèque principale comme esclave.

mysql-5.6.3 prend déjà en charge la réplication maître-esclave multithread. Le principe est similaire à celui de Ding Qi. Ding Qi utilise des tables comme multi-threads, tandis qu'Oracle utilise une base de données (schéma) comme unité pour effectuer des multi-threads. Différentes bibliothèques peuvent utiliser différents threads de réplication.

sync_binlog=1

Cela permet à MySQL de synchroniser le contenu du journal binaire sur le disque à chaque fois qu'il valide une transaction

Par défaut, toutes les écritures de Binlog ne sont pas synchronisées avec le disque dur . Par conséquent, si le système d'exploitation ou la machine (pas seulement le serveur MySQL) tombe en panne, 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. Si vous utilisez une table InnoDB, le serveur MySQL gère l'instruction COMMIT, il écrit l'intégralité de la transaction dans le binlog et valide la transaction dans InnoDB. Si un crash survient entre deux opérations, la transaction est annulée par InnoDB au redémarrage, mais elle existe toujours dans le binlog. Vous pouvez utiliser l'option --innodb-safe-binlog pour augmenter la cohérence entre le contenu de la table InnoDB et le binlog. (Remarque : --innodb-safe-binlog n'est pas requis dans MySQL 5.1 ; cette option est obsolète en raison de l'introduction de ) et (true par défaut) le journal InnoDB est synchronisé avec le disque dur. L'effet de cette option est que. lors du redémarrage après un crash, une fois la transaction annulée, le serveur MySQL coupe la transaction InnoDB annulée du journal binaire. Cela garantit que le binlog renvoie les données exactes de la table InnoDB, etc., et maintient le serveur esclave synchronisé avec le serveur maître (sans recevoir d'instructions de restauration).

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 (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 régler 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.

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!