基于 MySQL 8.0.35 GTID 主从环境，模拟主库误删数据后仍在持续写入的场景，使用两种方式进行基于位置点的恢复

• 主库：192.168.195.141（server-id=1）
• 恢复实例：192.168.195.142（server-id=2）
• XtraBackup版本：8.0.35-30

1. 环境信息

配置文件

主库 /etc/my.cnf（141）

[client] socket = /data/mysql/3306/data/mysql.sock [mysqld] basedir = /usr/local/mysql datadir = /data/mysql/3306/data user = mysql port = 3306 socket = /data/mysql/3306/data/mysql.sock log_error = /data/mysql/3306/data/mysqld.err log_timestamps = system log-bin = mysql-bin server-id = 1 gtid_mode = ON enforce_gtid_consistency = ON

恢复实例 /etc/my.cnf（142）

[client] socket = /data/mysql/3306/data/mysql.sock [mysqld] basedir = /usr/local/mysql datadir = /data/mysql/3306/data user = mysql port = 3306 socket = /data/mysql/3306/data/mysql.sock log_error = /data/mysql/3306/data/mysqld.err log_timestamps = system server-id = 2 gtid_mode = ON enforce_gtid_consistency = ON

2. 基于位置点恢复的原理

基于位置点的恢复（Point-in-Time Recovery, PITR），主要包含两步：

1. 恢复全量备份：将 Xtrabackup 全量备份恢复到新的空白实例上
2. 应用全量备份之后的 binlog 到指定位置点：通过 mysqlbinlog 或 START SLAVE UNTIL 方式回放 binlog 到误操作之前的位置

重要原则：恢复到新的空白实例上，不是直接恢复到线上出故障实例上。确认恢复无误后，再将数据从恢复实例导出导入到故障实例。

3. 安装 XtraBackup（在141主库上）

3.1 下载并解压

cd/usr/local/wgethttps://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.35-30/binary/tarball/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17.tar.gztarxzf percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17.tar.gzln-s/usr/local/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17 /usr/local/xtrabackup

说明：实际环境中XtraBackup已预先安装好，此处记录完整安装步骤供参考。

3.2 验证安装

# /usr/local/xtrabackup/bin/xtrabackup --versionxtrabackup version8.0.35-30 based on MySQL server8.0.35 Linux(x86_64)(revision id: 6beb4b49)

4. 创建备份用户（在141主库上执行）

CREATEUSER'backup_user'@'localhost'IDENTIFIEDBY'backup_pass';GRANTRELOAD,PROCESS,SHOWDATABASES,REPLICATIONCLIENT,SHOWVIEWON*.*TO'backup_user'@'localhost';GRANTBACKUP_ADMIN,SYSTEM_VARIABLES_ADMINON*.*TO'backup_user'@'localhost';GRANTSELECT,INSERT,CREATE,ALTERON`PERCONA_SCHEMA`.*TO'backup_user'@'localhost';GRANTSELECTON`mysql`.`component`TO'backup_user'@'localhost';GRANTSELECTON`performance_schema`.`keyring_component_status`TO'backup_user'@'localhost';GRANTSELECTON`performance_schema`.`log_status`TO'backup_user'@'localhost';GRANTSELECTON`performance_schema`.`replication_group_members`TO'backup_user'@'localhost';

5. 创建模拟数据并持续写入（在141主库上执行）

5.1 创建测试表

CREATEDATABASEsbtest;USEsbtest;CREATETABLEt1(idINTAUTO_INCREMENTPRIMARYKEY,insert_timeDATETIME(6));

字段说明：

• id：自增主键
• insert_time：插入时间，精度6位微秒，用于精确判断数据写入时间

5.2 创建持续写入脚本

# cat /tmp/insert_loop.sh#!/bin/bashi=1whiletrue;do/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -p'Root@123456'\-e"insert into sbtest.t1 (insert_time) values (now(6));"2>/dev/nullecho$i((i++))sleep0.1done

5.3 启动持续写入

# 在141主库上执行nohupsh/tmp/insert_loop.sh>/tmp/insert_loop.log2>&1&

6. 全量备份（在141主库上执行）

在 DROP 操作之前进行全量备份。

mkdir-p/data/backup/full xtrabackup--user=backup_user--password=backup_pass\--backup--parallel=10\--target-dir=/data/backup/full\--register-redo-log-consumer

–register-redo-log-consumer：注册redo log消费者，解决MySQL 8.0中redo log文件可能被覆盖的问题。

查看备份信息

# cat /data/backup/full/xtrabackup_binlog_infomysql-bin.00000319708ed8dab-8a22-11f1-8576-000c29048d1f:1-311

xtrabackup_binlog_info：记录备份完成时的 binlog 文件名、位置和 GTID 集合。这是恢复时确定 binlog 起始位置的依据。

# cat /data/backup/full/xtrabackup_checkpointsbackup_type=full-backuped from_lsn=0to_lsn=20361278last_lsn=20788428flushed_lsn=20767647redo_memory=0redo_frames=0

xtrabackup_checkpoints：记录 LSN 信息。to_lsn = 20361278表示备份完成时的数据一致性位置。

7. 模拟故障（在141主库上执行）

7.1 查看 DROP 前的数据状态

-- 在141主库上执行SELECTCOUNT(*)FROMsbtest.t1;-- +----------+-- | count(*) |-- +----------+-- | 784 |-- +----------+SELECT*FROMsbtest.t1ORDERBYinsert_timeDESCLIMIT1;-- +-----+----------------------------+-- | id | insert_time |-- +-----+----------------------------+-- | 784 | 2026-07-28 09:20:32.818621 |-- +-----+----------------------------+

7.2 模拟误删操作

-- 在141主库上执行DROPTABLEsbtest.t1;

7.3 模拟持续写入（其他业务不受影响）

误删 t1 表后，其他业务仍在持续写入。创建 t2 表模拟其他业务：

-- 在141主库上执行CREATETABLEsbtest.t2(idINTAUTO_INCREMENTPRIMARYKEY,insert_timeDATETIME(6));

启动 t2 表的持续写入脚本：

# cat /tmp/insert_t2_loop.sh#!/bin/bashi=1whiletrue;do/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -p'Root@123456'\-e"insert into sbtest.t2 (insert_time) values (now(6));"2>/dev/nullecho$i((i++))sleep0.1done# 启动nohupsh/tmp/insert_t2_loop.sh>/tmp/insert_t2_loop.log2>&1&

7.4 确认故障状态

-- 在141主库上执行SHOWTABLESFROMsbtest;-- Empty set -- t1已被DROP，t2尚未创建（如果已创建则显示t2）SHOWMASTERSTATUS;-- +---------------+----------+--------------+------------------+-------------------------------------------+-- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |-- +---------------+----------+--------------+------------------+-------------------------------------------+-- | mysql-bin.000003 | 142806 | | | 08ed8dab-8a22-11f1-8576-000c29048d1f:1-798 |-- +---------------+----------+--------------+------------------+-------------------------------------------+

GTID 798 就是 DROP TABLE 操作对应的事务。

8. 确定 DROP 操作对应的位置点（在141主库上执行）

这是基于位置点恢复的关键步骤，需要找到两个位置点：

1. start-position：备份集最后一个 GTID 对应事务的 COMMIT 位置点
2. stop-position：DROP 操作前一个事务的 COMMIT 位置点

8.1 查看备份对应的 binlog 位置点信息

# cat /data/backup/full/xtrabackup_binlog_infomysql-bin.00000319708ed8dab-8a22-11f1-8576-000c29048d1f:1-311

备份完成时的 GTID 集合为1-311，即最后一个事务的 GTID 是08ed8dab-8a22-11f1-8576-000c29048d1f:311。

8.2 确认恢复实例启动后的 GTID 状态

-- 在恢复实例（142）上执行（恢复备份后）SHOWMASTERSTATUS;-- +---------------+----------+--------------+------------------+-------------------------------------------+-- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |-- +---------------+----------+--------------+------------------+-------------------------------------------+-- | binlog.000001 | 157 | | | 08ed8dab-8a22-11f1-8576-000c29048d1f:1-311 |-- +---------------+----------+--------------+------------------+-------------------------------------------+

MySQL 8.0 注意事项：选择的位置点应该是实例启动后，最后一个 GTID 对应事务的 COMMIT 位置点，而不是 xtrabackup_binlog_info 中直接记录的 position 197（那是 binlog 文件头的位置）。

8.3 查找备份最后一个 GTID (311) 对应的 COMMIT 位置点

# 在141主库上执行mysqlbinlog-vv/data/mysql/3306/data/mysql-bin.000002|grep-A30"08ed8dab-8a22-11f1-8576-000c29048d1f:311"

输出（关键字段）：

SET @@SESSION.GTID_NEXT= '08ed8dab-8a22-11f1-8576-000c29048d1f:311'/*!*/; # at 90809 #260728 9:19:41 server id 1 end_log_pos 90892 CRC32 0xc0d06ffb Query thread_id=316 exec_time=0 error_code=0 SET TIMESTAMP=1785201581.319327/*!*/; BEGIN /*!*/; # at 90892 ... ### INSERT INTO `sbtest`.`t1` ### SET ### @1=298 /* INT meta=0 nullable=0 is_null=0 */ ### @2='2026-07-28 09:19:41.319327' /* DATETIME(6) meta=6 nullable=1 is_null=0 */ # at 90992 #260728 9:19:41 server id 1 end_log_pos 91023 CRC32 0x9475507e Xid = 969 COMMIT/*!*/; # at 91023 #260728 9:19:41 server id 1 end_log_pos 91070 CRC32 0xaeb81144 Rotate to mysql-bin.000003 pos: 4

91023是 GTID 311 对应事务 COMMIT 后的位置点。这就是–start-position的值。

8.4 查找 DROP 操作对应的位置点

# 在141主库上执行，逐个分析 binlog 找到 DROP TABLE 语句mysqlbinlog-vv/data/mysql/3306/data/mysql-bin.000003|grep-B30"DROP TABLE"

输出（关键字段）：

### INSERT INTO `sbtest`.`t1` ### SET ### @1=784 /* INT meta=0 nullable=0 is_null=0 */ ### @2='2026-07-28 09:20:32.818621' /* DATETIME(6) meta=6 nullable=1 is_null=0 */ # at 142564 #260728 9:20:32 server id 1 end_log_pos 142595 CRC32 0x4059dbfc Xid = 2449 COMMIT/*!*/; # at 142595 #260728 9:20:32 server id 1 end_log_pos 142672 CRC32 0x01475af6 GTID last_committed=486 sequence_number=487 rbr_only=no SET @@SESSION.GTID_NEXT= '08ed8dab-8a22-11f1-8576-000c29048d1f:798'/*!*/; # at 142672 #260728 9:20:32 server id 1 end_log_pos 142806 CRC32 0x8ac65208 Query thread_id=806 exec_time=0 error_code=0 Xid = 2452 SET TIMESTAMP=1785201632/*!*/; SET @@session.pseudo_thread_id=806/*!*/; DROP TABLE `sbtest`.`t1` /* generated by server */ /*!*/; # at 142806

关键位置点分析：

• 142595：DROP 前最后一个事务（GTID 797，INSERT id=784）的 COMMIT 位置点 →–stop-position
• 142672：DROP TABLE 语句的开始位置
• 142806：DROP TABLE 语句的结束位置
• GTID 798：DROP TABLE 对应的 GTID

8.5 位置点汇总

9. 方式一：Xtrabackup 备份 + mysqlbinlog 基于位置点恢复

9.1 恢复全量备份到新实例（在142恢复实例上执行）

(1) 将备份传到恢复实例

# 在141主库上执行scp-r/data/backup/full/* root@192.168.195.142:/data/backup/full/

(2) Prepare 备份

# 在142恢复实例上执行xtrabackup--prepare--target-dir=/data/backup/full

输出：

... 2026-07-28T09:23:56.486983+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 20788758 2026-07-28T09:23:57.488739+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

(3) 停止 MySQL，清空数据目录，恢复备份

# 在142恢复实例上执行systemctl stop mysqldrm-rf/data/mysql/3306/data/* xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/fullchown-Rmysql.mysql /data/mysql/3306/data/

注意：必须先清空数据目录，否则 copy-back 会报错。

(4) 清理残留的 binlog 文件（避免 GTID 冲突）

# 在142恢复实例上执行rm-f/data/mysql/3306/data/mysql-bin.000003rm-f/data/mysql/3306/data/mysql-bin.index

说明：备份集中可能包含主库的 binlog 文件，恢复后需要删除，让恢复实例启动时生成自己的 binlog。

(5) 启动恢复实例

# 在142恢复实例上执行systemctl start mysqld

(6) 验证恢复实例数据

-- 在142恢复实例上执行SELECTCOUNT(*)FROMsbtest.t1;-- +----------+-- | count(*) |-- +----------+-- | 298 |-- +----------+SELECT@@global.gtid_executed;-- 08ed8dab-8a22-11f1-8576-000c29048d1f:1-311

恢复到了备份时的 298 行，GTID 为 1-311。

9.2 拷贝主库 binlog 文件到恢复实例

# 在141主库上执行scp/data/mysql/3306/data/mysql-bin.000002 root@192.168.195.142:/data/backup/binlog/scp/data/mysql/3306/data/mysql-bin.000003 root@192.168.195.142:/data/backup/binlog/

说明：需要拷贝从备份位置点到 DROP 操作之间的所有 binlog 文件。

9.3 应用 binlog 到指定位置点

# 在142恢复实例上执行mysqlbinlog --start-position=91023--stop-position=142595\--skip-gtids\/data/backup/binlog/mysql-bin.000002\/data/backup/binlog/mysql-bin.000003\|mysql-uroot-S/data/mysql/3306/data/mysql.sock -p'Root@123456'

参数说明：

• --start-position=91023：从备份最后一个 GTID (311) 的 COMMIT 位置开始，对应第一个 binlog 文件（mysql-bin.000002）
• --stop-position=142595：到 DROP 前最后一个事务的 COMMIT 位置停止，对应最后一个 binlog 文件（mysql-bin.000003）
• --skip-gtids：跳过 GTID 检查，因为恢复实例已经执行了这些 GTID，不加此参数事务会被跳过
• 指定多个 binlog 时，--start-position针对第一个 binlog，--stop-position针对最后一个 binlog

9.4 验证恢复结果

-- 在142恢复实例上执行SELECTCOUNT(*)FROMsbtest.t1;-- +----------+-- | count(*) |-- +----------+-- | 784 |-- +----------+SELECT*FROMsbtest.t1ORDERBYinsert_timeDESCLIMIT5;-- +-----+----------------------------+-- | id | insert_time |-- +-----+----------------------------+-- | 784 | 2026-07-28 09:20:32.818621 |-- | 783 | 2026-07-28 09:20:32.712140 |-- | 782 | 2026-07-28 09:20:32.606528 |-- | 781 | 2026-07-28 09:20:32.500743 |-- | 780 | 2026-07-28 09:20:32.394990 |-- +-----+----------------------------+

恢复结果与 DROP 前完全一致：784 行，最新记录id=784, insert_time=2026-07-28 09:20:32.818621。✅

9.5 方式一优缺点

10. 方式二：Xtrabackup 备份 + START SLAVE UNTIL 基于位置点恢复

方式二的优势：使用复制线程回放 binlog，并行效率高，操作更简单，特别适合需要回放大量 binlog 的场景。

10.1 恢复全量备份到新实例

步骤与方式一相同（9.1节），此处不再重复。恢复后实例数据为 298 行，GTID 为 1-311。

10.2 配置指向主库的复制

-- 在142恢复实例上执行STOP SLAVE;CHANGE MASTERTOMASTER_HOST='192.168.195.141',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1,GET_MASTER_PUBLIC_KEY=1;

参数说明：

• MASTER_AUTO_POSITION=1：使用 GTID 自动定位，从库会告诉主库自己已执行了哪些 GTID（1-311），主库只发送缺失的事务（312及之后）
• GET_MASTER_PUBLIC_KEY=1：MySQL 8.0 默认使用 caching_sha2_password 认证插件，非 SSL 连接需获取公钥

10.3 使用 START SLAVE UNTIL SQL_BEFORE_GTIDS 恢复到指定 GTID

-- 在142恢复实例上执行STARTSLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS='08ed8dab-8a22-11f1-8576-000c29048d1f:798';STARTSLAVE IO_THREAD;

参数说明：

• SQL_BEFORE_GTIDS = 'UUID:798'：SQL 线程执行到 GTID 798之前停止，即执行完 GTID 797（DROP 前最后一个事务）后停止
• 先启动 SQL 线程设置 UNTIL 条件，再启动 IO 线程拉取 binlog

GTID 方式 vs 位置点方式：

• GTID 方式（推荐）：START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS = 'UUID:N'，直接指定 GTID，操作简单
• 位置点方式：START SLAVE UNTIL MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=142595，需要手动查找位置点

10.4 等待 SQL 线程执行到指定位置后自动停止

-- 在142恢复实例上执行，等待一段时间后检查SHOWSLAVESTATUS\G

输出（关键字段）：

Slave_IO_State: Waiting for source to send event Master_Host: 192.168.195.141 Master_User: repl Slave_IO_Running: Yes Slave_SQL_Running: No -- SQL线程已自动停止 Until_Condition: SQL_BEFORE_GTIDS -- 停止条件：GTID之前 Retrieved_Gtid_Set: 08ed8dab-8a22-11f1-8576-000c29048d1f:312-3102 Executed_Gtid_Set: 08ed8dab-8a22-11f1-8576-000c29048d1f:1-797 -- 执行到797（DROP前） Auto_Position: 1 Relay_Master_Log_File: mysql-bin.000003 Exec_Master_Log_Pos: 142595 -- 与方式一的stop-position一致

关键验证：

• Slave_SQL_Running: No：SQL 线程已自动停止
• Until_Condition: SQL_BEFORE_GTIDS：停止原因是达到了 GTID 条件
• Executed_Gtid_Set: 1-797：执行到了 DROP 前（GTID 798 之前）
• Exec_Master_Log_Pos: 142595：与方式一中的--stop-position=142595完全一致

10.5 验证恢复结果

-- 在142恢复实例上执行SELECTCOUNT(*)FROMsbtest.t1;-- +----------+-- | count(*) |-- +----------+-- | 784 |-- +----------+SELECT*FROMsbtest.t1ORDERBYinsert_timeDESCLIMIT5;-- +-----+----------------------------+-- | id | insert_time |-- +-----+----------------------------+-- | 784 | 2026-07-28 09:20:32.818621 |-- | 783 | 2026-07-28 09:20:32.712140 |-- | 782 | 2026-07-28 09:20:32.606528 |-- | 781 | 2026-07-28 09:20:32.500743 |-- | 780 | 2026-07-28 09:20:32.394990 |-- +-----+----------------------------+

恢复结果与 DROP 前完全一致：784 行，最新记录与方式一完全相同。✅

10.6 方式二优缺点

11. 两种方式对比

生产建议：

• 主库可用时，优先使用方式二（START SLAVE UNTIL），效率高、操作简单
• 主库不可用时，使用方式一（mysqlbinlog），可离线恢复
• 两种方式恢复结果完全一致，最终都恢复到 DROP 前最后一个事务的位置

12. 恢复后操作

确认恢复无误后，将数据从恢复实例导出，再导入到故障实例：

# 1. 从恢复实例导出误删表的数据mysqldump-uroot-S/data/mysql/3306/data/mysql.sock -p'Root@123456'\--single-transaction --set-gtid-purged=OFF\sbtest t1>/tmp/sbtest_t1_recover.sql# 2. 将导出文件传到故障实例scp/tmp/sbtest_t1_recover.sql root@192.168.195.141:/tmp/# 3. 在故障实例上导入数据mysql-uroot-S/data/mysql/3306/data/mysql.sock -p'Root@123456'\sbtest</tmp/sbtest_t1_recover.sql

注意：导入时使用--set-gtid-purged=OFF，避免 GTID 冲突。

13. 基于位置点的 START SLAVE UNTIL 语法参考

13.1 基于位置点（非GTID）

STARTSLAVE UNTIL MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=142595;

SQL 线程回放 binlog 到mysql-bin.000003的142595位置后停止。

13.2 基于 GTID（推荐）

STARTSLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS='08ed8dab-8a22-11f1-8576-000c29048d1f:798';

SQL 线程执行到 GTID798之前停止，即执行完797后停止。

STARTSLAVE SQL_THREAD UNTIL SQL_AFTER_GTIDS='08ed8dab-8a22-11f1-8576-000c29048d1f:797';

SQL 线程执行到 GTID797及之后停止，即执行完797后停止（与 SQL_BEFORE_GTIDS 效果相同）。

14. 关键注意事项

1. 恢复到新实例：必须恢复到新的空白实例上，不能直接恢复到故障实例
2. MySQL 8.0 的 start-position：应选择实例启动后最后一个 GTID 对应事务的 COMMIT 位置点，而非 xtrabackup_binlog_info 中直接记录的 position
3. –skip-gtids：方式一中必须使用此参数，否则恢复实例会因为 GTID 已存在而跳过事务
4. –start-position 和 --stop-position 的作用范围：指定多个 binlog 时，--start-position针对第一个 binlog，--stop-position针对最后一个 binlog
5. SQL_BEFORE_GTIDS vs SQL_AFTER_GTIDS：SQL_BEFORE_GTIDS = 'N'表示执行到 N 之前停止（执行完 N-1）；SQL_AFTER_GTIDS = 'N'表示执行完 N 后停止
6. 先启动 SQL 线程设置 UNTIL 条件：使用 START SLAVE UNTIL 时，先启动 SQL 线程（带 UNTIL 条件），再启动 IO 线程
7. 确认恢复无误后再导入：先在恢复实例上验证数据完整性，确认无误后再导出导入到故障实例

15. 连接信息

# 主库（141）/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -p'Root@123456'# 恢复实例（142）/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -p'Root@123456'