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 를 읽어서 처리하면,
높은 트래픽이나 부하와는 무관하게 테스트는 성공한다.
추가로 이슈가 있을 만한 케이스들을 기준으로 테스트를 진행한다.
- GTID Mode Test
- gtid mode 에서 --start-position option 으로는 binary log position 을 잡을 수 없을 듯
- --start-datetime option 을 통해 test 를 진행
- MySQL 8.0 Test
- 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 기능은 어느정도 호환이 된다.
하지만 크게 두가지 문제점이 있다.
- MySQL 에는 없는 "check_constraint_checks" variable 을 set 하려고 하는 점
- 꼭 해당 구문을 제거를 하고 import 를 해야한다.
- GTID Mode 일 경우에 GTID_NEXT 값을 발급 받지 못한다는 점
- flashback 을 위한 구문을 추출할 수는 있지만, 바로 적용은 안된다.
위 두 부분을 고려해서 구문만을 추출하여 적용하는 프로그램을 만들면 mysql flashback 기능 구현에는 무리가 없어보인다.
- MariaDB mysqlbinlog 를 이용하여 binary log formatting
- 커스텀 프로그램을 통해 구문 추출 후 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 |
+----+---------------------+------+------+------+------+
'MySQL > R&D' 카테고리의 다른 글
MySQL Large table drop issue 분석 (in source code) (0) | 2020.03.03 |
---|---|
MySQL Partition Key length 이슈 (in MySQL 5.7) (1) | 2020.02.12 |
MySQL JSON 이 BLOB, TEXT 에 비해 느린 이유 (JSON source 분석) (3) | 2020.01.22 |