MySQL で binlog を使用する場合の binlog 形式の選択

mysql チュートリアル 列では、binlog を使用する場合の binlog 形式の選択を紹介します。

1. binlog の 3 つのモード

1.ステートメント レベル モード

データを変更するすべての SQL はマスターに記録されます。ビンログ。スレーブがコピーすると、SQL プロセスはそれを元のマスター側で実行されたのと同じ SQL に解析し、再度実行します。 利点: ステートメント レベルの利点は、行レベルの欠点が最初に解決されることです。データの各行の変更を記録する必要がなく、バイナリ ログ ログの量が削減され、IO が節約され、パフォーマンスが向上します。マスター上で実行されたステートメントの詳細と、ステートメントが実行されたときのコンテキスト情報のみを記録する必要があるためです。 欠点: これは記録された実行ステートメントであるため、これらのステートメントがスレーブ側で正しく実行されるためには、すべてのステートメントが確実に実行されるように、各ステートメントの実行時にいくつかの関連情報、つまりコンテキスト情報も記録する必要があります。スレーブ側で実行してもマスター側で実行した場合と同様の結果が得られます。また、mysqlは現在急速に発展しており、新機能も多数追加されており、mysqlのレプリケーションには多くの課題があり、レプリケーションの内容が複雑になればなるほどバグが発生しやすくなります。ステートメント レベルでは、MySQL レプリケーションの問題を引き起こす多くの状況が発見されています。これは主に、データを変更するときに特定の関数が使用されるときに発生します。たとえば、sleep() は一部のバージョンでは使用できません。正しくコピーされました。

2.rowlevel モード

ログはデータの各行の変更された形式を記録し、スレーブ側で同じデータを変更します。 利点: bin ログには、実行された SQL ステートメントのコンテキスト関連情報を記録する必要はなく、どのレコードが変更されたか、およびその変更内容が記録されるだけで済みます。したがって、行レベルのログの内容には、データ変更の各行の詳細が明確に記録されます。また、ストアド プロシージャ、関数、トリガーの呼び出しやトリガーが特定の状況下で正しくコピーされないという問題は発生しません。 欠点: 行レベルでは、実行されたすべてのステートメントがログに記録されると、各行に記録される変更として記録されるため、大量のログ コンテンツが生成される可能性があります。たとえば、次のような更新ステートメントがあります: update product set owner_member_id= 'd' (owner_member_id='a' の場合) の実行後、ログに記録されるのは、この更新ステートメントに対応するイベント (mysql はイベントの形式で bin-log ログを記録します) ではなく、によって更新されたすべてのイベントです。 1 つのレコードの変更は、多数のレコードが更新される多数のイベントとして記録されます。当然、bin-log ログの量は多くなります。

3.混合モード

は、実際には最初の 2 つのモードを組み合わせたものです。混合モードでは、mysql は実行される特定の SQL ステートメントごとに記録されるログ形式を区別します。ステートメントと行のどちらかを選択します。新しいバージョンのステートメント レベルは以前と同じで、実行されたステートメントのみが記録されます。行レベル モードは、mysql の新しいバージョンで最適化されています。すべての変更が行レベルで記録されるわけではありません。たとえば、テーブル構造の変更が発生した場合、それらはステートメント モードで記録されます。SQL ステートメントが実際に update またはデータを変更する delete などのステートメントの場合、すべての行の変更が記録されます。

2. binlog を使用する場合、どの形式を選択する必要がありますか?

上記の紹介を通じて、binlog_format が STATEMENT であることがわかりました。これにより、一部のシナリオでは IO が節約され、同期が高速化されますが、これは InnoDB の場合に当てはまります。 , READ-COMMITTED、READ-UNCOMMITTED 分離レベルまたはパラメーター innodb_locks_unsafe_for_binlog が ON の場合、トランザクション エンジンは、binlog_format=statement での書き込みを禁止します。同時に、binlog_format=mixed は、非トランザクションのステートメント形式のデフォルトの書き込みモードです。エンジンおよびその他の分離レベル行フォーマットのみが記録されます。

> 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 */复制代码

ステートメント形式の binlog を READ-COMMITTED (RC) および READ-UNCOMMITTED で使用できないのはなぜですか?これは、ステートメントがトランザクションで実行されると、他のトランザクションによって送信されたデータまたは書き込まれているデータが表示される可能性があるためです。トランザクションがコミットされた後、バイナリログが書き込まれ、スレーブ ライブラリから再生されます。表示されるデータは、メイン ライブラリに書き込まれたデータと一致しません。 例えば： テーブルがあります:

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

次の操作を実行します:

1. session1 トランザクションで更新、UPDATE t1 SET a=11 where b=2;条件を満たします行(10,2)に未送信のレコードがあります。
2. session2 も更新操作を実行し、行 (20,1) を (20,2) に更新して送信します。
3. 次に、前のセッション 1 が更新を行 (10,2) に送信します。

ビンログがステートメント形式の記録を使用する場合、スレーブ再生中に、セッション 2 の更新が最初に送信されたため、最初に再生され、行 (20,1) が (20,1) に更新されます。 2)。次に、session1 のステートメントを再生します UPDATE t1 SET a=11 where b=2;ステートメントは 2 行 (10,2) と (20,2) を (11,2) に更新します。この結果、メイン ライブラリの動作は (11, 2)、(20,2) となり、スレーブ側は (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教程(视频)

以上がMySQL で binlog を使用する場合の binlog 形式の選択の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。