MySQL で binlog を使用する際のフォーマットの選択方法

1. binlogの3つのモード

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

データを変更するすべての SQL ステートメントは、マスターの bin-log に記録されます。スレーブがレプリケーションしている場合、SQL プロセスはそれを元のマスターによって実行されたのと同じ SQL に解析し、再度実行します。

利点: ステートメント レベルでの利点は、まず行レベルでの欠点を解決します。各データ行の変更を記録する必要がないため、bin-log ログの量が削減され、IO が節約され、パフォーマンスが向上します。マスター上で実行されたステートメントの詳細と、ステートメントが実行されたときのコンテキスト情報のみを記録する必要があるためです。

デメリット: 実行ステートメントを記録するため、これらのステートメントをスレーブ側で正しく実行するには、実行時に各ステートメントの関連情報、つまりコンテキスト情報も記録して、すべてのステートメントがスレーブ側で実行されたときにマスター側で実行されたときと同じ結果が得られるようにする必要があります。さらに、MySQL の急速な発展により、多くの新機能が追加され、MySQL レプリケーションにかなりの課題をもたらしました。当然、レプリケーションに関係するコンテンツが複雑になるほど、バグが発生する可能性が高くなります。ステートメント レベルでは、MySQL レプリケーションの問題を引き起こす状況が多数見つかりました。これらの問題は主に、データの変更時に特定の関数または機能を使用した場合に発生します。たとえば、一部のバージョンでは sleep() を正しくレプリケートできません。

2. 行レベルモード

ログには変更されたデータの各行が記録され、スレーブ側で同じデータが変更されます。

利点: Bin-log では、実行された SQL ステートメントのコンテキスト関連の情報を記録する必要はありません。どのレコードが変更され、どのように変更されたかを記録するだけで済みます。したがって、行レベルのログの内容には、データ変更の各行の詳細が明確に記録されます。さらに、特定の状況でストアド プロシージャ、関数、またはトリガーの呼び出しとトリガーが正しく複製されないという問題も発生しません。

デメリット: 行レベルでは、実行されたすべてのステートメントがログに記録されると、各行への変更として記録されます。これにより、大量のログ コンテンツが生成される場合があります。たとえば、update product set owner_member_id='d' where owner_member_id='a' のような更新ステートメントがあります。実行後、ログにはこの更新ステートメントに対応するイベントは記録されません (MySQL は、イベントの形式で bin-log ログを記録します)。ただし、このステートメントによって更新された各レコードの変更は記録されます。このように、更新される多くのレコードの多くのイベントが記録されます。当然、bin-log ログの量は非常に多くなります。

3. 混合モード

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

2. binlog を使用する場合、どのような形式を選択すればよいですか?

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

> @@tx_isolation を選択します。
+----------------+
| @@tx_isolation |
+----------------+
| コミット読み取り |
+----------------+

> テーブル t(c1 int) を作成します。engine=innodb;

> binlog_format=ステートメントを設定します。

> t値に挿​​入(1);
エラー 1665 (HY000): ステートメントを実行できません。BINLOG_FORMAT = STATEMENT であり、少なくとも 1 つのテーブルが行ベースのログ記録に制限されているストレージ エンジンを使用しているため、バイナリ ログに書き込むことができません。トランザクション分離レベルが READ COMMITTED または READ UNCOMMITTED の場合、InnoDB は行ログ記録に制限されます。

> binlog_format='mixed' を設定します。

> 'mysql-bin.000004' の binlog イベントを表示\G
************************** 3. 行 ****************************
 ログ名: mysql-bin.000002
  順位: 287
 イベントタイプ: Gtid
 サーバーID: 3258621899
終了ログ位置: 335
  情報: SET @@SESSION.GTID_NEXT= 'ed0eab2f-dfb0-11e7-8ad8-a0d3c1f20ae4:9375'
************************** 4. 行 ****************************
 ログ名: mysql-bin.000002
  ポジション: 335
 イベントタイプ: クエリ
 サーバーID: 3258621899
終了ログ位置: 407
  情報: 開始
************************** 5. 行 ****************************
 ログ名: mysql-bin.000002
  ポジション: 407
 イベントタイプ: テーブルマップ
 サーバーID: 3258621899
終了ログ位置: 452
  情報: table_id: 124 (test.t)
************************** 6. 行 ****************************
 ログ名: mysql-bin.000002
  ポジション: 452
 イベントタイプ: Write_rows_v1
 サーバーID: 3258621899
終了ログ位置: 498
  情報: table_id: 124 フラグ: STMT_END_F
************************** 7. 行 ****************************
 ログ名: mysql-bin.000002
  ポジション: 498
 イベントタイプ: Xid
 サーバーID: 3258621899
終了ログ位置: 529
  情報: COMMIT /* xid=18422 */

ステートメント形式の binlog を READ-COMMITTED (RC) モードと READ-UNCOMMITTED モードで使用できないのはなぜですか?これは、ステートメントがトランザクション内で実行されると、他のトランザクションによってコミットされたデータや書き込まれたデータが確認できるためです。トランザクションがコミットされた後、バイナリログが書き込まれます。その後、スレーブでそれを再生すると、表示されるデータはマスターに書き込まれたときと一致しなくなります。

例えば：

表があります:

+------+------+
| ア | ロ |
+------+------+
| 10 | 2 |
| 20 | 1 |
+------+------+

私たちは次のことを行います:

1. セッション1はトランザクションで更新を実行します。UPDATE t1 SET a=11 where b=2; 行(10,2)のレコードは条件を満たしていますが、コミットされません。
2. セッション2も更新操作を実行し、行(20,1)を(20,2)に更新して更新をコミットします。
3. 次に、前のセッション1が行(10,2)への更新をコミットします。

binlog が Statement 形式で記録されている場合、スレーブがデータを再生すると、最初に送信されたセッション 2 の更新が最初に再生され、行 (20,1) が (20,2) に更新されます。次に、セッション 1 でステートメント UPDATE t1 SET a=11 where b=2; を再生すると、2 つの行 (10,2) と (20,2) が (11,2) に更新されます。この結果、マスターデータベースの動作は (11, 2)、(20,2) となり、スレーブ側の動作は (11,2)、(11, 2) となります。

3. 問題分析

上記を具体的な例で説明します。根本的な理由は、RC トランザクション分離レベルがトランザクションのシリアル化実行要件を満たしておらず、非反復読み取りとファントム読み取りを解決できないことです。

Repetable-Read および Serializable 分離​​レベルの場合、ステートメントの形式が記録されるため、問題にはなりません。これは、RR および Serializable の場合、繰り返し読み取りが保証されるためです。更新を実行すると、該当する行をロックするだけでなく、条件を満たす行が挿入される可能性がある場合に GAP ロックが追加されます。上記の場合、セッション 1 が b = 2 で行を更新すると、すべての行と範囲がロックされるため、セッション 2 は更新時に待機する必要があります。分離レベルの観点から見ると、Serializable はトランザクションのシリアル化を満たすため、binlog シリアル レコード トランザクション ステートメント形式は受け入れられます。同時に、InnoDB の RR 分離レベルは、非反復読み取りとファントム読み取りを実際に解決し、ANSI SQL 標準のトランザクション分離要件を満たしています。

READ-COMMITTED および READ-UNCOMMITTED binlog_format の制限は、すべてのトランザクション エンジンに適用されます。

4. コンテンツを拡張する

InnoDB RR および Serializable 分離​​レベルでは、binlog レコード ステートメント形式が保証されますか?必ずしもそうではありません。 Innodb には、GAP ロックを制御するためのパラメータ innodb_locks_unsafe_for_binlog があります。デフォルト設定は OFF です。

mysql> 'innodb_locks_unsafe_for_binlog' のような変数を表示します。
+--------------------------------+-------+
| 変数名 | 値 |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | オフ |
+--------------------------------+-------+
セット内の1行（0.01秒）

つまり、RR レベル以上では、行ロックに加えて GAP ロックも適用されます。ただし、このパラメータを ON に設定すると、GAP ロックは現在の読み取りに追加されません。つまり、RR 分離レベルで Next-key ロックを必要とする現在の読み取りは、READ-COMMITTED に縮退します。したがって、このパラメータを ON に設定すると、使用されるトランザクション分離レベルが Repetable-Read であっても、スレーブ データの正確性は保証されません。

V. 結論

オンライン業務で、InnoDB などのトランザクション エンジンを使用する場合、RR 分離レベル以上の書き込みを保証する場合を除き、binlog_format を STATEMENT に設定しないでください。そうしないと、業務の書き込みができなくなります。混合モードの binlog_format の場合、RR 分離レベル以下のこれらのトランザクション エンジンは ROW イベントも書き込む必要があります。

MySQL で binlog を使用する際のフォーマットの選択方法についての記事はこれで終わりです。MySQL で binlog を使用する際のフォーマットの選択方法についての詳細は、123WORDPRESS.COM の過去の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。