Detaillierte Erklärung der MySQL Master-Slave-Inkonsistenz und Lösungen

1. MySQL Master-Slave-Asynchronität

1.1 Netzwerkverzögerung

Da die MySQL Master-Slave-Replikation eine asynchrone Replikation auf Basis von Binlog ist

Beim Übertragen von Binlog-Dateien über das Netzwerk ist die Netzwerklatenz natürlich der Hauptgrund für die Master-Slave-Asynchronität. Dies kann insbesondere dann passieren, wenn Daten zwischen Computerräumen synchronisiert werden. Daher ist eine Trennung von Lesen und Schreiben erforderlich, und dem vorläufigen Entwurf der Geschäftsschicht sollte besondere Aufmerksamkeit gewidmet werden.

1.2 Die Auslastung der Master- und Slave-Maschinen ist inkonsistent

Da die MySQL-Master-Slave-Replikation einen IO-Thread auf der Masterdatenbank und einen SQL-Thread sowie einen IO-Thread daraus startet, verfügen alle Threads über unzureichende Ressourcen, wenn die Last auf einem der Rechner sehr hoch ist und dieser überlastet ist. Dies führt zu Master-Slave-Inkonsistenzen.

1.3 Inkonsistente max_allowed_packet-Einstellungen

Der für die Master-Datenbank festgelegte max_allowed_packet-Wert ist größer als der für die Slave-Datenbank. Wenn eine große SQL-Anweisung für die Master-Datenbank ausgeführt werden kann, ist sie für die Slave-Datenbank zu klein festgelegt und kann nicht ausgeführt werden, was zu Inkonsistenzen zwischen Master und Slave führt.

1.4 Inkonsistente Auto-Inkrement-Schlüssel

Die Master-Slave-Inkonsistenz wird durch die Inkonsistenz zwischen dem Startschlüsselwert des Auto-Inkrement-Schlüssels und der Auto-Inkrement-Schritteinstellung verursacht.

1.5 Probleme bei der Einstellung der Synchronisationsparameter

Wenn im Falle einer anormalen Ausfallzeit von MySQL sync_binlog=1 oder innodb_flush_log_at_trx_commit=1 nicht festgelegt ist, ist es sehr wahrscheinlich, dass die Binlog- oder Relaylog-Dateien beschädigt werden, was zu Inkonsistenzen zwischen dem Master und dem Slave führt.

1.6 Fehler

Die durch den MySQL-Bug verursachte Master-Slave-Asynchronität

1.7 Versionsinkonsistenz

Insbesondere wenn die höhere Version der Master und die niedrigere Version der Slave ist, werden die von der Masterdatenbank unterstützten Funktionen von der Slavedatenbank nicht unterstützt.

1.8 Konfiguration zur Optimierung der Master-Slave-Inkonsistenz

Stellen Sie basierend auf der obigen Situation zunächst sicher, dass max_allowed_packet, der Startpunkt und der Wachstumspunkt des Auto-Increment-Schlüssels konsistent eingestellt sind, und opfern Sie dann etwas Leistung, um sync_binlog auf dem Master zu aktivieren. Für Bibliotheken, die innodb verwenden, wird die folgende Konfiguration empfohlen

innodb_flush_logs_at_trx_commit = 1
innodb-support_xa = 1 # MySQL 5.0 oder höher innodb_safe_binlog # MySQL 4.0

Fügen Sie gleichzeitig die folgenden beiden Parameter aus der obigen Empfehlung hinzu

überspringen_Slave_Start
schreibgeschützt

2. So lösen Sie das Master-Slave-Asynchronitätsproblem

2.1 Beschreibung des asynchronen Master-Slave-Szenarios

Heute habe ich festgestellt, dass die Master- und Slave-Datenbanken von MySQL nicht synchronisiert sind

Gehen Sie zunächst zur Master-Bibliothek:

mysql>Prozessliste anzeigen;

Überprüfen Sie, ob der Prozess zu viel schläft. Fand es normal.

Masterstatus anzeigen;

Überprüfen Sie, ob der Status der Hauptdatenbank normal ist.

mysql> Masterstatus anzeigen;
+-------------------+----------+--------------+-------------------------------------------+
| Datei | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+-------------------------------------------+
| mysqld-bin.000001 | 3260 | | mysql,test,informationsschema |
+-------------------+----------+--------------+-------------------------------------------+
1 Zeile im Satz (0,00 Sek.)

Dann überprüfen Sie Slave

mysql> Slave-Status anzeigen\G                        

Slave_IO_Running: Ja
Slave_SQL_Running: Nein

Dies zeigt, dass der Slave nicht synchron ist

2.2 Lösung 1: Ignorieren Sie den Fehler und fahren Sie mit der Synchronisierung fort

Diese Methode eignet sich für Situationen, in denen sich die Daten der Master- und Slave-Datenbanken nicht sehr unterscheiden oder die Daten nicht vollständig vereinheitlicht werden können. Sie eignet sich für Situationen, in denen die Datenanforderungen nicht streng sind:

Sklave stoppen;

Zeigt einen Fehler beim Überspringen eines Schritts an, die Zahl dahinter ist variabel

setze globalen sql_slave_skip_counter = 1;
Slave starten;

Verwenden Sie dann mysql> show slave status\G, um Folgendes anzuzeigen:

Slave_IO_Running: Ja
Slave_SQL_Running: Ja

OK, jetzt ist der Master-Slave-Synchronisierungsstatus normal. . .

2.3 Methode 2: Master-Slave wiederholen, vollständige Synchronisierung

Diese Methode eignet sich für Situationen, in denen sich die Daten der Master- und Slave-Datenbanken stark unterscheiden oder wenn die Daten vollständig vereinheitlicht werden müssen.

Die Lösungsschritte sind wie folgt:

1. Rufen Sie zuerst die Hauptdatenbank auf und sperren Sie die Tabelle, um das Schreiben von Daten zu verhindern

Verwenden Sie den Befehl:

mysql> Tabellen mit Lesesperre leeren;

Hinweis: Dies ist als schreibgeschützt gesperrt, die Anweisung unterscheidet nicht zwischen Groß- und Kleinschreibung.

2. Datensicherung durchführen

Sichern Sie die Daten in der Datei mysql.bak.sql

[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql

Hier ist eine Sache zu beachten: Datenbanksicherungen müssen regelmäßig durchgeführt werden. Sie können Shell-Skripte oder Python-Skripte verwenden, die bequemer sind, um sicherzustellen, dass die Daten sicher sind.

3. Überprüfen Sie den Masterstatus

mysql> Masterstatus anzeigen; 
+——————-+———-+————–+———————————-+ 
| Datei | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+——————-+———-+————–+———————————-+ 
| mysqld-bin.000001 | 3260 | | mysql,test,informationsschema | 
+——————-+———-+————–+———————————-+ 
1 Zeile im Satz (0,00 Sek.)

4. Übertragen Sie die MySQL-Sicherungsdatei zur Datenwiederherstellung auf den Slave-Computer

Verwenden des scp-Befehls

[root@server01 mysql]# scp mysql.bak.sql [email protected]:/tmp/

5. Beenden Sie den Slave-Status

mysql> Slave stoppen;

6. Führen Sie dann den MySQL-Befehl aus der Datenbank aus, um die Datensicherung zu importieren

mysql> Quelle /tmp/mysql.bak.sql

7. Richten Sie die Synchronisierung der Slave-Datenbank ein. Beachten Sie, dass der Synchronisierungspunkt das Element | Datei | Position in der Anzeige der Master-Statusinformationen der Master-Datenbank ist.

Ändern Sie Master in master_host = „192.168.1.206“, master_user = „rsync“, master_port=3306, master_password=”, master_log_file = „mysqld-bin.000001“, master_log_pos=3260;

8. Starten Sie die Slave-Synchronisation neu

mysql> Slave starten;

9. Überprüfen Sie den Synchronisierungsstatus

mysql> show slave status\G Ansicht:

Slave_IO_Running: Ja
Slave_SQL_Running: Ja

OK, die Synchronisierung ist abgeschlossen.

3. So überwachen Sie die Verzögerung zwischen MySQL-Master und -Slave

3.1 Einleitung:

Bei der täglichen Arbeit sind zwei Aspekte der MySQL-Master-Slave-Replikation zu überprüfen:

Stellen Sie sicher, dass die Gesamtstruktur der Kopie vollständig ist.

Es muss überprüft werden, ob die Daten konsistent sind.

Für Ersteres können wir überwachen, ob der Replikationsthread ordnungsgemäß funktioniert und ob die Master-Slave-Verzögerung innerhalb des Toleranzbereichs liegt. Für Letzteres können wir die Datenkonsistenz sicherstellen, indem wir prüfen, ob die MD5-Codes der Daten in den Master- und Slave-Tabellen konsistent sind. Zur Überprüfung können Sie das Tool mk-table-checksum im Maatkit-Toolkit verwenden.
In diesem Dokument wird beschrieben, wie die Master-Slave-Verzögerung überprüft wird.

Normalerweise gibt es zwei Methoden zur Beurteilung der Master-Slave-Verzögerung: Seconds_Behind_Master und mk-heartbeat

3.2 Methode 1.

Durch Überwachen des Werts des Parameters „Seconds_Behind_Master“ in der Ausgabe des Befehls „show slave status\G“ können Sie ermitteln, ob eine Master-Slave-Verzögerung vorliegt.

mysql> Slave-Status anzeigen\G;
*************************** 1. Reihe ***************************
        Slave_IO_State: Wartet darauf, dass der Master ein Ereignis sendet
         Master_Host: 192.168.1.205
         Master_Benutzer: repl
         Master_Port: 3306
        Verbindungswiederholung: 30
       Master_Log_File: edu-mysql-bin.000008
     Read_Master_Log_Pos: 120
        Relay_Log_Datei: edu-mysql-relay-bin.000002
        Relay_Log_Pos: 287
    Relay_Master_Log_File: edu-mysql-bin.000008
       Slave_IO_Running: Ja
      Slave_SQL_Running: Ja
       Replicate_Do_DB: 
     Replikat_Ignorieren_DB: 
      Tabelle_replizieren: 
    Tabelle_Ignorieren_replizieren: 
   Wild_Do_Tabelle replizieren: 
 Tabelle_Wild_Ignore_replizieren: 
          Last_Errno: 0
          Letzter_Fehler: 
         Skip_Counter: 0
     Exec_Master_Log_Pos: 120
       Relay_Log_Space: 464
       Until_Condition: Keine
        Bis_Log_Datei: 
        Bis_Log_Pos: 0
      Master_SSL_Allowed: Nein
      Master_SSL_CA_Datei: 
      Master_SSL_CA_Pfad: 
       Master_SSL_Zertifikat: 
      Master_SSL_Chiffre: 
        Master_SSL_Schlüssel: 
    Sekunden_Hinter_Master: 0
Master_SSL_Verify_Server_Cert: Nein
        Last_IO_Errno: 0
        Letzter_E/A-Fehler: 
        Last_SQL_Errno: 0
        Letzter_SQL_Fehler: 
 Server-IDs replizieren_ignorieren: 
       Master_Server_Id: 205
         Master_UUID: 7402509d-fd14-11e5-bfd0-000c2963dd15
       Master_Info_Datei: /home/mysql/data/master.info
          SQL_Delay: 0
     SQL_Remaining_Delay: NULL
   Slave_SQL_Running_State: Der Slave hat das gesamte Relay-Protokoll gelesen und wartet auf die Aktualisierung durch den Slave-E/A-Thread.
      Master_Retry_Count: 86400
         Master_Bind: 
   Zeitstempel des letzten IO-Fehlers: 
   Letzter_SQL_Fehler_Zeitstempel: 
        Master_SSL_Crl: 
      Master_SSL_Crlpfad: 
      Abgerufenes_Gtid_Set: 
      Ausgeführtes_Gtid_Set: 
        Auto_Position: 0
1 Zeile im Satz (0,00 Sek.)

Das Obige ist die Ausgabe von show slave status\G. Diese Strukturen liefern viele aussagekräftige Parameter für unsere Überwachung.

Slave_IO_Running Dieser Parameter kann als Überwachungselement für io_thread verwendet werden. „Ja“ bedeutet, dass io_thread normal mit der Master-Datenbank verbunden ist und Replikationsarbeiten durchführen kann. „Nein“ bedeutet, dass die Kommunikation mit der Master-Datenbank abnormal ist, was meist durch das Netzwerk zwischen Master und Slave verursacht wird.

Slave_SQL_Running Dieser Parameter gibt an, ob sql_thread normal ist, insbesondere, ob die Anweisung erfolgreich ausgeführt wurde. Es kommt häufig vor, dass der Primärschlüssel wiederholt wird oder eine Tabelle nicht existiert.

Seconds_Behind_Master ist ein Differenzwert, der durch Vergleichen des Zeitstempels des von sql_thread ausgeführten Ereignisses mit dem Zeitstempel des von io_thread kopierten Ereignisses (abgekürzt als ts) erhalten wird; NULL – zeigt an, dass entweder io_thread oder sql_thread fehlgeschlagen ist, d. h., der Ausführungsstatus des Threads ist „Nein“ und nicht „Ja“. 0 — Dieser Wert ist Null, was wir am meisten erwarten. Er bedeutet, dass die Master-Slave-Replikation gut ist und es keine Verzögerungen gibt. Positiver Wert - zeigt an, dass zwischen Master und Slave eine Verzögerung besteht. Je höher die Zahl, desto mehr hinkt der Slave dem Master hinterher. Negativer Wert – Wird selten gesehen. Ich habe nur gehört, dass einige erfahrene DBAs das schon gesehen haben. Tatsächlich handelt es sich um einen BUG-Wert. Dieser Parameter unterstützt keine negativen Werte, was bedeutet, dass er nicht erscheinen sollte.

Hinweis: Die Berechnungsmethode von Seconds_Behind_Master kann zu Problemen führen. Wie wir alle wissen, sind die Inhalte des Relay-Logs und des Bin-Logs der Master-Datenbank genau gleich. Beim Aufzeichnen von SQL-Anweisungen werden die ts zu diesem Zeitpunkt aufgezeichnet, sodass der Referenzwert aus dem Binlog stammt. Tatsächlich müssen Master und Slave nicht mit NTP synchronisiert werden, d. h. die Konsistenz der Master- und Slave-Uhren muss nicht sichergestellt werden. Sie werden auch feststellen, dass der Vergleich tatsächlich zwischen io_thread und sql_thread erfolgt und io_thread tatsächlich mit der Hauptdatenbank zusammenhängt, sodass das Problem auftritt.

Wenn die E/A-Last der Master-Datenbank hoch ist oder das Netzwerk blockiert ist, kann io_thread das Binärprotokoll nicht rechtzeitig kopieren (es wird nicht unterbrochen und kopiert weiter), während sql_thread immer mit dem io_thread-Skript mithalten kann. In diesem Fall ist der Wert von Seconds_Behind_Master 0.

Wir denken, dass es sich dabei nicht um eine Verzögerung handelt, aber eigentlich ist es das nicht. Aus diesem Grund wird die Ungenauigkeit der Verwendung dieses Parameters zur Überwachung, ob es in der Datenbank zu Verzögerungen kommt, kritisiert. Allerdings ist dieser Wert nicht immer ungenau.

Dieser Wert ist auch sehr wertvoll, wenn der io_thread und das Master-Netzwerk sehr gut sind. ''Zuvor haben wir erwähnt, dass der Parameter Seconds_Behind_Master einen negativen Wert hat. Wir wissen bereits, dass dieser Wert die Differenz zwischen den letzten ts von io_thread und den von sql_thread ausgeführten ts ist.

Ersteres ist immer größer als Letzteres. Die einzige Möglichkeit ist, dass im ts eines Ereignisses ein Fehler auftritt und dieser kleiner ist als der vorherige. In diesem Fall können negative Werte auftreten.

3.2 Methode 2.

mk-heartbeat: Ein Tool im universellen Maatkit-Toolkit, das als Methode zur genauen Bestimmung der Replikationsverzögerung gilt.

Die Implementierung von mk-heartbeat wird ebenfalls mithilfe eines Timestmp-Vergleichs erreicht. Zunächst muss sichergestellt werden, dass Master- und Slave-Server konsistent sind, indem die Uhr mit demselben NTP-Server synchronisiert wird. Es muss eine Heartbeat-Tabelle in der Master-Datenbank erstellt werden, die mindestens zwei Felder enthält, id und ts. Die ID ist server_id und ts ist der aktuelle Zeitstempel now(). Die Struktur wird auch in die Slave-Datenbank kopiert. Nachdem die Tabelle erstellt wurde, wird im Hintergrundprozessmodus eine Reihe von Update-Operation-Befehlen in der Master-Datenbank ausgeführt, um regelmäßig Daten in die Tabelle einzufügen. Der Standardzyklus beträgt 1 Sekunde. Gleichzeitig führt die Slave-Datenbank im Hintergrund auch einen Überwachungsbefehl aus, um den ts-Wert des kopierten Datensatzes mit demselben ts-Wert in der Master-Datenbank zu vergleichen. Wenn der Unterschied 0 ist, bedeutet dies, dass es keine Verzögerung gibt. Je größer der Unterschied, desto mehr Sekunden Verzögerung. Wir alle wissen, dass die Replikation asynchron erfolgt und die Zeitintervalle nicht vollständig konsistent sind. Daher lässt das Tool eine Lücke von einer halben Sekunde zu. Die Differenz innerhalb dieser Lücke kann ignoriert werden und wird als keine Verzögerung betrachtet. Dieses Tool verwendet echtes Kopieren und nutzt geschickt Zeitstempel, um die Verzögerung zu überprüfen.

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für jedermanns Studium hilfreich sein. Ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird. .