MySQL binlog2sql으로 flashback 복구하기

MySQL Flashback recovery

MySQL에서는 flashback recovery가 아직 지원되지 않습니다.
그래서 MariaDB의 mysqlbinlog utility를 가져와서 사용하곤 했는데
binlog2sql 이라는 utility를 사용하면 MySQL에서도 flashback recovery를 할 수 있습니다.

binlog2sql

mysqlbinlog 처럼 binary log를 parsing 하는 유틸리티
MariaDB 에서는 제공되던 flashback 기능을 mysql 에서 binlog2sql을 통해 사용할 수 있음
schema-fileter 기능 (database,table)
query-filter 기능 (dml-insert,update,delete)
interval-filter 기능 (file,datetime)
GTID 는 지원하지않음

install

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

mysql8 버전은 PyMySQL , mysql-replication 아래 버전 이상 사용해야함

PyMySQL==0.9.3
wheel==0.29.0
mysql-replication==0.21

필수 설정

### my.cnf
[mysqld]

server_id = 100            #mandatory
log_bin = mysql           #mandatory
max_binlog_size = 1G
binlog_format = row        #mandatory
binlog_row_image = full    #mandatory

### user privileges

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO

binlog_format=MIXED 설정 시 flashback SQL 생성 불가

binary log 에서 SQL 추출

schema-filter

python /engn001/binlog2sql/binlog2sql/binlog2sql.py --user=root -p'qhdks123' -d testdb -tbinlog2sql --start-file mysql57-bin.000015

DELETE FROM `testdb`.`binlog2sql` WHERE `age`=19 AND `id`=1 AND `name`='kimdubi' LIMIT 1; #start1311203 end 1311711 time 2020-03-28 11:13:56
DELETE FROM `testdb`.`binlog2sql` WHERE `age`=29 AND `id`=2 AND `name`='kimdubi2' LIMIT 1; #start1311203 end 1311711 time 2020-03-28 11:13:56
DELETE FROM `testdb`.`binlog2sql` WHERE `age`=39 AND `id`=3 AND `name`='kimdubi3' LIMIT 1; #start1311203 end 1311711 time 2020-03-28 11:13:56

=> -d , -t 옵션을 통해 특정 schema의 SQL 추출

query-filter

python /engn001/binlog2sql/binlog2sql/binlog2sql.py --user=root -p'qhdks123' --start-filemysql57-bin.000010 --stop-never --only-dml --sql-type DELETE

DELETE FROM `test`.`binlog2sql` WHERE `tcol22`='sed delectus sed velit nulla quidem.' AND`tcol23`='Way' AND `tcol20`='est laudantium distinctio rerum ipsa alias.' AND `tcol21`='ab nequeipsam minima dolorum sunt.' AND `tcol08`=1.20 AND `tcol09`='2020-03-28' AND `tcol24`='' AND`tcol25`='c' AND `tcol04`=1801160058 AND `tcol05`=3916589616287113937 AND `tcol06`=549458503723145 AND `tcol07`=0.939116 AND `tcol01`=5 AND `tcol02`=136 AND `tcol03`=172113 AND`tcol26`='red' AND `id`=1 AND `tcol27`=0.601999998092651 AND `tcol28`=0 AND`tcol19`='perspiciatis voluptatem asperiores impedit aut in.' AND `tcol18`='et voluptas et et.'AND `tcol13`=2019 AND `tcol12`='13:24:25' AND `tcol11`='2020-03-28 10:01:13' AND`tcol10`='2019-09-15 14:02:03' AND `tcol17`='nihil iusto odio eum ea voluptatibus et officiadolores.' AND `tcol16`='error sunt aut voluptatem eum cumque.' AND `tcol15`='He' AND`tcol14`='magni assumenda nemo placeat quo quam officia.' LIMIT 1; #start 1310403 end 1311172time 2020-03-28 10:52:57

=> –only-dml , –sql-type 옵션을 통해 특정 dml SQL 추출

interval-filter

python /engn001/binlog2sql/binlog2sql/binlog2sql.py --user=root -p'qhdks123' --start-filemysql57-bin.000004 --end-file mysql57-bin.000016 --start-datetime '2020-03-28 11:13:56'--stop-datetime '2020-03-29 11:00:00' 

INSERT INTO `testdb`.`binlog2sql`(`age`, `id`, `name`) VALUES (123, 12, 'kimubi1234'); #start1311203 end 1311711 time 2020-03-28 11:13:56
INSERT INTO `testdb`.`binlog2sql`(`age`, `id`, `name`) VALUES (12345, 11, 'kimdubi'); #start1311203 end 1311711 time 2020-03-28 11:13:56
INSERT INTO `testdb`.`binlog2sql`(`age`, `id`, `name`) VALUES (12345, 10, 'kimdubi'); #start1311203 end 1311711 time 2020-03-28 11:13:56

=> file, datetime 옵션으로 특정 시간대 별 SQL 추출

gtid는 지원안됨

python /engn001/binlog2sql/binlog2sql/binlog2sql.py --user=root -p'qhdks123'--start-position='8043f172-5d51-11ea-b0ab-0242ac120003:1'

usage: binlog2sql.py [-h HOST] [-u USER] [-p [PASSWORD [PASSWORD ...]]]
                     [-P PORT] [--start-file START_FILE]
                     [--start-position START_POS] [--stop-file END_FILE]
                     [--stop-position END_POS] [--start-datetime START_TIME]
                     [--stop-datetime STOP_TIME] [--stop-never] [--help]
                     [-d [DATABASES [DATABASES ...]]]
                     [-t [TABLES [TABLES ...]]] [--only-dml]
                     [--sql-type [SQL_TYPE [SQL_TYPE ...]]] [-K] [-B]
                     [--back-interval BACK_INTERVAL]
binlog2sql.py: error: argument --start-position/--start-pos: invalid int value:'8043f172-5d51-11ea-b0ab-0242ac120003:1'

Flashback PITR

위에서 살펴본 SQL 추출 구문에서 –flashback 옵션 추가로 rollback 쿼리 추출할 수 있음

update 장애

mysql> update binlog2sql set tcol24='' ;
Query OK, 1000 rows affected (0.04 sec)
Rows matched: 1000  Changed: 1000  Warnings: 0

복구 대상 포지션 확인

BEGIN
/*!*/;
# at 291
.
.
.
#200328 10:01:13 server id 33  end_log_pos 871299 CRC32 0xf0f3dd99      Xid = 1260
COMMIT/*!*/;

flashback query 추출

python /engn001/binlog2sql/binlog2sql/binlog2sql.py --user=root -p'qhdks123' \
--start-file mysql57-bin.000015 --start-position=291 --stop-position=871299  --flashback >flashback.sql

recovery SQL 반영

$ head -n 1 flashback.sql

UPDATE `test`.`binlog2sql` SET `tcol22`='perferendis quibusdam iure sed!', `tcol23`='Sco',`tcol20`='sit excepturi dolorum et sunt ullam reiciendis ex.', `tcol21`='magni omnis inventoreest placeat sapiente!', `tcol08`=4.27, `tcol09`='2020-03-28', `tcol24`='Eric', `tcol25`='a',`tcol04`=734647498, `tcol05`=2996241080926269364, `tcol06`=6.63104915618896, `tcol07`=4.244445,`tcol01`=8, `tcol02`=139, `tcol03`=393191, `tcol26`='blue', `id`=1000, `tcol27`=0, `tcol28`=0,`tcol19`='ad est voluptates qui omnis quisquam.', `tcol18`='quia odit nulla fugiat eum!',`tcol13`=2019, `tcol12`='14:48:23', `tcol11`='2019-07-03 20:08:31', `tcol10`='2019-08-0604:04:28', `tcol17`='sed sit est repellendus aut necessitatibus ullam explicabo.',`tcol16`='ducimus minima officia aspernatur sit deserunt quo rerum sit.', `tcol15`='Br',`tcol14`='ut aliquam ea aut tempore magnam nemo voluptatem laudantium.' WHERE`tcol22`='perferendis quibusdam iure sed!' AND `tcol23`='Sco' AND `tcol20`='sit excepturi dolorumet sunt ullam reiciendis ex.' AND `tcol21`='magni omnis inventore est placeat sapiente!' AND`tcol08`=4.27 AND `tcol09`='2020-03-28' AND `tcol24`='' AND `tcol25`='a' AND `tcol04`=734647498AND `tcol05`=2996241080926269364 AND `tcol06`=6.63104915618896 AND `tcol07`=4.244445 AND`tcol01`=8 AND `tcol02`=139 AND `tcol03`=393191 AND `tcol26`='blue' AND `id`=1000 AND `tcol27`=0AND `tcol28`=0 AND `tcol19`='ad est voluptates qui omnis quisquam.' AND `tcol18`='quia odit nullafugiat eum!' AND `tcol13`=2019 AND `tcol12`='14:48:23' AND `tcol11`='2020-03-28 10:01:13' AND`tcol10`='2019-08-06 04:04:28' AND `tcol17`='sed sit est repellendus aut necessitatibus ullamexplicabo.' AND `tcol16`='ducimus minima officia aspernatur sit deserunt quo rerum sit.' AND`tcol15`='Br' AND `tcol14`='ut aliquam ea aut tempore magnam nemo voluptatem laudantium.' LIMIT1; #start 4 end 871268 time 2020-03-28 10:01:13


mysql < flashback.sql

MySQL Flashback recovery#

binlog2sql#

install#

binary log 에서 SQL 추출#

Flashback PITR#

MySQL Flashback recovery

binlog2sql

install

binary log 에서 SQL 추출

Flashback PITR