Gründe und Lösungen für die Verzögerung der MySQL-Master-Slave-Synchronisierung

MySQL-Master-Slave-Grundprinzipien, Hauptformen und Master-Slave-Synchronisationsverzögerungsprinzip (Lese-Schreib-Trennung) führen zu Problemen und Lösungen der Master-Slave-Dateninkonsistenz

1. Der Unterschied zwischen Master- und Slave-Datenbanken

Die Slave-Datenbank (Slave) ist eine Sicherung der Master-Datenbank (Master). , die Slave-Datenbank muss aktualisiert werden. Diese Datenbank-Software-Update-Zyklen können entworfen werden. Dies ist ein Mittel zur Verbesserung der Informationssicherheit. Die Master- und Slave-Datenbankserver befinden sich nicht am selben geografischen Standort, sodass die Datenbank im Falle eines Unfalls gespeichert werden kann.

(1) Master-Slave-Arbeitsteilung

Der Master ist für die Last der Schreibvorgänge verantwortlich, was bedeutet, dass alle Schreibvorgänge auf dem Master ausgeführt werden. während Lesevorgänge dem Slave zugewiesen werden. Dies kann die Leseeffizienz erheblich verbessern. Bei allgemeinen Internetanwendungen kommt man nach einigen Datenerhebungen zu dem Schluss, dass das Lese-/Schreibverhältnis etwa 10:1 beträgt, was bedeutet, dass sich eine große Anzahl von Datenvorgängen auf Lesevorgänge konzentriert, weshalb wir mehrere Slave-Gründe haben. Aber warum sollte man Lesen und Schreiben trennen? F&E-Mitarbeiter, die mit DB vertraut sind, wissen alle, dass Schreibvorgänge mit Sperrproblemen verbunden sind, unabhängig davon, ob es sich um Zeilensperren, Tabellensperren oder Blocksperren handelt, die die Effizienz der Systemausführung relativ verringern. Unsere Trennung besteht darin, Schreibvorgänge auf einen Knoten zu konzentrieren, während Lesevorgänge auf anderen N-Knoten ausgeführt werden. Dadurch wird die Leseeffizienz effektiv verbessert und die hohe Verfügbarkeit des Systems sichergestellt.

(2) Grundlegender Prozess
1) Die Master-Slave-Synchronisation von MySQL bedeutet, dass Daten, die sich im Master (Hauptbibliothek) ändern, mit dem Slave (Slave-Bibliothek) synchronisiert werden ) in Echtzeit .
2) Die Master-Slave-Replikation kann die Ladekapazität der Datenbank, Fehlertoleranz, Hochverfügbarkeit und Datensicherung horizontal erweitern.

3) Ob es sich um Lösch-, Aktualisierungs-, Einfüge- oder Erstellungsfunktionen oder gespeicherte Prozeduren handelt, sie befinden sich alle auf dem Master. Wenn der Master Vorgänge ausführt, empfängt der Slave diese Vorgänge schnell und führt eine Synchronisierung durch.

(3) Zweck und Bedingungen
1), Zweck der MySQL-Master-Slave-Replikation
●Echtzeit-Notfallwiederherstellung, verwendet für Failover
●Trennung von Lesen und Schreiben, Bereitstellung von Abfragediensten
●Sicherung, um Geschäftsbeeinträchtigungen zu vermeiden
2), notwendige Bedingungen für die Master-Slave-Bereitstellung:
●Aktivieren Sie Binlog-Protokolle im Hauptdatenbank (Legen Sie den Log-Bin-Parameter fest)
●Die Master-Slave-Server-ID ist unterschiedlich
●Der Slave-Server kann eine Verbindung zur Master-Datenbank herstellen

2. Granularität, Prinzip und Form der Master-Slave-Synchronisation:

(1), drei Hauptimplementierungsgranularitäten
Detaillierte Master-Slave-Synchronisation hat hauptsächlich drei Formen : Anweisung, Zeile, gemischt
　1), Anweisung: ja Schreiben Sie die SQL-Anweisungen für Datenbankoperationen in das Binlog
　2), Zeile: Schreiben Sie die Änderungen in jedem Datenelement in das Binlog.
3), gemischt: eine Mischung aus Aussage und Reihe. MySQL entscheidet, wann Binlog im Anweisungsformat und wann Binlog im Zeilenformat geschrieben wird.

(2), Hauptimplementierungsprinzipien, spezifische Vorgänge, schematisches Diagramm

1), Betrieb auf der Master-Maschine:
Wann Wenn sich die Daten auf dem Master ändern, werden die Ereignisänderungen der Reihe nach in das Bin-Protokoll geschrieben. Wenn der Slave mit dem Master verbunden ist, startet der Master-Computer den Binlog-Dump-Thread für den Slave. Wenn sich das Binlog des Masters ändert, benachrichtigt der Bin-Log-Dump-Thread den Slave und sendet den entsprechenden Binlog-Inhalt an den Slave.
2), auf dem Slave-Computer ausführen:

Wenn die Master-Slave-Synchronisierung aktiviert ist, werden auf dem Slave zwei Threads erstellt: IO-Thread. Dieser Thread ist mit dem Master-Computer verbunden und der Binlog-Dump-Thread auf dem Master-Computer sendet den Inhalt des Binlogs an den E/A-Thread. Nach dem Empfang des Binlog-Inhalts schreibt der E/A-Thread den Inhalt in das lokale Relay-Protokoll. Dieser Thread liest das vom E/A-Thread geschriebene Ralay-Protokoll. Und laut Relay-Log. Und führen Sie entsprechende Vorgänge in der Slave-Datenbank entsprechend dem Inhalt des Relay-Protokolls durch.

3) Das schematische Diagramm der MySQL-Master-Slave-Replikation lautet wie folgt:

Die Slave-Datenbank generiert zwei Threads, einen E/A Thread und ein SQL-Thread;
Der E/A-Thread fordert das Binlog-Protokoll der Hauptbibliothek an und schreibt das erhaltene Binlog-Protokoll in die Relay-Log-Datei (Relay-Log-Datei).
Die Hauptbibliothek generiert einen Log-Dump-Thread Bereitstellung von Binlog für den Slave.
Der SQL-Thread liest das Protokoll in der Relay-Protokolldatei und analysiert es in bestimmte Operationen, um konsistente Master-Slave-Operationen und konsistente Enddaten zu erreichen >

(2), Master-Slave-Form

MySQL-Master-Slave-Replikation ist flexibel

● Ein Master und ein Slave

● Master-Master-Replikation
● Eins Master und mehrere Slaves --- Erweiterung der Systemleseleistung, da der Lesevorgang aus der Slave-Bibliothek erfolgt
● Mehrere Master und ein Slave---unterstützt ab 5.7

● Kaskadenreplikation---

3. Probleme, Ursachen und Lösungen wie Verzögerung bei der Master-Slave-Synchronisation:

(1), Verzögerungsproblem in MySQL-Datenbank-Slave-Synchronisierung

1) Zugehörige Parameter:

Führen Sie zuerst „Show Slave Status“ auf dem Server aus. Sie können viele synchronisierte Parameter sehen:

Master_Log_File: Der Name der Binärprotokolldatei des Master-Servers, die derzeit vom E/A-Thread in SLAVE gelesen wird.
Read_Master_Log_Pos: Im aktuellen Binärprotokoll des Master-Servers die Position, die der E/A-Thread in SLAVE gelesen hat
Relay_Log_File: Der Name der Relay-Protokolldatei, die der SQL-Thread gerade liest und ausführt.
Relay_Log_Pos: Die Position im aktuellen Relay-Protokoll, die der SQL-Thread gelesen und ausgeführt hat.
Relay_Master_Log_File: Nach SQL-Thread Name der Binärprotokolldatei mit den neuesten Ereignissen
SLAVE_IO_RUNNING: Ob der I/O-Thread gestartet und erfolgreich mit dem Hauptserver verbunden wurde
SLAVE_SQL_Running: Ob der SQL-Thread aktiviert ist
Seconds_Behind_master: Die Zeitlücke zwischen der Server-SQL-Thread und der Slave-Server-E/A-Thread in Sekunden.
Slave-Bibliothekssynchronisierungsverzögerung tritt auf. ● Anzeigeparameter für den Slave-Status anzeigen Seconds_Behind_Master ist nicht 0, dieser Wert kann sehr groß sein. Das Protokoll ist in der Slave-Datenbank nicht zeitlich synchronisiert, daher sind das kürzlich ausgeführte Bin-Protokoll und das vom aktuellen E/A-Thread gelesene Bin-Protokoll sehr unterschiedlich.
● Es gibt eine große Anzahl von MySQL-Relay-Log-Protokollen in der Das Datenverzeichnis der MySQL-Slave-Datenbank wird nach Abschluss der Synchronisierung automatisch vom System gelöscht. Es gibt eine große Anzahl von Protokollen, was darauf hinweist, dass die Master-Slave-Synchronisierungsverzögerung sehr schwerwiegend ist >
(2), MySQL-Datenbank-Slave-Synchronisationsverzögerungsproblem

1), MySQL-Datenbank-Master-Slave-Synchronisationsverzögerungsprinzip MySQL-Master-Slave-Synchronisationsprinzip: die Hauptbibliothek Schreibt Binlog nacheinander für Schreibvorgänge, und der einzelne Thread der Slave-Bibliothek geht zur Hauptbibliothek, um nacheinander zu lesen und zu schreiben. „Operation binlog“, ruft das Binlog aus der Bibliothek ab und führt es lokal aus (zufälliges Schreiben), um sicherzustellen, dass der Master- Slave-Daten sind logisch konsistent. Die Master-Slave-Replikation von MySQL ist eine Single-Thread-Operation. Die Hauptbibliothek generiert das Binlog für alle DDLs und DMLs. Das Binlog wird daher sehr effizient geschrieben. Der Slave_IO_Running-Thread geht zur Hauptbibliothek , was effizienter ist, Frage: Hier implementiert der Slave_SQL_Running-Thread die DDL- und DML-Operationen der Hauptbibliothek auf dem Slave. Die E/A-Vorgänge von DML und DDL sind zufällig und nicht sequentiell, und die Kosten sind viel höher. Andere Abfragen auf dem Slave können ebenfalls zu Sperrenkonflikten führen. Da Slave_SQL_Running ebenfalls Single-Threaded ist, dauert die Ausführung eines DDL-Kartenmasters 10 Minuten. Dann warten alle nachfolgenden DDLs auf die Ausführung dieses DDLs, bevor sie fortfahren, was zu Verzögerungen führt. Einige Freunde werden fragen: „Derselbe DDL in der Hauptbibliothek muss auch 10 Minuten lang ausgeführt werden. Warum ist der Slave verzögert?“ Die Antwort ist, dass der Master gleichzeitig ausgeführt werden kann, der Slave_SQL_Running-Thread jedoch nicht.

2) Wie kommt es zur Master-Slave-Synchronisationsverzögerung in der MySQL-Datenbank? Wenn die TPS-Parallelität der Hauptbibliothek hoch ist und die Anzahl der generierten DDL die Kapazität eines SQL-Threads des Slaves übersteigt, kann es natürlich auch zu Verzögerungen kommen, die durch die großen Abfrageanweisungen des Slaves verursacht werden. Hauptgrund: Die Datenbank steht beim Lesen und Schreiben zu stark unter Druck, die CPU-Rechenlast ist hoch, die Netzwerkkartenlast ist hoch und die zufälligen E/A-Vorgänge auf der Festplatte sind zu hoch. Sekundäre Gründe: Die Auswirkungen des Lesens und Schreibens auf die Leistung Schreiben von Binlog und Netzwerkübertragungsverzögerung.

(3), Verzögerungslösung für die MySQL-Datenbank-Slave-Synchronisierung

1), Architekturaspekte

1. Die Implementierung der Geschäftspersistenzschicht übernimmt eine Subdatenbankarchitektur, und der MySQL-Dienst kann parallel erweitert werden, um den Druck zu verteilen. 2. Getrenntes Lesen und Schreiben in einer einzigen Bibliothek, einem Master und mehreren Slaves, Master-Schreibvorgängen und Slave-Lesevorgängen, um den Druck zu verteilen. Auf diese Weise ist der Druck der Slave-Bibliothek höher als der der Hauptbibliothek, wodurch die Hauptbibliothek geschützt wird.

3. Die Service-Infrastruktur fügt eine Memcache- oder Redis-Cache-Schicht zwischen dem Unternehmen und MySQL hinzu. Reduzieren Sie den Lesedruck von MySQL.

4. MySQL wird für verschiedene Unternehmen physisch auf verschiedenen Maschinen platziert, um den Druck zu verteilen.

5. Verwenden Sie eine bessere Hardwareausrüstung als die Hauptbibliothek, da MySQL weniger Druck ausübt und die Verzögerung natürlich geringer wird.

2) In Bezug auf die Hardware

1 Verwenden Sie einen guten Server, zum Beispiel hat 4u eine deutlich bessere Leistung als 2u, und 2u hat eine deutlich bessere Leistung als 1u . 2. Verwenden Sie SSD oder Disk-Array oder San als Speicher, um die Leistung beim zufälligen Schreiben zu verbessern.

3. Master und Slave befinden sich garantiert unter demselben Switch und in einer 10G-Umgebung.

Zusammenfassend lässt sich sagen, dass die Verzögerung natürlich kleiner wird, wenn die Hardware stark ist. Kurz gesagt: Die Lösung zur Minimierung der Latenz liegt in Geld und Zeit.

3), MySQL-Master-Slave-Synchronisationsbeschleunigung

1 Sync_binlog ist auf der Slave-Seite auf 0 gesetzt 2 -updates Slave Aktualisierungen, die der Server vom Master-Server empfängt, werden nicht in seinem Binärprotokoll protokolliert.

3. Deaktivieren Sie das Binlog direkt auf der Slave-Seite

4. Wenn auf der Slave-Seite die verwendete Speicher-Engine innodb ist, ist innodb_flush_log_at_trx_commit =2

4 ), aus dem Dateisystem Optimierung eigener Attribute

Die Master-Seite ändert das etime-Attribut von Dateien in Linux- und Unix-Dateisystemen. Da das Betriebssystem jedes Mal, wenn eine Datei gelesen wird, die Zeit des Lesevorgangs auf die Festplatte zurückschreibt, ist dies für Datenbankdateien mit häufigem Lesen nicht möglich Bei Bedarf wird die Belastung des Festplattensystems nur erhöht und die E/A-Leistung beeinträchtigt. Sie können das Betriebssystem so organisieren, dass es Atime-Informationen schreibt, indem Sie das Mount-Attribut des Dateisystems festlegen. Der Vorgang unter Linux lautet: Öffnen Sie /etc/fstab, fügen Sie den Noatime-Parameter /dev/sdb1 /data reiserfs noatime 1 2 hinzu und mounten Sie ihn dann erneut Dateisystem #mount -oremount /data

5) wird die Hauptbibliothek zur Synchronisierungsparameteranpassung geschrieben, die eine höhere Datensicherheit aufweist, wie sync_binlog=1, innodb_flush_log_at_trx_commit = 1 und andere Einstellungen Der Slave benötigt keine so hohe Datensicherheit. Sie können sync_binlog auch auf 0 setzen, um die Ausführungseffizienz von SQL zu verbessern

1 stellt einen sync_binlog-Parameter bereit, um das Binlog der Steuerdatenbank auf die Festplatte zu übertragen. Standardmäßig ist sync_binlog=0, was bedeutet, dass MySQL die Aktualisierung des Binlogs nicht steuert und das Dateisystem selbst die Aktualisierung seines Caches steuert. Die Leistung ist zu diesem Zeitpunkt am besten, aber das Risiko ist auch am höchsten. Sobald das System abstürzt, gehen alle Binlog-Informationen im binlog_cache verloren.

Wenn sync_binlog>0, bedeutet dies, dass jede sync_binlog-Transaktion übermittelt wird und MySQL den Aktualisierungsvorgang des Dateisystems aufruft, um den Cache zu leeren. Die sicherste Einstellung ist sync_binlog=1, was bedeutet, dass MySQL jedes Mal, wenn eine Transaktion übermittelt wird, das Binlog leert. Dies ist die sicherste Einstellung, weist jedoch den größten Leistungsverlust auf. In diesem Fall kann das System nur dann die Daten einer Transaktion verlieren, wenn das Betriebssystem des Hosts, auf dem sich die Datenbank befindet, beschädigt ist oder plötzlich die Stromversorgung ausfällt. Obwohl Binlog eine sequentielle E/A ist, wirkt sich die Einstellung von sync_binlog=1 und die gleichzeitige Übermittlung mehrerer Transaktionen auch stark auf die Leistung von MySQL und E/A aus. Obwohl es durch Gruppen-Commit-Patches gemildert werden kann, hat eine zu hohe Aktualisierungsfrequenz auch große Auswirkungen auf IO.

Bei Systemen mit vielen gleichzeitigen Transaktionen kann die Lücke in der Schreibleistung zwischen Systemen, bei denen „sync_binlog“ auf 0 und 1 gesetzt ist, das Fünffache oder mehr betragen. Daher ist das von vielen MySQL-Datenbankadministratoren festgelegte sync_binlog nicht die sicherste 1, sondern 2 oder 0. Dadurch wird ein gewisses Maß an Konsistenz geopfert, um eine höhere Parallelität und Leistung zu erreichen. Standardmäßig wird das Binlog nicht bei jedem Schreibvorgang mit der Festplatte synchronisiert. Wenn also das Betriebssystem oder die Maschine (nicht nur der MySQL-Server) abstürzt, ist es möglich, dass die letzte Anweisung im Binlog verloren geht. Um dies zu verhindern, können Sie die globale Variable sync_binlog (1 ist der sicherste Wert, aber auch der langsamste) verwenden, um Binlog nach jedem N Binlog-Schreibvorgang mit der Festplatte zu synchronisieren. Selbst wenn sync_binlog auf 1 gesetzt ist, kann es bei einem Absturz zu Inkonsistenzen zwischen dem Tabelleninhalt und dem Binlog-Inhalt kommen.

2. innodb_flush_log_at_trx_commit (das funktioniert sehr gut) Sich darüber beschweren, dass Innodb 100-mal langsamer ist als MyISAM? Dann haben Sie wahrscheinlich vergessen, diesen Wert anzupassen. Der Standardwert 1 bedeutet, dass jeder Transaktions-Commit oder jede Anweisung außerhalb der Transaktion das Protokoll auf die Festplatte schreiben muss (Flush), was sehr zeitaufwändig ist. Besonders wenn der batteriegepufferte Cache verwendet wird. Die Einstellung auf 2 ist für viele Anwendungen in Ordnung, insbesondere für solche, die aus MyISAM-Tabellen übertragen werden. Dies bedeutet, dass nicht auf die Festplatte, sondern in den Systemcache geschrieben wird. Die Protokolle werden weiterhin jede Sekunde auf die Festplatte geschrieben, sodass Sie im Allgemeinen nicht mehr als 1–2 Sekunden an Aktualisierungen verlieren. Die Einstellung auf 0 ist schneller, aber die Sicherheit ist schlecht. Selbst wenn MySQL hängen bleibt, können Transaktionsdaten verloren gehen. Der Wert 2 führt nur dann zu Datenverlust, wenn das gesamte Betriebssystem hängt.

3. Mit dem Befehl ls(1) können atime, ctime und mtime der Datei aufgelistet werden.

atime-Dateizugriffszeit ctime-Datei, die sich ändert, wenn die Datei gelesen oder ausgeführt wird. Erstellungszeit. mtime-Datei, die sich ändert, wenn sich der Inhalt des Inodes ändert, wenn die Datei geschrieben wird, Besitzer, Berechtigungen oder Linkeinstellungen geändert werden Der Dateiinhalt ändert sich beim Schreiben der Datei. ls -lc Dateiname listet die Ctimels der Datei auf. -lu Dateiname listet die Atimels der Datei auf. -l Dateiname listet den Mtimestat der Datei auf. Dateiname listet atime, mtime, ctimeatime auf, die nicht unbedingt in der Datei enthalten sind Nach dem Zugriff geändert, weil: Wenn bei Verwendung des ext3-Dateisystems der Parameter noatime während des Mountens verwendet wird, werden die atime-Informationen nicht aktualisiert. Wenn mtime und atime geändert werden, ändert sich definitiv der Inode. Der Grund, warum noatime in der Mount-Option verwendet wird Das System möchte dies nicht tun. Zu viele Änderungen zur Verbesserung der Leseleistung

(4), MySQL-Datenbanksynchronisierung, andere Probleme und Lösungen aus der Datenbank

1) Probleme mit der MySQL-Master-Slave-Replikation: ● Nach dem Ausfall der Hauptdatenbank können Daten verloren gehen. ● Die Slave-Datenbank verfügt nur über einen SQL-Thread, die Hauptdatenbank steht unter großem Schreibdruck und die Replikation wird sich wahrscheinlich verzögern 2). Lösungen: ● Halbsynchrone Replikation – löst das Problem des Datenverlusts ● Parallele Replikation – löst das Problem der Replikationsverzögerung aus der Datenbank

3), halbsynchrone Replikation von MySQL, halbsynchrone (halbsynchrone Replikation) halbsynchrone Replikation: ● 5.5 ist in MySQL integriert, liegt in Form eines Plug-Ins vor und benötigt muss separat installiert werden. ● Stellen Sie sicher, dass das Binlog nach der Übermittlung der Transaktion mindestens 1 ist. und die Antwortzeit wird länger sein. ● Eine Netzwerkanomalie oder eine Ausfallzeit der Slave-Bibliothek führt dazu, dass die Hauptbibliothek bis zum Timeout oder der Wiederherstellung der Slave-Bibliothek hängen bleibt. 4) Master-Slave-Replikation – Vergleich des Prinzips der asynchronen Replikation, der halbsynchronen Replikation und der parallelen Replikation Prinzipien

a. Prinzip der asynchronen Replikation:

b. Prinzip der halbsynchronen Replikation:

Nachdem die Hauptbibliothek geschrieben wurde Im Binlog muss die Transaktion eine akzeptierte Nachricht von der Bibliothek zurückgeben, bevor sie an den Client zurückgegeben wird. 5.5 ist in MySQL integriert und liegt in Form eines Plug-Ins vor, das separat installiert werden muss, um die Transaktion sicherzustellen. Das Binlog wird an mindestens eine Slave-Bibliothek übertragen. Es gibt keine Garantie dafür, dass die Binlog-Leistung der Slave-Bibliotheksanwendung beim Abschluss dieser Transaktion aufgrund von Netzwerkanomalien in gewissem Maße beeinträchtigt wird oder dass die Slave-Bibliothek ausgefallen ist und die Master-Bibliothek ausgefallen ist bleibt hängen, bis das Zeitlimit überschritten wird oder die Slave-Bibliothek wiederhergestellt wird Wird parallel auf Bibliotheksebene angewendet, und Datenänderungen in derselben Bibliothek erfolgen weiterhin seriell (die parallele Replikation in Version 5.7 basiert auf der Transaktionsgruppe). >Prinzip: Multi-Thread-Anwenden von Binlog aus der Bibliothek. In der Community 5.6 wird ein neues paralleles Anwendungs-Binlog hinzugefügt. Die 5.7-Version der parallelen Replikation basiert weiterhin auf Transaktionsgruppen >

Weitere technische Artikel zum Thema MySQL finden Sie in der Spalte

MySQL-Tutorial.

Das obige ist der detaillierte Inhalt vonGründe und Lösungen für die Verzögerung der MySQL-Master-Slave-Synchronisierung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!