はじめに
MySQLのbinlog機能は、データベースの変更履歴を記録し、任意の時間点までデータを復元（Point-in-Time Recovery: Pitr）する能力を提供します。これは重要なデータ損失防止策であり、リートラブルなデータ管理や業務継続計画（BCP）に不可欠です。
症状・背景
MySQLのbinlogが必要になる主な場面は以下の通りです：
- 誤ったDDL/DML操作：テーブル構造の変更やデータの誤削除等が発生した場合。
- ソフトウェアエラーによるデータ損失：DBサーバー上のソフトウェアエラーによりデータが破損した場合。
- 物理的な障害：ハードディスク故障など、物理的な障害によってデータが破損した場合。
手順・設定方法
ステップ1: binlogを有効にする
# binlogを有効にする
sudo systemctl restart mysql
# /etc/my.cnf に以下を追加または確認
[mysqld]
server-id=1
binlog-format=MIXED
expire_logs_days=7
# リスタートして設定を適用する
sudo systemctl restart mysqlステップ2: バックアップとリプリケーションの準備
# 既存のデータベースバックアップ
mysqldump -u username -p --all-databases > all_databases_backup.sql
# サーバーからローカルディレクトリにbinlogファイルを保存する
mkdir /var/lib/mysql/binlogs
chown mysql:mysql /var/lib/mysql/binlogsステップ3: Point-in-Time リカバリの実行
# MySQLクライアントで接続し、以下のSQLコマンドを実行します。
mysql -u root -p
use mysql;
flush tables with read lock;
# パスとファイル名を指定してbinlogを取得する
binlog_dump --stop-never --start-datetime="2023-10-01 08:00:00" --stop-datetime="2023-10-01 09:00:00" /var/lib/mysql/binlogs
# テーブルのロックを解除する
unlock tables;
# 古いデータベースバックアップを削除し、新しいbinlogファイルを使用して復元します。
rm -rf /path/to/old_backup
mysql -u root -p < all_databases_backup.sqlステップ4: 実践/トラブルシュート/監視
# binlogの状態を確認する
SHOW BINARY LOGS;
# Point-in-Time Recoveryの実行時間を監視する
SHOW PROCESSLIST;注意事項
- セキュリティ上の注意：binlogファイルは高価値なデータを含むため、適切に管理し、アクセスを制限する必要があります。
- パフォーマンス/運用上的注意：binlogの有効化はディスクストレージとI/O負荷を増やす可能性があるため、十分なリソースを持つこと。
まとめ
1. MySQLのbinlog機能: データベースの変更履歴を記録し、任意の時間点までデータを復元する能力。
2. Point-in-Time Recovery (Pitr): MySQLのbinlogを使用して任意の時間点でデータを復元する方法。
3. バックアップとリプリケーション: 事前にバックアップを作成し、必要に応じてbinlogを使用して新しいデータベースに復元を行う。
4. 監視とトラブルシュート: リカバリの実行時間を監視し、問題が発生した場合の対処方法を理解する。
5. セキュリティ: binlogファイルは高価値なデータを含むため、適切に管理し、アクセスを制限する。
関連記事: