Auswahl des Binlog-Formats bei Verwendung von Binlog in MySQL

Die Spalte

MySQL-Tutorial stellt die Wahl des Binlog-Formats bei der Verwendung von Binlog vor.

1. Drei Binlog-Modi

1.Anweisungsebenenmodus

Jedes SQL, das Daten ändert, wird im Bin-Log des Masters aufgezeichnet. Wenn der Slave kopiert, analysiert der SQL-Prozess ihn in denselben SQL-Code, der auf der ursprünglichen Masterseite ausgeführt wurde, und führt ihn erneut aus. Vorteile: Die Vorteile der Anweisungsebene bestehen darin, dass sie zunächst die Mängel der Zeilenebene behebt. Es ist nicht erforderlich, die Änderungen jeder Datenzeile aufzuzeichnen, die Anzahl der Bin-Log-Protokolle zu reduzieren, E/A zu sparen und die Leistung zu verbessern. Denn er muss nur die Details der auf dem Master ausgeführten Anweisungen und die Kontextinformationen aufzeichnen, wenn die Anweisungen ausgeführt werden. Nachteile: Da es sich um eine aufgezeichnete Ausführungsanweisung handelt, müssen für die korrekte Ausführung dieser Anweisungen auf der Slave-Seite auch einige relevante Informationen bei der Ausführung jeder Anweisung aufgezeichnet werden, dh Kontextinformationen, um sicherzustellen, dass alle Anweisungen ausgeführt werden Bei korrekter Ausführung auf der Slave-Seite kann das gleiche Ergebnis erzielt werden wie bei der Ausführung auf der Master-Seite. Da sich MySQL jetzt schnell weiterentwickelt, wurden außerdem viele neue Funktionen hinzugefügt, was die Replikation von MySQL vor große Herausforderungen stellt. Natürlich ist es umso einfacher, dass Fehler auftreten, je komplexer der Inhalt bei der Replikation ist. Auf Anweisungsebene wurden viele Situationen entdeckt, die zu MySQL-Replikationsproblemen führen. Dies tritt hauptsächlich auf, wenn bestimmte Funktionen oder Funktionen beim Ändern von Daten verwendet werden.

2.rowlevel-Modus

Das Protokoll zeichnet die geänderte Form jeder Datenzeile auf und ändert dann dieselben Daten auf der Slave-Seite Vorteile: Das Bin-Protokoll muss nicht die kontextbezogenen Informationen der ausgeführten SQL-Anweisung aufzeichnen, sondern nur aufzeichnen, welcher Datensatz geändert wurde und um welche Änderung es sich handelte. Daher werden im Inhalt des Protokolls auf Zeilenebene die Details jeder Datenänderungszeile klar aufgezeichnet. Und es stellt kein Problem dar, dass gespeicherte Prozeduren, Funktionen sowie Triggeraufrufe und Trigger unter bestimmten Umständen nicht korrekt kopiert werden können. Nachteile: Wenn auf Zeilenebene alle ausgeführten Anweisungen im Protokoll aufgezeichnet werden, werden sie als in jeder Zeile aufgezeichnete Änderungen aufgezeichnet, wodurch möglicherweise eine große Menge an Protokollinhalten generiert wird. Beispielsweise gibt es eine solche Aktualisierungsanweisung: Produktsatz aktualisieren Owner_member_id = 'd', wobei Owner_member_id = 'a' ist. Nach der Ausführung wird im Protokoll nicht das dieser Aktualisierungsanweisung entsprechende Ereignis aufgezeichnet (MySQL zeichnet das Bin-Log-Protokoll in Form von Ereignissen auf), sondern jedes von aktualisierte Ereignis Diese Anweisung wird als viele Ereignisse aufgezeichnet, bei denen viele Datensätze aktualisiert werden. Natürlich wird die Menge der Bin-Log-Protokolle groß sein.

3. Der gemischte Modus

ist eigentlich eine Kombination der ersten beiden Modi. Im gemischten Modus unterscheidet MySQL die aufzuzeichnende Protokollform basierend auf jeder einzelnen ausgeführten SQL-Anweisung, dh zwischen Anweisung und Zeile. Wählen Sie eine aus. Die Anweisungsebene ist in der neuen Version weiterhin dieselbe wie zuvor, es werden nur die ausgeführten Anweisungen aufgezeichnet. Der Zeilenebenenmodus wurde in der neuen Version von MySQL optimiert. Wenn sich die Tabellenstruktur beispielsweise ändert, wird sie tatsächlich im Anweisungsmodus aufgezeichnet Bei Anweisungen wie delete, die Daten ändern, werden alle Zeilenänderungen weiterhin aufgezeichnet.

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

Durch die obige Einführung wissen wir, dass binlog_format in einigen Szenarien IO sparen und die Synchronisierung beschleunigen kann. COMMITTED: Wenn die Isolationsstufe READ-UNCOMMITTED oder der Parameter innodb_locks_unsafe_for_binlog aktiviert ist, ist das Schreiben unter binlog_format=statement verboten. Gleichzeitig ist binlog_format=mixed das Standardformat für Schreibanweisungen für Nicht-Transaktions-Engines und andere Isolationsstufen. zeichnet nur das Zeilenformat auf.

> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+

> create table t(c1 int) engine=innodb;

> set binlog_format=statement;

> insert into t values(1);
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

> set binlog_format='mixed';

> show binlog events in 'mysql-bin.000004'\G
*************************** 3. row ***************************
   Log_name: mysql-bin.000002
        Pos: 287
 Event_type: Gtid
  Server_id: 3258621899
End_log_pos: 335
       Info: SET @@SESSION.GTID_NEXT= 'ed0eab2f-dfb0-11e7-8ad8-a0d3c1f20ae4:9375'
*************************** 4. row ***************************
   Log_name: mysql-bin.000002
        Pos: 335
 Event_type: Query
  Server_id: 3258621899
End_log_pos: 407
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: mysql-bin.000002
        Pos: 407
 Event_type: Table_map
  Server_id: 3258621899
End_log_pos: 452
       Info: table_id: 124 (test.t)
*************************** 6. row ***************************
   Log_name: mysql-bin.000002
        Pos: 452
 Event_type: Write_rows_v1
  Server_id: 3258621899
End_log_pos: 498
       Info: table_id: 124 flags: STMT_END_F
*************************** 7. row ***************************
   Log_name: mysql-bin.000002
        Pos: 498
 Event_type: Xid
  Server_id: 3258621899
End_log_pos: 529
       Info: COMMIT /* xid=18422 */复制代码

Warum kann ich das Anweisungsformat binlog unter READ-COMMITTED(RC) und READ-UNCOMMITTED nicht verwenden? Dies liegt daran, dass bei Ausführung einer Anweisung in einer Transaktion Daten angezeigt werden können, die von anderen Transaktionen übermittelt oder geschrieben werden. Nachdem die Transaktion festgeschrieben wurde, wird das Binlog geschrieben und dann von der Slave-Bibliothek wiedergegeben. Die angezeigten Daten stimmen nicht mit den in die Hauptbibliothek geschriebenen Daten überein. Zum Beispiel: Es gibt eine Tabelle:

+------+------+
| a    | b    |
+------+------+
|   10 |    2 |
|   20 |    1 |
+------+------+复制代码

Wir führen die folgenden Operationen aus:

1. session1 aktualisiert in der Transaktion, UPDATE t1 SET a=11 where b=2; Es gibt Zeilen (10,2), die Erfüllen Sie die Bedingungen. Ein Datensatz wurde nicht übermittelt.
UPDATE t1 SET a=11 where b=2;满足条件的有行(10,2)的一条记录，并未提交。
2. session2也做update操作，将行(20,1)更新为(20,2)并提交。
3. 然后前面的sesssion1提交对行(10,2)的更新。

如果binlog中使用Statement格式记录，在slave回放的时候，session2中的更新由于先提交会先回放，将行(20,1)更新为（20,2)。随后回放session1的语句UPDATE t1 SET a=11 where b=2;session2 führt außerdem einen Aktualisierungsvorgang durch, aktualisiert Zeile (20,1) auf (20,2) und übermittelt sie.

Dann schreibt die vorherige Sitzung1 die Aktualisierung in Zeile (10,2) fest. 🎜Wenn das Anweisungsformat für die Aufzeichnung im Binlog verwendet wird, wird während der Slave-Wiedergabe das Update in Sitzung2 zuerst abgespielt, da es zuerst übermittelt wurde, und Zeile (20,1) wird auf ( 20,2). Spielen Sie dann die Anweisung von session1 UPDATE t1 SET a=11 where b=2; ab und die beiden Zeilen (10,2) und (20,2) werden aktualisiert (11,2). Dies führt zum Verhalten der Hauptbibliothek (11, 2), (20,2) und auf der Slave-Seite zu (11,2), (11, 2). 🎜

三、问题分析

上面是通过一个具体的例子说明。本质原因是RC事务隔离级别并不满足事务串行化执行要求，没有解决不可重复和幻象读。

对于Repetable-Read和Serializable隔离级别就没关系，Statement格式记录。这是因为对于RR和Serializable，会保证可重复读，在执行更新时候除了锁定对应行还会在可能插入满足条件行的时候加GAP Lock。上述case更新时，session1更新b =2的行时，会把所有行和范围都锁住，这样session2在更新的时候就需要等待。从隔离级别的角度看Serializable满足事务的串行化，因此binlog串行记录事务statement格式是可以的。同时InnoDB的RR隔离级别实际已经解决了不可重复读和幻象读，满足了ANSI SQL标准的事务隔离性要求。

READ-COMMITTED、READ-UNCOMMITTED的binlog_format限制可以说对于所有事务引擎都适用。

四、拓展内容

对于InnoDB RR和Serializable隔离级别下就一定能保证binlog记录Statement格式么？也不一定。在Innodb中存在参数innodb_locks_unsafe_for_binlog控制GAP Lock，该参数默认为OFF：

mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   |
+--------------------------------+-------+
1 row in set (0.01 sec)复制代码

即RR级别及以上除了行锁还会加GAP Lock。但如果该参数设置为ON，对于当前读就不会加GAP Lock，即在RR隔离级别下需要加Next-key lock的当前读蜕化为READ-COMMITTED。所以如果此参数设置为ON时即便使用的事务隔离级别为Repetable-Read也不能保证从库数据的正确性。

五、总结

对于线上业务，如果使用InnoDB等事务引擎，除非保证RR及以上隔离级别的写入，一定不要设置为binlog_format为STATEMENT，否则业务就无法写入了。而对于binlog_format为Mixed模式，RR隔离级别以下这些事务引擎也一定写入的是ROW event。

更多相关免费学习推荐：mysql教程(视频)

Das obige ist der detaillierte Inhalt vonAuswahl des Binlog-Formats bei Verwendung von Binlog in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!