ポイントインタイムリカバリ（ PITR ）

任意の地点までのデータを復元します。

必要な要素

フルバックアップとフルバックアップから復元したい地点までのバイナリログを使用します。

1. フルバックアップ（ダンプファイル）
2. フルバックアップから復元したいポイントまでのバイナリログ

バイナリログの詳細は バイナリログ（ダンプファイル） を参照。

フルバックアップ（ダンプファイル）

ソースのフルバックアップ（ダンプファイル）を取得します。 source.dump と仮定します。

$ mysqldump \
  -h 127.0.0.1 \
  -P 3306 \
  -u root \
  -p \
  --single-transaction \
  --default-character-set=utf8mb4 \
  --source-data=2 \
  --routines --triggers --events \
  --hex-blob \
  --flush-logs \
  --all-databases > source.dump

# source.dump
$ less source.dump
...
Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=157;
...

上記例では binlog.000002 の 157 行目が該当します。

バイナリログ

パス

show binary logs; でバイナリログ情報を表示します。
以下例は /var/lib/mysql/binlog.000001, /var/lib/mysql/binlog.000002 になります。

show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   2940689 | No        |
| binlog.000002 |       157 | No        |
+---------------+-----------+-----------+

select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.01 sec)

リカバリの流れ

drop database sample; で誤って sample データベースを削除したと仮定しポイントインタイムリカバリを実行します。 フルバックアップファイル source.dump を取得済みと仮定します。

1. フルバックアップをリストア
2. ダンプファイルから リカバリ開始ポジション を取得
3. mysqlbinlog を使用してバイナリログからリカバリする処理の イベントポジション を取得
4. mysqlbinlog を使用して 2. の イベントポジション までリカバリ

1. フルバックアップをリストア

mysql -h 127.0.0.1 -u root -p < source.dump

2. ダンプファイルから リカバリ開始ポジション を取得

less source.dump | grep -A3 "Position to start replication or point-in-time recovery from"
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=157;

上記例のリカバリ開始ポジションは binlog.000002 の 157 です。

3. バイナリログから イベントポジション を取得

例： drop database sample で削除した sample データベースをリカバリします。

# バイナリログは絶体パスで指定する 
mysqlbinlog /var/lib/mysql/binlog.000002 | grep -B20 -i "drop database sample";
.....
.....   
# at 1140 /* <=========================================================== イベントポジション */
#240922 13:14:43 server id 1000  end_log_pos 1259 CRC32 0xb5cb06ba      Query   thread_id=9     exec_time=0     error_code=0    Xid = 689
SET TIMESTAMP=1727010883/*!*/;
drop database sample

イベントポジションは 1140 です。

4. バイナリログからリカバリ

mysqlbinlog --start-position=157 --stop-position=1140 \
/var/lib/mysql/binlog.000002 | mysql -h db-source -u root -p