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 リバース プロキシと負荷分散の実践

推薦する

MySQLの分離レベルとロックメカニズムの詳細な説明

目次簡単な説明: 1. 取引の4つの特徴2. 複数の同時トランザクションによって発生する問題3. ト...

WeChatミニプログラムが星評価を実装

この記事では、WeChatアプレットで星評価を実装するための具体的なコードを参考までに紹介します。具...

MySql の忘れたパスワードの変更方法はバージョン 5.7 以上に適しています

1. まずmysqld.exeプロセスを停止します2. cmd を開き、mysql の bin ディ...

Dockerを使用してMySQL 8.0をデプロイする方法の例

1. 公式サイトを参照してdockerをインストールする2. MySQLイメージをプルします(デフォ...

MySQL最適化ツール(推奨)

序文今日 GitHub を閲覧していたところ、SQL を最適化および書き換えるための sora とい...

HTMLの基本構文は、HTMLを学び始めたばかりの人にとって便利です。

1.1 一般的なマーキング一般的なタグは開始タグと終了タグで構成されます。構文は次のとおりです: ...

フレックスレイアウトのスペース間の最後の行の左揃えの問題を解決する方法

まずはコードと効果を見てみましょう↓ <スタイル> 。主要 { アウトライン: 1px ...

MySQLデータベースのマスタースレーブ同期構成と読み取り書き込み分離

MySQL マスター スレーブ レプリケーションを使用する利点は次のとおりです。 1. 安定性を向上...

HTML <!--...--> コメントタグの役割の詳細な分析

多くのウェブサイトのソースコードを確認すると、多くのコメントが見つかります。特に、ソース文書にコメン...

docker-compose ネットワーク設定についての簡単な説明

ネットワーク使用チュートリアル公式サイト docker-compose.yml リファレンスドキュメ...

ChromeはCookieの変更を監視し、値を割り当てます

次のコードは、Chrome による Cookie の変更の監視を導入しています。コードは次のとおりで...

CentOS 7.6 Telnetサービス構築プロセス(Opensshアップグレードバトル第一弾のバックアップトランスポートライン構築)

不明な点があるときはいつでも、Blog Park にアクセスして、いつでも答えやインスピレーションを...

MySQL 8.0 の降順インデックス

序文インデックスが順序付けられていることは誰もが知っていると思いますが、MySQL の以前のバージョ...

Ubuntu 16.04 カーネルのアップグレード手順

1. 環境仮想マシン上で実行されているUbuntu 16.04では、コマンドuname -rを使用し...

Reactプロジェクトで画像を導入するいくつかの方法

imgタグは画像を導入しますreactは実際にはjsリーダー関数を介してページをレンダリングするため...