MySQLのバイナリログとは
MySQLのバイナリログ（binlog）は、データベースの変更履歴を記録する非常に重要な機能です。このログには、すべてのDDL（データ定義言語）とDML（データ操作言語）ステートメント（SELECTクエリを除く）がイベント形式で記録され、各ステートメントの実行にかかった時間も含まれています。MySQLのバイナリログはトランザクションセーフです。
DDL（Data Definition Language）は、CREATE、ALTER、DROPなどのコマンドを含み、主にテーブルの構造、データ型、テーブル間の連結や制約などを定義または変更するために使用されます。一方、DML（Data Manipulation Language）は、SELECT、UPDATE、INSERT、DELETEなどのコマンドを含み、データベース内のデータを操作するために使用されます。
バイナリログのパフォーマンスへの影響
一般的に、バイナリログを有効にすると約1%のパフォーマンス低下が見込まれます。これは、データベースの変更を記録する追加の処理が必要になるためです。
バイナリログの使用シーン
バイナリログは主に以下の2つのシナリオで使用されます：
- MySQLレプリケーション：マスターサーバーでバイナリログを有効にし、そのバイナリログをスレーブサーバーに伝送することで、マスターとスレーブ間のデータ一貫性を保ちます。
- データ復旧：mysqlbinlogツールを使用して、特定の時点までデータを復元することができます。
バイナリログは2種類のファイルで構成されます：
- バイナリログインデックスファイル（拡張子が.index）：すべてのバイナリファイルを記録します。
- バイナリログファイル（拡張子が.00000*）：データベースのすべてのDDLとDML（SELECTクエリを除く）ステートメントイベントを記録します。
バイナリログの基本操作
すべてのバイナリログリストを表示
SHOW MASTER LOGS;マスターステータスの確認
最新のバイナリログの番号と名前、および最後の操作イベントの終了位置（Position）値を確認します。
SHOW MASTER STATUS;ログのフラッシュ
このコマンドを実行すると、新しい番号のバイナリログファイルが生成されます。
FLUSH LOGS;注意：mysqldサービスが再起動するたびに、このコマンドが自動的に実行され、バイナリログがフラッシュされます。また、mysqldumpでデータをバックアップする際に-Fオプションを付けると、バイナリログがフラッシュされます。
すべてのバイナリログのリセット（消去）
注意：この操作は慎重に行ってください。
RESET MASTER;バイナリログの内容を確認する方法
バイナリログの内容を確認するには、主に2つの方法があります。
方法1：mysqlbinlogコマンドを使用する
注意点：
- バイナリログはバイナリファイルであるため、cat、more、vimなどの通常のファイルビューアでは開けません。必ずmysqlbinlogコマンドを使用する必要があります。
- バイナリログファイルは、データベースファイルと同じディレクトリにあります。
- MySQL 5.5以下のバージョンでmysqlbinlogコマンドを使用する際にエラーが発生する場合は、「--no-defaults」オプションを追加してください。
時間を指定してバイナリログを確認することもできます。
mysqlbinlog --no-defaults mysql-bin.000720 --start-datetime="2018-09-12 18:45:00" --stop-datetime="2018-09-12:18:47:00"方法2：SHOW BINLOG EVENTSコマンドを使用する
上記の方法ではバイナリログの全文が表示され、posポイントの情報を特定するのが難しい場合があります。以下に、より便利なクエリコマンドを紹介します。
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];パラメータの説明：
- IN 'log_name'：クエリするバイナリログファイル名を指定します（指定しない場合は最初のバイナリログファイル）。
- FROM pos：どのposポイントからクエリを開始するかを指定します（指定しない場合はファイルの最初のposポイントから）。
- LIMIT [offset]：オフセット（指定しない場合は0）。
- row_count：クエリする総行数（指定しない場合はすべての行）。
使用例：
-- 最初のバイナリログをクエリ
SHOW BINLOG EVENTS\G;
-- mysql-bin.000002ファイルを指定してクエリ
SHOW BINLOG EVENTS IN 'mysql-bin.000002'\G;
-- mysql-bin.000002ファイルを指定し、posポイント624からクエリを開始
SHOW BINLOG EVENTS IN 'mysql-bin.000002' FROM 624\G;
-- mysql-bin.000002ファイルを指定し、posポイント624から10件（10ステートメント）をクエリ
SHOW BINLOG EVENTS IN 'mysql-bin.000002' FROM 624 LIMIT 10\G;
-- mysql-bin.000002ファイルを指定し、posポイント624から、2行オフセットして10件をクエリ
SHOW BINLOG EVENTS IN 'mysql-bin.000002' FROM 624 LIMIT 2,10\G;バイナリログを使用したMySQLデータの復元
分析の結果、データベースopsのデータ破損の原因となったposポイントの範囲が3064-3153の間であり、データベースops1のデータ破損の原因となったposポイントの範囲が3218-3310の間であることが判明しました。適切なposポイントの前まで復元するだけで問題を解決できます。
復元コマンドの構文
mysqlbinlog mysql-bin.0000xx | mysql -uユーザー名 -pパスワード データベース名例：
mysqlbinlog --start-position=3153 --stop-position=3880 /application/mysql3306/mysql_data/mysql-bin.000002 | /application/mysql3307/bin/mysql -uroot -S /application/mysql3307/logs/mysql.sock -p123456 -vよく使用されるパラメータオプションの説明
- --start-position=875：開始posポイント
- --stop-position=954：終了posポイント
- --start-datetime="2016-9-25 22:01:08"：開始時間ポイント
- --stop-datetime="2019-9-25 22:09:46"：終了時間ポイント
- --database=ops：opsデータベースのみを復元するように指定（1台のホストには複数のデータベースがあることが多く、ローカルログに限定）
あまり使用されないオプション
- -u --user=name：リモートホストに接続するユーザー名
- -p --password[=name]：リモートホストに接続するパスワード
- -h --host=name：リモートホストからバイナリログを取得
- --read-from-remote-server：特定のMySQLサーバーからバイナリログを読み取る
要約：実際には、読み取ったバイナリログの内容をパイプを使用してmysqlコマンドに渡しています。これらのコマンドやファイルは、できるだけ絶対パスで記述してください。
mysqlbinlogコマンドの構文
mysqlbinlog [options] logfile1 logfile2 ...よく使用されるコマンド
バイナリログを解析して問題を特定
単に解析して確認するだけの場合は、--base64-output=decode-rowsを追加して行形式の内容を非表示にできます。
mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000201特定のGTIDのトランザクションを解析
特定のトランザクションが何をしたかを分析するために使用します。
mysqlbinlog --no-defaults -vv --base64-output=decode-rows --include-gtids='b0ca6715-7554-11ea-a684-02000aba3dad:614037' mysql-bin.000199特定の範囲のバイナリログを解析
a. 時間範囲
--start-datetime、--stop-datetimeを使用して、指定した時間範囲内のバイナリログを解析します。これは大まかな解析にのみ適しており、正確ではないため、バイナリログの再生には使用しないでください。便利なテクニック：おおよその時間範囲しかわからず、どのバイナリログにあるか不明な場合、複数のバイナリログを直接解析できます。例えば、11:20-12:00の間にテーブル削除操作が行われたが、この時間内に複数のバイナリログがある場合、次のようにできます。
mysqlbinlog --no-defaults -vv --base64-output=decode-rows --start-datetime='2020-08-18 11:20:00' --stop-datetime='2020-08-18 12:00:00' mysql-bin.000203 mysql-bin.000204 mysql-bin.000205b. オフセット範囲
--start-position、--stop-positionを使用して、バイナリログの指定したオフセット範囲内のバイナリログを解析します。--start-positionと--stop-positionの両方を指定し、複数のバイナリログを解析する場合、--start-positionは最初のバイナリログにのみ適用され、--stop-positionは最後のバイナリログにのみ適用されます。
これは一般的なシナリオです：一度バイナリログを解析し、対象トランザクションの開始positionを取得した後、このセクションのバイナリログを正確に解析します。
mysqlbinlog --no-defaults -vv --base64-output=decode-rows --start-position='537' --stop-position='945' mysql-bin.000204
# at 537 "開始位置はGTIDイベントの前のこのposition"
#200818 11:29:03 server id 3 end_log_pos 602 CRC32 0x7f07dd8c GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614061'/*!*/;
...
...
#200818 11:29:03 server id 3 end_log_pos 945 CRC32 0xedf2b011 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1597721343/*!*/;
COMMIT /*!*/;
# at 945 "終了位置はCOMMITイベントの後のこのposition"c. GTID範囲
--include-gtids、--exclude-gtidsの詳細については、パラメータの説明を参照してください。
バイナリログの再生
- 再生する際は、--base64-output=decode-rowsパラメータを追加しないでください。これにより、行形式（バイナリログの実際の有効部分）が解析されなくなります。
- 再生にも上記の範囲を指定するパラメータを使用できます。
- バイナリログを解析して同じインスタンスに再生する場合、server idを変更する必要はありませんが、GTIDが既に存在するかどうかに注意する必要があります。
- GTIDが既に存在する場合、再生はエラーになりませんが、これらのトランザクションは実際には再生されません。--skip-gtidsパラメータを使用してGTIDの制限をスキップできます。
mysqlbinlog --no-defaults --skip-gtids mysql-bin.000203 | mysql -S /data/mysql/data/3306/mysqld.sock -prootパラメータの説明
--no-defaults
my.cnfで[client]にmysqlbinlogにない特定のパラメータが設定されていることによるmysqlbinlogの失敗を避けることができます。
-v
このオプションを追加しない場合、行形式（文字列の羅列）のみが表示され、疑似SQLを取得できません。
-vを追加すると、行形式から疑似SQL（コメント付き）が再構築されますが、binlog_rows_query_log_eventsパラメータの効果は表示されません。
-vv
-vvを追加すると、行形式から疑似SQLが再構築され、フィールドのデータ型のコメントが追加されます。binlog_rows_query_log_eventsパラメータの効果も表示されます。
--base64-output=decode-rows
行形式を非表示にします。同時に-vパラメータを追加すると、行形式からコメント付きの疑似SQLにデコードされます。
--skip-gtids
GTIDイベント情報を保持しません。これにより、バイナリログを再生する際に新しいトランザクションを実行するのと同じように、新しいGTIDが生成されます。比較は以下の通りです。
--include-gtids
指定したGTIDのトランザクションのみを解析します。
[root@localhost 3306]# mysqlbinlog --no-defaults -vv --base64-output=decode-rows \
> --include-gtids='b0ca6715-7554-11ea-a684-02000aba3dad:614037-614040' mysql-bin.000199 |grep GTID
#200807 17:32:17 server id 2 end_log_pos 194 CRC32 0xc840be04 Previous-GTIDs
#200807 17:32:17 server id 2 end_log_pos 3818435 CRC32 0x9fdea913 GTID last_committed=3 sequence_number=5 rbr_only=yes
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614037'/*!*/;
#200807 17:32:17 server id 2 end_log_pos 5726909 CRC32 0x51b51cc1 GTID last_committed=4 sequence_number=6 rbr_only=yes
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614038'/*!*/;
#200807 17:32:17 server id 2 end_log_pos 5727523 CRC32 0x758852f1 GTID last_committed=6 sequence_number=7 rbr_only=yes
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614039'/*!*/;
#200807 17:32:17 server id 2 end_log_pos 7635997 CRC32 0x47c43f83 GTID last_committed=6 sequence_number=8 rbr_only=yes
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614040'/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;--exclude-gtids
指定したGTIDのトランザクションを解析しません。
mysqlbinlogのオプション
-d, --database=name 指定されたデータベースのダンプ内容のみを表示します。
-o, --offset=# 最初のN行のログエントリをスキップします。
-r, --result-file=name 入力されたテキスト形式のファイルを指定されたファイルにダンプします。
-s, --short-form 簡易形式を使用します。
--set-charset=name ダンプファイルの先頭に'SET NAMES character_set'ステートメントを追加します。
--start-datetime=name ダンプするログの開始時間。
--stop-datetime=name ダンプするログの終了時間。
-j, --start-position=# ダンプするログの開始位置。
--stop-position=# ダンプするログの終了位置。
--rewrite-db=name --rewrite-db='mydb1->mydb2' I mysql、mydb1のバイナリログをmydb2に適用します。実用例
N個のエントリをスキップする
mysqlbinlog -o 10000 mysqld-bin.000001特定の時間のエントリを表示する
時間に基づいて切り取る --start-datetime --stop-datetime
例：
mysqlbinlog --stop-datetime="2017-08-16 15:00:00" mysqld-bin.000001