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リバースプロキシを使用するときに長時間接続を維持する方法

推薦する

WeChatミニプログラムにナビゲーション機能を実装する方法

1. レンダリング2. 操作手順1. テンセントマップキーを申請する - 住所2. ミニプログラムの...

MySQL 5.7.18 zip バージョンのインストールと設定方法のグラフィック チュートリアル (win7)

Windows に mysql5.7.18zip バージョンをインストールするには、使用前に解凍し...

mysqlのデータディレクトリ内のファイルを直接コピーしてデータを復元する実装

mysqlはデータディレクトリ内のファイルをコピーしてデータを復元します背景: MySQL がクラッ...

抽選効果を実現するJavaScript

この記事では、宝くじマシンの効果を実現するためのJavaScriptの具体的なコードを参考までに共有...

Docker で Redis クラスターを素早く構築する方法の例

Redis クラスターとはRedis クラスターは、R​​edis が提供する分散データベース ソリ...

Vueは買い物数量を変更できるショッピングカートを実装します

この記事では、Vueを使用してショッピングカートの数量を変更する方法を紹介します。具体的な内容は次の...

HTML に埋め込まれた Flash HTML ウェブページ コードに Flash ファイルを埋め込むソリューション (パート 2)

上の記事で、SWFObject V1.5 の使い方の紹介は一旦終了です。これから、SWFObject...

Webリクエストと処理のTomcatソースコード分析

目次序文1. エンドポイント2. 接続ハンドラ3. コヨーテ4. コンテナ責任チェーンパターン序文T...

Vue-CLI3.xはプロジェクトをサーバーに自動的にデプロイします

目次序文1. scp2をインストールする2. テスト/本番環境サーバーのSSHリモートログインアカウ...

MySQLにおける遅いSQLの最適化の方向性について詳しく話しましょう

目次序文SQL文の最適化遅いクエリSQLを記録する設定を変更する方法スロークエリログを表示するSQL...

ユーザーエクスペリエンスの要素またはWebデザインの要素

システムとユーザー環境の設計<br />Apple システムの成功は、そのシステム アー...

postcss-pxtorem モバイル適応の実装

コマンドを実行してプラグインpostcss-pxtoremをインストールします npm インストール...

JavaScript 定期検証パスワード強度実装方法

展示する デザインパスワード強度分析パスワードは数字、文字、特殊記号で構成されていますパスワード: ...

Reactはグローバル箇条書きボックスメソッドをカプセル化します

この記事の例では、Reactカプセル化グローバルポップアップボックスの具体的なコードを参考までに共有...

CSS scroll-snap スクロールイベント停止と要素位置検出の実装

1. スクロールスナップはフロントエンド開発者にとって必須のスキルですCSS スクロール スナップは...