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提供了一种高效可靠的数据恢复方案。
1388
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
