[MySQL] DUMP, Xtrabackup, Binlog를 사용한 PITR

Dump : 논리적 DB 백업 -> CREATE, INSERT 등의 구문으로 백업 및 복원을 하는 방식

데이터 사이즈에 따라 속도 차이가 심하지만 간편하며 어떤 MYSQL 서버에서도 사용이 가능한 장점

Xtrabackup : 물리적 DB 백업으로 파일 시스템 자체를 복사하는 방식이므로 속도가 빠르다

 

 

시점복원을 통한 데이터 복원하기

SQL SERVER 에서는 풀백업이 있다는 가정하에 로그 백업을 통하여 시점 복원을 통하여 DB 를 복원하고(STOPAT)

(카탈로그 DB명은 별도로 생성) 서비스의 테이블과 복원한 테이블의 데이터를 비교하여 서비스 테이블의 데이터를 복원할 수 있다.

MySQL에서는 이러한 장애 상황에서 어떤 방식으로 진행할 수 있을지 체크해보았다.

DUMP + BINLOG 를 이용한 복원

1. 데이터 입력 (insert)

2. dump
mysqldump -u계정명 -p'' master-data=2 --no-create-db backup_test > backup_test_dump.sql

3. 데이터 삭제 (delete from test1)
4. show binary logs

5. flush logs;

 

6. DUMP 파일 마지막 BINLOG 확인
cat backup_test_dump.sql | grep CHANGE

7. 임시 db 생성 및 복원
backup_test_tmp 명칭의 db 생성 후 덤프 복원 진행
mysql -u계정 -p'패스워드' database= backup_test_tmp  < DUMP파일

8. binlog 편집
mysqlbinlog mysql-bin.000009 --start-position="15997" --stop-datetime="2023-11-27 04:38:30" -v -d backup_test_tmp --rewrite-db 'backup_test->backup_test_tmp' > binlog.2.sql
해당 명령어를 통해서 db명 바꿔서 binlog 파일 sql 파일로 변환 가능

9. 복원
mysql -u계정명 -p'' --database=backup_test_tmp < binlog.2.sql 

 

Xtrabackup + BINLOG를 이용한 복원

1. 1차 데이터 입력

2. xtrabackup  진행

xtrabackup --defaults-file=/etc/my.cnf --user=계정명 --password='' --backup --target-dir=타겟 경로 --slave-info --socket=/tmp/mysql.sock --databases="backup_test" 

3. 추가 데이터 입력

4. 데이터 삭제

DELETE FROM test1

5. show binary logs;

6. flush logs;

7. BINLOG SQL 파일로 변경
mysqlbinlog mysql-bin.000011 --start-position="954952" --stop-datetime="2023-11-28 00:41:00" -v -d backup_test_tmp --rewrite-db 'backup_test->backup_test_tmp' > binlog.1.sql

8. xtrabackup 복원
innobackupex --defaults-file=/etc/my.cnf --apply-log  --export /DB/Backup/Full/backup_test/

9. backup_test_tmp 에 테이블 생성

show create table 구문으로 추출 또는 frm 파일을 sql 파일로 변경 (MySQL8 부터는 ibd 파일에 테이블 정보가 들어가 frm 파일이 없으므로 ibd2sdi툴을 사용하여 추출할 수 있)

10. ibd 파일 변경
ALTER TABLE backup_test_tmp.test1 DISCARD TABLESPACE;  (ibd 파일이 삭제됨)

11. 파일 이동 
cp test1.ibd /DB/Data/backup_test_tmp/
12. ibd 파일 할당
ALTER TABLE backup_test_tmp.test1 IMPORT TABLESPACE;

13. BINLOG 복원

mysql -u계정명 -p'' --database=backup_test_tmp <  binlog.1.sql

 

해당 과정을 통해 mysql 을 내리지 않고 다른 DB 스키마를 생성하여 일부 테이블을 PITR 하였다.

라이브 운영 중 데이터 복구 이슈가 필요할 경우 해당 방법을 통해 서비스 다운을 최소화하여 복원이 가능하다.

 

etc : 특정 테이블만 복원을 하기 위해 cat bin.sql파일 | grep -e table 명 > bin.table.sql 로 처리하고자 하였으나 binlog 파일 형식(statement) SQL 구문으로 생성이 되지 않아서 어려움이 있음.
특정 테이블만 처리하기 위해서는 특정 테이블만 생성하고 9번의 복원 단계에서 --force 옵션을 주면 가능

→ 테스트하였을 때 순서, 에러에 상관 없이 테이블이 있는 경우 데이터가 입력됨.

 

 

특정 DB만

mysqldump -u root -p DB명 > ~~.sql

특정 테이블만

mysqldump -u root -p DB명 table명 > ~~.sql

특정 스키마만

mysqldump -u root -p -d DB명

 

옵션들

• -A, –all-databases : 모든 DB를 덤프
• -B, –databases : 여러 DB를 동시에 덤프 할 때 사용
• -t : 데이터만 백업
• -d : 스키마만 백업  

새로운 DB명으로 복원

CREATE DATABASE serviceDB_tmp

 

dump 진행

mysqldump -u root -p --no-create-db serviceDB > serviceDB.sql

 

restore 진행 (mysql -u 계정명 -p [복원할 db명] < [덤프 파일명]

mysql -u root -p serviceDB_tmp < serviceDB.sql

or

use serviceDB_tmp;

SOURCE serviceDB.sql