MySQL, AWS

AWS RDS PITR+ロールフォワードによるリカバリ 【誤ったクエリ発行からの復旧】

| by 優

AWS

 

RDSのクエリ単位での復旧手順

ソシャゲとかECで、バグでアイテムを9999999999999個大量配布しちゃった時に、スーパーメンテナンスタイムからの問題あるクエリを回避しつつの真のリストアで通常運営できる状態に復旧します。

前提条件

AWS RDS PITR+ロールフォワードによるリカバリ 【下準備編】

 

RDS MySQL DBダンプコマンドのコツ

 

関連

 

 

テストデータ準備

 

 

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 -

Amazonおすすめ

iPad 9世代 2021年最新作

iPad 9世代出たから買い替え。安いぞ！🐱 初めてならiPad。Kindleを外で見るならiPad mini。ほとんどの人には通常のiPadをおすすめします＞＜

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。（スパム対策）