💫《博主介绍》：✨又是一天没白过，我是奈斯，DBA一名✨
💫《擅长领域》：✌️擅长Oracle、MySQL、SQLserver、阿里云AnalyticDB for MySQL(分布式数据仓库)、Linux，也在扩展大数据方向的知识面✌️
💖💖💖大佬们都喜欢静静的看文章，并且也会默默的点赞收藏加关注💖💖💖
这周末北京的天气直接飙到34度，热得让人只想往家里躲！不过正好可以利用这炎炎夏日，宅在家里安心写博客，今天这篇文章作为PXB系列的第四篇，是一篇经典的实战恢复案例，特地拿来给各位小伙伴消暑解闷。众所周知， 单纯的xtrabackup物理备份只能将MySQL实例恢复到备份文件所对应的时间点 。若将全量定时备份设置的间隔时间过长，这时如果还没发起下一个全量备份，但是又想要上一份全量备份到现在时间的全部数据，如果没有备份二进制日志的话就只能恢复到备份文件所对应的时间点，那么就会丢失上一份备份文件之后到现在的所有数据。因此，MySQL在备份时必须同时保留二进制日志，以便我们能够使用mysqlbinlog工具追溯并恢复备份之间的数据变更。接下来将详细解析这一过程，领略如何通过xtrabackup与mysqlbinlog的完美结合，将MySQL实例精准地恢复到最新的时间点。
用一篇文章是不能将Percona XtraBackup工具讲明白的，所以我将理论、命令、备份策略、全库恢复、使用场景等分成五篇去介绍，即使分为五篇也有部分内容没有涵盖到，但是这五篇文章都是精华，掌握了之后就可以轻松应对Percona XtraBackup工具的相关日常工作了，五篇文章的内容分别如下：
- 第一篇：Percona XtraBackup物理备份工具的基础理论概述
- 第二篇：Percona XtraBackup工具备份指南：常用备份命令详解与实践
- 第三篇：Percona XtraBackup标准化全库完整备份策略
- 第四篇：Percona XtraBackup全量+mysqlbinlog增量完成实例的全库恢复（当前篇）
- 第五篇：物理克隆数据clone插件、逻辑备份工具mysqldump/mysqlpump和物理备份工具Percona XtraBackup这三种的区别和各自的使用场景总汇
目录
2、使用xtrabackup全量+mysqlbinlog增量完成实例的全库恢复
1、mysqlbinlog工具介绍：
在开始恢复之前先介绍mysqlbinlog工具，这样才能更好的开展恢复工作。
mysqlbinlog是MySQL数据库提供的一个命令行工具，用于 解析和显示MySQL二进制日志文件 （binlog）的内容。这个工具可以用于查看二进制日志中的SQL语句，以及进行数据恢复、备份和复制等操作。通过使用mysqlbinlog命令来查看二进制日志文件的内容，然后根据需要进行相应的处理。
在使用mysqlbinlog工具之前，需要确保MySQL服务器已开启binlog功能。开启binlog功能后，就可以使用mysqlbinlog命令来查看、解析或处理binlog文件了。
二进制日志是什么：
记录mysql所有的DDL和DML(除了数据查询语句select）语句事件。用来记录数据库中发生的修改情况，数据的修改、表的创建及修改等。它既可以记录涉及修改的SQL，也可以记录数据修改的行变化记录，同时也记录了执行时间。类似于oracle的归档日志，二进制有可能会被重做日志替代（自己的猜测哦😁）。
官方文档对mysqlbinlog的介绍（8.0版本）：
MySQL :: MySQL 8.0 Reference Manual :: 6.6.9 mysqlbinlog — Utility for Processing Binary Log Files
mysqlbinlog工具参数详解：
| 参数选项 | 描述 |
| -j, --start-position=# | 解码二进制日志从第一个事件的位置等于或大于参数。指定的是show master status;的Position列的值，一般start-position的值为154，因为新的二进制日志切换后就是154，二进制的头部信息。 |
| --stop-position=# | 在第一个位置大于或等于参数的事件中停止解码二进制日志 。指定的是show master status;的Position列的值，Position是多少就设置多少，表示当前二进制的日志最后的写入位置。Position的位置数值，同样也是文件的bytes字节大小 |
| --start-datetime=name | 二进制日志开始的时间点，格式：mysqlbinlog --start-datetime="2005-12-25 11:25:56" binlog.000003 |
| --stop-datetime=name | 二进制日志结束的时间点，格式：mysqlbinlog --stop-datetime="2005-12-26 11:25:56" binlog.000003 |
| --base64-output=name | 此选项确定何时应使用BINLOG语句将事件编码为base-64字符串进行显示。该选项具有以下三个允许值（不区分大小写）： AUTO：AUTO（“自动”）或UNSPEC（“未指定”）在必要时自动显示BINLOG语句（即用于格式描述事件和行事件）。如果没有给出--base64-output输出选项，效果与--base64-output=AUTO相同。 NEVER：NEVER导致不显示BINLOG语句。如果发现必须使用BINLOG显示的行事件，mysqlbinlog将退出并返回错误。 DECODE-ROWS：DECODE-ROWS通过指定--verbose选项，向mysqlbinlog指定要解码的行事件，并将其显示为注释SQL语句。与NEVER一样，DECODE-ROWS禁止显示BINLOG语句，但与NEVER不同的是，如果发现行事件，它不会退出并出现错误。 |
| -v, --verbose | 重构行事件，并将其显示为带注释的SQL语句，并在适用的情况下提供表分区信息。如果两次给定此选项（通过传递其中一个“-vv”或“--verbose --verbose”），则如果binlog_rows_query_log_events变量设置为TRUE，则输出包括指示列数据类型和某些元数据的注释，以及行查询日志事件等信息性日志事件。 |
| --bind-address=name | 远程主机的ip地址 |
| -d, --database=name | 指定数据库，不能指定表针对性恢复 |
| -o, --offset=# | 跳过前N行 |
| --skip-gtids | 不要将二进制日志文件中的GTID包括在输出转储文件中，也就是忽略二进制日志中关于每个事务的GTID信息。这个参数一般在是gtid模式下的同机上恢复二进制日志或者异机上恢复二进制日志应用不了的情况，因为在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恢复时的注意事项：
在进行数据恢复时Oracle数据库以其强大的recover功能而著称，通过recover database using backup controlfile until cancel;命令通知数据库以联机日志的最大scn为终点，能够高效地利用归档日志实现数据库的精确恢复。然而，相比之下，MySQL并没有内置类似Oracle recover database这样的自动恢复机制。对于MySQL而言，要达到时间点恢复的目的，通常需要 依赖mysqlbinlog工具，手动对二进制日志进行分析和恢复操作 。这种方式虽然相对繁琐，但在掌握了一定技巧后，依然能够实现精确且可靠的数据恢复。
以下是在进行mysqlbinlog恢复时需要注意的内容：
(1)恢复二进制通过使用“|”（官方恢复命令）管道符连接mysqlbinlog命令和mysql命令进行数据恢复。不能使用“>”标准输出文件命令连接mysqlbinlog命令和mysql命令，使用“>”虽然没有报错但日志不会被应用。
(2)恢复二进制使用mysqlbinlog命令时，输入mysqlbinlog --skip-gtids即可应用日志，使用mysqlbinlog --skip-gtids --base64-output=DECODE-ROWS --verbose --verbose反而不能应用日志，虽然没有报错但日志不会被应用。
(3)如果目标库开了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重新计数。
(4)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命令进行数据恢复
恢复二进制日志官方文档（时间点（增量）恢复）：
MySQL :: MySQL 8.0 Reference Manual :: 9.5 Point-in-Time (Incremental) Recovery
2、使用xtrabackup全量+mysqlbinlog增量完成实例的全库恢复
源库备份：
一、对生产的MySQL实例进行xtrabackup定时全库备份和binlog日志的定时备份。这里可以参考之前写的文章实现定时备份哦（直通车👉【MySQL篇】Percona XtraBackup标准化全库完整备份策略（第三篇，总共五篇）-CSDN博客👈）
Xtrabackup的定时全库备份：
binlog日志的定时备份：
二、插入几条测试数据，然后再执行一次binlog日志的定时备份
（1）插入测试数据
mysql> flush logs; ---刷新（切换）BINARY、ENGINE、ERROR、GENERAL、RELAY、SLOW日志 mysql> create table test07 (id int,name varchar(20)); insert into test07 values (1,'itpux01'); insert into test07 values (2,'itpux02'); insert into test07 values (3,'itpux03'); select * from test07; mysql> flush logs; ---刷新（切换）BINARY、ENGINE、ERROR、GENERAL、RELAY、SLOW日志
（2）binlog日志的定时备份：新增了111到114序号的binlog日志
目标库恢复：
一、将源库的xtrabackup的定时全库备份和binlog日志的定时备份传输到目标库上
二、创建一个新的MySQL实例，参数文件使用备份参数文件
这里就是初始化一个MySQL实例，初始化MySQL实例这步在这里就不做演示了，等以后会专门写一篇关于初始化MySQL的文章哦
三、将备份的xtrabackup全库备份导入新实例
（1）xtrabackup恢复时是需要确保实例处于打开状态的，查看新实例的数据文件路径
mysql> show variables like '%datadir%';
[root@mgr2 data]# rm -rf /mysql/data/3306/data/* ###在恢复之前需要先清空数据文件路径，如果数据文件路径下有文件在恢复时，会报错：Original data directory /mysql/data/3306/data is not empty!
（2）因为备份是采用的压缩备份，所以需要安装qpress-11-linux-x64.tar解压缩工具，并解压备份。关于qpress-11-linux-x64.tar的安装包自己在csdn尝试上传过，但是提示该资源已经在csdn存在了，所以不能进行上传了，所以需要的小伙伴在csdn上寻找吧
[root@mysql ~]# tar -xvf qpress-11-linux-x64.tar [root@mysql ~]# cp qpress /usr/bin/ [root@mysql ~]# qpress ---帮助 [root@mysql ~]# xtrabackup --decompress --use-memory=1G --parallel=2 --target-dir=/mysql/backup/full/xtrabackup_full_3306_20240726 ###1、--decompress：解压使用compress选项备份的文件，所有扩展名为.qp的文件 ###2、解压的备份还在同一个目录下，但原有的.qp文件不删除。在restore阶段只copy解压后的，解压前的.qp文件忽略，copy时不影响
（3）先recover恢复，到达数据一致性
[root@mysql ~]# xtrabackup --defaults-file=/mysql/data/3306/my.cnf --socket=/mysql/data/3306/mysql.sock --prepare --use-memory=1G --parallel=2 --target-dir=/mysql/backup/full/xtrabackup_full_3306_20240726 ###只是将数据达到一致性，不进行物理拷贝
（4）restore还原，将文件copy
[root@mysql ~]# xtrabackup --defaults-file=/mysql/data/3306/my.cnf --socket=/mysql/data/3306/mysql.sock --copy-back --use-memory=1G --parallel=2 --target-dir=/mysql/backup/full/xtrabackup_full_3306_20240726 ###通过参数文件的datadir参数，将数据文件拷贝到对应的数据目录下。不然就是按照备份时生成的backup-my.cnf进行数据目录恢复
四、验证文件，进行开库
[root@mysql1 data]# cd /mysql/data/3306/data/ [root@mysql1 data]# ll
（1）赋予数据文件权限：
[root@mysql1 data]# chown -R mysql:mysql /mysql/data/3306/data/* ###需要重新赋予数据目录下所有数据文件的属性，不然打开会报错：[ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
（2）因为是异库恢复，所以需要对undo、redo、共享表空间（ibdata）、临时表空间（ibtmp）进行检查（在oracle中这些都是在控制文件中定义的，恢复时就恢复了控制文件；而mysql没有控制文件这个概念，都是通过参数进行定义）
确定undo相关参数和数据文件路径下的实际情况：
###undo### innodb_rollback_segments = 128 ---128个段。5.7的innodb_undo_logs废除。 #innodb_undo_tablespaces = 3 ---在8.0中废除。5.7之前通过参数管理，8.0后可以创建undo表空间。如果使用此参数会报INNODB_UNDO_TABLESPACES设置已经弃用，不再使用。并且InnoDB总是创建2个undo表空间（即使通过参数指定了3个，也只会创建2个undo表空间），如果需要更多undo表空间，请使用CREATE UNDO TABLESPACE。 innodb_undo_log_truncate = 1 ---事务完是否回收undo。1表示允许自动回收段，同oracle innodb_max_undo_log_size = 2G ---undo最大大小
确定redo相关参数和数据文件路径下的实际情况：
###redo### innodb_log_file_size = 200M ---redo log大小，而oracle时直接创建文件 innodb_log_files_in_group = 2 ---redo log组个数 innodb_log_buffer_size = 16M ---redo log buffer大小，默认16M，而oracle由SGA自动控制 innodb_flush_log_at_trx_commit=1 ---5.7和8.0默认值1，该参数控制着redo log写入磁盘的过程。
共享表空间（ibdata）相关参数和数据文件路径下的实际情况：原有的数据文件实际的大小和在参数中设置的大小，大了不行，小了不行，必须相差在1M，不然报错
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
临时表空间（ibtmp）相关参数和数据文件路径下的实际情况：
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G
（3）开库。这里我采用的是将MySQL服务写入linux服务进行启动，如果是其他命令行方式等，可以参考之前写的文章哦，直通车👉MySQL篇—启动、关闭、重启linux下MySQL数据库的多种方式_mysql 重启-CSDN博客👈
[root@mysql1 data]# systemctl restart mysqld.service
五、通过mysqlbinlog恢复增量数据
（1）查看xtrabackup备份时的二进制日志和position点，那么就需要从记录的日志序号和position开始恢复数据
[root@mysql1 2021-07-24_01-02-11]# more xtrabackup_binlog_info ###记录当前备份时写入的二进制文件和position点和gtid点信息
（2）根据传输过来的最后一个binlog日志的position点确定最后需要恢复的--stop-postition位置（position点就是字节大小）
注意：要在恢复之前就要确定最后需要恢复的--stop-postition位置。因为在逻辑导入时就会产生二进制日志，所以一定要确定position点，不然数据重复（物理恢复时不会产生日志，所以可以先导入，然后确定position位置）
（3）总结一下需要从哪里开始恢复，到哪里结束
那么就要从111号日志的236点开始（备份记录的点），到传输过来的最后一个114号日志的288点中间的所有二进制日志
（4）通过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 --start-position=236 itpuxdb-binlog.000111 | mysql -uroot -p'123456' --socket=/mysql/data/3306/mysql.sock --force ###从111号日志的236点开始（备份记录的点）开始恢复 mysql> show master status; [root@mgr2 binlog]# mysqlbinlog itpuxdb-binlog.000112 itpuxdb-binlog.000113 | mysql -uroot -p'123456' --socket=/mysql/data/3306/mysql.sock --force ###mysqlbinlog命令支持多日志联合解析，直接binlog.000001 binlog.000002即可。为了更好的看每个日志被应用建议使用逐个日志应用，也可以考虑多日志联合应用 mysql> show master status; [root@mysql1 binlog]# mysqlbinlog --stop-position=288 itpuxdb-binlog.000114 | mysql -uroot -p'123456' --socket=/mysql/data/3306/mysql.sock --force ###最后一定要指定日志结束的pos点 mysql> show master status;
六、验证目标库与源库的数据、对象
第一步：查看用户以及权限
mysql> select host,user from mysql.user; mysql> show grants for root@'%';
第二步：验证数据库是否恢复和备份之后创建表的数据是否恢复
mysql> show databases; mysql> select * from test07;
第三步：验证对象数量
mysql> select * from sys.schema_object_overview where db='itpuxdb'; ---数据对象
mysql> select engine,count(*) from information_schema.tables group by engine; ---存储引擎分类
这篇文章到这里就结束了，既然各位集帅已经耐心阅读完这篇博客，何不顺手 点个赞、收藏起来，再加个关注呢？ 各位的支持是我持续创作的最大动力。
1626
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
