【MySQL篇】mysqlbinlog 官方工具增量恢复实践：基于 Position/GTID 的恢复原理与踩坑总结

最新推荐文章于 2026-08-01 17:10:57 发布

原创最新推荐文章于 2026-08-01 17:10:57 发布 · 345 阅读

5 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

MySQL专栏

💫《博主主页》：
　　　🔎 CSDN主页： 奈斯DB
　　　🔎 IF Club社区主页： 奈斯、
🔥《擅长领域》：
　　　🗃️ 数据库：阿里云AnalyticDB(云原生分布式数据仓库)、Oracle、MySQL、SQLserver、NoSQL(Redis)
　　　🛠️ 运维平台与工具：Prometheus监控、DataX离线异构同步工具
💖如果觉得文章对你有所帮助，欢迎点赞收藏加关注💖

在这里插入图片描述

这篇文章讲解一下MySQL的增量恢复，聊之前，先提一下Oracle。Oracle的recover database using backup controlfile until cancel;确实省事且方便，一条命令下去，数据库自动以联机日志的最大SCN为终点，归档日志一追，恢复完事。但MySQL可没有（毕竟开源），想做到时间点、pos位置点增量恢复，只能靠mysqlbinlog一条条解析、手工定位、分段应用。🤷‍♂️

简要对比下两者差异：

维度	Oracle	MySQL
恢复命令	recover database 一键搞定	无内置命令，靠mysqlbinlog手动
恢复终点	自动以联机日志最大SCN为终点	需手动指定时间点或位点
归档处理	自动追溯并应用归档日志	需手动指定binlog文件列表及顺序
易用性	高，一条命令完成增量恢复	低，步骤多且易出错

Oracle 是把复杂留给自己，简单留给 DBA；MySQL 是把工具给你，细节自己把控。繁琐是真繁琐，但只要把原理吃透，一样能快速恢复。💪

好了，回到正题，下面就从博主踩坑的视角，聊聊MySQL增量恢复到底怎么做，以及那些文档里不会写的血泪教训。😂💥

恢复二进制日志官方文档（时间点（增量）恢复）：

9.5 Point-in-Time (Incremental) Recovery

官方文档对mysqlbinlog的介绍（8.0版本）：
6.6.9 mysqlbinlog — Utility for Processing Binary Log Files

介绍一下mysqlbinlog工具

mysqlbinlog是MySQL数据库提供的一个命令行工具，用于解析和显示MySQL二进制日志文件（binlog）的内容。这个工具可以用于查看二进制日志中的SQL语句，以及进行数据恢复、备份和复制等操作。通过使用mysqlbinlog命令来查看二进制日志文件的内容，然后根据需要进行相应的处理。
在使用mysqlbinlog工具之前，需要确保MySQL服务器已开启binlog功能。开启binlog功能后，就可以使用mysqlbinlog命令来查看、解析或处理binlog文件了。
小提示：二进制日志是记录mysql所有的DDL和DML(除了数据查询语句select）语句事件。用来记录数据库中发生的修改情况，数据的修改、表的创建及修改等。它既可以记录涉及修改的SQL，也可以记录数据修改的行变化记录，同时也记录了执行时间。类似于oracle的归档日志，二进制有可能会被重做日志替代。

mysqlbinlog工具参数详解：

[root@mgr1 ~]# mysqlbinlog --help  ---mysql官方提供的分析二进制日志工具

进行mysqlbinlog恢复时的注意事项：

注意事项一： 恢复二进制通过使用“|”（官方恢复命令）管道符连接mysqlbinlog命令和mysql命令进行数据恢复。不能使用“>”标准输出文件命令连接mysqlbinlog命令和mysql命令，使用“>”虽然没有报错但日志不会被应用。

案例：
1）刷新新的日志，然后创建表、插入数据、更新数据、删除表
mysql>
flush logs;

use liudbywcs_recover;

create table test_recover( 
`id` int(10) unsigned not null, 
`name` varchar(16) not null, 
`sex` enum('m','w') not null default 'm', 
`age` tinyint(3) unsigned not null 
) engine=innodb default charset=utf8; 

insert into test_recover(`id`,`name`,`sex`,`age`) values 
(1,'test01','w',21), 
(2,'test02','m',22), 
(3,'test03','w',23), 
(4,'test04','m',24), 
(5,'test05','w',25); 
commit;


update test_recover set name='test03_new' where id=3;
update test_recover set name='test04_new' where id=4;

drop table test_recover;

select * from test_recover;
2）将表恢复到drop table test_recover;之前的内容
因为刷新过一次日志，所以从create table到drop table的操作都在105日志中

3）对日志进行解析
[root@mgr1 binlog]# mysqlbinlog --base64-output=DECODE-ROWS --verbose --verbose liu-bin.000105
从create table的上一个end_log_pos点，也就是355点开始恢复，到drop table的上一个end_log_pos点，也就是2189点结束

4）开始恢复
验证一：使用“>”标准输出文件命令追二进制（虽然没有报错但日志不会被应用）：
[root@mgr1 binlog]# mysqlbinlog --skip-gtids --start-position=355 --stop-position=2189 liu-bin.000105 > mysql -uroot -p'123456' --socket=/liu_data/mysql8.0/data/3306/liu.sock  
虽然没有报错但日志不会被应用

验证二：使用“|”（官方恢复命令）管道符追二进制（日志成功被应用）：
[root@mgr1 binlog]# mysqlbinlog --skip-gtids --start-position=355 --stop-position=2189 liu-bin.000105 | mysql -uroot -p'123456' --socket=/liu_data/mysql8.0/data/3306/liu.sock
日志成功被应用

注意：官方文档介绍，除了使用“|”管道符连接mysqlbinlog命令和mysql命令进行数据恢复，还可以将多个日志写入单个SQL文件，然后处理该文件。但是测试了这种方式后发现如果实例gtid开启的情况下，也会出现二进制日志未应用的情况：
方式一：在mysqlbinlog中加上–skip-gtids参数
在多个日志写入单个SQL文件的时候加上–skip-gtids参数，那么日志中每个事务都会忽略关于GTID信息部分，这样就可以应用了（忽略每个事务的GTID部分的截图可以参考下面的(3)）。因为跳过gtid参数只能在mysqlbinlog工具上加，mysql工具是没有的。
方式二：reset master;
如果是新实例的话，可以执行reset master（清空所有二进制日志，重新开始新的二进制），该命令在清空所有二进制日志的同时也会重置show master status命令中的File日志序号、Position点、Executed_Gtid_Set执行的gtid。相当于gtid重新计数。

注意事项二： 恢复二进制使用mysqlbinlog命令时，输入mysqlbinlog --skip-gtids即可应用日志，使用mysqlbinlog --skip-gtids --base64-output=DECODE-ROWS --verbose --verbose反而不能应用日志，虽然没有报错但日志不会被应用。

案例：
1）刷新新的日志，然后创建表、插入数据、更新数据、删除表
mysql>
flush logs;

use liudbywcs_recover;

create table test_recover( 
`id` int(10) unsigned not null, 
`name` varchar(16) not null, 
`sex` enum('m','w') not null default 'm', 
`age` tinyint(3) unsigned not null 
) engine=innodb default charset=utf8; 

insert into test_recover(`id`,`name`,`sex`,`age`) values 
(1,'test01','w',21), 
(2,'test02','m',22), 
(3,'test03','w',23), 
(4,'test04','m',24), 
(5,'test05','w',25); 
commit;


update test_recover set name='test03_new' where id=3;
update test_recover set name='test04_new' where id=4;

drop table test_recover;

select * from test_recover;
2）将表恢复到drop table test_recover;之前的内容
因为刷新过一次日志，所以从create table到drop table的操作都在105日志中

3）对日志进行解析
[root@mgr1 binlog]# mysqlbinlog --base64-output=DECODE-ROWS --verbose --verbose liu-bin.000105
从create table的上一个end_log_pos点，也就是355点开始恢复，到drop table的上一个end_log_pos点，也就是2189点结束

4）开始恢复
验证一：mysqlbinlog --skip-gtids --base64-output=DECODE-ROWS --verbose --verbose不能应用日志：
[root@mgr1 binlog]# mysqlbinlog --skip-gtids --start-position=355 --stop-position=2189 liu-bin.000105 > mysql -uroot -p'123456' --socket=/liu_data/mysql8.0/data/3306/liu.sock  
应用了create table，但是DML操作都没有被应用，所以应用不全

验证二：mysqlbinlog --skip-gtids应用日志：
[root@mgr1 binlog]# mysqlbinlog --skip-gtids --start-position=355 --stop-position=2189 liu-bin.000105 | mysql -uroot -p'123456' --socket=/liu_data/mysql8.0/data/3306/liu.sock
日志成功被应用

注意事项三： 如果目标库开了gtid模式（gtid_mode=on和enforce_gtid_consistency=1），在对二进制恢复时会发生日志应用不了的情况，因为在gtid模式下二进制日志中每个事务在数据库是唯一的，而要恢复的二进制日志中gtid编号要比当前数据库的要旧或者都不是同一个gtid（server_uuid），所以就会出现二进制日志应用不了的情况，因为gtid（server_uuid）要是同一个并且只能向前推进，可以两种办法解决：

方式一：在mysqlbinlog中加上–skip-gtids参数
–skip-gtids参数：不要将二进制日志文件中的GTID包括在输出转储文件中，也就是忽略二进制日志中关于每个事务的GTID信息。
这种方式需要在mysqlbinlog工具每应用完成一个二进制日志后就需要查看Position点的变化（变化通过show master status;命令查看）
加上–skip-gtids参数后，通过对比工具对比解析成明文的二进制日志中每个事务都会忽略关于GTID信息部分（红框中的内容）
方式二：reset master;
如果是新实例的话，可以执行reset master（清空所有二进制日志，重新开始新的二进制），该命令在清空所有二进制日志的同时也会重置show master status命令中的File日志序号、Position点、Executed_Gtid_Set执行的gtid。相当于gtid重新计数。

注意事项四： mysqlbinlog命令支持多日志联合解析，没有像binlog2sql有–start-file和–stop-file参数，直接binlog.000001 binlog.000002即可。

使用mysqlbinlog恢复语法：

[root@mgr1 ~]# mysqlbinlog --stop-position=pos_point --stop-position=pos_point 二进制日志 | mysql -uroot -p'密码' 数据库名 --force
###恢复二进制通过使用“|”（官方恢复命令）管道符连接mysqlbinlog命令和mysql命令进行数据恢复
–force：强制继续，即使我们得到一个SQL错误。如果表存在（ERROR 1050 (42S01) at line 24: Table ‘qwe’ already exists）强制继续，提示的话忽略，因为强制了
mysqlbinlog支持多日志联合解析：
（1）通过wc -l（打印换行计数）可以看到每增加一个二进制日志换行计数就会增多。

（2）通过>输出文件可以看到每增加一个二进制日志输出的文件大小就会变大。

官方mysqlbinlog恢复案例一：误删表。测试机进行全库恢复+二进制日志（使用–start-position和–stop-position=，同样适用于–start-datetime=和–stop-datetime=）

方式一：备份+ mysqlbinlog追二进制恢复

源库：
一、对生产的MySQL实例进行mysqldump的定时全库备份和binlog日志的定时备份。参考“mysqldump、备份一致性保证”文档的案例：2、linux系统上制定mysqldump的定时全库备份和binlog日志的定时备份
mysqldump的定时全库备份：
在这里插入图片描述
binlog日志的定时备份：

二、插入几条测试数据，然后再执行一次binlog日志的定时备份
（1）插入测试数据

mysql> flush logs;          ---刷新（切换）BINARY、ENGINE、ERROR、GENERAL、RELAY、SLOW日志

mysql> 
use liudbywcs_recover;

create table test_recover( 
`id` int(10) unsigned not null, 
`name` varchar(16) not null, 
`sex` enum('m','w') not null default 'm', 
`age` tinyint(3) unsigned not null 
) engine=innodb default charset=utf8; 

insert into test_recover(`id`,`name`,`sex`,`age`) values 
(1,'test01','w',21), 
(2,'test02','m',22), 
(3,'test03','w',23), 
(4,'test04','m',24), 
(5,'test05','w',25); 
commit;

mysql> flush logs;        ---刷新（切换）BINARY、ENGINE、ERROR、GENERAL、RELAY、SLOW日志

（2）被删之前更新了表

mysql> update test_recover set name='test03_new' where id=3;
mysql> update test_recover set name='test04_new' where id=4;
mysql> commit;
mysql> select * from test_recover;

在这里插入图片描述

（3）drop表

mysql> drop table test_recover;

（4）binlog日志的定时备份：新增了93到98序号的binlog日志
在这里插入图片描述

目标库：
一、将源库的mysqldump的定时全库备份和binlog日志的定时备份传输到目标库上
在这里插入图片描述

二、创建一个新的MySQL实例

三、将备份的mysqldump全库备份导入新实例
（1）后台导入备份文件

[root@mysql ~]# nohup mysql -uroot -p123456 --socket=/mysql/data/3306/mysql.sock --force < mysqldump_full_3306.sql &

###全备导出时用户和权限也导出了（因为权限和用户都在mysql数据库中，备份的文件包括mysql数据库，所以导入mysql库会引起冲突，使用–force：强制继续）

（2）后台任务消失导入才算完成：

[root@mgr2 data]# ps -ef | grep force

在这里插入图片描述

（3）查看恢复过程的后台日志：

[root@mgr2 full]# tail -200f /mysql/backup/full/nohup.out

注意：
日志输出一：[Warning] Using a password on the command line interface can be insecure.告警提示在命令中使用了明文密码，所以导致的提示，可以忽略，如果有其他内容需要具体看报错分析原因
在这里插入图片描述

四、恢复权限和用户（方式一或方式二，任选一个）
方式一：导出默认数据库mysql中的权限（因为权限都在mysql数据库中）

方式一：导出默认数据库mysql中的权限（因为权限都在mysql数据库中）

###备份采用了–all-databases导出所有数据库。对于默认数据库只导出数据库mysql，其他三个库不导出
在这里插入图片描述

方式二：通过语句导出SQL，直接在恢复机上执行
在这里插入图片描述

[root@mgr2 full]# more mysql_exp_grants_3306_20240130.sql

在这里插入图片描述

五、通过mysqlbinlog恢复增量数据
（1）查看备份时记录的日志序号和position，那么就需要从记录的日志序号和position开始恢复数据

[root@mgr1 backup]# more mysqldump_full_3306.sql

在这里插入图片描述

（2）找出drop table test_recover;操作是在哪个二进制日志中操作的：

[root@mgr1 binlog]# mysqlbinlog --base64-output=DECODE-ROWS --verbose --verbose liu-bin.000088 | grep -i --color 'drop table'
[root@mgr1 binlog]# mysqlbinlog --base64-output=DECODE-ROWS --verbose --verbose liu-bin.000089 | grep -i --color 'drop table'
[root@mgr1 binlog]# mysqlbinlog --base64-output=DECODE-ROWS --verbose --verbose liu-bin.000090 | grep -i --color 'drop table'
[root@mgr1 binlog]# mysqlbinlog --base64-output=DECODE-ROWS --verbose --verbose liu-bin.000091 | grep -i --color 'drop table'
[root@mgr1 binlog]# mysqlbinlog --base64-output=DECODE-ROWS --verbose --verbose liu-bin.000092 | grep -i --color 'drop table'
[root@mgr1 binlog]# mysqlbinlog --base64-output=DECODE-ROWS --verbose --verbose liu-bin.000093 | grep -i --color 'drop table'
[root@mgr1 binlog]# mysqlbinlog --base64-output=DECODE-ROWS --verbose --verbose liu-bin.000094 | grep -i --color 'drop table'
[root@mgr1 binlog]# mysqlbinlog --base64-output=DECODE-ROWS --verbose --verbose liu-bin.000095 | grep -i --color 'drop table'
[root@mgr1 binlog]# mysqlbinlog --base64-output=DECODE-ROWS --verbose --verbose liu-bin.000096 | grep -i --color 'drop table'          ---找到drop table test_recover;在96号日志中

在这里插入图片描述

找到相关二进制日志后，通过mysqlbinlog找到drop之前的pos点进行恢复

[root@mgr2 binlog]# mysqlbinlog --base64-output=DECODE-ROWS --verbose --verbose liu-bin.000096 > drop.txt
[root@mgr2 binlog]# vi drop.txt

###时间点是250824 23:26:32，要恢复到上一个end_log_pos点，也就是1217点
在这里插入图片描述

（3）总结一下需要从哪里开始恢复，到哪里结束
那么就要从88号日志的276点开始（备份记录的点），到被drop表之前的96号日志的1217点中间的所有二进制日志
在这里插入图片描述

（5）通过mysqlbinlog进行二进制恢复
注意：应用二进制之前，需要先记录下当前写入的二进制日志文件名，因为可能会因为GITD点导致二进制日志未应用。

如果目标库开了gtid模式（gtid_mode=on和enforce_gtid_consistency=1），在对二进制恢复时会发生日志应用不了的情况，因为在gtid模式下二进制日志中每个事务在数据库是唯一的，而要恢复的二进制日志中gtid编号要比当前数据库的要旧或者都不是同一个gtid（server_uuid），所以就会出现二进制日志应用不了的情况，因为gtid（server_uuid）要是同一个并且只能向前推进，可以两种办法解决：

方式一：在mysqlbinlog中加上–skip-gtids参数
–skip-gtids参数：不要将二进制日志文件中的GTID包括在输出转储文件中，也就是忽略二进制日志中关于每个事务的GTID信息。
这种方式需要在mysqlbinlog工具每应用完成一个二进制日志后就需要查看Position点的变化（变化通过show master status;命令查看）
加上–skip-gtids参数后，通过对比工具对比解析成明文的二进制日志中每个事务都会忽略关于GTID信息部分（红框中的内容）

在这里插入图片描述

方式二：reset master;
如果是新实例的话，可以执行reset master（清空所有二进制日志，重新开始新的二进制），该命令在清空所有二进制日志的同时也会重置show master status命令中的File日志序号、Position点、Executed_Gtid_Set执行的gtid。相当于gtid重新计数。

通过mysqlbinlog应用二进制日志：

[root@mgr2 binlog]# mysqlbinlog --skip-gtids --start-position=276 liu-bin.000088 |  mysql -uroot -p'123456' --socket=/liu_data/mysql8.0/data/3306/liu.sock --force  
###从88号日志的276点开始（备份记录的点）开始恢复
mysql> show master status;

[root@mgr2 binlog]# mysqlbinlog --skip-gtids liu-bin.000089 liu-bin.000090 liu-bin.000091 liu-bin.000092 liu-bin.000093 liu-bin.000094 liu-bin.000095 | mysql -uroot -p'123456' --socket=/liu_data/mysql8.0/data/3306/liu.sock --force  
###mysqlbinlog命令支持多日志联合解析，直接binlog.000001 binlog.000002即可。为了更好的看每个日志被应用建议使用逐个日志应用，也可以考虑多日志联合应用
mysql> show master status;

[root@mysql1 binlog]# mysqlbinlog --skip-gtids --stop-position=1217 liu-bin.000096 | mysql -uroot -p'123456' --socket=/liu_data/mysql8.0/data/3306/liu.sock --force  
###最后一定要指定日志结束的pos点，因为在应用前面日志的时候，也会生成日志，那么要是应用了整个4号日志，数据会重复插入，亲测
mysql> show master status;

七、备份这个表，导入生产中

[root@mgr1 backup]# mysqldump -uroot -p123456 --set-gtid-purged=OFF --single-transaction --master-data=2 --flush-logs --routines --events --log-error=mysqldump_tb1_3306_err.log --tables liudbywcs_recover test_recover  --skip-add-locks  --socket=/mysql/data/3306/mysql.sock  > mysqldump_tb1_3306.sql

方式二：备份 + SQL文件（mysqlbinlog输出的文件）追二进制恢复
官方文档介绍，除了使用“|”管道符连接mysqlbinlog命令和mysql命令进行数据恢复，还可以将多个日志写入单个SQL文件，然后处理该文件。但是测试了这种方式后发现如果实例gtid开启的情况下，也会出现二进制日志未应用的情况：
方式一：在mysqlbinlog中加上–skip-gtids参数
在多个日志写入单个SQL文件的时候加上–skip-gtids参数，那么日志中每个事务都会忽略关于GTID信息部分，这样就可以应用了（忽略每个事务的GTID部分的截图可以参考下面的(3)）。因为跳过gtid参数只能在mysqlbinlog工具上加，mysql工具是没有的。
方式二：reset master;
如果是新实例的话，可以执行reset master（清空所有二进制日志，重新开始新的二进制），该命令在清空所有二进制日志的同时也会重置show master status命令中的File日志序号、Position点、Executed_Gtid_Set执行的gtid。相当于gtid重新计数。
在这里插入图片描述

mysql> select * from test_recover;

在这里插入图片描述

[root@mysql1 binlog]# mysqlbinlog --skip-gtids --start-position=276 liu-bin.000088 > statements.sql 
[root@mysql1 binlog]# mysqlbinlog --skip-gtids liu-bin.000089 liu-bin.000090 liu-bin.000091 liu-bin.000092 liu-bin.000093 liu-bin.000094 liu-bin.000095 >> statements.sql 
[root@mysql1 binlog]# mysqlbinlog --skip-gtids --stop-position=1217 liu-bin.000096 >> statements.sql 
###最后一定要指定日志结束的pos点，因为在应用前面日志的时候，也会生成日志，那么要是应用了整个日志，数据会重复插入，亲测

[root@mysql1 binlog]# mysql -u root -p123456 --socket=/liu_data/mysql8.0/data/3306/liu.sock -e "source statements.sql"
mysql> show master status;   
###mysqlbinlog命令支持多日志联合解析，直接binlog.000001 binlog.000002即可。为了更好的看每个日志被应用建议使用逐个日志应用，也可以考虑多日志联合应用