본문 바로가기

MySQL/R&D

MySQL FlashBack (5.7 / 8.0)

1. MySQL Flashback

기본적으로 MySQL Community Version 에서는 FlashBack 을 지원하지 않는다.

 

해당 기능은 MariaDB 10.2.4 이상의 "mysqlbinlog" 바이너리 파일에 탑재된 기능이다.

이는 Binary Log 를 이용하여 FlashBack 을 구현하기때문에, MySQL Community Version 에서도 정상 작동한다.

 

차후 출시된 MariaDB 의 신규 버전에서는 DDL (DROP, TRUNCATE, ALTER 등) 에 대한 FlashBack 이 지원될 예정이나 현재 버전에서는 지원하지않는다.

 

또한 이는 MariaDB 에서만 사용될 것이라고 생각된다.

MariaDB 10.2.4 Version 이상에서는 Server Parameter 로 "--flashback"  옵션이 제공되는데,

MySQL Community Version 에는 해당 옵션이 존재하지않아 DDL 문들의 Flashback 을 지원하기가 어렵지않을까 싶다. (특히 drop 과 truncate 시에 데이터는..?)

 

$ ./mysqlbinlog
Ver 3.4 for Linux at x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Dumps a MariaDB binary log in a format usable for viewing or for piping to
the mysql command line client.
...
...
...
  -B, --flashback Flashback feature can rollback you committed data to a special time point.
...
...
...

 

 

2. 구현 방식

Binary Log 의 Row Image 통해 구현을 한다.

 

INSERT Event →  DELETE 

DELETE Event → INSERT

UPDATE Event → 전후 이미지 교환 ( row image 가 full 이므로 WHERE 절과 SET 절을 switching 하면 됨 )

 

 

 

3. 제약사항

1) binlog_row_image=FULL

Row Image 를 이용해 flashback 을 구현하므로, 해당 옵션을 꼭 사용해야한다.

해당 옵션을 사용하기때문에, 자연스럽게 binary log format 은 "ROW" 형태로 사용해야한다.

 

2) Memory 

"mysqlbinlog" 바이너리 파일 실행 시 "--flashback" ("-B" 옵션과 동일하다.) 옵션을 함께 사용하면,

Flashback Events 는 memory 상에 저장된다.

따라서 flashback 을 실행하는 서버의 충분한 메모리가 있는지 확인해야한다.

 

 

 

4.  Flashback Test

해당 기능은 MySQL 에 Binary Log 를 기준으로 움직인다.

따라서 MySQL 에서 Write 가 Success 되었다고 판단하고 있는 Binary Log 를 읽어서 처리하면, 

높은 트래픽이나 부하와는 무관하게 테스트는 성공한다.

 

추가로 이슈가 있을 만한 케이스들을 기준으로 테스트를 진행한다.

 

  1. GTID Mode Test
    1. gtid mode 에서 --start-position option 으로는 binary log position 을 잡을 수 없을 듯
    2. --start-datetime option 을 통해 test 를 진행
  2. MySQL 8.0 Test
    1. session variable 등 binlog 를 포맷팅 시에 적용되는 구분에 대한 영향도 확인

 

1) MySQL 5.7 - Non-GTID

a. UPDATE 실행 

mysql> select * from t1;
+----+---------------------+------+------+------+------+
| id | dt                  | c1   | c2   | i1   | i2   |
+----+---------------------+------+------+------+------+
|  1 | 2020-02-18 19:31:44 | a    | A    |    1 |    2 |
|  2 | 2020-02-18 19:32:22 | b    | B    |    3 |    4 |
|  3 | 2020-02-18 19:32:22 | c    | C    |    5 |    6 |
|  4 | 2020-02-18 19:32:22 | d    | D    |    7 |    8 |
|  5 | 2020-02-18 19:32:22 | e    | E    |    9 |   10 |
+----+---------------------+------+------+------+------+
5 rows in set (0.00 sec)


mysql> UPDATE t1
    -> SET id = 10
    -> WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from t1;
+----+---------------------+------+------+------+------+
| id | dt                  | c1   | c2   | i1   | i2   |
+----+---------------------+------+------+------+------+
|  2 | 2020-02-18 19:32:22 | b    | B    |    3 |    4 |
|  3 | 2020-02-18 19:32:22 | c    | C    |    5 |    6 |
|  4 | 2020-02-18 19:32:22 | d    | D    |    7 |    8 |
|  5 | 2020-02-18 19:32:22 | e    | E    |    9 |   10 |
| 10 | 2020-02-18 19:31:44 | a    | A    |    1 |    2 |
+----+---------------------+------+------+------+------+
5 rows in set (0.00 sec)

 

 

b. flashback 추출

$ ./mysqlbinlog mysql-bin.000009 -vv -d d1 -T t1 \
    --start-datetime="2020-02-18 20:05:00" --flashback > flashback.sql


$ vi flashback.sql
...
...
...
### UPDATE `d1`.`t1`
### WHERE
###   @1=10 /* INT meta=0 nullable=0 is_null=0 */
###   @2='2020-02-18 19:31:44' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @3='a' /* VARSTRING(44) meta=44 nullable=1 is_null=0 */
###   @4='A' /* VARSTRING(44) meta=44 nullable=1 is_null=0 */
###   @5=1 /* INT meta=0 nullable=1 is_null=0 */
###   @6=2 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='2020-02-18 19:31:44' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @3='a' /* VARSTRING(44) meta=44 nullable=1 is_null=0 */
###   @4='A' /* VARSTRING(44) meta=44 nullable=1 is_null=0 */
###   @5=1 /* INT meta=0 nullable=1 is_null=0 */
###   @6=2 /* INT meta=0 nullable=1 is_null=0 */
...
...
...

→ UPDATE Image 의 변환은 정상적으로 잘 된다.

 

 

c. flashback 적용

$ mysql -uroot -p1234 < flashback.sql
ERROR 1193 (HY000) at line 44: Unknown system variable 'check_constraint_checks'

→ 구문 추출은 정상적으로 되지만, 추출한 그대로 적용은 불가능하다.

 

이는 MariaDB 의 "mysqlbinlog" 바이너리 파일 사용 시 아래 라인의 구문들이 추가가 되기때문이다.

그 중 "check_constraint_checks" 옵션은 MariaDB 에만 존재하는 Parameter 이다.

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;

따라서 위 부분은 제거하고 진행한다.

 

d. check_constraint_checks 옵션 제거

$ ./mysqlbinlog mysql-bin.000009 -vv -d d1 -T t1     \ 
  --start-datetime="2020-02-18 20:30:00" --flashback > flashback.sql ; sed -i '/SET @@session.foreign_key_checks/d' ./flashback.sql

e. flashback 적용

$ mysql -uroot -p1234 < flashback.sql

$ mysql -uroot -p1234 -e "select * from d1.t1"
+----+---------------------+------+------+------+------+
| id | dt                  | c1   | c2   | i1   | i2   |
+----+---------------------+------+------+------+------+
|  1 | 2020-02-18 19:31:44 | a    | A    |    1 |    2 |
|  2 | 2020-02-18 19:32:22 | b    | B    |    3 |    4 |
|  3 | 2020-02-18 19:32:22 | c    | C    |    5 |    6 |
|  4 | 2020-02-18 19:32:22 | d    | D    |    7 |    8 |
|  5 | 2020-02-18 19:32:22 | e    | E    |    9 |   10 |
+----+---------------------+------+------+------+------+

→ FlashBack 이 정상적으로 적용된 것을 확인할 수 있다.

 

 

2) MySQL 5.7 - GTID

a. GTID 설정

mysql> set global gtid_mode=OFF_PERMISSIVE;
Query OK, 0 rows affected (0.01 sec)

mysql> set global gtid_mode=ON_PERMISSIVE;
Query OK, 0 rows affected (0.01 sec)

mysql> set global ENFORCE_GTID_CONSISTENCY=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set global gtid_mode=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> flush logs;

 

b. UPDATE 실행

mysql > select * from t1;
+----+---------------------+------+------+------+------+
| id | dt                  | c1   | c2   | i1   | i2   |
+----+---------------------+------+------+------+------+
|  1 | 2020-02-18 19:31:44 | a    | A    |    1 |    2 |
|  2 | 2020-02-18 19:32:22 | b    | B    |    3 |    4 |
|  3 | 2020-02-18 19:32:22 | c    | C    |    5 |    6 |
|  4 | 2020-02-18 19:32:22 | d    | D    |    7 |    8 |
|  5 | 2020-02-18 19:32:22 | e    | E    |    9 |   10 |
+----+---------------------+------+------+------+------+
5 rows in set (0.00 sec)


mysql> UPDATE t1 SET id = 30 WHERE id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from t1;
+----+---------------------+------+------+------+------+
| id | dt                  | c1   | c2   | i1   | i2   |
+----+---------------------+------+------+------+------+
|  1 | 2020-02-18 19:31:44 | a    | A    |    1 |    2 |
|  2 | 2020-02-18 19:32:22 | b    | B    |    3 |    4 |
|  4 | 2020-02-18 19:32:22 | d    | D    |    7 |    8 |
|  5 | 2020-02-18 19:32:22 | e    | E    |    9 |   10 |
| 30 | 2020-02-18 19:32:22 | c    | C    |    5 |    6 |
+----+---------------------+------+------+------+------+
5 rows in set (0.00 sec)

 

 c. flashback 추출

$ mysql-bin.000013 -vv -d d1 -T t1     --start-datetime="2020-02-18 20:45:00" --flashback > flashback.sql ; sed -i '/SET @@session.foreign_key_checks/d' ./flashback.sql


$ vi flashback.sql
...
...
...
### UPDATE `d1`.`t1`
### WHERE
###   @1=30 /* INT meta=0 nullable=0 is_null=0 */
###   @2='2020-02-18 19:32:22' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @3='c' /* VARSTRING(44) meta=44 nullable=1 is_null=0 */
###   @4='C' /* VARSTRING(44) meta=44 nullable=1 is_null=0 */
###   @5=5 /* INT meta=0 nullable=1 is_null=0 */
###   @6=6 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='2020-02-18 19:32:22' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @3='c' /* VARSTRING(44) meta=44 nullable=1 is_null=0 */
###   @4='C' /* VARSTRING(44) meta=44 nullable=1 is_null=0 */
###   @5=5 /* INT meta=0 nullable=1 is_null=0 */
###   @6=6 /* INT meta=0 nullable=1 is_null=0 */
...
...
...

 

d. flashback 적용

$ mysql -uroot -p1234 < flashback.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1782 (HY000) at line 18: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.

→ GTID Mode 에서는 Binary Log 의 형태를 정상적으로 바꾸긴하지만,

    Write 를 위한 GTID 를 발급 받지 않고, ANON 형태로 쿼리가 들어와서 불가능하다.

 

 

 

 

3) MySQL 8.0 - Non-GTID

a. UPDATE 실행 

mysql> select * from t1;
+----+---------------------+------+------+------+------+
| id | dt                  | c1   | c2   | i1   | i2   |
+----+---------------------+------+------+------+------+
|  1 | 2020-02-18 19:31:44 | a    | A    |    1 |    2 |
|  2 | 2020-02-18 19:32:22 | b    | B    |    3 |    4 |
|  3 | 2020-02-18 19:32:22 | c    | C    |    5 |    6 |
|  4 | 2020-02-18 19:32:22 | d    | D    |    7 |    8 |
|  5 | 2020-02-18 19:32:22 | e    | E    |    9 |   10 |
+----+---------------------+------+------+------+------+
5 rows in set (0.00 sec)


mysql> UPDATE t1
    -> SET id = 10
    -> WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from t1;
+----+---------------------+------+------+------+------+
| id | dt                  | c1   | c2   | i1   | i2   |
+----+---------------------+------+------+------+------+
|  2 | 2020-02-18 19:32:22 | b    | B    |    3 |    4 |
|  3 | 2020-02-18 19:32:22 | c    | C    |    5 |    6 |
|  4 | 2020-02-18 19:32:22 | d    | D    |    7 |    8 |
|  5 | 2020-02-18 19:32:22 | e    | E    |    9 |   10 |
| 10 | 2020-02-18 19:31:44 | a    | A    |    1 |    2 |
+----+---------------------+------+------+------+------+
5 rows in set (0.00 sec)

 

b. flashback 추출

$ mysql-bin.000013 -vv -d d1 -T t1     --start-datetime="2020-02-18 20:45:00" --flashback > flashback.sql ; sed -i '/SET @@session.foreign_key_checks/d' ./flashback.sql


$ vi flashback.sql
...
...
...
### UPDATE `d1`.`t1`
### WHERE
###   @1=1
###   @2='2020-02-17 23:24:00'
###   @3='a'
###   @4='A'
###   @5=1
###   @6=2
### SET
###   @1=10
###   @2='2020-02-17 23:24:00'
###   @3='a'
###   @4='A'
###   @5=1
###   @6=2
...
...
...

 

c. flashback 적용

$ mysql -uroot -p1234 < flashback.sql

$ mysql -uroot -p1234 -e "select * from d1.t1"
+----+---------------------+------+------+------+------+
| id | dt                  | c1   | c2   | i1   | i2   |
+----+---------------------+------+------+------+------+
|  1 | 2020-02-18 19:31:44 | a    | A    |    1 |    2 |
|  2 | 2020-02-18 19:32:22 | b    | B    |    3 |    4 |
|  3 | 2020-02-18 19:32:22 | c    | C    |    5 |    6 |
|  4 | 2020-02-18 19:32:22 | d    | D    |    7 |    8 |
|  5 | 2020-02-18 19:32:22 | e    | E    |    9 |   10 |
+----+---------------------+------+------+------+------+

→ FlashBack 이 정상적으로 적용된 것을 확인할 수 있다.

 

 

4) GTID Mode (8.0)

a. UPDATE 실행 

mysql> show global variables like 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+
1 row in set (0.00 sec)


mysql> select * from t1;
+----+---------------------+------+------+------+------+
| id | dt                  | c1   | c2   | i1   | i2   |
+----+---------------------+------+------+------+------+
|  1 | 2020-02-17 23:24:00 | a    | A    |    1 |    2 |
|  2 | 2020-02-17 23:24:10 | b    | B    |    3 |    4 |
|  3 | 2020-02-18 17:26:54 | c    | C    |    5 |    6 |
|  4 | 2020-02-18 17:27:00 | d    | D    |    7 |    8 |
|  5 | 2020-02-18 17:27:08 | e    | E    |    9 |   10 |
+----+---------------------+------+------+------+------+
5 rows in set (0.00 sec)


mysql> update t1 set id=6 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from t1;
+----+---------------------+------+------+------+------+
| id | dt                  | c1   | c2   | i1   | i2   |
+----+---------------------+------+------+------+------+
|  2 | 2020-02-17 23:24:10 | b    | B    |    3 |    4 |
|  3 | 2020-02-18 17:26:54 | c    | C    |    5 |    6 |
|  4 | 2020-02-18 17:27:00 | d    | D    |    7 |    8 |
|  5 | 2020-02-18 17:27:08 | e    | E    |    9 |   10 |
|  6 | 2020-02-17 23:24:00 | a    | A    |    1 |    2 |
+----+---------------------+------+------+------+------+
5 rows in set (0.00 sec)

 

b. flashback 추출

$ ./mysqlbinlog mysql-bin.000008 -vv -d d1 -T t1 \
    --start-datetime="2020-02-18 18:00:00" --flashback > flashback.sql


$ vi flashback.sql
...
...
...
### UPDATE `d1`.`t1`
### WHERE
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2='2020-02-17 23:24:00' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @3='a' /* VARSTRING(44) meta=44 nullable=1 is_null=0 */
###   @4='A' /* VARSTRING(44) meta=44 nullable=1 is_null=0 */
###   @5=1 /* INT meta=0 nullable=1 is_null=0 */
###   @6=2 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='2020-02-17 23:24:00' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @3='a' /* VARSTRING(44) meta=44 nullable=1 is_null=0 */
###   @4='A' /* VARSTRING(44) meta=44 nullable=1 is_null=0 */
###   @5=1 /* INT meta=0 nullable=1 is_null=0 */
###   @6=2 /* INT meta=0 nullable=1 is_null=0 */
...
...
...

 

c. flashback 적용

$ mysql -uroot -p1234 < flashback.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1782 (HY000) at line 19: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.

→  5.7 과 동일하게 gtid mode 에서는 구문 추출은 정상적으로 되지만, 추출한 그대로 적용은 불가능하다.

 

 

5) 테스트 결과

MariaDB 의 "mysqlbinlog" 바이너리 파일을 이용한 MySQL 의 Flashback 기능은 어느정도 호환이 된다.

 

하지만 크게 두가지 문제점이 있다.

 

  1. MySQL 에는 없는 "check_constraint_checks" variable 을 set 하려고 하는 점
    • 꼭 해당 구문을 제거를 하고 import 를 해야한다.
  2. GTID Mode 일 경우에 GTID_NEXT 값을 발급 받지 못한다는 점
    • flashback 을 위한 구문을 추출할 수는 있지만, 바로 적용은 안된다.

 

위 두 부분을 고려해서 구문만을 추출하여 적용하는 프로그램을 만들면 mysql flashback 기능 구현에는 무리가 없어보인다.

  1. MariaDB mysqlbinlog 를 이용하여 binary log formatting
  2. 커스텀 프로그램을 통해 구문 추출 후 MySQL 에 적용

 

 

6) 이슈 케이스 회피법 

a. check_constraint_checks

위의 테스트 케이스 같이 같이 해당 구문을 제거하면 된다.

 

b. GTID

적용이 되지 않는 근본적인 문제는 GTID_NEXT 값을 setting 하지 않아서이다.

따라서 강제로 GTID_NEXT 를 넣어서 회피할 수 있다.

 

  • UPDATE 적용
mysql> select * from t1;
+----+---------------------+------+------+------+------+
| id | dt                  | c1   | c2   | i1   | i2   |
+----+---------------------+------+------+------+------+
|  1 | 2020-02-17 23:24:00 | a    | A    |    1 |    2 |
|  2 | 2020-02-17 23:24:10 | b    | B    |    3 |    4 |
|  3 | 2020-02-18 17:26:54 | c    | C    |    5 |    6 |
|  4 | 2020-02-18 17:27:00 | d    | D    |    7 |    8 |
|  5 | 2020-02-18 17:27:08 | e    | E    |    9 |   10 |
+----+---------------------+------+------+------+------+
5 rows in set (0.00 sec)

mysql> update t1 set id=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+----+---------------------+------+------+------+------+
| id | dt                  | c1   | c2   | i1   | i2   |
+----+---------------------+------+------+------+------+
|  2 | 2020-02-17 23:24:10 | b    | B    |    3 |    4 |
|  3 | 2020-02-18 17:26:54 | c    | C    |    5 |    6 |
|  4 | 2020-02-18 17:27:00 | d    | D    |    7 |    8 |
|  5 | 2020-02-18 17:27:08 | e    | E    |    9 |   10 |
| 10 | 2020-02-17 23:24:00 | a    | A    |    1 |    2 |
+----+---------------------+------+------+------+------+
5 rows in set (0.00 sec)

 

  • flashback 적용
$ ./mysqlbinlog ./mysql-bin.000022 -vv -d d1 -T t1 --start-datetime="2020-02-19 14:30:00" --flashback > flashback.sql

$ cat ./flashback.sql | awk 'BEGIN {"uuidgen -t" |& getline u} /^BEGIN/ {c += 1 ; print "SET @@SESSION.GTID_NEXT= \x27" u ":" c "\x27/*!*/;"} {print}' | sed -e s/', @@session.check_constraint_checks=1//g' > uzi.sql

$ mysql -uroot -p < ./uzi.sql

$ mysql -uroot -p1234 -e "select * from d1.t1";
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+---------------------+------+------+------+------+
| id | dt                  | c1   | c2   | i1   | i2   |
+----+---------------------+------+------+------+------+
|  1 | 2020-02-17 23:24:00 | a    | A    |    1 |    2 |
|  2 | 2020-02-17 23:24:10 | b    | B    |    3 |    4 |
|  3 | 2020-02-18 17:26:54 | c    | C    |    5 |    6 |
|  4 | 2020-02-18 17:27:00 | d    | D    |    7 |    8 |
|  5 | 2020-02-18 17:27:08 | e    | E    |    9 |   10 |
+----+---------------------+------+------+------+------+