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

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 変数の宣言とストアド プロシージャの分析などを参照してください。質問がある場合は、メッセージを残してください。誰でもコミュニケーションして話し合うことができます。

以下もご興味があるかもしれません:
  • mysql binlog (バイナリログ) を表示する方法
  • MySQL でデータ復旧に binlog を使用する方法
  • Mysql Binlogデータの表示方法の詳細な説明
  • MySQL マスターライブラリ binlog (master-log) とスレーブライブラリ relay-log 間のコードの詳細な説明
  • MySQLの誤操作後にbinlog2sqlを使用して素早くロールバックする方法の詳細な説明
  • mysql binlog ログを正しくクリーンアップする 2 つの方法
  • MySQL binlog を開く手順

<<:  Reactの仮想DOMとdiffアルゴリズムの詳細な説明

>>:  Nginx リバース プロキシと負荷分散の実践

推薦する

HTM と HTML の違いは何ですか? HTM と HTML の違いは何ですか?

Web デザインを学習する過程で、html と htm の関係など、遭遇した多くの問題について深く...

MySQL データベース アーキテクチャの詳細

目次1. MySQL アーキテクチャ2. ネットワーク接続層3. データベースサービス層4. 接続プ...

Linux でのマルチスレッドおよびマルチプロセス クラッシュのシミュレーションに関する簡単な説明

結論:マルチスレッド環境では、スレッドの 1 つがクラッシュすると、他のスレッド (プロセス全体) ...

HTML フォームタグチュートリアル (2):

このチュートリアルでは、ウェブデザインにおけるFORMフォームタグのさまざまな属性の応用を紹介します...

MySQL の複数テーブル関連付け 1 対多クエリを使用して最新のデータを取得する方法の例

この記事では、MySQL で複数のテーブルを使用して 1 対多のクエリを使用して最新のデータを取得す...

TypeScript ジェネリックパラメータのデフォルト型と新しい厳密なコンパイルオプション

目次概要コンポーネントクラスの型定義を作成するジェネリック型を使用してPropsとStateを定義す...

MySQL自動シャットダウン問題への対処の実践記録

最近、あるプロジェクトを手伝ったのですが、MySQL マシンがしばらくすると自動的に停止し続けました...

CentOS 上での MySQL 5.6 のコンパイルとインストール、および複数の MySQL インスタンスのインストールの詳細な説明

--1. mysql用の新しいグループとユーザーを作成する # ユーザー追加 -M -s /sbin...

HTML内の画像はbase64でエンコードされた文字列に直接置き換えられます

最近、画像はあるのに外部画像リソースが参照されていないウェブページを見つけました。気になりました。コ...

正の整数かどうかを判断するMYSQLカスタム関数の例コード

関数を記述できます。主に正規表現を使用して判断を行います。入力文字が空の場合は、「-」を使用して置き...

jQuery プロジェクトで重複送信を防ぐ方法

新しいプロジェクトでは、axios によって重複した送信を防ぐことができますが、古いプロジェクト (...

MySQL インデックスに関するヒントのまとめ

目次1. インデックスの基礎知識1.1 インデックスの利点1.2 インデックスの有用性1.3 インデ...

MySQL テーブルスペースとは何ですか?

今日皆さんにお伝えしたいトピックは、「皆さんがよく話題にするテーブル スペースとは一体何でしょうか。...

jQueryはシンプルなポップアップウィンドウ効果を実装します

この記事では、簡単なポップアップウィンドウ効果を実現するためのjQueryの具体的なコードを参考まで...