MySQL binlog の解析

1. binlogの紹介

binlog はバイナリログ、バイナリログファイルです。データベースによって実行されたすべての DDL および DML ステートメント (select や show などのデータクエリステートメントを除く) を記録し、イベントの形式で記録してバイナリファイルに保存します。

Binlog には、主に 2 つのアプリケーションシナリオがあります。1 つはレプリケーション用です。マスターはバイナリログをスレーブに渡して、マスターとスレーブ間のデータの一貫性を実現します。 2 つ目は、データの回復用です。たとえば、バックアップを復元した後、バックアップ後に新しく生成された binlog を再実行して、データベースを最新の状態に保つことができます。これら 2 つの主な用途に加えて、binlog は異種システム間のデータ相互作用にも使用できます。binlog は、レコードの前後のレコードを完全に保存します。DTS サービスを使用すると、MySQL データをほぼリアルタイムで HBase、Hive、Spark などの基盤となるデータプラットフォームに抽出し、OLTP と OLAP を接続できます。

binlog ログには、 STATEMENT 、 ROW 、 MIXED 3 つのモードがあります。以下は、これら 3 つのモードの簡単な紹介です。

ステートメント: SQL ステートメントのレプリケーションに基づいて、データを変更する各 SQL ステートメントが binlog に記録されます。このモードで生成される binlog ログの量は比較的少なくなりますが、マスターとスレーブのデータ間に不整合が生じる可能性があります。
ROW : 行ベースのレプリケーションでは、実行された特定の SQL ステートメントがすべて記録されるのではなく、変更されたデータと、変更前後のデータの外観のみが記録されます。このモードで生成される binlog ログの量は比較的多くなりますが、データ変更の各行の詳細が明確に記録され、マスタースレーブレプリケーションが失敗しないという利点があります。
混合: 混合モードレプリケーション。上記の 2 つのモードを組み合わせたものです。一般的なレプリケーションでは、ステートメントモードを使用してバイナリログを保存します。ステートメントモードでレプリケートできない操作の場合、ROW モードを使用してバイナリログを保存します。MySQL は、実行された SQL ステートメントに基づいてログ保存方法を選択します。

デフォルトの binlog モードは、MySQL 5.7.7 より前では STATEMENT であり、それ以降のバージョンでは ROW です。 ROW モードの方が安全であり、データ変更の各行の詳細を明確に記録できるため、ここでは ROW モードを使用することをお勧めします。

2. Binlog関連のパラメータ

Binlog はデフォルトでは有効になっていませんが、通常は、初期化時に構成ファイルに log-bin パラメータを追加して、binlog を有効にすることをお勧めします。

# 設定ファイルにlog-bin設定を追加する[mysqld]
ログビン = binlog

# パスを指定しない場合は、データディレクトリがデフォルトになります。パス [mysqld] を指定することもできます。
ログ bin = /data/mysql/logs/binlog

# データベースで binlog が有効になっているかどうかを確認します
'log_bin%' のような変数を表示します。

binlog を有効にした後、binlog 関連のいくつかのパラメータに注意する必要があります。以下は、関連するパラメータの簡単な紹介です。

バイナリログフォーマット
binlog モードを設定します。ROW に設定することをお勧めします。
binlog_do_db
このパラメータは、指定されたデータベースのバイナリログのみが記録されることを意味します。デフォルトでは、すべてのログが記録されます。通常、変更することは推奨されません。
binlog_ignore_db
このパラメータは、指定されたデータベースのバイナリログが記録されないことを示します。上記のように、通常は明示的に指定されません。
有効期限
このパラメータは、バイナリログファイルを保持する日数を制御します。デフォルト値は 0 で、自動削除が行われないことを意味します。0 ～ 99 に設定できます。 15日間保持、30日間保持など、実際の状況に応じて設定できます。 MySQL 8.0 では、代わりに binlog_expire_logs_seconds パラメータを使用できます。
最大バイナリログサイズ
単一のバイナリログのサイズを制御します。現在のログファイルのサイズがこの変数を超えると、スイッチアクションが実行されます。このパラメータの最大値とデフォルト値は 1GB です。この設定では、特に Binlog が最大値に近く、比較的大きなトランザクションに遭遇した場合、Binlog のサイズを厳密に制御することはできません。トランザクションの整合性を確保するために、ログを切り替えることはできません。トランザクションのすべての SQL ステートメントは、トランザクションが終了するまで現在のログにのみ記録されます。通常はデフォルト値を使用できます。
log_bin_trust_function_creators
このパラメータは、バイナリログが有効な場合に有効になります。これは、ストアド関数の作成者が、バイナリログに書き込み、安全でないイベントを引き起こすストアド関数を作成しないことを信頼できるかどうかを制御します。 0 (デフォルト) に設定すると、CREATE ROUTINE または ALTER ROUTINE 権限に加えて SUPER 権限を持っていない限り、ユーザーはストアド関数を作成または変更できません。推奨設定は 1 です。
同期バイナリログ
MySQL サーバーがバイナリログをディスクに同期する頻度を制御します。デフォルト値は 1 です。
0 に設定すると、MySQL は binlog のフラッシュを制御せず、ファイルシステム自体がキャッシュのフラッシュを制御します。
1 に設定すると、MySQL はトランザクションの送信ごとに binlog をフラッシュします。これは最も安全な設定ですが、ディスク書き込み回数が増加するため、パフォーマンスに悪影響を与える可能性があります。
n に設定します。ここで、n は 0 または 1 以外の値です。n 個のトランザクションがコミットされると、MySQL は fsync などのディスク同期命令を実行して、Binlog ファイルキャッシュをディスクに更新します。
推奨設定は 1 ですが、パフォーマンス上の理由から調整できます。

binlog の操作と管理に関連する SQL ステートメントも多数あります。次に、よく使用されるステートメントをいくつか示します。

3. バイナリログの内容を分析する

前述したように、データベースへのすべての変更は binglog に記録されます。ただし、binlog はバイナリファイルであるため、直接表示することはできません。より直感的に観察したい場合は、mysqlbinlog コマンドツールを使用する必要があります。次の内容では、主に mysqlbinlog を使用して binlog ログの内容を解析する方法を紹介します。

ストーリーをスムーズに展開するために、まずはbinlogに切り替え、テストライブラリとテストテーブルを作成し、データを挿入して、データを更新します。これらの事前操作は現時点では表示されません。生成された binlog コンテンツを解析して表示する方法を見てみましょう。

# この分析は MySQL 8.0 に基づいており、インスタンスでは gtid が有効になっており、モードは ROW です。

[root@centos ログ]# mysqlbinlog --no-defaults --base64-output=decode-rows -vv binlog.000013
/*!50530 @@SESSION.PSEUDO_SLAVE_MODE を 1 に設定します*/;
/*!50003 @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0 に設定*/;
...
...
#200708 16:52:09 サーバー ID 1003306 end_log_pos 1049 CRC32 0xbcf3de39 クエリ thread_id=85 exec_time=0 error_code=0 Xid = 1514
`bindb`/*!*/ を使用します。
タイムスタンプを 1594198329/*!*/ に設定します。
@@session.explicit_defaults_for_timestamp=1/*!*/ を設定します。
/*!80013 @@session.sql_require_primary_key を 0 に設定します*//*!*/;
テーブル `bin_tb` を作成します (
  `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自動増分主キー',
  `stu_id` int(11) NOT NULL COMMENT '学生ID',
  `stu_name` varchar(20) デフォルト NULL コメント '学生名',
  `create_time` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成時刻',
  `update_time` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '変更時刻',
  主キー (`increment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='テスト binlog'
//*!*/;
# 1049 で
#200708 16:52:45 サーバー ID 1003306 end_log_pos 1128 CRC32 0xf19ea0a9 GTID last_committed=2 シーケンス番号=3 rbr_only=yes original_committed_timestamp=1594198365741300 immediate_commit_timestamp=1594198365741300 transaction_length=468
/*!50718 トランザクション分離レベルを READ COMMITTED に設定*//*!*/;
# オリジナルコミットタイムスタンプ=1594198365741300 (2020-07-08 16:52:45.741300 CST)
# 即時コミットタイムスタンプ=1594198365741300 (2020-07-08 16:52:45.741300 CST)
/*!80001 @@session.original_commit_timestamp=1594198365741300* を設定します//*!*/;
/*!80014 @@session.original_server_version=80019* を設定します//*!*/;
/*!80014 @@session.immediate_server_version=80019* を設定します//*!*/;
@@SESSION.GTID_NEXT= '0032d819-2d32-11ea-91b5-5254002ae61f:24883' を設定します。
# 1128 で
#200708 16:52:45 サーバー ID 1003306 end_log_pos 1204 CRC32 0x5b4b03db クエリ thread_id=85 exec_time=0 error_code=0
タイムスタンプを 1594198365/*!*/ に設定します。
始める
//*!*/;
# 1204 で
#200708 16:52:45 サーバー ID 1003306 end_log_pos 1268 CRC32 0xd4755d50 Table_map: `bindb`.`bin_tb` が番号 139 にマップされました
# 1268 で
#200708 16:52:45 サーバー ID 1003306 end_log_pos 1486 CRC32 0x274cf734 Write_rows: テーブル ID 139 フラグ: STMT_END_F
### `bindb`.`bin_tb` に挿入します
＃＃＃ セット
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=1001 /* INT meta=0 nullable=0 is_null=0 */
### @3='from1' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### `bindb`.`bin_tb` に挿入します
＃＃＃ セット
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2=1002 /* INT meta=0 nullable=0 is_null=0 */
### @3='dfsfd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
...
# 1486 で
#200708 16:52:45 サーバー ID 1003306 end_log_pos 1517 CRC32 0x0437e777 Xid = 1515
専念 /*！*/;
...
# 1596年
#200708 16:54:35 サーバー ID 1003306 end_log_pos 1681 CRC32 0x111539b6 クエリ thread_id=85 exec_time=0 error_code=0
タイムスタンプを 1594198475/*!*/ に設定します。
始める
//*!*/;
# 1681年
#200708 16:54:35 サーバー ID 1003306 end_log_pos 1745 CRC32 0x6f0664ee Table_map: `bindb`.`bin_tb` が番号 139 にマップされました
# 1745年
#200708 16:54:35 サーバー ID 1003306 end_log_pos 1939 CRC32 0xfafe7ae8 Update_rows: テーブル ID 139 フラグ: STMT_END_F
### `bindb`.`bin_tb` を更新します
＃＃＃どこ
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=1005 /* INT meta=0 nullable=0 is_null=0 */
### @3='dsfsdg' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
＃＃＃ セット
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=1005 /* INT meta=0 nullable=0 is_null=0 */
### @3=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198475 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### `bindb`.`bin_tb` を更新します
＃＃＃どこ
### @1=6 /* INT meta=0 nullable=0 is_null=0 */
### @2=1006 /* INT meta=0 nullable=0 is_null=0 */
### @3='fgd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
＃＃＃ セット
### @1=6 /* INT meta=0 nullable=0 is_null=0 */
### @2=1006 /* INT meta=0 nullable=0 is_null=0 */
### @3=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
### @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=1594198475 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
...
# 1939年
#200708 16:54:35 サーバー ID 1003306 end_log_pos 1970 CRC32 0x632a82b7 Xid = 1516
専念 /*！*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* mysqlbinlog によって追加されました */ /*!*/;
区切り文字 ;
# ログファイルの終了
/*!50003 COMPLETION_TYPE を @OLD_COMPLETION_TYPE に設定*/;
/*!50530 @@SESSION.PSEUDO_SLAVE_MODE を 0 に設定します*/;

# バイナリログには、各 SQL ステートメントの実行によって生じた変更が詳細に記録されていることがわかります。
また、実行時間、pos の場所、server_id などのシステム値も含まれます。

特定のデータベースまたは特定の期間内の操作のみを解析するなど、mysqlbinlog ツールを使用するためのヒントは他にもたくさんあります。以下に一般的なステートメントをいくつか示します。詳細については、公式ドキュメントを参照してください。

mysqlbinlog --no-defaults --base64-output=decode-rows -vv binlog.000013 > /tmp/bin13.sql
解析されたSQLをファイルにインポートする
mysqlbinlog --no-defaults --base64-output=decode-rows -vv --database=testdb binlog.000013
特定のライブラリの操作のみを解析する
mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-datetime="2020-01-11 01:00:00" --stop-datetime="2020-01-11 23:59:00" binlog.000008
指定された期間内の操作を分析する
mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-position=204136360 --stop-position=204136499 binlog.000008
指定された位置内の操作を解析します
mysqlbinlog --no-defaults --start-position=204136360 --stop-position=204136499 binlog.000008 | mysql -uroot -pxxxx testdb
指定されたライブラリ内の指定されたサイト間の復元操作

IV. 結論

いつの間にかかなり長くなってしまった記事です。binlogに関する様々な豆知識を解説しています。読んでいただければbinlogへの理解が深まるかと思います。実際、最も重要なのは練習です。より多くを学び、より多く使用することでのみ、より上手に習得することができます。これは非常に重要な知識なので、必要なときに読んでいただければ幸いです。より多くの人に見てもらえるよう、転送したり共有したりしていただければ幸いです。

上記はMySQL binlogの解析の詳細な内容です。MySQL binlogの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。

以下もご興味があるかもしれません: