# MySQLの復旧をお願いします！！

## 問題
あるデベロッパーから「dev環境と間違えて、prod環境のMySQLで直接`truncate table sbtest3;`を実行してsbtest3テーブルのデータを削除してしまいました、復旧をお願いします！！」と緊急の連絡を受けました。
このクエリの実行後、すぐに間違いに気づいたデベロッパーはサービスをメンテナンス状態にし、アプリケーションサーバを停止したそうです。

#### 問1(q01)

`truncate table sbtest3;` というクエリが実行された日時を`yymmdd HH:MM:SS`のフォーマットで報告してください。
また、どのようにこの日時を特定したかを説明してください。

#### 問2(q02)

`truncate table`が実行される直前の状態(`truncate table`が実行される1つ前のクエリが実行された状態)にデータを復旧し、復旧後`checksum table sysbench.sbtest3;`の結果を報告してください。
また、データの復旧に必要な手順を説明してください。

## 情報

### 状況

- このMySQLは毎日定時に`sysbench database`のバックアップを取得していて(コンテスト問題の作成上truncate table文が実行された日まで)、偶然truncate文が実行される直前にこの日のバックアップが完了していた
- バックアップは以下のコマンドで取得されている
- `mysqldump --opt --single-transaction --master-data=2 --default-character-set=utf8mb4 --databases sysbench > /root/backup/backup.dump`
- `mysql -u root -p < /root/backup/backup.dump`でバックアップが取得された時点に復旧できる
- adminユーザからsudo suすることでrootユーザから操作してください

### 問題サーバー

- IPアドレス: `192.168.0.1`
ユーザー: admin
パスワード: USerPw@19
- DBユーザー: `root`
- DBパスワード: `root`

### ゴール

- 問1, `truncate table sbtest3;` というクエリが実行された日時を特定し、その特定方法を説明していること
- 問2, MySQLのデータが復旧し、その手順の説明と`checksum table sysbench.sbtest3;`の結果が報告されていること
- 問1に正解すると部分点が与えられます。

## 解答

### q01
`191120 16:30:55`

#### 特定方法
`/var/lib/mysql`にてMySQLのbinlogの出力がされていた為、これらのログと`mysqlbinlog`コマンドを使用して特定を試みた。

`binlog.000018`のログにて以下の情報が抽出できた
`root@ubuntu:/var/lib/mysql# mysqlbinlog -vvv --base64-output=DECODE-ROWS binlog.000018 | grep -C 10 truncate`

/*!80001 SET @@session.original_commit_timestamp=1574235055802260*//*!*/;
/*!80014 SET @@session.original_server_version=80018*//*!*/;
/*!80014 SET @@session.immediate_server_version=80018*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 54452036
#191120 16:30:55 server id 1  end_log_pos 54452141 CRC32 0x7e67e1d2 	Query	thread_id=11	exec_time=0	error_code=0	Xid = 189179
use `sysbench`/*!*/;
SET TIMESTAMP=1574235055/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
truncate table sbtest3
/*!*/;
# at 54452141
#191120 16:30:55 server id 1  end_log_pos 54452220 CRC32 0x9f923dc7 	Anonymous_GTID	last_committed=31010	sequence_number=31011	rbr_only=yes	original_committed_timestamp=1574235055973705	immediate_commit_timestamp=1574235055973705	transaction_length=1271
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1574235055973705 (2019-11-20 16:30:55.973705 JST)
# immediate_commit_timestamp=1574235055973705 (2019-11-20 16:30:55.973705 JST)
/*!80001 SET @@session.original_commit_timestamp=1574235055973705*//*!*/;
/*!80014 SET @@session.original_server_version=80018*//*!*/;
/*!80014 SET @@session.immediate_server_version=80018*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

よって、ここから`truncate table sbtest3`が実行された日時`191120 16:30:55`を得た。

### q02
`3499048903`

#### 手順
1. q01の`binlog.000018`のログ情報から、`truncate table sbtest3`実行直前のログポジション`54452036`を得る。
2. また、バックアップ`backup.dump`の先頭付近に書かれている`CHANGE MASTER TO MASTER_LOG_FILE='binlog.000018', MASTER_LOG_POS=34626719`の情報から、このバックアップ復元後のログポジション`34626719`を得る。
3. この2つのログポジションを利用して、`binlog.000018`からバックアップ後から`trucate table sbtest3`直前までに実行されたsqlクエリ(ポジション`34626719`から`54452036`)を復元する。
`mysqlbinlog binlog.000018 --start-position 34626719 --stop-position 54452036 > binlog.000018.sql`
4. バックアップ`backup.dump`をリストアする。
`mysql -u root -p < /root/backup/backup.dump`
5. 手順3で作った`binlog.000018.sql`を実行する。
`mysql -u root -p < binlog.000018.sql`