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

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 をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL シリーズ: redo ログ、undo ログ、binlog の詳細な説明
  • MySQL binlog_ignore_dbパラメータの具体的な使用法
  • MySQLを監視するためのbinlogログ解析ツールの詳しい説明:Canal
  • MySQL 8.0 の binlog の詳細な説明
  • MYSQL の binlog 最適化に関する考察の要約
  • MySQLデータベースのbinlogクリーンアップコマンドの詳細な説明
  • MySQLデータベース監視binlogを有効にする手順
  • MySQL の innodb_flush_log_at_trx_commit と sync_binlog を区別する方法

<<:  VueはExcelデータをエクスポートするパブリック関数メソッドをカプセル化します

>>:  nginxリバースプロキシを使用するときに長時間接続を維持する方法

推薦する

Zabbix 5.0 ディスク自動検出と読み取り/書き込み監視の問題を分析する

ディスクを自動的に検出する構成キーの値注: このキー値は Linux プラットフォームでのみサポート...

MySql COALESCE 関数の使用コード例

COALESCE は、各パラメータ式 (expression_1、expression_2、...、...

Vueはフォーム検証機能を実装します

この記事では主に、NUXT の validate メソッドに基づいてフォーム検証を実装する方法につい...

同じ日の最初の3つのデータを取得するためのMySQLタイムラインデータ

テーブルデータを作成する テーブル `praise_info` を作成します ( `id` bigi...

MySQL における datetime と timestamp の違いと使い方

1. MySQL で現在の時刻を表現するにはどうすればよいでしょうか?実際、表現方法はいろいろありま...

Linux で環境変数 JAVA_HOME を変更/設定する方法について簡単に説明します。

1. 永久的な変更、すべてのユーザーに有効# vi /etc/プロファイル//キーボードの[Shi...

角度付き双方向バインディングの詳細な説明

目次双方向バインディングの原理ngモデルレンダリングカスタム双方向バインディングプロパティコンポーネ...

JavaScript で charAt() を使用して、最も頻繁に出現する文字とその出現回数をカウントする方法を教えます。

前回は、JavaScript の charAt() メソッドの使い方を紹介しました。今日は、最も多く...

Tomcat 初回展開 Web プロジェクト プロセス図

独自のWebプロジェクトをtomcatディレクトリの下のwebappsディレクトリに配置します。 R...

AngularでTweenMaxアニメーションライブラリを使用する際の問題と解決策

最近何もすることがないのでCSSをいじっていますより良いアニメーションライブラリTweenMaxを見...

Rancher で Kubernetes 用の標準化された VMware イメージを構築する方法

Kubernetes を学習するときは、Kubernetes 環境で練習する必要があります。ただし、...

仕事の効率を上げるJS略語スキル20選

目次複数の変数を同時に宣言する場合は、1 行に短縮できます。分割代入は複数の変数に同時に値を割り当て...

最小限の展開で CentOS8 に OpenStack Ussuri をインストールする方法の詳細なチュートリアル

CentOS8 に最小限のデプロイメントで OpenStack Ussuri をインストールするため...

CSS でホバー ドロップダウン メニューを実装する方法

いつものように、今日は非常に実用的な CSS 効果についてお話します。マウスがボタンに移動すると、ド...

Alibaba Cloud ECSインスタンスのユーザールートパスワードとリモート接続方法を設定する方法

Alibaba Cloud サーバーを購入した後、新しいインスタンスが正常に動作できるようにするには...