バイナリログの概要と設定
MySQL サーバーでは、データベースに対する変更操作を記録する複数のログタイプが存在します。その中でもバイナリログ（Binary Log）は、すべてのデータ変更语句（DDL および DML）を記録するため、誤った操作によるデータ損失が発生した場合に、特定の時点までの状態へ復元を行うために不可欠な要素となります。
バイナリログを有効化するには、設定ファイル（my.cnf または my.ini）において log-bin オプションを定義し、MySQL サービスを再起動する必要があります。設定例を以下に示します。
[mysqld]
log-bin=/var/lib/mysql/binlog
この設定により、サーバー起動時または FLUSH LOGS 命令実行ごとに、新しいログファイルが sequentially に生成されます。例えば、初回起動で binlog.000010、次いで binlog.000011 というようにファイル名が連番で増加していきます。
復元作業の実践シナリオ
ここでは、誤って削除してしまったデータをバイナリログを用いて復元する具体的な手順を解説します。
- まず、現在のログファイルを確定させるため、
FLUSH LOGS;を実行し、新しいバイナリログファイルを作成します。 shop_dbデータベース内のorder_itemsテーブルを確認します。既存のレコードに対し、新たに 2 件のデータ（ID: 101, 102）を挿入します。- 何らかの操作ミスにより、刚刚挿入した 2 件のレコードが削除されてしまったと仮定します。
- この状況において、バイナリログを用いて削除前の状態へ戻すことを検討します。ログディレクトリを確認すると、複数のバイナリファイルが存在し、最も新しいファイル（例：
binlog.000015）に当該の挿入および削除操作が記録されています。 - まず、
mysqlbinlogユーティリティを使用して、バイナリファイルをテキスト形式で出力し、内容を確認します。
mysqlbinlog /var/lib/mysql/binlog.000015 > recovery_log.txt
出力されたテキストファイルを確認すると、トランザクション単位で SQL 语句が記録されていることがわかります。以下はログの一部抜粋です。
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230515 10:00:00 server id 1 end_log_pos 120 Start: binlog v 4, server v 8.0.32
BINLOG '
...
'/*!*/;
# at 120
#230515 10:05:00 server id 1 end_log_pos 250 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1684144500/*!*/;
BEGIN
/*!*/;
# at 250
#230515 10:05:00 server id 1 end_log_pos 400 Query thread_id=10 exec_time=0 error_code=0
use `shop_db`/*!*/;
SET TIMESTAMP=1684144500/*!*/;
INSERT INTO `order_items` (`item_id`, `product_name`, `quantity`) VALUES (101, 'Keyboard', 5)
/*!*/;
# at 400
#230515 10:05:00 server id 1 end_log_pos 550 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1684144500/*!*/;
INSERT INTO `order_items` (`item_id`, `product_name`, `quantity`) VALUES (102, 'Mouse', 10)
/*!*/;
# at 550
#230515 10:05:00 server id 1 end_log_pos 620 Xid = 512
COMMIT/*!*/;
# at 620
#230515 10:30:00 server id 1 end_log_pos 700 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1684146000/*!*/;
BEGIN
/*!*/;
# at 700
#230515 10:30:00 server id 1 end_log_pos 850 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1684146000/*!*/;
DELETE FROM `order_items` WHERE `item_id`=101
/*!*/;
# at 850
#230515 10:30:00 server id 1 end_log_pos 1000 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1684146000/*!*/;
DELETE FROM `order_items` WHERE `item_id`=102
/*!*/;
# at 1000
#230515 10:30:00 server id 1 end_log_pos 1070 Xid = 520
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
ログ内容から、ID 101 および 102 の挿入処理はポジション 620 の COMMIT で完了しており、その後の 620 以降の位置から削除操作が始まっていることが確認できます。
データを復元するため、削除操作が行われる直前のポジションまでを指定してログを適用します。ここでは --stop-position=620 を使用します。
mysqlbinlog --stop-position=620 /var/lib/mysql/binlog.000015 | mysql -u root -p
このコマンドを実行後、order_items テーブルを再確認すると、削除されていた 2 件のレコードが正常に復元されていることが検証できます。