binlog2sql: Binlog를 이용한 Point In Time Recovery

percona에서 새로 소개한 binlog2sql에 대해 설명한다.
[출처] www.percona.com/blog/2020/07/09/binlog2sql-binlog-to-raw-sql-conversion-and-point-in-time-recovery/

 

1. 개요

mysqlbinlog 툴을 사용하면 binary log를 binary에서 텍스트 형식으로 변환할 수 있다.
"binlog_format & binlog_rows_query_log_events "매개 변수 에 따라 다르지만 mysqlbinlog 유틸리티를 사용하여 정확한 SQL 문을 생성할 수도 있다.
하지만 mysqlbinlog 툴에는 ROLLBACK 문을 생성하는 기능이 없다.
percona binlog2sql은 MySQL binary log를 디코딩하고 raw SQL을 추출하며, 플래시 백 기능을 사용하여 PITR (Point-Time Recovery)을 위한 ROLLBACK 문을 생성한다.

 

 

2. binlog2sql은 무엇인가?

• binary log를 구문 분석하는 오픈 소스 도구다.
• binary log에서 raw SQL 문을 추출하는 기능이 있다.
• 특정 시점 복구를 위해 binary log에서 ROLLBACK SQL을 생성하는 기능이 있다.

 

 

3. Mysqlbinlog vs Binlog2sql

binlog2sql 도구로 이동하기 전에 Mysqlbinlog와 bilog2sql의 출력을 비교한다.
아래 쿼리를 사용하여 binlog 이벤트를 생성한다.

 

3.1. Mysqlbinlog ( binlog_format = STATEMENT )

> mysqlbinlog --base64-output=decode-rows -vv --start-position 1682 --stop-position 2009 /log/mysql-log/mysql-bin.000005 >> test1.log

SQL 형태로 결과물을 제공한다.

 

3.2 Mysqlbinlog ( binlog_format=ROW, binlog_rows_query_log_events=OFF )

> mysqlbinlog --base64-output=decode-rows -vv --start-position 1629 --stop-position 1903 /log/mysql-log/mysql-bin.000005 >> test2.log

텍스트 형식이긴 하지만 SQL 형태는 아니다.

 

3.3. Mysqlbinlog ( binlog_format=ROW and binlog_rows_query_log_events=ON )

> mysqlbinlog --base64-output=decode-rows -vv --start-position 2325 --stop-position 2669 /log/mysql-log/mysql-bin.000005 >> test3.log

SQL 형태로 결과물을 제공한다.

 

3.4. Binlog2sql

> ./binlog2sql-master/binlog2sql/binlog2sql.py --user root -p --start-file mysql-bin.000005

SQL 형태로 결과물을 제공한다.

 

 

4. MySQL 요구 사항

다음 파라미터를 활성화한다.

binlog_format = row binlog_row_image = full

binlog2sql은 BINLOG_DUMP 프로토콜을 기반으로 binlog 컨텐츠를 얻는다.
binlog2sql은 서버에서 INFORMATION_SCHEMA.COLUMNS 테이블을 읽기 위해 메타 데이터 테이블이 필요하다.

 

 

5. 설치

5.1. 테스트 환경

파이선 2.7
MySQL 5.7.28
참고 : MySQL 8 이상을 지원하려면 “pymysql – 0.9.3”이 있어야 한다.

다음 명령어를 통해 설치 가능하다.

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql pip install -r requirements.txt

 

5.2. Binlog2sql 옵션

 

6. Binary Log에서 Raw SQL 추출하는 방법

> create table binlog2sql(id int primary key auto_increment, name varchar(16), status enum('A','NA'), up_date datetime default current_timestamp); Query OK, 0 rows affected (0.01 sec) > insert into binlog2sql (name,status) values ('kani','A'),('ram','A'),('durai','A'),('asha','A'),('sakthi','A'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 ./binlog2sql-master/binlog2sql/binlog2sql.py -uroot -p --start-file mysql-bin.000005 --start-position 3717 --stop-position 4209 | cut -f1 -d"#"

 

7. binlog2sql의 PITR 사용법

Binlog2sql 툴에 “–flashback” 옵션을 사용하여 ROLLBACK 문을 생성한다.
INSERT, DELETE, UPDATE 문에서 데이터를 복구할 수 있다.
DROP, TRUNCATE는 binary logs를 사용할 수 없으므로 지원하지 않는다.

 

7.1. Case 1 – DELETE PITR :

테스트 테이블 "binlog2sql" 에서 "id in (4,5)"의 두 레코드를 삭제한다.

 

참고를 위해 binlog2sql을 사용하여 원시 SQL을 생성한다.

./binlog2sql-master/binlog2sql/binlog2sql.py -uroot -p --start-file mysql-bin.000005 --start-position 4209 --stop-position 4571 | cut -f1 -d"#"

 

위의 DELETE는 레코드를 삭제하기 위해 실행된 명령문이다.

이제 아래와 같이 “–flashback”옵션을 사용하여 ROLLBACK 문을 생성한다.

./binlog2sql-master/binlog2sql/binlog2sql.py -uroot -p --start-file mysql-bin.000005 --start-position 4209 --stop-position 4571 --flashback | cut -f1 -d"#"

해당 DELETE 문이 INSERT 문으로 변환된 것을 볼 수 있다.

 

이제  롤백 명령문을 사용하여 복구를 시도한다.

./binlog2sql-master/binlog2sql/binlog2sql.py -uroot -p --start-file mysql-bin.000005 --start-position 4209 --stop-position 4571 --flashback | cut -f1 -d"#" >> rollback.sql mysql -uroot -p -D test -vvv < rollback.sql

DELETE에서 데이터를 복구했다.

 

7.2. Case 2 – UPDATE PITR :

테이블 "binlog2sql" 에서 "id in (4,5)"의 두 레코드의 상태를 “A”에서 “NA”로 변경했다.

 

참조를 위해 binlog2sql을 사용하여 실행된 UPDATE를 생성한다.

./binlog2sql-master/binlog2sql/binlog2sql.py -uroot -p --start-file mysql-bin.000005 --start-position 5409 --stop-position 5817 | cut -f1 -d"#"

 

ROLLBACK 문을 생성한다.

./binlog2sql-master/binlog2sql/binlog2sql.py -uroot -p --start-file mysql-bin.000005 --start-position 5409 --stop-position 5817 --flashback | cut -f1 -d"#"

 

이제  롤백 명령문을 사용하여 복구를 시도한다.

./binlog2sql-master/binlog2sql/binlog2sql.py -uroot -p --start-file mysql-bin.000005 --start-position 5409 --stop-position 5817 --flashback | cut -f1 -d"#" >> rollback.sql mysql -uroot -p -D test -vvv < rollback.sql

변경 사항이 rollback 되었다.

 

 

8. 결론

앞에서 언급했듯이이 binlog2sql 도구는 INSERT, DELETE, UPDATE에 대해서만 롤백을 지원한다.
DDL 문은 binary log에 실제 데이터를 기록하지 않으므로 DDL을 지원하지 않는다.
binlog2sql은 암호화, 압축 binary log에서 작동하지 않는다.
binlog2sql은 MySQL 5.6 및 MySQL 5.7 환경에서 테스트되었다.