GreatSQL MGR三節點基於時間點恢復
前言
本文將介紹DDL模擬誤操作數據庫後，怎麼恢復到誤操作時間點？
解決方案：利用binlog偽裝master實例（搭建偽主從複製環境），讓複製應用binlog停留在具體時間點對應的gtid上。
方案可以幫助客户在發生DDL事故時快速恢復數據到誤操作之前，避免進一步的損失。
文章分為三個階段：
- 自行準備一套GreatSQL MGR三節點集羣環境
- 使用clone提前物理備份一次用來後面恢復使用，集羣需要準備測試數據使用sysbench造數據，然後對數據庫誤操作DDL，再備份走binlog文件用於偽裝master。
- 數據恢復到誤操作DDL具體時間點對應的gtid上。
MGR組複製三節點環境介紹
| hostname | ip | port | role | version |
|---|---|---|---|---|
| zhangbei-node1 | 192.168.56.221 | 3001 | primary | GreatSQL-8.0.32-27 |
| zhangbei-node2 | 192.168.56.99 | 3001 | secondary | GreatSQL-8.0.32-27 |
| zhangbei-node3 | 192.168.56.6 | 3001 | secondary | GreatSQL-8.0.32-27 |
準備好MGR三節點集羣
以下是GreatSQL MGR三節點集羣結構信息
greatsql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | a4eadfd5-408e-11f0-abe0-00163ecf1759 | 192.168.56.221 | 3001 | ONLINE | PRIMARY | 8.0.32 | XCom |
| group_replication_applier | a8f6d0b9-408e-11f0-ac0f-00163ecf10b8 | 192.168.56.99 | 3001 | ONLINE | SECONDARY | 8.0.32 | XCom |
| group_replication_applier | a91ddcd1-408e-11f0-8ff1-00163efe4d00 | 192.168.56.6 | 3001 | ONLINE | SECONDARY | 8.0.32 | XCom |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)
創建testdb數據庫，用於後面sysbench讀寫
greatsql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.01 sec)
Clone備份數據庫實例
以下是clone備份MGR主節點 192.168.56.221:3001 實例到本地，用於後面臨時恢復出集羣的一個基礎實例。
$ mkdir -p /backup
$ chown greatsql:greatsql /backup
$ /usr/local/greatsql/bin/mysqld -S /tmp/greatsql3001.sock
greatsql> CLONE LOCAL DATA DIRECTORY='/backup/paxos3001';
Query OK, 0 rows affected (6.86 sec)
$ ll /backup/paxos3001/
total 1107340
drwxr-x--- 2 greatsql greatsql 4096 Jun 17 00:53 #clone
-rw-r----- 1 greatsql greatsql 6289 Jun 17 00:53 ib_buffer_pool
-rw-r----- 1 greatsql greatsql 1073741824 Jun 17 00:53 ibdata1
drwxr-x--- 2 greatsql greatsql 4096 Jun 17 00:53 #innodb_redo
drwxr-x--- 2 greatsql greatsql 4096 Jun 17 00:53 mysql
-rw-r----- 1 greatsql greatsql 26214400 Jun 17 00:53 mysql.ibd
drwxr-x--- 2 greatsql greatsql 4096 Jun 17 00:53 sys
-rw-r----- 1 greatsql greatsql 376832 Jun 17 00:53 sys_mac.ibd
-rw-r----- 1 greatsql greatsql 16777216 Jun 17 00:53 undo_001
-rw-r----- 1 greatsql greatsql 16777216 Jun 17 00:53 undo_002
sysbench準備數據
向Primary節點的testdb數據庫使用sysbench造一些數據，為了後續使用這部分測試數據誤操作和恢復。
$ sysbench /usr/local/share/sysbench/oltp_read_write.lua \
> --db-driver=mysql --mysql-host=192.168.56.221 --mysql-port=3001 --mysql-user=wanli --mysql-password=wanli \
> --mysql-db=testdb --tables=8 --table-size=10000 --create-secondary=on --report-interval=1 \
> --threads=8 --reconnect=0 --db-ps-mode=disable --skip_trx=off --events=2000000 --auto_inc=0 --time=600 \
> --mysql-ignore-errors=6002,6004,4012,2013,4016,1062,8532,8530,8551,8516 prepare
sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)
Initializing worker threads...
Creating table 'sbtest6'...Creating table 'sbtest2'...
Creating table 'sbtest3'...
Creating table 'sbtest5'...
Creating table 'sbtest4'...
Creating table 'sbtest8'...
Creating table 'sbtest1'...
Creating table 'sbtest7'...
Inserting 10000 records into 'sbtest7'
Inserting 10000 records into 'sbtest6'
Inserting 10000 records into 'sbtest4'
Inserting 10000 records into 'sbtest5'
Inserting 10000 records into 'sbtest1'
Inserting 10000 records into 'sbtest3'
Inserting 10000 records into 'sbtest2'
Inserting 10000 records into 'sbtest8'
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest8'...
誤操作數據庫
- 在sysbench繼續run壓測讀寫數據的模式下。
- 進行切割binog為了多產生一些binlog文件。
- 測試更新update埋點數據後，再刪除testdb數據庫，此時sysbench進程會報錯終止。
sysbench繼續壓測中
$ sysbench /usr/local/share/sysbench/oltp_read_write.lua \
> --db-driver=mysql --mysql-host=192.168.56.221 --mysql-port=3001 --mysql-user=wanli --mysql-password=wanli \
> --mysql-db=testdb --tables=8 --table-size=10000 --create-secondary=on --report-interval=1 \
> --threads=8 --reconnect=0 --db-ps-mode=disable --skip_trx=off --events=2000000 --auto_inc=0 --time=900 \
> --mysql-ignore-errors=6002,6004,4012,2013,4016,1062,8532,8530,8551,8516 run
sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 8
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 8 tps: 511.40 qps: 10379.45 (r/w/o: 7271.20/2077.48/1030.77) lat (ms,95%): 23.95 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 8 tps: 345.46 qps: 6775.94 (r/w/o: 4740.25/1349.78/685.90) lat (ms,95%): 20.74 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 8 tps: 307.88 qps: 6290.62 (r/w/o: 4406.33/1263.52/620.77) lat (ms,95%): 22.28 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 8 tps: 310.02 qps: 6147.36 (r/w/o: 4309.25/1218.07/620.04) lat (ms,95%): 22.28 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 8 tps: 284.11 qps: 5725.24 (r/w/o: 4008.57/1148.45/568.22) lat (ms,95%): 21.89 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 8 tps: 316.95 qps: 6239.03 (r/w/o: 4358.32/1246.81/633.90) lat (ms,95%): 18.95 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 8 tps: 312.99 qps: 6369.76 (r/w/o: 4460.83/1282.95/625.98) lat (ms,95%): 18.95 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 8 tps: 321.74 qps: 6363.85 (r/w/o: 4462.39/1260.98/640.48) lat (ms,95%): 19.65 err/s: 0.00 reconn/s: 0.00
切割binlog文件
greatsql> SHOW BINARY LOGS;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000003 | 193 | No |
| mysql-bin.000004 | 87720660 | No |
| mysql-bin.000005 | 7202697 | No |
+------------------+-----------+-----------+
3 rows in set (0.00 sec)
greatsql> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.06 sec)
greatsql> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.02 sec)
greatsql> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.01 sec)
greatsql> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.02 sec)
greatsql> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.01 sec)
greatsql> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.01 sec)
greatsql> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.02 sec)
greatsql> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.02 sec)
greatsql> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.02 sec)
greatsql> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.02 sec)
greatsql> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.02 sec)
greatsql> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.02 sec)
greatsql> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.01 sec)
greatsql> SHOW BINARY LOGS;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000003 | 193 | No |
| mysql-bin.000004 | 87720660 | No |
| mysql-bin.000005 | 9590028 | No |
| mysql-bin.000006 | 2642522 | No |
| mysql-bin.000007 | 735834 | No |
| mysql-bin.000008 | 3114129 | No |
| mysql-bin.000009 | 2595175 | No |
| mysql-bin.000010 | 4431921 | No |
| mysql-bin.000011 | 4323716 | No |
| mysql-bin.000012 | 10490537 | No |
| mysql-bin.000013 | 3813720 | No |
| mysql-bin.000014 | 4515287 | No |
| mysql-bin.000015 | 4463553 | No |
| mysql-bin.000016 | 4255894 | No |
| mysql-bin.000017 | 2667369 | No |
| mysql-bin.000018 | 1873005 | No |
+------------------+-----------+-----------+
16 rows in set (0.00 sec)
用update語句更新一條數據，來設置埋點數據
greatsql> USE testdb;
Database changed
greatsql> SELECT * FROM sbtest1 LIMIT 1;
+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 6462 | 01827431929-96493593496-34123137724-20587427608-00689345478-40151015374-92698484513-00365713924-30181341062-76715092993 | 22195207048-70116052123-74140395089-76317954521-98694025897 |
+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)
greatsql> SELECT now();begin;update sbtest1 SET c='wanli' WHERE id=1;commit;
+---------------------+
| now() |
+---------------------+
| 2025-06-17 01:08:02 |
+---------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
greatsql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2025-06-17 01:08:08 |
+---------------------+
1 row in set (0.00 sec)
此時進行誤操作。
greatsql> DROP DATABASE testdb;
Query OK, 8 rows affected (0.11 sec)
greatsql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2025-06-17 01:08:22 |
+---------------------+
1 row in set (0.01 sec)
greatsql> SHOW BINARY LOGS;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000003 | 193 | No |
| mysql-bin.000004 | 87720660 | No |
| mysql-bin.000005 | 9590028 | No |
| mysql-bin.000006 | 2642522 | No |
| mysql-bin.000007 | 735834 | No |
| mysql-bin.000008 | 3114129 | No |
| mysql-bin.000009 | 2595175 | No |
| mysql-bin.000010 | 4431921 | No |
| mysql-bin.000011 | 4323716 | No |
| mysql-bin.000012 | 10490537 | No |
| mysql-bin.000013 | 3813720 | No |
| mysql-bin.000014 | 4515287 | No |
| mysql-bin.000015 | 4463553 | No |
| mysql-bin.000016 | 4255894 | No |
| mysql-bin.000017 | 2667369 | No |
| mysql-bin.000018 | 163136954 | No |
+------------------+-----------+-----------+
16 rows in set (0.00 sec)
在testdb庫誤操作被刪除情況下，sysbench進程報錯終止。
現在需要恢復數據到時間點大概是誤操作時間 2025-06-17 01:08:08 左右，在這個時間點左右來確認binlog文件。
備份binlog
現在備份主節點binlog
greatsql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | a4eadfd5-408e-11f0-abe0-00163ecf1759 | 192.168.56.221 | 3001 | ONLINE | PRIMARY | 8.0.32 | XCom |
| group_replication_applier | a8f6d0b9-408e-11f0-ac0f-00163ecf10b8 | 192.168.56.99 | 3001 | ONLINE | SECONDARY | 8.0.32 | XCom |
| group_replication_applier | a91ddcd1-408e-11f0-8ff1-00163efe4d00 | 192.168.56.6 | 3001 | ONLINE | SECONDARY | 8.0.32 | XCom |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)
$ cd /data/paxos/paxos3001/logs/
$ ll -h mysql-bin.*
-rw-r----- 1 greatsql greatsql 193 Jun 17 00:36 mysql-bin.000003
-rw-r----- 1 greatsql greatsql 84M Jun 17 01:03 mysql-bin.000004
-rw-r----- 1 greatsql greatsql 9.2M Jun 17 01:03 mysql-bin.000005
-rw-r----- 1 greatsql greatsql 2.6M Jun 17 01:03 mysql-bin.000006
-rw-r----- 1 greatsql greatsql 719K Jun 17 01:03 mysql-bin.000007
-rw-r----- 1 greatsql greatsql 3.0M Jun 17 01:03 mysql-bin.000008
-rw-r----- 1 greatsql greatsql 2.5M Jun 17 01:03 mysql-bin.000009
-rw-r----- 1 greatsql greatsql 4.3M Jun 17 01:03 mysql-bin.000010
-rw-r----- 1 greatsql greatsql 4.2M Jun 17 01:04 mysql-bin.000011
-rw-r----- 1 greatsql greatsql 11M Jun 17 01:04 mysql-bin.000012
-rw-r----- 1 greatsql greatsql 3.7M Jun 17 01:04 mysql-bin.000013
-rw-r----- 1 greatsql greatsql 4.4M Jun 17 01:04 mysql-bin.000014
-rw-r----- 1 greatsql greatsql 4.3M Jun 17 01:04 mysql-bin.000015
-rw-r----- 1 greatsql greatsql 4.1M Jun 17 01:04 mysql-bin.000016
-rw-r----- 1 greatsql greatsql 2.6M Jun 17 01:04 mysql-bin.000017
-rw-r----- 1 greatsql greatsql 156M Jun 17 01:08 mysql-bin.000018
-rw-r----- 1 greatsql greatsql 704 Jun 17 01:04 mysql-bin.index
$ mkdir -p /backup/paxos-binlog
$ cp -a mysql-bin.* /backup/paxos-binlog/
$ ll /backup/paxos-binlog/
total 301316
-rw-r----- 1 greatsql greatsql 193 Jun 17 00:36 mysql-bin.000003
-rw-r----- 1 greatsql greatsql 87720660 Jun 17 01:03 mysql-bin.000004
-rw-r----- 1 greatsql greatsql 9590028 Jun 17 01:03 mysql-bin.000005
-rw-r----- 1 greatsql greatsql 2642522 Jun 17 01:03 mysql-bin.000006
-rw-r----- 1 greatsql greatsql 735834 Jun 17 01:03 mysql-bin.000007
-rw-r----- 1 greatsql greatsql 3114129 Jun 17 01:03 mysql-bin.000008
-rw-r----- 1 greatsql greatsql 2595175 Jun 17 01:03 mysql-bin.000009
-rw-r----- 1 greatsql greatsql 4431921 Jun 17 01:03 mysql-bin.000010
-rw-r----- 1 greatsql greatsql 4323716 Jun 17 01:04 mysql-bin.000011
-rw-r----- 1 greatsql greatsql 10490537 Jun 17 01:04 mysql-bin.000012
-rw-r----- 1 greatsql greatsql 3813720 Jun 17 01:04 mysql-bin.000013
-rw-r----- 1 greatsql greatsql 4515287 Jun 17 01:04 mysql-bin.000014
-rw-r----- 1 greatsql greatsql 4463553 Jun 17 01:04 mysql-bin.000015
-rw-r----- 1 greatsql greatsql 4255894 Jun 17 01:04 mysql-bin.000016
-rw-r----- 1 greatsql greatsql 2667369 Jun 17 01:04 mysql-bin.000017
-rw-r----- 1 greatsql greatsql 163136954 Jun 17 01:08 mysql-bin.000018
-rw-r----- 1 greatsql greatsql 704 Jun 17 01:04 mysql-bin.index
在此進行使用mysqlbinlog工具進行解析binlog文件，選擇這個binlog文件屬性時間和誤操作時間相對應，所以是mysql-bin.000018，通過解析binlog文件，搜索 drop database 關鍵詞，此時可以獲取這個DDL誤操作刪除數據庫的動作的gtid
$ /usr/local/greatsql/bin/mysqlbinlog --no-defaults /backup/paxos-binlog/mysql-bin.000018 |less
SET @@SESSION.GTID_NEXT= '3001aaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:122961'/*!*/;
恢復到誤操作刪除數據庫時間點之前
拉起之前克隆備份物理文件啓動一個實例，端口為3002
$ cd /backup/paxos3001/
$ cat my.cnf
[mysqld]
port = 3002
socket = /tmp/greatsql3002.sock
mysqlx = OFF
lower_case_table_names = 1
$ /usr/local/greatsql/bin/mysqld_safe --defaults-file=./my.cnf --datadir=./ --user=greatsql &
[1] 6402
mysqld_safe Adding '/opt/greatsql/GreatSQL-8.0.32-25-Linux-glibc2.17-x86_64/lib/mysql/libjemalloc.so.1' to LD_PRELOAD for mysqld
Logging to './zhangbei-node1.err'.
2025-06-16T17:23:30.966273Z mysqld_safe Starting mysqld daemon with databases from .
登錄
$ /usr/local/greatsql/bin/mysql -S /tmp/greatsql3002.sock
greatsql>
再準備一個數據庫單機實例，端口為3003
$ mkdir -p /data/paxos/greatsql3003/{data,logs,tmp}
$ cp /data/paxos/paxos3001/my3001.cnf /data/paxos/greatsql3003/my3003.cnf
$ sed -i 's/3001/3003/g' /data/paxos/greatsql3003/my3003.cnf
$ chown -R greatsql:greatsql /data/paxos/greatsql3003
$ sed -i 's#/data/paxos/paxos3003#/data/paxos/greatsql3003#g' /data/paxos/greatsql3003/my3003.cnf
$ /usr/local/greatsql/bin/mysqld --defaults-file=/data/paxos/greatsql3003/my3003.cnf --initialize-insecure
$ /usr/local/greatsql/bin/mysqld --defaults-file=/data/paxos/greatsql3003/my3003.cnf &
$ /usr/local/greatsql/bin/mysql -S /tmp/greatsql3003.sock
greatsql> CREATE USER 'repl'@'%' IDENTIFIED BY '123';
Query OK, 0 rows affected (10.02 sec)
greatsql> GREAT replication slave ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)
greatsql> RESET MASTER;
Query OK, 0 rows affected (0.01 sec)
greatsql> SHUTDOWN;
Query OK, 0 rows affected (0.00 sec)
將之前備份的binlog放到這個單機實例裏面作為臨時複製binlog的主庫
# 進到binlog目錄裏
$ cd greatsql3003/logs/
# 刪除歷史的binlog文件
$ \rm -rf mysql-bin.*
# 將之前備份的binlog文件拷貝過來
$ cp -a /backup/paxos-binlog/mysql-bin.* .
# 重新構建binlog的index索引文件
$ ls /data/paxos/greatsql3003/logs/mysql-bin.* > /data/paxos/greatsql3003/logs/mysql-bin.index
# 修改binlog屬主屬組權限
$ chown -R greatsql:greatsql /data/paxos/greatsql3003
# 最後啓動greatsql3003實例
$ /usr/local/greatsql/bin/mysqld --defaults-file=/data/paxos/greatsql3003/my3003.cnf &
[1] 7202
# 登錄greatsql3003實例
$ /usr/local/greatsql/bin/mysql -S /tmp/greatsql3003.sock
# 查看確認實例內可以看到備份的這些binlog
greatsql> SHOW BINARY LOGS;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000003 | 193 | No |
| mysql-bin.000004 | 87720660 | No |
| mysql-bin.000005 | 9590028 | No |
| mysql-bin.000006 | 2642522 | No |
| mysql-bin.000007 | 735834 | No |
| mysql-bin.000008 | 3114129 | No |
| mysql-bin.000009 | 2595175 | No |
| mysql-bin.000010 | 4431921 | No |
| mysql-bin.000011 | 4323716 | No |
| mysql-bin.000012 | 10490537 | No |
| mysql-bin.000013 | 3813720 | No |
| mysql-bin.000014 | 4515287 | No |
| mysql-bin.000015 | 4463553 | No |
| mysql-bin.000016 | 4255894 | No |
| mysql-bin.000017 | 2667369 | No |
| mysql-bin.000018 | 163136954 | No |
| mysql-bin.000019 | 193 | No |
+------------------+-----------+-----------+
17 rows in set (0.00 sec)
此時登錄greatsql3002實例，建立複製，去複製greatsql3003實例，並且複製的sql_thread線程需要停留到誤操作刪除DDL動作的gtid：'3001aaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:122961'
$ /usr/local/greatsql/bin/mysql -S /tmp/greatsql3002.sock
greatsql> CHANGE MASTER TO MASTER_HOST='192.168.56.221',
-> MASTER_PORT=3003,
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123',
-> master_auto_position=1,
-> get_master_public_key=1;
Query OK, 0 rows affected, 9 warnings (0.02 sec)
greatsql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.56.221
Master_User: repl
Master_Port: 3003
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: zhangbei-node1-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 157
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 3001aaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-12
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 1
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
greatsql> START SLAVE io_thread;
Query OK, 0 rows affected, 1 warning (0.01 sec)
greatsql> START SLAVE sql_thread until sql_before_gtids='3001aaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:122961';
Query OK, 0 rows affected, 1 warning (0.04 sec)
-- 以下複製還在追延遲
greatsql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.56.221
Master_User: repl
Master_Port: 3003
Connect_Retry: 60
Master_Log_File: mysql-bin.000019
Read_Master_Log_Pos: 193
Relay_Log_File: zhangbei-node1-relay-bin.000002
Relay_Log_Pos: 58259314
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 58260524
Relay_Log_Space: 308504005
Until_Condition: SQL_BEFORE_GTIDS
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 4653
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2213003
Master_UUID: 260a57ad-4ad9-11f0-904f-00163ecf1759
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Waiting for replica workers to process their queues
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 3001aaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:13-122961
Executed_Gtid_Set: 3001aaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-12269
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 1
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
-- 以下是複製延遲已經追完，並且sql_thread線程已經回放停止。
-- 並確認Executed_Gtid_Set信息應用到了: 3001aaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:122960，説明停留在了誤操作刪除gtid之前的上一個gtid.
greatsql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.56.221
Master_User: repl
Master_Port: 3003
Connect_Retry: 60
Master_Log_File: mysql-bin.000019
Read_Master_Log_Pos: 193
Relay_Log_File: zhangbei-node1-relay-bin.000030
Relay_Log_Pos: 163136976
Relay_Master_Log_File: mysql-bin.000018
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 163136768
Relay_Log_Space: 163137915
Until_Condition: SQL_BEFORE_GTIDS
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2213003
Master_UUID: 260a57ad-4ad9-11f0-904f-00163ecf1759
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 3001aaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:13-122961
Executed_Gtid_Set: 3001aaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-122960
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 1
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
檢查之前的埋點數據，testdb.sbtest1表，id字段為1.
greatsql> SHOW TABLES FROM testdb;
+------------------+
| Tables_in_testdb |
+------------------+
| sbtest1 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
+------------------+
8 rows in set (0.01 sec)
-- 此時看到買點數據
greatsql> SELECT * FROM testdb.sbtest1 WHERE id=1;
+----+------+-------+-------------------------------------------------------------+
| id | k | c | pad |
+----+------+-------+-------------------------------------------------------------+
| 1 | 6462 | wanli | 22195207048-70116052123-74140395089-76317954521-98694025897 |
+----+------+-------+-------------------------------------------------------------+
1 row in set (0.01 sec)
再將testdb庫備份邏輯導出
注意參數--set-gtid-purged=OFF，不備份記錄gtid。因為這些gtid在MGR集羣上已經被執行過。
$ /usr/local/greatsql/bin/mysqldump -S /tmp/greatsql3002.sock \
> --set-gtid-purged=OFF --single-transaction --source-data=2 \
> --max-allowed-packet=32M -B testdb > testdb.sql
恢復到MGR集羣主節點
$ time /usr/local/greatsql/bin/mysql -S /tmp/greatsql3001.sock -f < testdb.sql
real 10m5.107s
user 0m0.168s
sys 0m0.046s
等到誤操作刪除的數據恢復後，再次查看埋點數據
# 登錄MGR主節點
$ /usr/local/greatsql/bin/mysql -S /tmp/greatsql3001.sock testdb
greatsql> SELECT * FROM sbtest1 WHERE id=1;
+----+------+-------+-------------------------------------------------------------+
| id | k | c | pad |
+----+------+-------+-------------------------------------------------------------+
| 1 | 6462 | wanli | 22195207048-70116052123-74140395089-76317954521-98694025897 |
+----+------+-------+-------------------------------------------------------------+
1 row in set (0.00 sec)
greatsql> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| sbtest1 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
+------------------+
8 rows in set (0.01 sec)
總結
文章詳細介紹了一種利用binlog和GTID機制恢復誤操作數據庫的方法。當發生DDL誤操作（如誤刪數據庫）時，可以通過以下步驟快速恢復數據：首先使用clone備份創建基礎實例，然後通過解析binlog定位誤操作的GTID位置，接着搭建偽主從複製環境，使SQL線程精確停止在誤操作前。最後導出數據並恢復到原集羣。這種方法能精確恢復到指定時間點，避免數據丟失，特別適合生產環境中突發誤操作後的緊急恢復。整個過程充分利用了GreatSQL的binlog和複製功能，為DBA提供了一種高效可靠的數據恢復方案。
