基于 MySQL 8.0.35 GTID 主从环境模拟主库误删数据后仍在持续写入的场景使用两种方式进行基于位置点的恢复主库192.168.195.141server-id1恢复实例192.168.195.142server-id2XtraBackup版本8.0.35-301. 环境信息项目主库 (Master)恢复实例 (Recover)IP192.168.195.141192.168.195.142MySQL版本8.0.35二进制包glibc2.178.0.35二进制包glibc2.17server-id12gtid_modeONONserver_uuid08ed8dab-8a22-11f1-8576-000c29048d1f53c25061-8a23-11f1-92f5-000c29a59955配置文件主库 /etc/my.cnf141[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.cnf142[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 ON2. 基于位置点恢复的原理基于位置点的恢复Point-in-Time Recovery, PITR主要包含两步恢复全量备份将 Xtrabackup 全量备份恢复到新的空白实例上应用全量备份之后的 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主库上执行CREATEUSERbackup_userlocalhostIDENTIFIEDBYbackup_pass;GRANTRELOAD,PROCESS,SHOWDATABASES,REPLICATIONCLIENT,SHOWVIEWON*.*TObackup_userlocalhost;GRANTBACKUP_ADMIN,SYSTEM_VARIABLES_ADMINON*.*TObackup_userlocalhost;GRANTSELECT,INSERT,CREATE,ALTERONPERCONA_SCHEMA.*TObackup_userlocalhost;GRANTSELECTONmysql.componentTObackup_userlocalhost;GRANTSELECTONperformance_schema.keyring_component_statusTObackup_userlocalhost;GRANTSELECTONperformance_schema.log_statusTObackup_userlocalhost;GRANTSELECTONperformance_schema.replication_group_membersTObackup_userlocalhost;5. 创建模拟数据并持续写入在141主库上执行5.1 创建测试表CREATEDATABASEsbtest;USEsbtest;CREATETABLEt1(idINTAUTO_INCREMENTPRIMARYKEY,insert_timeDATETIME(6));字段说明id自增主键insert_time插入时间精度6位微秒用于精确判断数据写入时间5.2 创建持续写入脚本# cat /tmp/insert_loop.sh#!/bin/bashi1whiletrue;do/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456\-einsert into sbtest.t1 (insert_time) values (now(6));2/dev/nullecho$i((i))sleep0.1done5.3 启动持续写入# 在141主库上执行nohupsh/tmp/insert_loop.sh/tmp/insert_loop.log216. 全量备份在141主库上执行在 DROP 操作之前进行全量备份。mkdir-p/data/backup/full xtrabackup--userbackup_user--passwordbackup_pass\--backup--parallel10\--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-311xtrabackup_binlog_info记录备份完成时的 binlog 文件名、位置和 GTID 集合。这是恢复时确定 binlog 起始位置的依据。# cat /data/backup/full/xtrabackup_checkpointsbackup_typefull-backuped from_lsn0to_lsn20361278last_lsn20788428flushed_lsn20767647redo_memory0redo_frames0xtrabackup_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/bashi1whiletrue;do/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456\-einsert 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.log217.4 确认故障状态-- 在141主库上执行SHOWTABLESFROMsbtest;-- Empty set -- t1已被DROPt2尚未创建如果已创建则显示t2SHOWMASTERSTATUS;-- ------------------------------------------------------------------------------------------------------ | 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主库上执行这是基于位置点恢复的关键步骤需要找到两个位置点start-position备份集最后一个 GTID 对应事务的 COMMIT 位置点stop-positionDROP 操作前一个事务的 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-A3008ed8dab-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_id316 exec_time0 error_code0 SET TIMESTAMP1785201581.319327/*!*/; BEGIN /*!*/; # at 90892 ... ### INSERT INTO sbtest.t1 ### SET ### 1298 /* INT meta0 nullable0 is_null0 */ ### 22026-07-28 09:19:41.319327 /* DATETIME(6) meta6 nullable1 is_null0 */ # 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: 491023是 GTID 311 对应事务 COMMIT 后的位置点。这就是–start-position的值。8.4 查找 DROP 操作对应的位置点# 在141主库上执行逐个分析 binlog 找到 DROP TABLE 语句mysqlbinlog-vv/data/mysql/3306/data/mysql-bin.000003|grep-B30DROP TABLE输出关键字段### INSERT INTO sbtest.t1 ### SET ### 1784 /* INT meta0 nullable0 is_null0 */ ### 22026-07-28 09:20:32.818621 /* DATETIME(6) meta6 nullable1 is_null0 */ # 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_committed486 sequence_number487 rbr_onlyno 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_id806 exec_time0 error_code0 Xid 2452 SET TIMESTAMP1785201632/*!*/; SET session.pseudo_thread_id806/*!*/; DROP TABLE sbtest.t1 /* generated by server */ /*!*/; # at 142806关键位置点分析142595DROP 前最后一个事务GTID 797INSERT id784的 COMMIT 位置点 →–stop-position142672DROP TABLE 语句的开始位置142806DROP TABLE 语句的结束位置GTID 798DROP TABLE 对应的 GTID8.5 位置点汇总项目值备份GTID集合08ed8dab-8a22-11f1-8576-000c29048d1f:1-311备份最后一个事务的COMMIT位置mysql-bin.000002, position91023DROP TABLE的GTID08ed8dab-8a22-11f1-8576-000c29048d1f:798DROP前最后一个事务的COMMIT位置mysql-bin.000003, position1425959. 方式一Xtrabackup 备份 mysqlbinlog 基于位置点恢复9.1 恢复全量备份到新实例在142恢复实例上执行(1) 将备份传到恢复实例# 在141主库上执行scp-r/data/backup/full/* root192.168.195.142:/data/backup/full/(2) Prepare 备份# 在142恢复实例上执行xtrabackup--prepare--target-dir/data/backup/full输出... 2026-07-28T09:23:56.48698308:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 20788758 2026-07-28T09:23:57.48873908: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 |-- ----------SELECTglobal.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 root192.168.195.142:/data/backup/binlog/scp/data/mysql/3306/data/mysql-bin.000003 root192.168.195.142:/data/backup/binlog/说明需要拷贝从备份位置点到 DROP 操作之间的所有 binlog 文件。9.3 应用 binlog 到指定位置点# 在142恢复实例上执行mysqlbinlog --start-position91023--stop-position142595\--skip-gtids\/data/backup/binlog/mysql-bin.000002\/data/backup/binlog/mysql-bin.000003\|mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456参数说明--start-position91023从备份最后一个 GTID (311) 的 COMMIT 位置开始对应第一个 binlog 文件mysql-bin.000002--stop-position142595到 DROP 前最后一个事务的 COMMIT 位置停止对应最后一个 binlog 文件mysql-bin.000003--skip-gtids跳过 GTID 检查因为恢复实例已经执行了这些 GTID不加此参数事务会被跳过指定多个 binlog 时--start-position针对第一个 binlog--stop-position针对最后一个 binlog9.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 行最新记录id784, insert_time2026-07-28 09:20:32.818621。✅9.5 方式一优缺点优点缺点不依赖主库可离线恢复mysqlbinlog 应用是串行的效率慢适用于主库不可用的场景需要一个个 binlog 去判断找位置点操作复杂精确控制恢复位置需要回放的 binlog 很多时几十上百个速度很慢10. 方式二Xtrabackup 备份 START SLAVE UNTIL 基于位置点恢复方式二的优势使用复制线程回放 binlog并行效率高操作更简单特别适合需要回放大量 binlog 的场景。10.1 恢复全量备份到新实例步骤与方式一相同9.1节此处不再重复。恢复后实例数据为 298 行GTID 为 1-311。10.2 配置指向主库的复制-- 在142恢复实例上执行STOP SLAVE;CHANGE MASTERTOMASTER_HOST192.168.195.141,MASTER_USERrepl,MASTER_PASSWORD123456,MASTER_AUTO_POSITION1,GET_MASTER_PUBLIC_KEY1;参数说明MASTER_AUTO_POSITION1使用 GTID 自动定位从库会告诉主库自己已执行了哪些 GTID1-311主库只发送缺失的事务312及之后GET_MASTER_PUBLIC_KEY1MySQL 8.0 默认使用 caching_sha2_password 认证插件非 SSL 连接需获取公钥10.3 使用 START SLAVE UNTIL SQL_BEFORE_GTIDS 恢复到指定 GTID-- 在142恢复实例上执行STARTSLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS08ed8dab-8a22-11f1-8576-000c29048d1f:798;STARTSLAVE IO_THREAD;参数说明SQL_BEFORE_GTIDS UUID:798SQL 线程执行到 GTID 798之前停止即执行完 GTID 797DROP 前最后一个事务后停止先启动 SQL 线程设置 UNTIL 条件再启动 IO 线程拉取 binlogGTID 方式 vs 位置点方式GTID 方式推荐START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS UUID:N直接指定 GTID操作简单位置点方式START SLAVE UNTIL MASTER_LOG_FILEmysql-bin.000003, MASTER_LOG_POS142595需要手动查找位置点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 -- 执行到797DROP前 Auto_Position: 1 Relay_Master_Log_File: mysql-bin.000003 Exec_Master_Log_Pos: 142595 -- 与方式一的stop-position一致关键验证Slave_SQL_Running: NoSQL 线程已自动停止Until_Condition: SQL_BEFORE_GTIDS停止原因是达到了 GTID 条件Executed_Gtid_Set: 1-797执行到了 DROP 前GTID 798 之前Exec_Master_Log_Pos: 142595与方式一中的--stop-position142595完全一致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 方式二优缺点优点缺点使用复制线程回放 binlog并行效率高依赖主库在线可用GTID 方式操作简单无需手动查找位置点恢复期间主库需保持运行特别适合需要回放大量 binlog 的场景IO 线程持续拉取 binlog 占用网络带宽SQL_BEFORE_GTIDS直接指定 GTID精确可靠需要主库创建复制用户11. 两种方式对比对比项方式一mysqlbinlog方式二START SLAVE UNTIL回放方式mysqlbinlog 解析 mysql 串行回放复制线程并行回放回放效率慢串行逐事务回放快复制线程并行操作复杂度高需手动查找位置点、指定多个binlog低GTID方式直接指定GTID即可对主库依赖不依赖离线恢复依赖需主库在线适用场景主库不可用、需离线恢复主库可用、需快速恢复大量binlog回放非常慢快速GTID指定方式–start-position --stop-positionSQL_BEFORE_GTIDS恢复结果784行 ✅784行 ✅生产建议主库可用时优先使用方式二START SLAVE UNTIL效率高、操作简单主库不可用时使用方式一mysqlbinlog可离线恢复两种方式恢复结果完全一致最终都恢复到 DROP 前最后一个事务的位置12. 恢复后操作确认恢复无误后将数据从恢复实例导出再导入到故障实例# 1. 从恢复实例导出误删表的数据mysqldump-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456\--single-transaction --set-gtid-purgedOFF\sbtest t1/tmp/sbtest_t1_recover.sql# 2. 将导出文件传到故障实例scp/tmp/sbtest_t1_recover.sql root192.168.195.141:/tmp/# 3. 在故障实例上导入数据mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456\sbtest/tmp/sbtest_t1_recover.sql注意导入时使用--set-gtid-purgedOFF避免 GTID 冲突。13. 基于位置点的 START SLAVE UNTIL 语法参考13.1 基于位置点非GTIDSTARTSLAVE UNTIL MASTER_LOG_FILEmysql-bin.000003,MASTER_LOG_POS142595;SQL 线程回放 binlog 到mysql-bin.000003的142595位置后停止。13.2 基于 GTID推荐STARTSLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS08ed8dab-8a22-11f1-8576-000c29048d1f:798;SQL 线程执行到 GTID798之前停止即执行完797后停止。STARTSLAVE SQL_THREAD UNTIL SQL_AFTER_GTIDS08ed8dab-8a22-11f1-8576-000c29048d1f:797;SQL 线程执行到 GTID797及之后停止即执行完797后停止与 SQL_BEFORE_GTIDS 效果相同。14. 关键注意事项恢复到新实例必须恢复到新的空白实例上不能直接恢复到故障实例MySQL 8.0 的 start-position应选择实例启动后最后一个 GTID 对应事务的 COMMIT 位置点而非 xtrabackup_binlog_info 中直接记录的 position–skip-gtids方式一中必须使用此参数否则恢复实例会因为 GTID 已存在而跳过事务–start-position 和 --stop-position 的作用范围指定多个 binlog 时--start-position针对第一个 binlog--stop-position针对最后一个 binlogSQL_BEFORE_GTIDS vs SQL_AFTER_GTIDSSQL_BEFORE_GTIDS N表示执行到 N 之前停止执行完 N-1SQL_AFTER_GTIDS N表示执行完 N 后停止先启动 SQL 线程设置 UNTIL 条件使用 START SLAVE UNTIL 时先启动 SQL 线程带 UNTIL 条件再启动 IO 线程确认恢复无误后再导入先在恢复实例上验证数据完整性确认无误后再导出导入到故障实例15. 连接信息# 主库141/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456# 恢复实例142/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456
