MySQL 시점복구 빠르게 수행하는 팁
mysql에서 시점복구를 할 때 가장 흔히 사용하는 방법이 mysqlbinlog를 통해
binary log을 parsing 하고 이를 순차적으로 적용하는 방법인데요
이 방법은 single thread로 수행되기 때문에 그 속도가 굉장히 느릴 수 있습니다.
MariaDB에서에서 이러한 문제점을 피하기 위해 mysql replication의 SQL-thread을
사용한 방법을 공유한 적이 있습니다.
https://sarc.io/index.php/mariadb/1438-mariadb-point-in-time-recovery
mysql 과 mariadb는 gtid 사용방법이 조금 달라서 위의 방법도 조금 다른데요
이번 글에서는 mysql에서 수행하는 방법을 공유드리겠습니다.
필요 설정
gtid-mode = ON
enforce-gtid-consistency
replicate-same-server-id=1
=> 원래 replication은 node 간의 server-id 가 달라야하는데
이번 케이스는 자기자신에게 replication을 걸기 때문에 replicate-same-server-id=1 설정 필요
binary - relay log 간 동일한 position을 갖는 gtid 설정 필요
test 데이터 준비
mysql> show master status;
+------------------+----------+--------------+-----------------+----------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |Executed_Gtid_Set |
+------------------+----------+--------------+-----------------+----------------------------------------------+
| mysql-bin.000001 | 3420155 | | |e07a4297-48a5-11ea-81de-005056ac6a32:1-10000 |
+------------------+----------+--------------+-----------------+----------------------------------------------+
1 row in set (0.00 sec)
mysql> select count(*) from tb_test;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.01 sec)
backup
mysqldump -uadmin -p --set-gtid-purged=off --socket=/home1/irteam/db/mysql/tmp/mysql.sock --all-databases > bk.sql
=> –set-gtid-purged 옵션 사용은 상관없으나 set-gtid-purged=on 설정 시엔 아래와 같은 구문이 백업 덤프에 포함됨
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'e07a4297-48a5-11ea-81de-005056ac6a32:1-10000';
데이터 유실 시나리오
- delete 사고 발생
mysql> delete from tb_test;
Query OK, 10000 rows affected (0.03 sec)
- data 사고와 무관한 정상 데이터 유입
mysql> call insert_test(500);
Query OK, 0 rows affected (6.02 sec)
- delete gtid position 찾기
mysql> pager grep -A 1 -B 2 'tb_test' | grep -B 4 delete
PAGER set to 'grep -A 1 -B 2 'tb_test' | grep -B 4 delete'
mysql> show binlog events in 'mysql-bin.000001';
| mysql-bin.000001 | 3420034 | Write_rows | 7833 | 3420124 | table_id: 1100 flags:STMT_END_F |
--
| mysql-bin.000001 | 3420155 | Gtid | 7833 | 3420234 | SET @@SESSIONGTID_NEXT= 'e07a4297-48a5-11ea-81de-005056ac6a32:10001' |
| mysql-bin.000001 | 3420234 | Query | 7833 | 3420318 |BEGIN |
| mysql-bin.000001 | 3420318 | Query | 7833 | 3420416 | use `test3`; deletefrom tb_test |
50006 rows in set (0.08 sec)
=> 풀백업 본으로 1차 복구 + 데이터 유실 부분 skip + 그 이후의 데이터 2차 복구 하는 시나리오
self - replication 을 통한 시점 복구
- binary log 백업
[testuser@testserver 10:59:04 ~/db/mysql/data ]$ cp ./mysql-bin.000001 ../tmp/
- 풀백업본으로 데이터 유실 전 시점으로 복구
[testuser@testserver 10:59:04 ~/db/mysql/data ]$ mysql -uroot -p < bk.sql
mysql> select count(*) from test3.tb_test;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.00 sec)
- self-replication 설정
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
SET @@GLOBAL.GTID_PURGED='e07a4297-48a5-11ea-81de-005056ac6a32:1-10001';
=> 데이터 유실 부분 skip
mysql> SET GLOBAL SLAVE_PARALLEL_TYPE='LOGICAL_CLOCK';
mysql> SET GLOBAL SLAVE_PARALLEL_WORKERS=8;
mysql> change master to master_host='11.111.11.111', master_user='repl',master_password=''master_port=3306, master_auto_position=1;
[testuser@testserver 21:59:51 ~/db/mysql/data ]$ cp ../tmp/mysql-bin.000001 relay-bin.000001
[testuser@testserver 21:59:51 ~/db/mysql/data ]$ ls ./relay-bin.0*> relay-bin.index
mysql> start slave;
=> 자기자신에게 parallel replication 설정
- parallel replication 동작 확인
mysql> select * from performance_schema.replication_applier_status_by_worker\G;
*************************** 1. row ***************************
CHANNEL_NAME:
WORKER_ID: 1
THREAD_ID: 50001
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION:e07a4297-48a5-11ea-81de-005056ac6a32:510001
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2020-05-01 10:55:22.422507
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2020-05-01 10:55:22.422507
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2020-05-01 11:08:44.116971
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2020-05-01 11:08:44.117571
*************************** 2. row ***************************
CHANNEL_NAME:
WORKER_ID: 2
THREAD_ID: 50002
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION:e07a4297-48a5-11ea-81de-005056ac6a32:509160
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2020-05-01 10:55:21.911208
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2020-05-01 10:55:21.911208
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2020-05-01 11:08:43.725018
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2020-05-01 11:08:43.725531
*************************** 3. row ***************************
CHANNEL_NAME:
WORKER_ID: 3
THREAD_ID: 50003
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION:e07a4297-48a5-11ea-81de-005056ac6a32:161547
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2020-05-01 10:52:00.619125
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2020-05-01 10:52:00.619125
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2020-05-01 11:06:06.907250
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2020-05-01 11:06:06.907747
*************************** 4. row ***************************
CHANNEL_NAME:
WORKER_ID: 4
THREAD_ID: 50004
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION:
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
.
.
.
.
*************************** 5. row ***************************