binlogを用いた差分バックアップ

事前準備

binlogを出力する設定

# vi /etc/my.cnf

+) log-bin = mysql-bin
+) expire_logs_days = 2
+) sync_binlog = 1

log-bin = mysql-bin
MySQLのルートディレクトリにmysql-binというファイルを出力する。

expire_logs_days = 2
バイナリログを自動削除する日数。
例は2日間のバイナリログを残す。

sync_binlog = 1
1回バイナリログへ更新を行うことでディスクへのフラッシュを行うことを表すオプションであるため、ディスクへの負荷が高くなる。高速なディスク、特にバッテリー付きのRAID装置などを利用していないと性能が極端に落ちてしまう。

バックアップ

#mysqldump
 -u root \
 -ppassword \
 --single-transaction \
 --master-data=2 \
 --flush-logs \
     --databases DBNAME \
     or
     --all-databases \
 | gzip > dbbackup.$(date "+%Y%m%d_%H%M%S").gz

–single-transaction
ダンプ時のデータのトランザクションの一貫性を保つことができます。

–master-data=2
指定するとCHANGE MASTER TOがコメントアウトされた状態になります。
–maser-data=1
指定するとコメントアウトされずにダンプされます。

–flush-logs
ダンプを開始する前に、MySQL サーバ内のログファイルをフラッシュします。

リストア

バックアップからLOGのポジションを確認する

# zcat dbbackup.20140401＿040101.gz | egrep -i MASTER_LOG_POS
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000146', MASTER_LOG_POS=107;

zcat
gzip圧縮されたファイルを解凍せずに確認（cat）する。

例では、ダンプを取る際にmysql-bin.000146に切り替わったので、mysql-bin.000145までは処理済み。
最新まで復旧するためには、mysql-bin.000146以降の処理を適用する必要がある。

誤った操作の手前まで戻したい⇒binlogから誤った操作の手前のポジションを確認する

# mysqlbinlog --no-defaults /mnt/addVol/mysql/mysql-bin.000146 | egrep -i -B5 truncate
COMMIT
/*!*/;
# at 1008
#140418 12:30:01 server id 181  end_log_pos 314172554   Query   thread_id=1605543       exec_time=0     error_code=0
SET TIMESTAMP=1397791801/*!*/;
TRUNCATE TABLE TESTTABLE

例では、誤ってtruncateしてしまったので、間際の処理までを復活するためにポジションを確認している。
mysqlbinlogコマンドでbinlogの中をみて、egrepでtruncate処理を検索、-B5オプションで処理の5行前までを表示する。
表示から、間際の処理は1008（# at 1008）ということが分かった。

binlogからリカバリに必要な差分のsqlを作成する

誤った操作の手前までの差分を抽出する

ダンプファイルから開始位置は107、binlogから終了位置は1008

# mysqlbinlog --no-defaults --start-position=107 --stop-position=1008 mysql-bin.000146 > restore.sql

リストア実行

# mysql -u root -ppassword < restore.sql

※万が一途中でエラーになった場合は、エラーを対処してから「-f」オプションで再実行
　「-f」エラーをスキップして続行

# mysql -u root -ppassword -f < restore.sql

目次