RDSのクエリ単位での復旧手順
ソシャゲとかECで、バグでアイテムを9999999999999個大量配布しちゃった時に、スーパーメンテナンスタイムからの問題あるクエリを回避しつつの真のリストアで通常運営できる状態に復旧します。
もくじ
前提条件
関連
- AWS RDS PITR+ロールフォワードによるリカバリ 【下準備編】
- AWS RDS PITR+ロールフォワードによるリカバリ 【汎用 RDSのディスク障害からの復旧】
- AWS RDS PITR+ロールフォワードによるリカバリ 【誤ったクエリ発行からの復旧】
テストデータ準備
create table staff(
id int auto_increment not null,
name varchar(50),
regdate timestamp not null default current_timestamp(),
index(id)
);
insert into staff(name) values('tanaka');
insert into staff(name) values('suzuki');
insert into staff(name) values('takashi');
insert into staff(name) values('yuu');
insert into staff(name) values('mitani');
insert into staff(name) values('kato');
insert into staff(name) values('miyuki');
insert into staff(name) values('erika');
insert into staff(name) values('1115-1946');
insert into staff(name) values('1115-1947');
insert into staff(name) values('1115-1948');
insert into staff(name) values('1115-1949');
insert into staff(name) values('1115-1950');
DELETE FROM staff WHERE name = "yuu"; ●問題のクエリ
insert into staff(name) values('1115-1951');
insert into staff(name) values('1115-1952');
mysql> SELECT * FROM staff; +----+-----------+---------------------+ | id | name | regdate | +----+-----------+---------------------+ | 1 | tanaka | 2021-11-15 19:45:47 | | 2 | suzuki | 2021-11-15 19:45:47 | | 3 | takashi | 2021-11-15 19:45:47 | | 5 | mitani | 2021-11-15 19:45:47 | | 6 | kato | 2021-11-15 19:45:47 | | 7 | miyuki | 2021-11-15 19:45:47 | | 8 | erika | 2021-11-15 19:45:48 | | 9 | 1115-1946 | 2021-11-15 19:46:23 | | 10 | 1115-1947 | 2021-11-15 19:47:07 | | 11 | 1115-1948 | 2021-11-15 19:48:11 | | 12 | 1115-1949 | 2021-11-15 19:49:13 | | 13 | 1115-1950 | 2021-11-15 19:50:12 | | 14 | 1115-1951 | 2021-11-15 19:51:02 | | 15 | 1115-1952 | 2021-11-15 19:52:01 | +----+-----------+---------------------+ 15 rows in set (0.01 sec)
このクエリによってデータ破損を確認＞＜
DELETE FROM staff WHERE name = "yuu";
あばばばばばばばばばば…
// 例としてわかりやすくしてますが、実際のあるあるなケースだと
DELETE FROM staff; や UPDATE staff SET name = "名無しさん";
WHEREを忘れたバグなどで全レコード削除したり、全件更新してしまうケースです。。怖いですね；；
でも安心してください。
復旧できます。
メンテナンスモード
書き込みが発生するEC2サーバを全台停止させます！
PITR(特定時点による復元)を行います
2021年11月15日19時48分00秒で復元しました。
mysql> SELECT * FROM staff; +----+-----------+---------------------+ | id | name | regdate | +----+-----------+---------------------+ | 1 | tanaka | 2021-11-15 19:45:47 | | 2 | suzuki | 2021-11-15 19:45:47 | | 3 | takashi | 2021-11-15 19:45:47 | | 5 | mitani | 2021-11-15 19:45:47 | | 6 | kato | 2021-11-15 19:45:47 | | 7 | miyuki | 2021-11-15 19:45:47 | | 8 | erika | 2021-11-15 19:45:48 | | 9 | 1115-1946 | 2021-11-15 19:46:23 | | 10 | 1115-1947 | 2021-11-15 19:47:07 | +----+-----------+---------------------+ 15 rows in set (0.01 sec)
レコードid=4のデータもなければ、19時48分以降のデータもありません。
みなさんのバックアップ、リストアはこうなってませんか？🐱 💦
真のリストアはここからです。
クライアント EC2側作業
S3に退避しているbinlogをダウンロードする
$ cd /ManageBinlog/ $ sh ./downloadBinlogFromS3.sh
binlogを確認する
# ls -laht /ManageBinlog/download/testdb/ total 40K drwxrwxrwx 2 root root 4.0K Nov 12 04:55 . -rw-r----- 1 root root 511 Nov 12 04:55 mysql-bin-changelog.001150 ・・・ -rw-r----- 1 root root 511 Nov 12 04:55 mysql-bin-changelog.001107 -rw-r----- 1 root root 2.0K Nov 12 04:53 mysql-bin-changelog.001106 -rw-r--r-- 1 root root 679 Nov 12 04:47 mysql-bin-changelog.001105
# mysqlbinlog --base64-output=DECODE-ROWS -vv mysql-bin-changelog.001205
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#211115 19:45:01 server id 312454088 end_log_pos 123 CRC32 0x5052a304 Start: binlog v 4, server v 5.7.33-log created 211115 19:45:01
# at 123
#211115 19:45:01 server id 312454088 end_log_pos 154 CRC32 0x184a6211 Previous-GTIDs
# [empty]
# at 154
#211115 19:45:27 server id 312454088 end_log_pos 219 CRC32 0xfa2610aa Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#211115 19:45:27 server id 312454088 end_log_pos 452 CRC32 0xc6cb4a8b Query thread_id=3708 exec_time=0 error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1636973127/*!*/;
SET @@session.pseudo_thread_id=3708/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
SET @@session.explicit_defaults_for_timestamp=1/*!*/;
create table staff(
id int auto_increment not null,
name varchar(50),
regdate timestamp not null default current_timestamp(),
index(id)
)
/*!*/;
# at 452
#211115 19:45:47 server id 312454088 end_log_pos 517 CRC32 0xaecfc3d1 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 517
#211115 19:45:47 server id 312454088 end_log_pos 600 CRC32 0xe55caaa4 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973147/*!*/;
BEGIN
/*!*/;
# at 600
# at 632
#211115 19:45:47 server id 312454088 end_log_pos 632 CRC32 0x15d7147a Intvar
SET INSERT_ID=1/*!*/;
#211115 19:45:47 server id 312454088 end_log_pos 750 CRC32 0xc6d1fb89 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973147/*!*/;
insert into staff(name) values('tanaka')
/*!*/;
# at 750
#211115 19:45:47 server id 312454088 end_log_pos 781 CRC32 0x79c0cb81 Xid = 41035
COMMIT/*!*/;
# at 781
#211115 19:45:47 server id 312454088 end_log_pos 846 CRC32 0x3e7a2a54 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 846
#211115 19:45:47 server id 312454088 end_log_pos 929 CRC32 0x4db34516 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973147/*!*/;
BEGIN
/*!*/;
# at 929
# at 961
#211115 19:45:47 server id 312454088 end_log_pos 961 CRC32 0xf205b4ea Intvar
SET INSERT_ID=2/*!*/;
#211115 19:45:47 server id 312454088 end_log_pos 1079 CRC32 0xa470e190 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973147/*!*/;
insert into staff(name) values('suzuki')
/*!*/;
# at 1079
#211115 19:45:47 server id 312454088 end_log_pos 1110 CRC32 0x664534b0 Xid = 41036
COMMIT/*!*/;
# at 1110
#211115 19:45:47 server id 312454088 end_log_pos 1175 CRC32 0x8cfccc42 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1175
#211115 19:45:47 server id 312454088 end_log_pos 1258 CRC32 0x4ca077a5 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973147/*!*/;
BEGIN
/*!*/;
# at 1258
# at 1290
#211115 19:45:47 server id 312454088 end_log_pos 1290 CRC32 0x9ce5d2fa Intvar
SET INSERT_ID=3/*!*/;
#211115 19:45:47 server id 312454088 end_log_pos 1409 CRC32 0xf97ebf2a Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973147/*!*/;
insert into staff(name) values('takashi')
/*!*/;
# at 1409
#211115 19:45:47 server id 312454088 end_log_pos 1440 CRC32 0xe6a7faba Xid = 41037
COMMIT/*!*/;
# at 1440
#211115 19:45:47 server id 312454088 end_log_pos 1505 CRC32 0x83f5158b Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1505
#211115 19:45:47 server id 312454088 end_log_pos 1588 CRC32 0x1394a48c Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973147/*!*/;
BEGIN
/*!*/;
# at 1588
# at 1620
#211115 19:45:47 server id 312454088 end_log_pos 1620 CRC32 0x89ab027f Intvar
SET INSERT_ID=4/*!*/;
#211115 19:45:47 server id 312454088 end_log_pos 1735 CRC32 0xcfb2d64c Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973147/*!*/;
insert into staff(name) values('yuu')
/*!*/;
# at 1735
#211115 19:45:47 server id 312454088 end_log_pos 1766 CRC32 0x6c992dd0 Xid = 41038
COMMIT/*!*/;
# at 1766
#211115 19:45:47 server id 312454088 end_log_pos 1831 CRC32 0x49c2fa20 Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1831
#211115 19:45:47 server id 312454088 end_log_pos 1914 CRC32 0x13266bcb Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973147/*!*/;
BEGIN
/*!*/;
# at 1914
# at 1946
#211115 19:45:47 server id 312454088 end_log_pos 1946 CRC32 0xb1017024 Intvar
SET INSERT_ID=5/*!*/;
#211115 19:45:47 server id 312454088 end_log_pos 2064 CRC32 0x246eb768 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973147/*!*/;
insert into staff(name) values('mitani')
/*!*/;
# at 2064
#211115 19:45:47 server id 312454088 end_log_pos 2095 CRC32 0x3649c6ad Xid = 41039
COMMIT/*!*/;
# at 2095
#211115 19:45:47 server id 312454088 end_log_pos 2160 CRC32 0x25ff6b83 Anonymous_GTID last_committed=6 sequence_number=7 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2160
#211115 19:45:47 server id 312454088 end_log_pos 2243 CRC32 0x1ea0f380 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973147/*!*/;
BEGIN
/*!*/;
# at 2243
# at 2275
#211115 19:45:47 server id 312454088 end_log_pos 2275 CRC32 0x19340ef2 Intvar
SET INSERT_ID=6/*!*/;
#211115 19:45:47 server id 312454088 end_log_pos 2391 CRC32 0xf74ace80 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973147/*!*/;
insert into staff(name) values('kato')
/*!*/;
# at 2391
#211115 19:45:47 server id 312454088 end_log_pos 2422 CRC32 0xc7190872 Xid = 41040
COMMIT/*!*/;
# at 2422
#211115 19:45:47 server id 312454088 end_log_pos 2487 CRC32 0x3e5d6014 Anonymous_GTID last_committed=7 sequence_number=8 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2487
#211115 19:45:47 server id 312454088 end_log_pos 2570 CRC32 0x22c5a1c3 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973147/*!*/;
BEGIN
/*!*/;
# at 2570
# at 2602
#211115 19:45:47 server id 312454088 end_log_pos 2602 CRC32 0xc6fe5668 Intvar
SET INSERT_ID=7/*!*/;
#211115 19:45:47 server id 312454088 end_log_pos 2720 CRC32 0x5fe9fd14 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973147/*!*/;
insert into staff(name) values('miyuki')
/*!*/;
# at 2720
#211115 19:45:47 server id 312454088 end_log_pos 2751 CRC32 0xdae58371 Xid = 41041
COMMIT/*!*/;
# at 2751
#211115 19:45:48 server id 312454088 end_log_pos 2816 CRC32 0xdbe837c6 Anonymous_GTID last_committed=8 sequence_number=9 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2816
#211115 19:45:48 server id 312454088 end_log_pos 2899 CRC32 0x69b9ad18 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973148/*!*/;
BEGIN
/*!*/;
# at 2899
# at 2931
#211115 19:45:48 server id 312454088 end_log_pos 2931 CRC32 0x7e33a5bc Intvar
SET INSERT_ID=8/*!*/;
#211115 19:45:48 server id 312454088 end_log_pos 3048 CRC32 0x720c3113 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973148/*!*/;
insert into staff(name) values('erika')
/*!*/;
# at 3048
#211115 19:45:48 server id 312454088 end_log_pos 3079 CRC32 0x3da6a8a9 Xid = 41042
COMMIT/*!*/;
# at 3079
#211115 19:46:23 server id 312454088 end_log_pos 3144 CRC32 0x5b6ab0ce Anonymous_GTID last_committed=9 sequence_number=10 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 3144
#211115 19:46:23 server id 312454088 end_log_pos 3227 CRC32 0xe78c1926 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973183/*!*/;
BEGIN
/*!*/;
# at 3227
# at 3259
#211115 19:46:23 server id 312454088 end_log_pos 3259 CRC32 0xa8c0ec24 Intvar
SET INSERT_ID=9/*!*/;
#211115 19:46:23 server id 312454088 end_log_pos 3380 CRC32 0x4c88f223 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973183/*!*/;
insert into staff(name) values('1115-1946')
/*!*/;
# at 3380
#211115 19:46:23 server id 312454088 end_log_pos 3411 CRC32 0x37d337be Xid = 41083
COMMIT/*!*/;
# at 3411
#211115 19:46:43 server id 312454088 end_log_pos 3476 CRC32 0x23afb995 Anonymous_GTID last_committed=10 sequence_number=11 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 3476
#211115 19:46:43 server id 312454088 end_log_pos 3552 CRC32 0x46e8f213 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1636973203/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=8/*!*/;
BEGIN
/*!*/;
# at 3552
#211115 19:46:43 server id 312454088 end_log_pos 3737 CRC32 0x14b91458 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1636973203/*!*/;
INSERT INTO mysql.rds_heartbeat2(id, value) values (1,1636973203446) ON DUPLICATE KEY UPDATE value = 1636973203446
/*!*/;
# at 3737
#211115 19:46:43 server id 312454088 end_log_pos 3768 CRC32 0x378a297a Xid = 41104
COMMIT/*!*/;
# at 3768
#211115 19:47:07 server id 312454088 end_log_pos 3833 CRC32 0x26b2be55 Anonymous_GTID last_committed=11 sequence_number=12 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 3833
#211115 19:47:07 server id 312454088 end_log_pos 3916 CRC32 0x199d6075 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973227/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
BEGIN
/*!*/;
# at 3916
# at 3948
#211115 19:47:07 server id 312454088 end_log_pos 3948 CRC32 0xab3839eb Intvar
SET INSERT_ID=10/*!*/;
#211115 19:47:07 server id 312454088 end_log_pos 4069 CRC32 0xd0f34e4b Query thread_id=3708 exec_time=0 error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1636973227/*!*/;
insert into staff(name) values('1115-1947')
/*!*/;
# at 4069
#211115 19:47:07 server id 312454088 end_log_pos 4100 CRC32 0x4c2766a5 Xid = 41154
COMMIT/*!*/;
# at 4100
#211115 19:48:11 server id 312454088 end_log_pos 4165 CRC32 0x61613288 Anonymous_GTID last_committed=12 sequence_number=13 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 4165
#211115 19:48:11 server id 312454088 end_log_pos 4248 CRC32 0xef2c9d66 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973291/*!*/;
BEGIN
/*!*/;
# at 4248
# at 4280
#211115 19:48:11 server id 312454088 end_log_pos 4280 CRC32 0x70a98745 Intvar
SET INSERT_ID=11/*!*/;
#211115 19:48:11 server id 312454088 end_log_pos 4401 CRC32 0xabe24dfd Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973291/*!*/;
insert into staff(name) values('1115-1948')
/*!*/;
# at 4401
#211115 19:48:11 server id 312454088 end_log_pos 4432 CRC32 0xd5121f98 Xid = 41209
COMMIT/*!*/;
# at 4432
#211115 19:49:13 server id 312454088 end_log_pos 4497 CRC32 0x160aa9f3 Anonymous_GTID last_committed=13 sequence_number=14 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 4497
#211115 19:49:13 server id 312454088 end_log_pos 4580 CRC32 0x02043029 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973353/*!*/;
BEGIN
/*!*/;
# at 4580
# at 4612
#211115 19:49:13 server id 312454088 end_log_pos 4612 CRC32 0x86b19ac4 Intvar
SET INSERT_ID=12/*!*/;
#211115 19:49:13 server id 312454088 end_log_pos 4733 CRC32 0xfaf1c4b6 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973353/*!*/;
insert into staff(name) values('1115-1949')
/*!*/;
# at 4733
#211115 19:49:13 server id 312454088 end_log_pos 4764 CRC32 0x45e9c6ab Xid = 41264
COMMIT/*!*/;
# at 4764
#211115 19:50:00 server id 312454088 end_log_pos 4821 CRC32 0x11056d72 Rotate to mysql-bin-changelog.001206 pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
# at 4069
#211115 19:47:07 server id 312454088 end_log_pos 4100 CRC32 0x4c2766a5 Xid = 41154
COMMIT/*!*/;
# at 4100
#211115 19:48:11 server id 312454088 end_log_pos 4165 CRC32 0x61613288 Anonymous_GTID last_committed=12 sequence_number=13 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 4165
#211115 19:48:11 server id 312454088 end_log_pos 4248 CRC32 0xef2c9d66 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973291/*!*/;
BEGIN
/*!*/;
# at 4248
# at 4280
#211115 19:48:11 server id 312454088 end_log_pos 4280 CRC32 0x70a98745 Intvar
SET INSERT_ID=11/*!*/;
#211115 19:48:11 server id 312454088 end_log_pos 4401 CRC32 0xabe24dfd Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973291/*!*/;
insert into staff(name) values('1115-1948')
/*!*/;
# at 4401
#211115 19:48:11 server id 312454088 end_log_pos 4432 CRC32 0xd5121f98 Xid = 41209
19時48分00秒からなのでpositionは4100。このbinlogファイルの最終positionは4764でした。
復旧クエリファイルrecovery_1.sqlを作成
$ mysqlbinlog --skip-gtids -vv --base64-output=DECODE-ROWS \ --start-position=4100 --stop-position=4764 \ mysql-bin-changelog.001205 > recovery_1.sql
# mysqlbinlog --base64-output=DECODE-ROWS -vv mysql-bin-changelog.001206
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#211115 19:50:00 server id 312454088 end_log_pos 123 CRC32 0x40750749 Start: binlog v 4, server v 5.7.33-log created 211115 19:50:00
# at 123
#211115 19:50:00 server id 312454088 end_log_pos 154 CRC32 0xb0a4e268 Previous-GTIDs
# [empty]
# at 154
#211115 19:50:12 server id 312454088 end_log_pos 219 CRC32 0x0ff2dacd Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#211115 19:50:12 server id 312454088 end_log_pos 302 CRC32 0x99e7aded Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973412/*!*/;
SET @@session.pseudo_thread_id=3708/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 302
# at 334
#211115 19:50:12 server id 312454088 end_log_pos 334 CRC32 0x9a8e2347 Intvar
SET INSERT_ID=13/*!*/;
#211115 19:50:12 server id 312454088 end_log_pos 455 CRC32 0xc5e0ebb5 Query thread_id=3708 exec_time=0 error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1636973412/*!*/;
insert into staff(name) values('1115-1950')
/*!*/;
# at 455
#211115 19:50:12 server id 312454088 end_log_pos 486 CRC32 0x19198e73 Xid = 41324
COMMIT/*!*/;
# at 486
#211115 19:50:25 server id 312454088 end_log_pos 551 CRC32 0xdec47acf Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 551
#211115 19:50:25 server id 312454088 end_log_pos 634 CRC32 0x6c9df196 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973425/*!*/;
BEGIN
/*!*/;
# at 634
#211115 19:50:25 server id 312454088 end_log_pos 748 CRC32 0xfe9f5ea7 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973425/*!*/;
DELETE FROM staff WHERE name = "yuu"
/*!*/;
# at 748
#211115 19:50:25 server id 312454088 end_log_pos 779 CRC32 0x6859cf28 Xid = 41336
COMMIT/*!*/;
# at 779
#211115 19:51:02 server id 312454088 end_log_pos 844 CRC32 0xa7ca02ef Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 844
#211115 19:51:02 server id 312454088 end_log_pos 927 CRC32 0x64e705a5 Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973462/*!*/;
BEGIN
/*!*/;
# at 927
# at 959
#211115 19:51:02 server id 312454088 end_log_pos 959 CRC32 0x9d45507c Intvar
SET INSERT_ID=14/*!*/;
#211115 19:51:02 server id 312454088 end_log_pos 1080 CRC32 0x3172ccfc Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973462/*!*/;
insert into staff(name) values('1115-1951')
/*!*/;
# at 1080
#211115 19:51:02 server id 312454088 end_log_pos 1111 CRC32 0x0e4509f3 Xid = 41386
COMMIT/*!*/;
# at 1111
#211115 19:51:58 server id 312454088 end_log_pos 1176 CRC32 0xa88c5569 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1176
#211115 19:51:58 server id 312454088 end_log_pos 1252 CRC32 0x4ed1b5a2 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1636973518/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=8/*!*/;
BEGIN
/*!*/;
# at 1252
#211115 19:51:58 server id 312454088 end_log_pos 1437 CRC32 0x1bd6fe16 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1636973518/*!*/;
INSERT INTO mysql.rds_heartbeat2(id, value) values (1,1636973518260) ON DUPLICATE KEY UPDATE value = 1636973518260
/*!*/;
# at 1437
#211115 19:51:58 server id 312454088 end_log_pos 1468 CRC32 0x351bf345 Xid = 41423
COMMIT/*!*/;
# at 1468
#211115 19:52:01 server id 312454088 end_log_pos 1533 CRC32 0xcf2032f4 Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1533
#211115 19:52:01 server id 312454088 end_log_pos 1616 CRC32 0x62d8c93a Query thread_id=3708 exec_time=0 error_code=0
SET TIMESTAMP=1636973521/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
BEGIN
/*!*/;
# at 1616
# at 1648
#211115 19:52:01 server id 312454088 end_log_pos 1648 CRC32 0x6ccec2c7 Intvar
SET INSERT_ID=15/*!*/;
#211115 19:52:01 server id 312454088 end_log_pos 1769 CRC32 0x6be082f5 Query thread_id=3708 exec_time=0 error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1636973521/*!*/;
insert into staff(name) values('1115-1952')
/*!*/;
# at 1769
#211115 19:52:01 server id 312454088 end_log_pos 1800 CRC32 0x575135b6 Xid = 41451
COMMIT/*!*/;
# at 1800
#211115 19:55:00 server id 312454088 end_log_pos 1857 CRC32 0x876fb267 Rotate to mysql-bin-changelog.001207 pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
問題のクエリを発見
# at 551 #211115 19:50:25 server id 312454088 end_log_pos 634 CRC32 0x6c9df196 Query thread_id=3708 exec_time=0 error_code=0 SET TIMESTAMP=1636973425/*!*/; BEGIN /*!*/; # at 634 #211115 19:50:25 server id 312454088 end_log_pos 748 CRC32 0xfe9f5ea7 Query thread_id=3708 exec_time=0 error_code=0 SET TIMESTAMP=1636973425/*!*/; DELETE FROM staff WHERE name = "yuu" /*!*/; # at 748 #211115 19:50:25 server id 312454088 end_log_pos 779 CRC32 0x6859cf28 Xid = 41336 COMMIT/*!*/; # at 779
問題のクエリを跨ぐようにしてmysql-bin-changelog.001206から2つの復旧クエリファイルを作成します。
$ mysqlbinlog --skip-gtids -vv --base64-output=DECODE-ROWS \ --start-position=4 --stop-position=634 \ mysql-bin-changelog.001206 > recovery_2.sql
$ mysqlbinlog --skip-gtids -vv --base64-output=DECODE-ROWS \ --start-position=748 --stop-position=1800 \ mysql-bin-changelog.001206 > recovery_3.sql
# mysqlbinlog --base64-output=DECODE-ROWS -vv mysql-bin-changelog.001207 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #211115 19:55:00 server id 312454088 end_log_pos 123 CRC32 0x89c9b866 Start: binlog v 4, server v 5.7.33-log created 211115 19:55:00 # at 123 #211115 19:55:00 server id 312454088 end_log_pos 154 CRC32 0xa94574aa Previous-GTIDs # [empty] SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
こちらは4から始まって…時間でも指定できることを示したいので、
endは時間で指定してみました。
$ mysqlbinlog --skip-gtids -vv --base64-output=DECODE-ROWS \ --start-position=4 --stop-datetime='2021-11-15 19:55:00' \ mysql-bin-changelog.001207 > recovery_4.sql
これで復旧クエリはできました。
RDSのエラー回避
エラーが発生する記述を復旧クエリから除外していきます。
sed -i -e '/DEFINER/d' recovery_1.sql sed -i -e '/DEFINER/d' recovery_2.sql sed -i -e '/DEFINER/d' recovery_3.sql sed -i -e '/DEFINER/d' recovery_4.sql sed -i -e '/session.pseudo_thread_id/d' recovery_1.sql sed -i -e '/session.pseudo_thread_id/d' recovery_2.sql sed -i -e '/session.pseudo_thread_id/d' recovery_3.sql sed -i -e '/session.pseudo_thread_id/d' recovery_4.sql sed -i -e '/PSEUDO/d' recovery_1.sql sed -i -e '/PSEUDO/d' recovery_2.sql sed -i -e '/PSEUDO/d' recovery_3.sql sed -i -e '/PSEUDO/d' recovery_4.sql sed -i -e '/GTID/d' recovery_1.sql sed -i -e '/GTID/d' recovery_2.sql sed -i -e '/GTID/d' recovery_3.sql sed -i -e '/GTID/d' recovery_4.sql
↑ 数が多い場合はスクリプトがあるので使ってください
ManageBinlog]$ mv *.sql /ManageBinlog/recoverySql/ ManageBinlog]$ chmod +x renovateRecoverySql.sh ManageBinlog]$ sh renovateRecoverySql.sh
リストア
# mysql -u admin -h {リカバリ用新RDSホスト名} -p < recovery_1.sql
# mysql -u admin -h {リカバリ用新RDSホスト名} -p < recovery_2.sql
# mysql -u admin -h {リカバリ用新RDSホスト名} -p < recovery_3.sql
# mysql -u admin -h {リカバリ用新RDSホスト名} -p < recovery_4.sql
必ずPITRで複製したリカバリ用の新RDSに対してロールフォワードをかけてください。
確認
mysql> SELECT * FROM staff; +----+-----------+---------------------+ | id | name | regdate | +----+-----------+---------------------+ | 1 | tanaka | 2021-11-15 19:45:47 | | 2 | suzuki | 2021-11-15 19:45:47 | | 3 | takashi | 2021-11-15 19:45:47 | | 4 | yuu | 2021-11-15 19:45:47 | ●おおっ！ | 5 | mitani | 2021-11-15 19:45:47 | | 6 | kato | 2021-11-15 19:45:47 | | 7 | miyuki | 2021-11-15 19:45:47 | | 8 | erika | 2021-11-15 19:45:48 | | 9 | 1115-1946 | 2021-11-15 19:46:23 | | 10 | 1115-1947 | 2021-11-15 19:47:07 | | 11 | 1115-1948 | 2021-11-15 19:48:11 | | 12 | 1115-1949 | 2021-11-15 19:49:13 | | 13 | 1115-1950 | 2021-11-15 19:50:12 | | 14 | 1115-1951 | 2021-11-15 19:51:02 | | 15 | 1115-1952 | 2021-11-15 19:52:01 | +----+-----------+---------------------+ 15 rows in set (0.01 sec)
神🐱✨
EC2との連携
EC2の.envをリカバリをした新RDSに連携させます。
動作確認
アプリサーバを起動し、新しいRDSに書き込みができることを確認してください。
関連記事 - More from my site -
AWS RDS PITR+ロールフォワードによるリカバリ 【汎用 RDSのディスク障害からの復旧】- AWS RDS PITR+ロールフォワードによるリカバリ 【下準備編】
ポートフォリオ制作なうなんだが…その１- AWSでのMySQLデータ生存戦略
【非公開】AWS RDSインスタンスタイプ選択基準
EC2からRDSに接続する MySQL, PostgreSQL
AWSの料金早見ツール 汎用EC2, RDS選択再考- RDS Aurora Release note
- EC2のNetworkIn ネットワーク帯域目安
- RDS MySQL DBダンプコマンドのコツ
- EC2 Userdata 起動時にwww-dataユーザでスクリプトを実行させる
- Amazon Linux2 MySQL5.7 clientのインストール
- AWS EC2 ec2-userでApache2.4+PHP-FPMの設定
- AWS EC2 PHP Fatal error: Uncaught Error: Class ‘Imagick’ not found