MySQL データベースの Binlog 使用法の概要 (必読)

MySQL データベースにとって binlog バイナリ ログがどれほど重要であるかについては詳しく説明しません。私の日々の運用経験とオンライン参考資料に基づいて、binlog ログの使用を整理します。

1. binlogの紹介

1) binlog とは何ですか?
binlog ログは、データを更新するか、または更新される可能性のあるデータを持つすべてのステートメント (たとえば、どの行にも一致しない DELETE) を記録するために使用されます。ステートメントは、データの変更を記述する「イベント」の形式で保存されます。

2) バイナリログ機能
データ更新のバイナリログがあるため、リアルタイムバックアップに使用でき、マスター/スレーブレプリケーションと組み合わせることができます。

3) binlogに関連するパラメータ
ログビン

このパラメータを設定すると、binlog機能が有効になり、パス名が指定されます。

ログビンインデックス

このパラメータを設定すると、バイナリインデックスファイルのパスと名前が指定されます。

binlog_do_db

このパラメータは、指定されたデータベースのバイナリログのみが記録されることを意味します。

binlog_ignore_db
このパラメータは、指定されたデータベースのバイナリ ログが記録されないことを示します。

最大バイナリログキャッシュサイズ

このパラメータは、binlogが使用するメモリの最大サイズを示します。

binlog_cache_size

このパラメータは、binlog によって使用されるメモリ サイズを示します。これは、ステータス変数 binlog_cache_use および binlog_cache_disk_use を使用してテストできます。

binlog_cache_use:バイナリログキャッシュを使用しているトランザクションの数

binlog_cache_disk_use:バイナリログキャッシュを使用したが、binlog_cache_size 値を超え、トランザクション内のステートメントを保存するために一時ファイルを使用したトランザクションの数

最大バイナリログサイズ

Binlog の最大値とデフォルト値は 1GB です。この設定では、Binlog のサイズを厳密に制御することはできません。特に、Binlog が最大値に近く、比較的大きなトランザクションに遭遇した場合はそうです。トランザクションの整合性を確保するために、ログを切り替えることはできません。トランザクションのすべての SQL ステートメントは、トランザクションが終了するまで現在のログにのみ記録されます。

同期バイナリログ

このパラメータはMySQLのパフォーマンスと整合性に直接影響します。

同期バイナリログ=0

トランザクションがコミットされると、Mysql は binlog_cache のデータを Binlog ファイルに書き込むだけで、fsync などのディスク同期命令を実行してファイル システムにキャッシュをディスクに更新するように通知しません。代わりに、ファイル システムが同期するタイミングを決定できるようにします。これが最高のパフォーマンスです。

sync_binlog=n の場合、n 個のトランザクションがコミットされた後、Mysql は fsync などのディスク同期命令を実行し、comrade ファイル システムは Binlog ファイル キャッシュをディスクに更新します。

Mysql のデフォルト設定は sync_binlog=0 です。これは、必須のディスク更新指示が行われないことを意味します。これにより、最高のパフォーマンスが得られますが、リスクも最大になります。システムがクラッシュすると、ファイル システム キャッシュ内のすべての Binlog 情報が失われます。

4) バイナリログの削除

Binlog は手動または自動で削除できます。

a) binlogを自動的に削除する

binlogパラメータ（expire_logs_days）を使用して、mysqlのbinlogの自動削除を実現します。

mysql> バイナリログを表示します。
mysql> show variables like 'expire_logs_days'; //このパラメータは、binlog ログが自動的に削除/期限切れになるまでの日数を示します。デフォルト値は 0 で、自動的に削除されないことを示します。
mysql> set global expire_logs_days=3; // は、ログが 3 日間保持され、3 日後に自動的に期限切れになることを意味します。

b) binlogを手動で削除する

mysql> reset master; //マスターのbinlogを削除します。つまり、すべてのbinlogログを手動で削除します。
mysql> reset slave; //スレーブのリレーログを削除する
mysql> purge master logs before '2012-03-30 17:20:00'; // 指定された日付より前のログインデックス内の binlog ログファイルを削除します
mysql> purge master logs to 'binlog.000002'; //指定されたログファイルのログインデックス内のbinlogログファイルを削除します

mysql> set sql_log_bin= 1/0 ; // ユーザーにスーパー権限がある場合は、現在のセッションの binlog レコードを有効または無効にできます。
mysql> show master logs; //マスターのbinlogログリストを表示する
mysql> show binary logs; //マスターのbinlogログファイルのサイズを表示します
mysql> show master status; //マスターバイナリログファイルのステータス情報を提供するために使用されます
mysql> show slave hosts; //現在登録されているスレーブのリストを表示します。 --report-host=slave_name オプションで始まらないスレーブはこのリストに表示されません。

mysql> flush logs; //新しいbinlogログファイルを生成する

MySQL binlog ログの自動クリーンアップと手動削除のケースの説明:

MySQL データベースのマスター/スレーブを有効にすると、mysql-bin.00000* ログなどの大量のファイルが生成され、ハードディスクの容量が大量に消費されます。 
mysql-bin.000001 
mysql-bin.000002 
mysql-bin.000003 
mysql-bin.000004 
mysql-bin.000005 
… 
 
これらの binlog ログを削除するには、次の 3 つの解決策があります。 
1. MySQL マスターとスレーブを閉じ、binlog を閉じます。 
操作例は次のとおりです。 
[root@huqniupc ~]# vim /etc/my.cnf //log-binとbinlog_formatをコメントアウト 
# レプリケーションマスターサーバー（デフォルト） 
# レプリケーションにはバイナリログが必要です 
# ログ bin = mysql bin 
# バイナリログ形式 - 混合推奨 
# binlog_format=混合 
次に、データベースを再起動します。2. MySQL マスター/スレーブを有効にし、expire_logs_days を設定します。 
操作例は次のとおりです。 
[root@huqniupc ~]# vim /etc/my.cnf //expire_logs_daysを変更します。xは自動削除の日数で、通常は10などの短い値に設定されます。 
expire_logs_days = x //バイナリログが自動的に削除されるまでの日数。デフォルト値は0で、「自動削除なし」を意味します。 
この方法ではMySQLを再起動する必要があります 
 
もちろん、MySQLを再起動せずにMySQLマスタースレーブを起動し、MySQLでexpire_logs_daysを直接設定することもできます。 
> バイナリログを表示します。 
> '%log%' のような変数を表示します。 
> グローバルのexpire_logs_daysを10に設定します。 
 
 
3. binlog ファイルを手動でクリアします (例: Mysql> PURGE MASTER LOGS TO 'MySQL-bin.010';) 
操作例は次のとおりです。 
[root@huqniupc ~]# /usr/local/mysql/bin/mysql -u root -p 
> PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY); // 10 日前の MySQL binlog ログを削除します。付録 2 には、PURGE MASTER LOGS を手動で削除するための手順と例が記載されています。 > show master logs; 
  
マスターをリセットしてすべての binlog ファイルを削除することもできます。 
# /usr/local/mysql/bin/mysql -u ルート -p 
> reset master; //付録3: binlogをクリアした場合のmysqlへの影響の説明---------------------------------------------------------------- 
PURGE MASTER LOGS 手動削除の使用法と例、MASTER と BINARY は同義語です> PURGE {MASTER | BINARY} LOGS TO 'log_name'
> 'date' より前の {MASTER | BINARY} ログを消去
指定されたログまたは日付より前のログ インデックス内のすべてのバイナリ ログを削除します。これらのログは、ログ インデックス ファイルに記録された MySQL BIN-LOG ログのリストからも削除されるため、指定されたログが最初のログになります。 
 
例: 
> PURGE MASTER LOGS TO 'MySQL-bin.010'; //MySQL-bin.010 ログを消去> PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00'; //2008-06-22 13:00:00 より前の binlog ログを消去> PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY); //3 日前より前の binlog ログを消去 BEFORE の場合、変数の日付引数は 'YYYY-MM-DD hh:mm:ss' の形式にすることができます。 
-----------------------------------------------------

5) binlogをクリアする際のMySQLへの影響

削除しようとしているログの 1 つを現在読み取っているアクティブなスレーブ サーバーが存在する場合、このステートメントは効果がなく、エラーで失敗します。ただし、スレーブ サーバーがシャットダウンされ (またはマスターとスレーブの関係がダウンし)、読み取りたいログの 1 つをクリーンアップすると、スレーブ サーバーは起動後にレプリケートできなくなります。このステートメントは、スレーブ サーバーがレプリケートしている間、スレーブ サーバーを停止することなく安全に実行できます。

6) ビンログを見る

mysqlbinlogコマンドでbinlogの内容を表示できます。

[root@localhost ~]# mysqlbinlog /home/mysql/binlog/binlog.000003 | 続き
/*!40019 @@session.max_insert_delayed_threads を 0 に設定します*/;
/*!50003 @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0 に設定*/;
区切り文字 /*!*/;
# 4時
#120330 16:51:46 サーバー ID 1 end_log_pos 98 開始: binlog v 4、サーバー v 5.0.45-log 作成 120330 1
6:51:46
# 警告: この binlog は適切に閉じられませんでした。おそらく、書き込み中に mysqld がクラッシュしたと考えられます。
# 196 で
#120330 17:54:15 サーバー ID 1 end_log_pos 294 クエリ thread_id=3 exec_time=2 error_code=0
タイムスタンプを 1333101255/*!*/ に設定します。
tt7 に挿入します。tt7/*!*/ から * を選択します。
# 294 位
#120330 17:54:46 サーバー ID 1 end_log_pos 388 クエリ thread_id=3 exec_time=28 error_code=0
タイムスタンプを 1333101286/*!*/ に設定します。
テーブル tt7 を変更します。engine=innodb/*!*/;

binlog 形式の解析:

位置

ファイル内の位置。「at 196」は「イベント」の開始点を示し、196 バイト目から始まります。「end_log_pos 294」は 294 バイト目で終了することを示します。

タイムスタンプ

イベントのタイムスタンプ: 「120330 17:54:46」

イベント実行時間

イベントの実行にかかる時間: "exec_time=28"

エラーコード

エラーコードは次のとおりです: "error_code=0"

サーバーID

サーバー ID: "サーバー ID 1"

次の点に注意してください。

1. MySQL ログをいつでも元の状態に復元できると考えるのは不可能です。この復元には前提条件があります。
ログ記録の開始時から少なくとも 1 つのデータベース バックアップが必要です。ログを介してデータベースを復元することは、実際には以前の操作を再生するプロセスにすぎません。あまり深く考えないでください。

再生操作なので注意が必要です。リカバリを2回実行すると、2回再生したことになり、その結果は想像がつきます。

それで：

1) 復元する前に必ずデータをバックアップしてください。

2) バイナリファイルが多く、復元するデータ範囲が広いため、リカバリ中にログファイルをマージすることを検討できます。

2. バイナリログを有効にする

ログを通じてデータベースを復元するには、まず my.cnf ファイルで log-bin=mysql-bin を定義する必要があります。生成された binlog ログ名は、mysql-bin にちなんで命名されます。

3. 新しい binlog ファイルはいつ生成されますか?

1) バックアップ中に --flush-logs を追加する

2) mysqlサービスを再起動するとき

特別な注意: mysql が起動されるたびに、mysql-bin.00000n に似たファイルが再生成されます。mysql を 1 日に 1 回再起動する必要がある場合は、間違ったログ ファイルを選択しないように注意する必要があります。

2. binlogログ形式の概要

(1) MySQL binlogには、Statement、MiXED、ROWの3つの形式があります。

1) ステートメント: データを変更するすべてのSQLステートメントはbinlogに記録されます。

利点: 各行の変更を記録する必要がないため、binlog ログの量が削減され、IO が節約され、パフォーマンスが向上します。 (ROW 形式と比較して、パフォーマンスとログ ボリュームをどれだけ節約できるかは、アプリケーションの SQL 状況によって異なります。通常、ROW 形式で同じレコードを変更または挿入することによって生成されるログの量は、ステートメントによって生成されるログの量よりも少なくなります。ただし、条件付き更新操作、テーブル全体の削除、ALTER TABLE などの操作がある場合、ROW 形式は大量のログを生成します。したがって、ROW 形式のログを使用するかどうかを検討するときは、アプリケーションの実際の状況、生成されるログの量がどれだけ増加するか、およびそれがもたらす IO パフォーマンスの問題を考慮する必要があります。)

デメリット: 実行されたステートメントのみが記録されるため、これらのステートメントをスレーブ上で正しく実行するには、実行中の各ステートメントの関連情報も記録して、すべてのステートメントがマスター上で実行されたときと同じ結果をスレーブ上で得られるようにする必要があります。さらに、MySQL レプリケーションでは、特定の関数などにより、スレーブがマスターと一致することがあり、これにより多くの関連する問題が発生します (sleep() 関数、last_insert_id()、ユーザー定義関数 (udf) など)。

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

* LOAD_FILE()
* UUID()
* ユーザー()
* 見つかった行()
* SYSDATE() (起動時に --sysdate-is-now オプションが有効になっていない限り)

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

2) 行: SQL文のコンテキスト関連情報は記録せず、どのレコードが変更されたかのみを保存します。

利点: Binlog は、実行された SQL ステートメントのコンテキスト関連の情報を記録する必要はなく、レコードが変更された内容のみを記録する必要があります。したがって、行レベルのログ コンテンツには、データ変更の各行の詳細が明確に記録されます。また、ストアドプロシージャ、関数、またはトリガー呼び出しとトリガーが特定の状況で正しくコピーされないという問題も発生しません。

デメリット:実行されたすべてのステートメントがログに記録されると、各レコード行の変更として記録されるため、大量のログ コンテンツが生成される場合があります。たとえば、update ステートメントが複数のレコードを変更する場合、各変更が binlog に記録されるため、大量の binlog ログが発生します。特に、alter table などのステートメントを実行すると、テーブル構造の変更により各レコードが変更されるため、テーブルの各レコードがログに記録されます。

3) Mixedlevel: 上記の 2 つのレベルの組み合わせです。一般的なステートメントの変更では、ステートメント形式を使用して binlog を保存します。たとえば、一部の関数とステートメントはマスタースレーブレプリケーション操作を完了できないため、行形式を使用して binlog を保存します。MySQL は、実行される特定の SQL ステートメントごとに記録するログ形式を区別します。つまり、ステートメントと行のどちらかを選択します。新しいバージョンの MySQL の行レベルモードも最適化されています。すべての変更が行レベルで記録されるわけではありません。たとえば、テーブル構造が変更されると、ステートメントモードで記録されます。更新や削除など、データを変更するステートメントについては、すべての行に対する変更が記録されます。

混合ログの説明:

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

（２）binlogの基本構成とフォーマット設定

1) 基本的な準備

binlog ログ形式は、mysql my.cnf ファイルの属性 binlog_format で指定できます。たとえば、次のようになります。
binlog_format = MIXED //binlog ログ形式
log_bin = directory/mysql-bin.log //binlog ログ名
expire_logs_days = 7 // binlog の有効期限のクリーンアップ時間
max_binlog_size 100m //各binlogログファイルのサイズ

binlog-do-db=バックアップするデータベースの名前。複数のデータベースをバックアップする場合は、このオプションを繰り返し設定します。
binlog-ignore-db=バックアップする必要のないデータベースは削除されます。複数のデータベースをバックアップする場合は、このオプションを繰り返し設定してください。

2) Binlogログ形式の選択

Mysql はデフォルトでステートメント ログ形式を使用しますが、MIXED が推奨されます。
いくつかの特殊な用途では、binlog ログを介してデータの変更を同期するなど、ROWED の使用を検討できます。これにより、関連する操作が大幅に節約されます。バイナリログデータの処理が非常に簡単になり、混合データに比べて解析も非常に簡単になります（もちろん、ログ量の増加によるIOオーバーヘッドが許容範囲内であることが前提です）。

3) mysqlbinlog 形式の選択

MySQL のログ形式の選択原則: INSERT、UPDATE、DELETE などの直接テーブル操作を使用する場合、ログ形式は binlog_format の設定に従って記録されます。GRANT、REVOKE、SET PASSWORD などの管理ステートメントを使用する場合は、とにかく SBR モードを使用して記録されます。

（３）MySQL Binlogログ分析

次のように、MysqlBinlog コマンドを使用して特定の mysql ログを表示します。

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

タイムスタンプを 1350355892/*!*/ に設定します。

始める

//*!*/;

# 1643330 で

#121016 10:51:32 サーバー ID 1 end_log_pos 1643885 クエリ thread_id=272571 exec_time=0 error_code=0

タイムスタンプを 1350355892/*!*/ に設定します。

T_test に挿入します。

//*!*/;

# 1643885 で

#121016 10:51:32 サーバー ID 1 end_log_pos 1643912 Xid = 0

専念 /*！*/;

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

1. 物事を始める時間:

タイムスタンプを 1350355892/*!*/ に設定します。

始める

2.sqlイベントの開始点

#at 1643330: イベントの開始点。1643330 バイトから始まります。

3. sqleventが発生した時刻

#121016 10:51:32: はイベントが発生した時刻です。

4.サーバーID

サーバーID 1: マスターのサーバーID

5.sqleventエンドポイントと経過時間、エラーコード

end_log_pos 1643885: イベントの終了ポイントで、1643885 バイトで終わります。

execTime 0: 経過時間

error_code=0: エラーコード

Xid: イベントはコミットされたXAトランザクションを示します

3. MySQLログの最適化の説明（binlogログに重点を置く）

MySQL システムは拡張性が高く、十分なハードウェア リソースを備えた環境で効率的に実行できるだけでなく、リソースが非常に少ない環境でも適切に実行できます。
しかし、どのような場合でも、十分なハードウェア リソースは MySQL のパフォーマンスを向上させるのに常に役立ちます。

以下では、MySQL ログ (主に Binlog) がシステム パフォーマンスに与える影響を分析することに焦点を当て、ログの関連する特性に基づいて対応する最適化のアイデアを導き出します。

1) ログのパフォーマンスへの影響
ログ記録によって直接的に生じるパフォーマンスの低下は、データベース システムで最もコストのかかる IO リソースです。

MySQL ログには、主にエラー ログ (ErrorLog)、更新ログ (UpdateLog) 、バイナリ ログ (Binlog)、クエリ ログ (QueryLog)、スロー クエリ ログ (SlowQueryLog) などが含まれます。
特記事項: 更新ログは MySQL の古いバージョンでのみ使用可能であり、バイナリ ログに置き換えられています。

デフォルトでは、システムはエラー ログのみを開き、他のすべてのログを閉じて、IO 損失を最小限に抑え、システム パフォーマンスを向上させます。
ただし、一般的に、より重要な実際のアプリケーション シナリオでは、少なくともバイナリ ログを開く必要があります。これは、多くの MySQL ストレージ エンジンの増分バックアップの基礎であり、MySQL がレプリケーションを実現するための基本条件であるためです。
場合によっては、MySQL のパフォーマンスをさらに最適化し、実行が遅い SQL ステートメントを見つけるために、多くのシステムでは、実行時間が特定の値 (自分で設定) を超える SQL ステートメントを記録するために、スロー クエリ ログも開きます。

一般的に、クエリ ログが実稼働システムで有効になることはほとんどありません。クエリ ログをオンにすると、MySQL で実行されたすべてのクエリがログに記録されるため、システムに比較的大きな IO 負荷がかかりますが、実際にもたらされるメリットはそれほど大きくありません。通常、ログは、特定の機能にどの SQL ステートメントが使用されているかを特定するために、開発およびテスト環境でのみ分析のために短期間開かれます。
そのため、MySQL システムにおいて、パフォーマンスに影響を与える MySQL ログ (各ストレージ エンジン自体のログを除く) は主に Binlog になります。

2) Binlog関連のパラメータと最適化戦略

まず、Binlog の関連パラメータを見てみましょう。以下のコマンドを実行すると、Binlog の関連パラメータを取得できます。
もちろん、Innodb ストレージ エンジンに固有で Binlog に関連する「innodb_locks_unsafe_for_binlog」パラメータも表示されます。

mysql> '%binlog%' のような変数を表示します。 
+-----------------------------------------+----------------------+ 
| 変数名 | 値 | 
+-----------------------------------------+----------------------+ 
| binlog_cache_size | 16777216 | 
| binlog_checksum | CRC32 | 
| binlog_direct_non_transactional_updates | オフ | 
| binlog_error_action | IGNORE_ERROR | 
| binlog_format | 混合 | 
| binlog_gtid_simple_recovery | オフ | 
| binlog_max_flush_queue_time | 0 | 
| binlog_order_commits | オン | 
| binlog_row_image | フル | 
| binlog_rows_query_log_events | オフ | 
| binlog_stmt_cache_size | 32768 | 
| binlogging_impossible_mode | IGNORE_ERROR | 
| innodb_api_enable_binlog | オフ | 
| innodb_locks_unsafe_for_binlog | オフ | 
| 最大バイナリログキャッシュサイズ | 18446744073709547520 | 
| 最大バイナリログサイズ | 1073741824 | 
| 最大 binlog_stmt キャッシュ サイズ | 18446744073709547520 | 
| 簡素化されたbinlog_gtid_recovery | オフ | 
| 同期バイナリログ | 1 | 
+-----------------------------------------+----------------------+ 
セット内の行数は 19 です (0.00 秒)

"binlog_cache_size" : トランザクション中にバイナリ ログ SQL ステートメントを保持するキャッシュのサイズ。バイナリ ログ キャッシュは、サーバーがトランザクション ストレージ エンジンをサポートし、サーバーがバイナリ ログを有効にしている場合 (--log-bin オプション)、各クライアントに割り当てられるメモリです。各クライアントは、設定されたサイズの binlogcache スペースを割り当てることができることに注意してください。システムで複数ステートメントのトランザクションが頻繁に発生する場合は、パフォーマンスを向上させるために値を増やすことができます。もちろん、MySQL の次の 2 つのステータス変数、Binlog_cache_use と Binlog_cache_disk_use を通じて、現在の binlog_cache_size ステータスを判別できます。

「max_binlog_cache_size」 : 「binlog_cache_size」に対応しますが、binlog が使用できる最大キャッシュ メモリ サイズを表します。マルチステートメント トランザクションを実行するときに、max_binlog_cache_size が十分な大きさでない場合、システムから「マルチステートメント トランザクションには 'max_binlog_cache_size' バイトを超えるストレージが必要です」というエラーが報告されることがあります。

「max_binlog_size」 : Binlog ログの最大値。通常は 512M または 1G に設定されますが、1G を超えることはできません。このサイズでは、特に Binlog が終了に近づき、大きなトランザクションに遭遇した場合、Binlog のサイズを厳密に制御することはできません。トランザクションの整合性を確保するために、システムはログを切り替えることができず、トランザクションが終了するまで、トランザクションのすべての SQL ステートメントを現在のログに記録することしかできません。これは Oracle の Redo ログとは少し異なります。Oracle の Redo ログは、データ ファイルの物理的な場所の変更を記録し、Redo および Undo に関連する情報も記録します。したがって、同じトランザクションが同じログにあるかどうかは、Oracle にとって重要ではありません。 MySQL が Binlog に記録するのは、MySQL がイベントと呼ぶデータベース ロジックの変更情報です。これは、実際にはデータベースの変更をもたらす DML などのクエリ ステートメントです。

「sync_binlog」 : このパラメータは、MySQL システムにとって非常に重要です。これは、MySQL への Binlog のパフォーマンス低下に影響するだけでなく、MySQL 内のデータの整合性にも影響します。以下は、「sync_binlog」パラメータのさまざまな設定の説明です。

sync_binlog=0の場合、トランザクションがコミットされた後、MySQL は fsync などのディスク同期命令を実行して binlog_cache 内の情報をディスクに更新しませんが、ファイルシステムに同期のタイミングを決定させるか、キャッシュがいっぱいの場合にのみディスクに同期させます。

sync_binlog=n の場合、n 個のトランザクションがコミットされるたびに、MySQL は fsync などのディスク同期命令を実行して、binlog_cache 内のデータを強制的にディスクに書き込みます。

MySQL では、システムのデフォルト設定は sync_binlog=0 であり、これは必須のディスク更新命令が実行されないことを意味します。この時点でパフォーマンスは最高ですが、リスクも最大になります。システムがクラッシュすると、binlog_cache 内のすべての binlog 情報が失われるためです。 「1」に設定すると、最も安全な設定になりますが、パフォーマンスの低下は最大になります。 1 に設定すると、システムがクラッシュしても、binlog_cache 内の未完了のトランザクションが最大で 1 つ失われ、実際のデータに大きな影響はありません。過去の経験と関連テストに基づくと、同時トランザクションが多いシステムでは、「sync_binlog」を 0 に設定した場合と 1 に設定した場合の書き込みパフォーマンスの差は 5 倍以上になる可能性があります。

他の：

MySQL レプリケーションは、実際には、IO スレッドを使用してネットワーク経由でマスター側の Binlog をスレーブ側にコピーし、SQL スレッドを介して Binlog 内のログを解析してデータベースに適用することによって実現されます。したがって、Binlog のサイズは、IO スレッドとマスターとスレーブ間のネットワークに直接影響を及ぼします。

MySQL で生成される Binlog の量は変更できません。クエリがデータベース内のデータを変更する限り、クエリに対応するイベントが Binlog に記録される必要があります。では、レプリケーションを最適化する方法はないのでしょうか?もちろん違います。MySQL レプリケーション環境には、レプリケートまたは無視する必要がある DB またはテーブルを制御できるパラメーターが実際に 8 つあります。

Binlog_Do_DB : Binlog を記録する必要があるデータベース (スキーマ) を設定します。

Binlog_Ignore_DB : Binlog を記録しないデータベース (スキーマ) を設定します。

Replicate_Do_DB : 複製するデータベース (スキーマ) を設定します。複数の DB はカンマ (",") で区切られます。

Replicate_Ignore_DB : 無視できるデータベース (スキーマ) を設定します。

Replicate_Do_Table : 複製するテーブルを設定します。

Replicate_Ignore_Table : 無視できるテーブルを設定します。

Replicate_Wild_Do_Table : Replicate_Do_Table と同じ機能ですが、ワイルドカードを使用して設定できます。

Replicate_Wild_Ignore_Table : Replicate_Ignore_Table と同じ機能ですが、ワイルドカードを使用して設定できます。

上記の 8 つのパラメータにより、実際のニーズに応じて、マスターからスレーブへの Binlog の量を簡単に最小限に制御できます。これにより、マスターからスレーブへのネットワーク トラフィックが削減され、IO スレッドの IO 量が削減され、SQL を解析して適用する SQL スレッドの数も削減され、最終的にスレーブ上のデータ遅延の問題が改善されます。

実際、上記の 8 つのパラメータのうち最初の 2 つはマスター側で設定され、最後の 6 つのパラメータはスレーブ側で設定されます。最初の 2 つのパラメータと最後の 6 つのパラメータは機能的には直接関連していませんが、MySQL レプリケーションを最適化するための同様の機能を有効にすることができます。もちろん、いくつかの違いはありますが、主な違いは次のとおりです。

最初の 2 つのパラメータをマスター側で設定すると、マスター側の Binlog レコードによる IO 量が削減されるだけでなく、マスター側の IO スレッドも Binlog の読み取り量を削減できるため、スレーブ側の IO スレッドに渡される Binlog の量も自然に少なくなります。これを実行する利点は、ネットワーク IO を削減し、スレーブ IO スレッドの IO ボリュームを削減し、スレーブ SQL スレッドのワークロードを削減することで、レプリケーション パフォーマンスを最大限に最適化できることです。もちろん、MySQL はイベントを生成したクエリによって変更されたデータに基づいてイベントを複製するかどうかを決定するため、マスター側で設定することにはいくつかの欠点があります。

クエリが実行されるデータベースは、デフォルトのスキーマに基づくのではなく、クエリが実行されるデフォルトのスキーマ、つまり、ログイン時に指定された DB または「USE DATABASE」の実行時に指定された DB に基づきます。現在のデフォルト DB と構成で設定された DB が完全に一致している場合にのみ、IO スレッドはスレーブの IO スレッドにイベントを読み取ります。そのため、システム内でデフォルトの DB とレプリケーション対象の DB が異なり、レプリケーション対象の DB 内の特定のテーブルのデータが変更された場合、イベントはスレーブにレプリケーションされず、スレーブ側のデータとマスター上のデータに不整合が発生します。同様に、レプリケーションの必要がないスキーマのデータがデフォルト スキーマで変更された場合、そのデータはスレーブ側にレプリケーションされます。スレーブ側にこのスキーマがない場合、レプリケーションは失敗して停止します。

スレーブ側で次の 6 つのパラメータが設定されている場合、レプリケーションの必要があるかどうかに関係なくすべてのイベントが IO スレッドによってスレーブ側に読み取られるため、ネットワーク IO 量が増加するだけでなく、スレーブ側の IO スレッドの RelayLog 書き込み量も増加するため、パフォーマンスの最適化はマスター側よりもわずかに劣る可能性があります。ただし、スレーブ側のスレーブ SQL スレッドによるログ適用量は依然として削減できます。パフォーマンスは若干劣りますが、スレーブ側にレプリケーション フィルタリング メカニズムを設定すると、スレーブとマスターのデータに不整合が生じたり、デフォルトのスキーマの問題によってレプリケーション エラーが発生したりすることがなくなります。

3) スロークエリログ クエリログ関連のパラメータと使用方法の提案
SlowQueryLog の関連パラメータ設定を見てみましょう。場合によっては、システム内の効率の悪いクエリ ステートメントを見つけるために、遅いクエリ ログ、つまり SlowQueryLog を開く必要があります。システムのスロークエリログの関連設定は次のように表示できます。

mysql> 'log_slow%' のような変数を表示します。 
+------------------+-------+ 
| 変数名 | 値 | 
+------------------+-------+ 
| log_slow_queries | オン | 
+------------------+-------+ 
セット内の 1 行 (0.00 秒) 
 
mysql> 'long_query%' のような変数を表示します。 
+-----------------+-------+ 
| 変数名 | 値 | 
+-----------------+-------+ 
| 長いクエリ時間 | 1 | 
+-----------------+-------+ 
セット内の1行（0.01秒） 

「log_slow_queries」パラメータは、システムが SlowQueryLog 機能をオンにしているかどうかを示し、「long_query_time」パラメータは、クエリの実行時間が現在のシステム設定の SlowQuery レコードをどれだけ超過しているかを示します。 MySQL AB がリリースした MySQL バージョンでは、SlowQueryLog が設定できる最短のスロークエリ時間は 1 秒であり、場合によっては要件を完全に満たさない可能性があります。スロークエリの時間制限をさらに短縮したい場合は、Percona が提供する microslow-patch (mslPatch と呼ばれます) を使用してこの制限を打破できます。 mslpatch は、スロークエリ時間を数ミリ秒に短縮できるだけでなく、特定のテーブルやその他の追加機能を含む SlowQuery のみを記録するなど、記録された SQL を特定のルールに従ってフィルタリングすることもできます。

SlowQueryLog 機能をオンにした場合のシステム パフォーマンスへの全体的な影響は、Binlog ほど大きくはありません。結局のところ、SlowQueryLog のデータ量は比較的少ないため、それがもたらす IO 損失も比較的小さくなります。ただし、システムは各クエリの実行時間を計算する必要があるため、常にある程度の消費、主に CPU 消費が発生します。システムに十分な CPU リソースがある場合は、この小さな損失を心配する必要はありません。 結局のところ、これによりパフォーマンスの最適化が向上する可能性があります。ただし、CPU リソースが比較的不足している場合は、ほとんどの場合この機能を完全にオフにすることができ、遅いクエリを見つけるために SlowQueryLog 関数を断続的にオンにするだけで済みます。

その他の MySQL ログはほとんど使用されない (QueryLog) か、パフォーマンスにほとんど影響がないため、ここでは詳細に分析しません。

MySQL データベースでの Binlog ログの使用に関する上記の要約 (必読) は、編集者が皆さんと共有する内容のすべてです。参考になれば幸いです。また、123WORDPRESS.COM をサポートしていただければ幸いです。