MySQL 특정 테이블만 시점복구 하기

얼마 전 서비스 로직 이슈로 특정 데이터들이 CASCADE 삭제되어 데이터가 유실되는 일이 발생했습니다.
영향 받은 테이블들은 전체 테이블들 중 두개였고 전체 서비스를 장애 시점 이전으로 롤백시킬 수는 없는 상황이었기 때문에 해당 테이블들만 복구를 진행했습니다.
대상 테이블들은 특히 또 FOREIGN KEY constraint 가 걸려있어서 여러모로 생각해줘야 하는 부분들이 많았습니다.

먼저 복구 시나리오는 아래와 같습니다.

* 복구 시점 찾기
* 최신 백업본 로드 (10/30일 새벽 백업본)
* 10/30일 새벽 백업본 + 장애 시점 전 까지의 binary log 적용 ( 대상 테이블 관련 DML만 추출해서 적용하기 )
* 장애유발쿼리 제거하고 그 이후부터 현재까지의 binary log 적용 (대상 테이블 관련 DML만 추출해서 적용하기 )
  • 복구 대상 binlog 추출
$ mysqlbinlog mysql-bin.000001 --start-position="1234" --verbose -d testdb > pre_restore_1_000001.sql
$ mysqlbinlog mysql-bin.000002 --verbose -d testdb > pre_restore_2_000002.sql
$ mysqlbinlog mysql-bin.000003 --verbose -d testdb > pre_restore_3_000003.sql
$ mysqlbinlog mysql-bin.000004 --stop-position="4567" --verbose -d testdb > pre_restore_4_000004.sql

=> 백업본 dump file에 –dump-slave / –master-data 옵션으로 찍힌 master_log_pos를 start position 으로 지정함

  • 추출한 binlog 에서 복구 대상 테이블들만 추출
$ cat pre_restore_1_000001.sql  | grep -e tb_test1 -e tb_test2 > pre_restore_1_000001.sql 
$ cat pre_restore_2_000002.sql  | grep -e tb_test1 -e tb_test2 > pre_restore_2_000002.sql 
$ cat pre_restore_3_000003.sql  | grep -e tb_test1 -e tb_test2 > pre_restore_3_000003.sql 
$ cat pre_restore_4_000004.sql  | grep -e tb_test1 -e tb_test2 > pre_restore_4_000004.sql
  • 그 중에서 테이블들을 각각 추출
$ cat pre_restore_1_000001.sql | grep "tb_test1"  > restore_tb_test1.sql
$ cat pre_restore_2_000002.sql | grep "tb_test1"  >> restore_tb_test1.sql
$ cat pre_restore_3_000003.sql | grep "tb_test1"  >> restore_tb_test1.sql
$ cat pre_restore_4_000004.sql | grep "tb_test1"  >> restore_tb_test1.sql
    
$ cat pre_restore_1_000001.sql | grep "tb_test2"  > restore_tb_test2.sql
$ cat pre_restore_2_000002.sql | grep "tb_test2"  >> restore_tb_test2.sql
$ cat pre_restore_3_000003.sql | grep "tb_test2"  >> restore_tb_test2.sql
$ cat pre_restore_4_000004.sql | grep "tb_test2"  >> restore_tb_test2.sql
  • FK 상관없이 DML 적용하기 위한 FK OFF 옵션 추가
$ vi fk_check_off.txt
SET FOREIGN_KEY_CHECKS = 0;
    
$ vi fk_check_on.txt
SET FOREIGN_KEY_CHECKS = 1;
    
$ cat fk_check_off.txt restore_tb_test1.sql fk_check_on.txt > final_tb_test1.sql
$ cat fk_check_off.txt restore_tb_test2.sql fk_check_on.txt > final_tb_test2.sql
  • 백업본에 추출한 쿼리 적용
### 10/30 백업본으로 복구용 DB 생성
mysql testdb_bk < bk_testdb_1030.sql 
    
mysql bk_testdb < final_tb_test1.sql
mysql bk_testdb < final_tb_test2.sql
  • 복구 테이블과 리얼 테이블 rename 바꿔치기
rename table testdb.tb_test1 to bk_testdb.bk_tb_test1 , bk_testdb.tb_test1 to testdb.tb_test1;
rename table testdb.tb_test2 to bk_testdb.bk_tb_test2 , bk_testdb.tb_test2 to testdb.tb_test2;
  • FK 조건 다시 정의하기
### show create table testdb.tb_test2

CREATE TABLE `tb_test2` (
`tenant_id` varchar(255) DEFAULT NULL,
`id` varchar(36) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`loadbalancer_id` varchar(36) DEFAULT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `tb_test2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `bk_testdb`.`tb_test1` (`id`)
    

### show create table bk_testdb.bk_tb_test2

CREATE TABLE `bk_tb_test2` (
`tenant_id` varchar(255) DEFAULT NULL,
`id` varchar(36) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`loadbalancer_id` varchar(36) DEFAULT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `tb_test2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `testdb`.`tb_test1` (`id`)
    

### 복구완료된 테이블 FK 재생성 

alter table testdb.tb_test2 drop foreign key tb_test2_ibfk_1;
ALTER TABLE testdb.tb_test2 ADD FOREIGN KEY (id) REFERENCES testdb.tb_test1 (id);
    

### 백업 테이블 FK DROP

alter table bk_testdb.bk_tb_test2 drop foreign key tb_test2_ibfk_1;

=> rename table 을 해도 FK는 원본 부모 테이블을 계속 바라보고 있음
결국 bk_tb_test1 테이블들은 서비스에서 사용하지 않지만 FK constraint 에서 계속 참조되고 있어서 DML이 실패하게 됨

MySQL [testdb]>  delete from tb_test2 where id='be124575-7791-4939-8b97-a7fcaac99151';

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`bk_testdb`.`bk_tb_test1`, CONSTRAINT `bk_tb_test1_ibfk_1` FOREIGN KEY (`id`) REFERENCES `testdb`.`tb_test1` (`id`))

기존 백업 테이블도 drop 후 완료

sed -n -e ‘/DROP TABLE.* 테이블명 /,/UNLOCK TABLES/p’ dump파일명.sql > 생성할파일명.sql