So wählen Sie das Format bei der Verwendung von Binlog in MySQL

1. Drei Binlog-Modi

1.Anweisungsebenenmodus

Jede SQL-Anweisung, die Daten ändert, wird im Binärprotokoll des Masters aufgezeichnet. Wenn der Slave repliziert, analysiert der SQL-Prozess es in dasselbe SQL, das vom ursprünglichen Master ausgeführt wurde, und führt es erneut aus.

Vorteile: Die Vorteile auf Anweisungsebene lösen zunächst die Nachteile auf Zeilenebene. Es ist nicht erforderlich, die Änderungen jeder Datenzeile aufzuzeichnen, wodurch die Menge an Binärprotokollen reduziert, E/A gespart und die Leistung verbessert wird. Denn er muss nur die Details der auf dem Master ausgeführten Anweisungen und die Kontextinformationen bei der Ausführung der Anweisungen aufzeichnen.

Nachteile: Da die Ausführungsanweisungen aufgezeichnet werden, müssen, damit diese Anweisungen auf der Slave-Seite korrekt ausgeführt werden, auch einige relevante Informationen zu jeder Anweisung bei der Ausführung aufgezeichnet werden, d. h. Kontextinformationen, um sicherzustellen, dass alle Anweisungen bei der Ausführung auf der Slave-Seite dieselben Ergebnisse erzielen wie bei der Ausführung auf der Master-Seite. Darüber hinaus wurden aufgrund der rasanten Entwicklung von MySQL viele neue Funktionen hinzugefügt, was die MySQL-Replikation vor erhebliche Herausforderungen gestellt hat. Natürlich ist es umso wahrscheinlicher, dass Fehler auftreten, je komplexer der zu replizierende Inhalt ist. Auf Anweisungsebene wurden viele Situationen festgestellt, die MySQL-Replikationsprobleme verursachen. Diese Probleme treten hauptsächlich auf, wenn beim Ändern von Daten bestimmte Funktionen oder Features verwendet werden. Beispielsweise kann sleep() in einigen Versionen nicht korrekt repliziert werden.

2. Zeilenebenenmodus

Das Protokoll zeichnet jede geänderte Datenzeile auf und ändert anschließend dieselben Daten auf der Slave-Seite.

Vorteile: Bin-Log muss die kontextbezogenen Informationen der ausgeführten SQL-Anweisung nicht aufzeichnen. Es muss nur aufzeichnen, welcher Datensatz geändert wurde und wie er geändert wurde. Daher zeichnet der Inhalt des Protokolls auf Zeilenebene die Details jeder Datenänderungszeile klar auf. Darüber hinaus tritt das Problem nicht auf, dass gespeicherte Prozeduren, Funktionen oder Triggeraufrufe und Trigger in bestimmten Situationen nicht korrekt repliziert werden können.

Nachteile: Wenn auf Zeilenebene alle ausgeführten Anweisungen im Protokoll aufgezeichnet werden, werden sie als Änderungen an jeder Zeile aufgezeichnet. Dies kann eine große Menge an Protokollinhalten erzeugen. Beispielsweise gibt es eine Aktualisierungsanweisung wie diese: update product set owner_member_id='d' where owner_member_id='a'. Nach der Ausführung zeichnet das Protokoll nicht das Ereignis auf, das dieser Aktualisierungsanweisung entspricht (MySQL zeichnet Binärprotokollprotokolle in Form von Ereignissen auf), sondern die Änderungen jedes Datensatzes, der durch diese Anweisung aktualisiert wurde. Auf diese Weise werden viele Ereignisse vieler Datensätze aufgezeichnet, die aktualisiert werden. Natürlich wird die Menge der Binärprotokolle sehr groß sein.

3. Gemischter Modus

Tatsächlich handelt es sich um eine Kombination der ersten beiden Modi. Im gemischten Modus unterscheidet MySQL das Protokollformat für jede ausgeführte SQL-Anweisung, d. h. es wählt zwischen Anweisung und Zeile. Die Anweisungsebene ist in der neuen Version noch immer die gleiche wie vorher, es werden lediglich die ausgeführten Anweisungen protokolliert. Die neue Version von MySQL hat den Zeilenebenenmodus optimiert. Nicht alle Änderungen werden auf Zeilenebene aufgezeichnet. Wenn sich beispielsweise die Tabellenstruktur ändert, wird dies im Anweisungsmodus aufgezeichnet. Wenn die SQL-Anweisung tatsächlich eine Aktualisierungs- oder Löschanweisung ist, die Daten ändert, werden alle Zeilenänderungen dennoch aufgezeichnet.

2. Welches Format sollten wir bei der Verwendung von Binlog wählen?

Aus der obigen Einführung wissen wir, dass binlog_format=STATEMENT in einigen Szenarien IO sparen und die Synchronisierung beschleunigen kann. Bei Transaktions-Engines wie InnoDB ist das Schreiben unter binlog_format=statement jedoch verboten, wenn die Isolationsebenen READ-COMMITTED und READ-UNCOMMITTED sind oder der Parameter innodb_locks_unsafe_for_binlog eingeschaltet ist. Gleichzeitig zeichnet binlog_format=mixed, das Standardformat zum Schreiben von Anweisungen für Nicht-Transaktions-Engines und andere Isolationsebenen, nur das Zeilenformat auf.

> wählen Sie @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| GELESEN-ENGAGIERT |
+----------------+

> Tabelle erstellen t(c1 int) engine=innodb;

> setze binlog_format=Anweisung;

> in t-Werte einfügen (1);
FEHLER 1665 (HY000): Anweisung kann nicht ausgeführt werden: Schreiben in Binärprotokoll nicht möglich, da BINLOG_FORMAT = STATEMENT und mindestens eine Tabelle eine Speicher-Engine verwendet, die auf zeilenbasiertes Protokollieren beschränkt ist. InnoDB ist auf zeilenbasiertes Protokollieren beschränkt, wenn die Transaktionsisolationsebene READ COMMITTED oder READ UNCOMMITTED ist.

> setze binlog_format='gemischt';

> Binlog-Ereignisse in 'mysql-bin.000004'\G anzeigen
*************************** 3. Reihe ***************************
 Protokollname: mysql-bin.000002
  Position: 287
 Ereignistyp: Gtid
 Server_id: 3258621899
End_log_pos: 335
  Informationen: SET @@SESSION.GTID_NEXT= 'ed0eab2f-dfb0-11e7-8ad8-a0d3c1f20ae4:9375'
*************************** 4. Reihe ***************************
 Protokollname: mysql-bin.000002
  Position: 335
 Event_type: Abfrage
 Server_id: 3258621899
End_log_pos: 407
  Info: BEGIN
*************************** 5. Reihe ***************************
 Protokollname: mysql-bin.000002
  Position: 407
 Ereignistyp: Table_map
 Server_id: 3258621899
End_log_pos: 452
  Info: Tabellen-ID: 124 (test.t)
*************************** 6. Reihe ***************************
 Protokollname: mysql-bin.000002
  Position: 452
 Ereignistyp: Write_rows_v1
 Server_id: 3258621899
End_log_pos: 498
  Info: Tabellen-ID: 124 Flags: STMT_END_F
*************************** 7. Reihe ***************************
 Protokollname: mysql-bin.000002
  Position: 498
 Ereignistyp: Xid
 Server_id: 3258621899
End_log_pos: 529
  Informationen: COMMIT /* xid=18422 */

Warum kann das Anweisungsformat Binlog nicht in den Modi READ-COMMITTED (RC) und READ-UNCOMMITTED verwendet werden? Dies liegt daran, dass eine Anweisung beim Ausführen innerhalb einer Transaktion Daten sehen kann, die von anderen Transaktionen festgeschrieben oder geschrieben wurden. Nachdem die Transaktion bestätigt wurde, wird das Binärprotokoll geschrieben. Wenn Sie es dann auf dem Slave erneut abspielen, entsprechen die angezeigten Daten nicht mehr denen, die auf dem Master geschrieben wurden.

Zum Beispiel:

Es gibt eine Tabelle:

+------+------+
| ein | b |
+------+------+
| 10 | 2 |
| 20 | 1 |
+------+------+

Wir machen Folgendes:

1. Sitzung1 führt eine Aktualisierung in der Transaktion UPDATE t1 SET a=11 aus, wobei b=2; ein Datensatz in Zeile (10,2) erfüllt die Bedingung, wird aber nicht festgeschrieben.
2. Sitzung2 führt außerdem eine Aktualisierungsoperation aus, aktualisiert die Zeile (20,1) auf (20,2) und führt die Aktualisierung aus.
3. Anschließend führt die vorherige Sitzung1 die Aktualisierung in Zeile (10,2) durch.

Wenn das Binärprotokoll im Anweisungsformat aufgezeichnet wird und der Slave die Daten erneut wiedergibt, wird das Update in Sitzung2 zuerst wiedergegeben, da es zuerst übermittelt wurde, und die Zeile (20,1) wird auf (20,2) aktualisiert. Führen Sie dann die Anweisung UPDATE t1 SET a=11 where b=2; in Sitzung 1 erneut aus, und die beiden Zeilen (10,2) und (20,2) werden auf (11,2) aktualisiert. Dies führt dazu, dass sich die Masterdatenbank wie folgt verhält: (11, 2), (20,2) und das Slave-Ende wie folgt: (11,2), (11, 2).

3. Problemanalyse

Das oben Gesagte wird anhand eines konkreten Beispiels erläutert. Der grundlegende Grund besteht darin, dass die RC-Transaktionsisolationsebene die Anforderungen an die Ausführung der Transaktionsserialisierung nicht erfüllt und die nicht wiederholbaren und Phantom-Lesevorgänge nicht löst.

Für die Isolationsebenen „Repetable-Read“ und „Serializable“ spielt dies keine Rolle, das Anweisungsformat wird aufgezeichnet. Dies liegt daran, dass für RR und Serializable wiederholbares Lesen garantiert ist. Beim Durchführen eines Updates wird zusätzlich zum Sperren der entsprechenden Zeile eine GAP-Sperre hinzugefügt, wenn eine Zeile eingefügt werden kann, die die Bedingungen erfüllt. Im obigen Fall werden alle Zeilen und Bereiche gesperrt, wenn Sitzung1 die Zeile mit b = 2 aktualisiert, sodass Sitzung2 beim Aktualisieren warten muss. Aus Sicht der Isolationsebene erfüllt Serializable die Serialisierung von Transaktionen, sodass das Transaktionsanweisungsformat für serielle Datensätze im Binlog akzeptabel ist. Gleichzeitig hat die RR-Isolationsebene von InnoDB tatsächlich nicht wiederholbare Lesevorgänge und Phantomlesevorgänge gelöst und erfüllt die Transaktionsisolierungsanforderungen des ANSI-SQL-Standards.

Die binlog_format-Einschränkungen READ-COMMITTED und READ-UNCOMMITTED gelten für alle Transaktions-Engines.

4. Inhalte erweitern

Kann das Binlog-Datensatzanweisungsformat unter den Isolationsebenen InnoDB RR und Serializable garantiert werden? Nicht unbedingt. Innodb, es gibt einen Parameter innodb_locks_unsafe_for_binlog zur Steuerung der GAP-Sperre. Die Standardeinstellung ist OFF:

mysql> Variablen wie „innodb_locks_unsafe_for_binlog“ anzeigen;
+--------------------------------+----------+
| Variablenname | Wert |
+--------------------------------+----------+
| innodb_locks_unsafe_for_binlog | AUS |
+--------------------------------+----------+
1 Zeile im Satz (0,01 Sek.)

Das heißt, dass ab der RR-Ebene zusätzlich zur Zeilensperre auch die GAP-Sperre vorhanden ist. Wenn dieser Parameter jedoch auf ON gesetzt ist, wird dem aktuellen Lesevorgang keine GAP-Sperre hinzugefügt, d. h. der aktuelle Lesevorgang, der eine Next-Key-Sperre auf der RR-Isolationsebene erfordert, degeneriert zu READ-COMMITTED. Wenn dieser Parameter auf ON gesetzt ist, kann die Richtigkeit der Slave-Daten daher nicht garantiert werden, selbst wenn die verwendete Transaktionsisolationsebene „Repetable-Read“ ist.

V. Fazit

Wenn Sie für Online-Geschäfte eine Transaktions-Engine wie InnoDB verwenden, setzen Sie binlog_format nicht auf STATEMENT, es sei denn, Sie möchten das Schreiben auf der Isolationsebene RR oder höher sicherstellen. Andernfalls können die Geschäftsdaten nicht geschrieben werden. Für binlog_format im gemischten Modus müssen diese Transaktions-Engines unterhalb der RR-Isolationsebene auch ROW-Ereignisse schreiben.

Dies ist das Ende dieses Artikels zur Auswahl des Formats bei Verwendung von Binlog in MySQL. Weitere Informationen zur Auswahl des Formats bei Verwendung von Binlog in MySQL 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!