Wie stellt MySQL die Master-Slave-Konsistenz sicher?

Frage: Wie wir alle wissen, kann Binlog zum Archivieren und zur Master-Slave-Synchronisierung verwendet werden, aber was ist sein Inhalt? Warum kann die Standby-Datenbank nach der Ausführung von Binlog nicht mit der Primärdatenbank konsistent sein?

Das Grundprinzip von MySQL Master-Slave

Abbildung 1 MySQL Master-Slave-Umschaltprozess

Im Zustand 1 greifen die Lese- und Schreiboperationen des Clients direkt auf Knoten A zu, und Knoten B ist die Backup-Datenbank von A. Er synchronisiert lediglich alle Updates von A und führt diese lokal aus. Dadurch bleiben die Daten auf den Knoten B und A gleich.
Wenn ein Umschalten erforderlich ist, wechseln Sie in den Zustand 2. Zu diesem Zeitpunkt liest und schreibt der Client auf Knoten B und Knoten A ist die Sicherungsdatenbank von B.
Obwohl im Zustand 1 nicht direkt auf Knoten B zugegriffen wird, wird empfohlen, Knoten B (also die Standby-Datenbank) in den schreibgeschützten Modus zu setzen . Hierzu sind mehrere Aspekte zu berücksichtigen:

• Manchmal werden einige betriebliche Abfrageanweisungen zur Abfrage in die Standby-Datenbank gestellt. Wenn Sie sie auf schreibgeschützt setzen, können Sie Fehlbedienungen vermeiden.
• Verhindern Sie Fehler in der Umschaltlogik , wie z. B. doppeltes Schreiben während des Umschaltvorgangs, das zu Inkonsistenzen zwischen Primär- und Standby-Server führt .
• Der schreibgeschützte Status kann verwendet werden, um die Rolle eines Knotens zu bestimmen .

Ich habe die Backup-Datenbank auf schreibgeschützt eingestellt. Wie kann ich sie synchron mit der primären Datenbank auf dem neuesten Stand halten?
Sie müssen sich über dieses Problem keine Sorgen machen. Weil die schreibgeschützte Einstellung für Benutzer mit Superberechtigungen ungültig ist und der für synchrone Updates verwendete Thread über Superberechtigungen verfügt.

Abbildung 2 Flussdiagramm Aktiv/Standby

Nach dem Empfang der Aktualisierungsanforderung vom Client führt die Masterdatenbank die Aktualisierungslogik der internen Transaktion aus und schreibt gleichzeitig das Binärprotokoll. Zwischen der Standby-Datenbank B und der primären Datenbank A wird eine lange Verbindung aufrechterhalten. Innerhalb der Masterdatenbank A gibt es einen Thread, der ausschließlich für die Bereitstellung der Langzeitverbindung der Standbydatenbank B zuständig ist. Der vollständige Ablauf einer Transaktionsprotokollsynchronisierung läuft wie folgt ab:

• Verwenden Sie in der Standby-Datenbank B den Befehl „change master“, um die IP, den Port, den Benutzernamen und das Kennwort der Master-Datenbank A sowie die Position festzulegen, von der aus mit der Anforderung des Binärprotokolls begonnen werden soll (einschließlich des Dateinamens und des Protokolloffsets).
• Führen Sie den Befehl „Start Slave“ auf Slave-Datenbank B aus. Die Slave-Datenbank startet zwei Threads, nämlich io_thread und sql_thread in der Abbildung. Unter diesen ist io_thread für die Herstellung einer Verbindung mit der Hauptbibliothek verantwortlich.
• Nachdem die Masterdatenbank A den Benutzernamen und das Kennwort überprüft hat, beginnt sie, das von der Standbydatenbank B übermittelte Binärprotokoll vom lokalen Speicherort zu lesen und sendet es an B.
• Nach dem Empfang des Binärprotokolls schreibt die Standby-Datenbank B es in eine lokale Datei, die als Relay-Protokoll bezeichnet wird.
• sql_thread liest das Übertragungsprotokoll, analysiert die Befehle im Protokoll und führt sie aus.

Später, mit der Einführung von Multithread-Replikationslösungen, entwickelte sich sql_thread zu mehreren Threads .

Vergleich dreier Binlog-Formate

Binlog hat zwei Formate, eines ist Anweisung und das andere ist Zeile. Möglicherweise sehen Sie auch ein drittes Format namens „Mixed in Other Materials“. Tatsächlich handelt es sich dabei um eine Mischung der ersten beiden Formate.

mysql> CREATE TABLE t (
	id int(11) NICHT NULL,
	ein int(11) DEFAULT NULL,
	t_modified Zeitstempel NICHT NULL STANDARD CURRENT_TIMESTAMP,
	Primärschlüssel (ID),
	SCHLÜSSEL a (a),
	SCHLÜSSEL t_modifiziert(t_modifiziert)
)ENGINE=InnoDB;

einfügen in t-Werte (1,1, '2018-11-13');
einfügen in t-Werte (2,2, '2018-11-12');
einfügen in t-Werte (3,3, '2018-11-11');
einfügen in t-Werte (4,4, '2018-11-10');
einfügen in t-Werte (5,5, '2018-11-09');

Wenn Sie eine Datenzeile in der Tabelle löschen möchten, sehen wir uns an, wie das Binärprotokoll dieser Löschanweisung aufgezeichnet wird.

mysql> löschen aus t /Kommentar/, wobei a>=4 und t_modified<='2018-11-10' Limit 1;

Wenn binlog_format=statement, wird der Originaltext der SQL-Anweisung in binlog aufgezeichnet . Sie können den Befehl mysql> show binlog events in 'master.000001'; verwenden, um den Inhalt des Binärprotokolls anzuzeigen.

Abbildung 3. Beispiel für das Anweisungsformat binlog

Sehen wir uns nun die Ausgabe in Abbildung 3 an.

• Die erste Zeile SET @@SESSION.GTID_NEXT='ANONYMOUS' können Sie vorerst ignorieren. Wir werden sie später im Artikel bei der Einführung des Aktiv/Standby-Schalters erwähnen.
• Die zweite Zeile ist ein BEGIN, das dem Commit in der vierten Zeile entspricht und anzeigt, dass dazwischen eine Transaktion stattfindet.
• Die dritte Zeile ist die eigentliche Ausführungsanweisung. Sie können sehen, dass es einen „use ‚test‘“-Befehl gibt, bevor der eigentliche Löschbefehl ausgeführt wird. Dieser Befehl wird von uns nicht aktiv ausgeführt, sondern von MySQL basierend auf der Datenbank hinzugefügt, in der sich die zu bedienende Tabelle befindet. Auf diese Weise wird sichergestellt, dass beim Übertragen des Protokolls zur Ausführung in die Standbydatenbank die Tabelle t in der Testdatenbank korrekt aktualisiert werden kann, unabhängig davon, in welcher Datenbank sich der aktuelle Arbeitsthread befindet.
• Die Löschanweisung nach dem Befehl „use ‚test‘ ist der ursprüngliche SQL-Text, den wir eingegeben haben. Wie Sie sehen, zeichnet Binlog die SQL-Befehle und sogar die Kommentare „getreu“ auf.
• Die letzte Zeile ist ein COMMIT. Sie können darin xid=61 geschrieben sehen.

Um den Unterschied zwischen Anweisungs- und Zeilenformaten zu veranschaulichen, schauen wir uns das Ausführungseffektdiagramm dieses Löschbefehls an:

Abbildung 4: Warnungen zur Löschausführung

Das Ausführen dieses Löschbefehls generiert eine Warnung, da das aktuelle Binärprotokoll auf das Anweisungsformat eingestellt ist und in der Anweisung eine Beschränkung vorliegt . Daher ist dieser Befehl möglicherweise unsicher. Warum sage ich das? Dies liegt daran, dass der Löschbefehl eine Beschränkung hat, die zu Inkonsistenzen zwischen den Primär- und Standbydaten führen kann .

Wenn die Löschanweisung den Index a verwendet, wird die erste Zeile, die die Bedingung erfüllt, basierend auf dem Index a gefunden, d. h. die Zeile mit a = 4 wird gelöscht.
Wenn jedoch der Index t_modified verwendet wird, wird die Zeile mit t_modified='2018-11-09', also a=5, gelöscht.

Da der Originaltext der Anweisung im Anweisungsformat im Binärprotokoll aufgezeichnet wird, kann die folgende Situation auftreten: Wenn die SQL-Anweisung in der primären Datenbank ausgeführt wird, wird der Index a verwendet. Wenn die SQL-Anweisung jedoch in der Standby-Datenbank ausgeführt wird, wird der Index t_modified verwendet. Aus diesem Grund hält MySQL es für riskant, so zu schreiben .

Wenn ich das Binlog-Format in binlog_format='row' ändere, verschwindet dieses Problem dann?

Abbildung 5 Beispiel für das Zeilenformat Binlog

Im Vergleich zum Anweisungsformat Binlog sind BEGIN und COMMIT davor und danach identisch. Der ursprüngliche Text der SQL-Anweisung befindet sich jedoch nicht mehr im Binärprotokoll im Zeilenformat. Stattdessen wird er durch zwei Ereignisse ersetzt: Table_map und Delete_rows.

• Table_map-Ereignis, das verwendet wird, um anzuzeigen, dass die nächste zu bearbeitende Tabelle die Tabelle t in der Testdatenbank ist;
• Ereignis „Delete_rows“, das verwendet wird, um das Löschverhalten zu definieren.

Verwenden Sie mithilfe des Tools mysqlbinlog den folgenden Befehl, um den Inhalt des Binärprotokolls zu analysieren und anzuzeigen. Das Binärprotokoll dieser Transaktion beginnt an Position 8900. Daher kann mit dem Parameter „Startposition“ angegeben werden, dass die Protokollanalyse an dieser Position beginnt.

mysqlbinlog -vv data/master.000001 --start-position=8900;

Abbildung 6 Details eines Binärprotokollbeispiels im Zeilenformat

Aus dieser Abbildung können wir die folgenden Informationen ersehen:

• Server-ID 1 bedeutet, dass diese Transaktion auf der Datenbank mit der Server-ID=1 ausgeführt wird.
• Jedes Ereignis hat einen CRC32-Wert, weil ich den Parameter binlog_checksum auf CRC32 gesetzt habe.
• Das table_map-Ereignis ist dasselbe wie in Abbildung 5 und zeigt die nächste zu öffnende Tabelle mit der Zuordnung zur Nummer 226. Derzeit bearbeitet unsere SQL-Anweisung nur eine Tabelle. Was passiert, wenn wir mehrere Tabellen bearbeiten möchten? Jede Tabelle verfügt über ein entsprechendes Table_map-Ereignis, das einer separaten Nummer zugeordnet ist, um Vorgänge an verschiedenen Tabellen zu unterscheiden.
• Wir haben den Parameter -vv im Befehl mysqlbinlog verwendet, um den gesamten Inhalt zu analysieren, sodass wir im Ergebnis die Werte jedes Felds sehen können (z. B. @1=4, @2=4).
• Die Standardkonfiguration von binlog_row_image ist FULL, daher enthält Delete_event die Werte aller Felder der gelöschten Zeile. Wenn binlog_row_image auf MINIMAL eingestellt ist, werden nur die erforderlichen Informationen aufgezeichnet. In diesem Beispiel werden nur die Informationen zu id=4 aufgezeichnet.

Das letzte Xid-Ereignis wird verwendet, um anzuzeigen, dass die Transaktion erfolgreich abgeschlossen wurde.

Wenn binlog_format das Zeilenformat verwendet, wird die Primärschlüssel-ID der tatsächlich gelöschten Zeile im Binlog aufgezeichnet. Auf diese Weise wird die Zeile mit der ID = 4 definitiv gelöscht, wenn das Binlog in die Slave-Datenbank übertragen wird, und es besteht kein Problem, dass Master und Slave unterschiedliche Zeilen löschen .

Warum gibt es ein Binlog mit gemischten Formaten?

Da einige Binärprotokolle im Anweisungsformat zu Inkonsistenzen zwischen Master und Slave führen können, sollte das Zeilenformat verwendet werden .

Der Nachteil des Zeilenformats besteht jedoch darin, dass es viel Platz beansprucht . Wenn Sie beispielsweise mit einer Delete-Anweisung 100.000 Datenzeilen löschen , wird eine SQL-Anweisung im Binärprotokoll aufgezeichnet, die Dutzende Bytes Speicherplatz beansprucht. Wenn Sie Binlog jedoch im Zeilenformat verwenden, müssen Sie alle 100.000 Datensätze in Binlog schreiben. Dadurch wird nicht nur mehr Speicherplatz beansprucht, sondern es werden auch IO-Ressourcen zum Schreiben des Binärprotokolls verbraucht, was sich auf die Ausführungsgeschwindigkeit auswirkt .

Aus diesem Grund hat MySQL eine Kompromisslösung gewählt, d. h. es gibt ein Binlog mit gemischtem Format. Das gemischte Format bedeutet, dass MySQL ermittelt, ob diese SQL-Anweisung zu Inkonsistenzen zwischen dem Master und dem Slave führen kann. Wenn möglich, wird das Zeilenformat verwendet, andernfalls das Anweisungsformat . Mit anderen Worten: Das gemischte Format kann die Vorteile des Anweisungsformats nutzen und gleichzeitig das Risiko von Dateninkonsistenzen vermeiden.

Mittlerweile erfordern immer mehr Szenarien, dass das MySQL-Binlog-Format auf „row“ eingestellt wird . Dafür gibt es viele Gründe, einer liegt sofort auf der Hand: Datenwiederherstellung .

Wir werden das Problem der Datenwiederherstellung aus der Perspektive von drei SQL-Anweisungen betrachten: Löschen, Einfügen und Aktualisieren.

• Wie Sie in Abbildung 6 sehen können, speichert das Zeilenformat-Binlog die gesamten Zeileninformationen der gelöschten Zeile, selbst wenn ich eine Löschanweisung ausführe. Wenn Sie daher nach dem Ausführen einer Löschanweisung feststellen, dass Sie die falschen Daten gelöscht haben, können Sie die im Binärprotokoll aufgezeichnete Löschanweisung direkt in eine Einfügung umwandeln und die gelöschten Daten anschließend durch erneutes Einfügen wiederherstellen.
• Was passiert, wenn Sie die falsche Insert-Anweisung ausführen? Das ist direkter. Im Zeilenformat werden alle Feldinformationen im Binärprotokoll der Einfügeanweisung aufgezeichnet, mit dem die gerade eingefügte Zeile genau lokalisiert werden kann. Zu diesem Zeitpunkt können Sie die Einfügeanweisung einfach in eine Löschanweisung umwandeln und die versehentlich eingefügte Datenzeile löschen.
• Wenn eine Aktualisierungsanweisung ausgeführt wird, zeichnet das Binärprotokoll die gesamte Datenzeile vor und nach der Änderung auf. Wenn Sie also versehentlich eine Aktualisierungsanweisung ausführen, müssen Sie nur die beiden Informationszeilen vor und nach dem Ereignis vertauschen und sie dann in der Datenbank ausführen, um den Aktualisierungsvorgang wiederherzustellen.

Bei Datenoperationsfehlern, die durch Lösch-, Einfüge- oder Aktualisierungsanweisungen verursacht werden, ist häufig eine Wiederherstellung des Zustands vor der Operation erforderlich. Das Flashback-Tool von MariaDB führt ein Rollback der Daten basierend auf den oben beschriebenen Prinzipien durch.

mysql> in t-Werte einfügen (10,10, jetzt ());

Wenn Sie das Binärprotokollformat auf „gemischt“ einstellen, glauben Sie, dass MySQL es im Zeilenformat oder im Anweisungsformat aufzeichnet?

Abbildung 7: Gemischtes Format und jetzt()

MySQL verwendet tatsächlich das Anweisungsformat. Als nächstes verwenden wir das Tool mysqlbinlog, um einen Blick darauf zu werfen:

Abbildung 8 TIMESTAMP-Befehl

Es stellt sich heraus, dass Binlog beim Aufzeichnen des Ereignisses einen zusätzlichen Befehl aufzeichnet: SET TIMESTAMP=1546103491. Dabei wird der Befehl SET TIMESTAMP verwendet, um den Rückgabezeitpunkt der nachfolgenden now()-Funktion zu vereinbaren . Durch diesen SET TIMESTAMP-Befehl stellt MySQL die Konsistenz der Primär- und Standbydaten sicher.

Beim Wiedergeben von Binlog-Daten wird Folgendes durchgeführt: Verwenden Sie mysqlbinlog, um das Protokoll zu analysieren, und kopieren Sie dann die darin enthaltenen Anweisungen direkt und führen Sie sie aus .
Wie Sie jetzt wissen, ist dieser Ansatz riskant. Da die Ausführungsergebnisse einiger Anweisungen von den Kontextbefehlen abhängen, sind die Ergebnisse der direkten Ausführung wahrscheinlich falsch .

Daher besteht die Standardmethode zum Verwenden von Binlog zum Wiederherstellen von Daten darin, diese mit dem Tool mysqlbinlog zu analysieren und dann das gesamte analysierte Ergebnis zur Ausführung an MySQL zu senden . Ein Befehl ähnlich dem folgenden:

mysqlbinlog master.000001 --start-position=2738 --stop-position=2942 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

Dieser Befehl dient dazu, den Inhalt von Byte 2738 bis Byte 2942 in der Datei master.000001 zu analysieren und ihn zur Ausführung in MySQL zu übertragen.

Problem der zirkulären Replikation

Die Binlog-Funktion stellt sicher, dass die Ausführung desselben Binlogs auf der Slave-Datenbank denselben Status wie auf der Master-Datenbank erreichen kann. Wir können davon ausgehen, dass im Normalfall die Daten des Primär- und des Backup-Gerätes konsistent sind . Das heißt, die Inhalte der Knoten A und B in Abbildung 1 sind konsistent. Tatsächlich handelt es sich bei dem, was ich in Abbildung 1 gezeichnet habe, um die MS-Struktur. In der tatsächlichen Produktion wird jedoch häufiger die Dual-M-Struktur verwendet . Dabei handelt es sich um den in Abbildung 9 gezeigten Aktiv-Standby-Umschaltvorgang.

Abbildung 9 MySQL Master-Slave-Umschaltprozess - Duale M-Struktur

Die Knoten A und B stehen immer in einer Master-Slave-Beziehung zueinander. Somit ist es nicht notwendig, die Master-Slave-Beziehung beim Umschalten zu verändern.

Bei der Doppel-M-Struktur ist jedoch noch ein Problem zu lösen .

Die Geschäftslogik aktualisiert eine Anweisung auf Knoten A und sendet dann das generierte Binärprotokoll an Knoten B. Knoten B generiert nach der Ausführung der Aktualisierungsanweisung ebenfalls ein Binärprotokoll. (Ich schlage vor, dass Sie den Parameter log_slave_updates auf „on“ setzen, was bedeutet, dass die Standby-Datenbank nach der Ausführung des Relay-Protokolls ein Binärprotokoll generiert).

Wenn Knoten A dann auch die Sicherungsdatenbank von Knoten B ist, entspricht dies dem erneuten Ausführen des neu generierten Binärprotokolls von Knoten B. Anschließend wird diese Aktualisierungsanweisung wiederholt zwischen Knoten A und Knoten B ausgeführt, was einer zirkulären Replikation entspricht. Wie kann dieses Problem gelöst werden ?

Wie in Abbildung 6 oben zu sehen ist, zeichnet MySQL die Server-ID der Instanz im Binärprotokoll auf, auf der dieser Befehl zuerst ausgeführt wurde . Daher können wir die folgende Logik verwenden, um das Problem der zirkulären Replikation zwischen zwei Knoten zu lösen:

• Es ist festgelegt, dass die Server-IDs der beiden Datenbanken unterschiedlich sein müssen . Wenn sie gleich sind, können sie nicht als primäre und Sicherungsbeziehung festgelegt werden.
• Eine Standby-Datenbank empfängt das Binärprotokoll und generiert während des Wiedergabevorgangs ein neues Binärprotokoll mit derselben Server-ID wie das ursprüngliche Binärprotokoll .
• Nachdem jede Datenbank das von ihrer Hauptdatenbank gesendete Protokoll empfangen hat, ermittelt sie zunächst die Server-ID . Wenn diese mit ihrer eigenen übereinstimmt, bedeutet dies, dass das Protokoll von ihr selbst generiert wurde, und sie wird das Protokoll direkt verwerfen.

Wenn wir dieser Logik zufolge eine doppelte M-Struktur einrichten, sieht der Ausführungsfluss des Protokolls folgendermaßen aus:

• Für Transaktionen, die vom Knoten A aktualisiert werden, wird die Server-ID von A im Binärprotokoll aufgezeichnet.
• Nach der Übertragung an Knoten B und einmaligen Ausführung ist die Server-ID des von Knoten B generierten Binärprotokolls auch die Server-ID von A.
• Anschließend wird es an Knoten A zurückgesendet. Wenn A feststellt, dass die Server-ID mit seiner eigenen übereinstimmt, wird das Protokoll nicht mehr verarbeitet. Daher wird hier die Endlosschleife unterbrochen.

Zusammenfassen:

Damit ist dieser Artikel darüber, wie MySQL die Konsistenz zwischen dem primären und dem Backup-Server sicherstellt, abgeschlossen. Weitere Informationen dazu, wie MySQL die Konsistenz zwischen dem primären und dem Backup-Server sicherstellt, finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!