点击参与 GreatSQL 8.0.32-26 文档捉虫~~~

GreatSQL社区

搜索

GreatSQL社区

好友数 0 | 博客数 377 | 回帖数 40 | 主题数 10

GreatSQL MGR 三节点基于时间点恢复

GreatSQL社区 已有 13 次阅读2025-12-12 11:08 |系统分类:运维实战

GreatSQL MGR 三节点基于时间点恢复

前言

本文将介绍 DDL 模拟误操作数据库后，怎么恢复到误操作时间点？

解决方案：利用 binlog 伪装 master 实例（搭建伪主从复制环境），让复制应用 binlog 停留在具体时间点对应的 gtid 上。

方案可以帮助客户在发生 DDL 事故时快速恢复数据到误操作之前，避免进一步的损失。

文章分为三个阶段：

  1. 自行准备一套 GreatSQL MGR 三节点集群环境
  2. 使用 clone 提前物理备份一次用来后面恢复使用，集群需要准备测试数据使用 sysbench 造数据，然后对数据库误操作 DDL，再备份走 binlog 文件用于伪装 master。
  3. 数据恢复到误操作 DDL 具体时间点对应的 gtid 上。

MGR 组复制三节点环境介绍

hostnameipportroleversion
zhangbei-node1192.168.56.2213001primaryGreatSQL-8.0.32-27
zhangbei-node2192.168.56.993001secondaryGreatSQL-8.0.32-27
zhangbei-node3192.168.56.63001secondaryGreatSQL-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'...

误操作数据库

  1. 在 sysbench 继续 run 压测读写数据的模式下。
  2. 进行切割 binog 为了多产生一些 binlog 文件。
  3. 测试更新 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 进程报错终止。

img

现在需要恢复数据到时间点大概是误操作时间 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'/*!*/;

img

恢复到误操作删除数据库时间点之前

拉起之前克隆备份物理文件启动一个实例，端口为 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 提供了一种高效可靠的数据恢复方案。


收藏 0 邀请 举报

全部作者的其他最新博客

评论 (0 个评论)

facelist

您需要登录后才可以评论 登录 | 立即注册

互动与交流
社区活动
论坛问答
加入社区
贡献者指南
学习与应用
文章博客
文档资料
视频资料
常见问题
发现社区
社区准则
建议反馈
社区之星

合作电话：010-64087828

社区邮箱：greatsql@greatdb.com

社区公众号
社区小助手
QQ群
友情链接
万里数据库开源中国墨天轮开放原子开源基金会OpenEuler
©2022 GreatSQL All Rights Reserved. ( 京ICP备06057874号 )
GMT+8, 2025-12-14 04:07 , Processed in 0.016120 second(s), 9 queries , Redis On.
返回顶部