MySQL の binlog_format モードと設定の詳細な分析

MySQL レプリケーションには、SQL ステートメント ベースのレプリケーション (SBR)、行ベースのレプリケーション (RBR)、および混合ベースのレプリケーション (MBR) の 3 つの主な方法があります。それに応じて、binlog にはSTATEMENT、ROW、MIXEDの 3 つの形式があります。

① ステートメントモード（SBR）

データを変更するすべての SQL ステートメントは binlog に記録されます。利点は、すべての SQL ステートメントとすべてのデータ行の変更を記録する必要がないため、binlog ログの量が削減され、IO が節約され、パフォーマンスが向上することです。欠点は、場合によってはマスターとスレーブの間でデータの不整合が発生する可能性があることです (sleep() 関数、last_insert_id()、ユーザー定義関数 (udf) の問題など)。

② ROWモード（RBR）

各 SQL ステートメントのコンテキスト情報は記録せず、変更されたデータとその変更方法のみを記録します。さらに、特定の状況において、ストアド プロシージャ、関数、またはトリガーの呼び出しとトリガーが正しく複製されないという問題も発生しません。欠点は、特にテーブルを変更するときに大量のログが生成され、ログが爆発的に増加することです。

③ 混合モード（MBR）

上記 2 つのモードは組み合わせて使用​​されます。一般的なレプリケーションでは、STATEMENT モードを使用してバイナリログを保存します。STATEMENT モードでレプリケートできない操作では、ROW モードを使用してバイナリログを保存します。MySQL は、実行された SQL ステートメントに基づいてログ保存方法を選択します。

Binlog レプリケーション構成

MySQL設定ファイルmy.cnfでは、binglog関連のオプションを設定できます。

binlog_format = MIXED //binlog ログ形式、MySQL はデフォルトでステートメントを使用しますが、混合が推奨されます
log-bin = /data/mysql/mysql-bin.log //binlog ログ ファイル expire_logs_days = 7 //binlog 有効期限のクリーンアップ時間 max_binlog_size = 100m //binlog ログ ファイル サイズ binlog_cache_size = 4m //binlog キャッシュ サイズ max_binlog_cache_size = 512m //最大 binlog キャッシュ サイズ

3つのMIXEDの説明

実行された SQL ステートメントに now() などの時間関数が含まれている場合、ログには対応する時間文字列 unix_timestamp()*1000 が生成されます。スレーブは同期を完了すると、sqlEvent が発生した時間を使用してデータの正確性を確保します。さらに、一部の機能については、スレーブが対応するデータ同期を完了できます。スレーブが状況を認識しない上記の一部の UDF 機能については、これらの Binlog は ROW 形式で保存され、生成された Binlog を使用してスレーブがデータ同期を完了できるようにします。

それでは、SBR と RBR 2 の次のモードの長所と短所を比較してみましょう。

SBRの利点:

長い歴史と成熟した技術

バイナリログファイルは小さい

binlog にはデータベースのすべての変更情報が含まれており、データベースのセキュリティを監査するために使用できます。

Binlogはレプリケーションだけでなくリアルタイムの復元にも使用できます

マスターとスレーブのバージョンは異なっていてもよく、スレーブサーバーのバージョンはマスターサーバーのバージョンよりも高くてもよい。

SBR の欠点:

すべての UPDATE ステートメント、特に不確定な操作を含むステートメントを複製できるわけではありません。

非決定的要素を持つUDFを呼び出すと、レプリケーションが失敗することもあります。

次の関数を使用するステートメントもコピーできません。

* LOAD_FILE()

* UUID()

* ユーザー()

* 見つかった行()

* SYSDATE() (起動時に --sysdate-is-now オプションが有効になっていない限り)

INSERT ... SELECTはRBRよりも多くの行レベルロックを生成します。

完全なテーブルスキャンを必要とする UPDATE (WHERE 句でインデックスが使用されない) をレプリケートする場合、RBR の場合よりも多くの行レベルのロックが要求されます。

AUTO_INCREMENT フィールドを持つ InnoDB テーブルの場合、INSERT ステートメントは他の INSERT ステートメントをブロックします。

一部の複雑なステートメントでは、スレーブ サーバーのリソース消費がより深刻になります。RBR モードでは、変更されたレコードのみが影響を受けます。ストアド プロシージャではなくストアド関数も、呼び出されると NOW() 関数を 1 回実行します。これは、悪いことにも、良いことにもなり得ます。

決定されたUDFはスレーブサーバーでも実行する必要がある

データ テーブルはプライマリ サーバーとほぼ一致している必要があります。一致していない場合は、レプリケーション エラーが発生する可能性があります。

複雑なステートメントの実行中にエラーが発生すると、より多くのリソースが消費されます。

RBRの利点:

あらゆる状況をコピーすることができ、コピーするのに最も安全で信頼できる方法です

他のほとんどのデータベースシステムと同様に、レプリケーション技術は

ほとんどの場合、スレーブ上のテーブルに主キーがあれば、レプリケーションははるかに高速になります。

次のステートメントを複製するときに使用される行ロックが少なくなります。

* 挿入...選択

* AUTO_INCREMENTフィールドを含むINSERT

* 条件を持たない、または多くのレコードを変更しない UPDATE または DELETE ステートメント

INSERT、UPDATE、DELETE ステートメントを実行する際のロックが少なくなります。

複数のスレッドを使用してサーバーからのレプリケーションを実行することが可能です。

RBR の欠点:

Binlogははるかに大きい

複雑なロールバック中にバイナリログに大量のデータが含まれる

マスター サーバーで UPDATE ステートメントを実行すると、変更されたすべてのレコードが binlog に書き込まれますが、SBR は 1 回しか書き込まないため、binlog で同時書き込みの問題が頻繁に発生します。

UDFによって生成される大きなBLOB値はレプリケーションを遅くする可能性があります

バイナリログからどのステートメントがコピーされたかを確認することはできない

非トランザクション テーブルでスタックされた SQL ステートメントを実行する場合は、SBR モードを使用するのが最適です。そうしないと、マスター サーバーとスレーブ サーバーの間でデータの不整合が発生しやすくなります。また、システム ライブラリ mysql のテーブルの変更の処理ルールは次のとおりです。

INSERT、UPDATE、DELETE を使用してテーブルを直接操作した場合、ログの形式は binlog_format の設定に従って記録されます。

GRANT、REVOKE、SET PASSWORD などの管理ステートメントを使用する場合は、SBR モードのログ記録が使用されます。

注: RBR モードを採用すると、以前に発生していた重複する主キーの問題の多くが解決されます。

要約する

以上が、MySQL の binlog_format モードと設定の詳細な分析に関するこの記事の内容のすべてです。皆様のお役に立てれば幸いです。興味のある方は、いくつかの重要な MySQL 変数、MySQL の準備原則の詳細な説明、MySQL 変数の宣言とストアド プロシージャの分析などを参照してください。質問がある場合は、メッセージを残してください。誰でもコミュニケーションして話し合うことができます。