MySQL バイナリ ログに関する問題の詳細な説明

この記事の出典:
(出典への権利を保持することは、オリジナルの著作物ではありません。私のささやかな仕事はオリジナルの著作物とは程遠いものです。存在する可能性のあるいくつかの間違いは修正されるか、または原文にリンクするだけです。将来追加されるものであり、他には何もありません)

今日、Yunqi コミュニティが開催する MySQL の「シーズン 1: Xuanxian の遅い SQL パフォーマンス最適化コンテストに挑戦」を偶然発見しました。そのテスト スクリプトをテスト サーバー上で実行してデータを書き込むと、エラー メッセージは次のとおりです。
マルチ ステートメント トランザクションには 'max_binlog_cache_size' バイトを超えるストレージが必要です。この mysqld 変数を増やしてもう一度試してください

プロンプト max_binlog_cache_size のスペースが不足しています。バイナリ ログがオンになっているため、これがデフォルトでした。以前の設定では、大規模なトランザクション操作は発生せず、遭遇もありませんでした。今回の問題は、大規模なトランザクション操作が最初に失敗したことでした。
binlog_cache_size のサイズを変更した後、問題は解決されました。

デフォルトの innodb エンジンが使用されるため、バイナリ ログがオンになります。
トランザクション操作の場合、トランザクションが送信される前に、バイナリ ログが書き込まれる必要があります。実行された書き込み操作はキャッシュされます。 、全体が完了するまで、mysqld プロセスは全体をバイナリ ログに書き込みます。
何かが開始されると、binlog_cache_size システム変数で指定された値に従ってコンテンツ スペースが割り当てられます。指定された binlog_cache_size キャッシュ スペースが十分でない場合、実行されたトランザクション操作はロールバックされ、失敗メッセージが表示されます。

ところで、バイナリログとそれに関連するパラメータ情報をまとめてみましょう

バイナリログとは何ですか？
MySQL データベースへの書き込み操作 (追加、削除、変更、ただしクエリは除く) を記録するために使用されます。これは、sqlserver の完全復旧モードのトランザクション ログ ファイルに相当します。
バイナリログの役割は何ですか?
1. レプリケーションに使用されます。マスターとスレーブのレプリケーションが設定されている場合、マスター サーバーは生成したバイナリ ログをスレーブに送信し、スレーブはこのバイナリ ログの情報を使用してローカルで再実行し、マスターとスレーブの同期を実現します。
2. ユーザーリカバリの場合、MySQL はバイナリログを使用して、完全バックアップと差分バックアップに基づいた時点またはトランザクション ID に基づいてリカバリ操作を実行できます。原理は、マスター/スレーブ レプリケーションのログ REDO と似ています。

バイナリ ログの関連パラメータ情報

1、バイナリ ログを有効にする
バイナリ ログを有効にするには、log-bin パラメータのパスを指定する必要があります。例: log_bin=/var/lib/mysql/mysql- bin
バイナリログを起動すると、バイナリログを管理するlog_bin_indexファイルが自動生成されます。 log_bin オプションも on として表示されます。これは、バイナリ ログがオンであることを意味します。

2、バイナリログファイルの形式

バイナリログの形式は、パラメータ binlog_format によって制御されます。バイナリログには、ステートメントベース、行ベース、およびその組み合わせの 3 つのモードがあります。最初の 2 つは、混合モード (混合)
ステートメントベースのバイナリ関数には、(私の意見では) いくつかの欠陥があります。たとえば、同じ update ステートメント内で現在時刻を使用する now update 操作も、マスター上の現在時刻を取得します。サーバーとスレーブサーバーでは、マスター/スレーブレプリケーションによって得られる結果が異なります。
行ベースのバイナリ ログ モードは、ステートメント ベース モードの欠点の一部を解決しますが、場合によっては、大量のログが生成されます。たとえば、行の場合、更新操作で 100 万行のデータが更新されます。 -ベースのバイナリ ログの結果、100 万行のデータが生成されます。 ログ
ハイブリッド モードに基づいて、上記 2 つの方法の利点を組み合わせます。
設定ファイルで設定可能: binlog_format = MIXED

3. バイナリログ記録のタイミング

バイナリログ記録は同期可能、つまりトランザクションが送信された後にバイナリログに書き込むことができます。 、または非同期にすることもできます。ディスクに書き込むタイミングはオペレーティング システムのディスク キャッシュによって決まります。
パラメータ sync_binlog= n によって制御されます。 sync_binlog = 1 が設定されている場合、これは最高のセキュリティ レベルの書き込みを示します (ただし、トランザクション ログが失われないという保証はありません)。パフォーマンスに一定の影響を与えます。
個人的には、トランザクションエンジンであれば、安全性を確保するためだと思います。 sync_binlog を 1 に設定しない理由はありません。

sync_binlog を 1 に設定するとトランザクション ログが失われる可能性があると言われていますが、トランザクション エンジンであるため、元に戻すまたはやり直しログの層もバックアップとして存在するため、なぜ失われるのかわかりません。 ?

後で考えてみましょう、REDO ログと UNDO ログの存在により、マスター サーバー上で物事の一貫性が保証されます。マスター/スレーブ レプリケーション中に、失われた可能性のあるものが配信されない可能性があるはずです。スレーブサーバー。

4. バイナリログの 1 ファイルのサイズ

バイナリログのサイズは、通常、設定された 1 つのログファイルの最大サイズを超えることはありません。設定された最大制限を超えた場合、その後、ログ ローリングが発生します。つまり、バイナリ ログ ファイルが再生成されます。
Max_binlog_size = 100M
ここで示されている104857600の単位はバイト、つまり104857600/1024/1024 = 100M

5、ローリング後にバイナリログクリーニング

が生成されます ログを保存する新しいファイルs 、ログ ファイルは有効期限が切れると自動的に削除されます。それ以外の場合は、ログ ファイルの継続的なストリームが生成されます。たとえば、有効期限を 2 に設定できます。設定可能な値は、expire_logs_days = 2 です。3 つ以上のバイナリ ログ。日数が経過したものは自動的に削除されます。
show master logsコマンドを使用して、現在のバイナリログファイルの数を表示できます

6、バイナリログファイルのスクロール

1) 通常の状況では、レコードがいっぱいになると、自動的にスクロールします。サフィックス名は+1になります

2) mysqlサービスを再起動すると、指定された最大容量に従ってログファイルがいっぱいかどうかに関係なく、自動的にスクロールします
3) 手動スクロールでは、flush logsコマンドを実行した後、実行します次のようにログをフラッシュすると、バイナリ ログ ファイルが再生成されます

4 ) バイナリ ログを手動で削除します

コマンド

purge binary logs to fileName を使用して、指定された fileName より前のファイルを削除できます

コマンド purge binary logs before '2017-03-10 10:10:00' を使用して、指定された時刻を削除できます。 前のファイル

指定されたログを削除します。 date_sub( now( ) より前のバイナリ ログを削除します。間隔 7 日) );

Xiaoxiang マスターは、date_sub( now( )、間隔 7 日) より前のマスター ログをパージしていますが、これは影響を与えるはずです (バイナリ キーワードとマスター キーワード)。

7、バイナリログバインディング（または除外）データベース

一部のデータベースをバイナリログを有効にするように設定することも、一部のデータベースをバイナリログを有効にしないように設定することもできます

# binlog_do_db: マスター/スレーブを設定するときに使用されます
# binlog - ignore-db: ログを記録しないデータベースを設定します。
MySQL 5.7.18 で設定されています (my.cnf で設定されています) が、

8、バイナリ ログ キャッシュおよびキャッシュを実行する場合は役に立たないようです。サイズ設定

binlog_cache_size のサイズ、最初に述べた問題、起動時に、binlog_cache_size システム変数で指定された値に従ってコンテンツ領域が割り当てられます。指定された binlog_cache_size キャッシュ領域が十分でない場合は、エラーが発生します。が報告され、ロールバックされます

こちら 表示されるレコードの単位もバイトです 1024を2で割った後の容量です ここの20971520/1024/1024は20MBに相当します。
テスト中など、大規模なトランザクション操作がある場合は、キャッシュを比較的大きく設定する必要があります。そうでないと、ステートメントが正常に実行されません

max_binlog_cache_size と binlog_cache_size の違いは、前者がインスタンス レベルのキャッシュであることです。後者はセッションレベルのキャッシュです。同時実行の量が多い場合は、max_binlog_cache_size を少し大きく設定することを検討する必要があります。

デフォルトの max_binlog_cache_size は 4GB で、最大値も 4GB です。ここでのテスト用の設定は 100MB (104857600/1024.0/1024.0) です

。

max_binlog_cache_size で設定される最大メモリ サイズは 4GB です。サーバーのコンテンツが 128GB 以上など大きい場合は、同時書き込みを確実に成功させる必要があるため、max_binlog_cache_size をデフォルトで最大値に設定しても問題ありません。
セッションレベルの binlog_cache_size については、通常の OLTP 操作に加えて、スケジュールされたジョブもいくつかあるため、ビジネスの状況に応じて調整できます。およびその他のデータ抽出またはデータのマージ操作により、大量のログが生成される可能性があります。
binlog_cache_disk_useとbinlog_cache_useを見ることでbinlog_cache_sizeの調整が必要かどうか判断できると言われています。
しかし、このパラメータはMySQL5.7.18では見つかりませんでした

9、バイナリログの他のパラメータ

max_binlog_stmt_cache_sizeは非トランザクションステートメント用であり、非トランザクションパラメータは当面は関係ありません
あるマスターは、innodb エンジンの利点はトランザクションのサポートだけではなく、非トランザクションの myisam エンジンと比較して、読み取りパフォーマンスの差がますます小さくなっていると述べていたため、MySQL は innodb を次のように設定します。デフォルトのエンジン。
myisam を放棄して innodb に行くのが正しい方法です。
binlog_checksum はレプリケーションのマスター/スレーブチェックサムとして使用されます。このパラメータについてはまだ勉強していないので今回は気にしません
詳細はXiang Xiangshenの記事を参照してください。

概要:

MySQL バイナリ ログは、機能 (マスター/スレーブ レプリケーション) だけでなく、セキュリティ (バイナリ ログ) や、バイナリ ログがオンになっている場合のトランザクション操作にも使用されます。環境は必須の構成と考えることができます。
同時に、そのさまざまなパラメータは特定の操作に影響を与えるため、使用時にデータベースの機能と可用性が保証されるように、バイナリログのパラメータには特別な注意を払う必要があります。

参考:

《Smear MySQL》

そして様々なパラパラ漫画、オンライン教材

行動を起こすことで思考パターンや恐怖心を変えることができます。

以上がMySQL バイナリ ログに関する問題の詳細な説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。