巩固一下MySQL备份恢复相关知识，整理分享至此，希望对大家有帮助。

最近项目碰到备份恢复的相关的事项，结合自己的经验，巩固一下知识。

怎样理解备份恢复

MySQL使用环境中，基本都会搭建高可用架构最基本的主从，当主库发生故障导致无法使用的时，可以切换从节点提供服务。

那如果：

删除操作:DROP TABLE操作 ，在Row模式下，可以通过binlog进行恢复,那再如果DROP DATABASE,那就无法恢复了。在一次迁移升级过程中，bug导致数据库无法启动需要找回前两天的数据云平台全面瘫痪，虽然出现概率很小

这时可以通过之前备份+binglog进行恢复数据。

↓

备份的目的是发生灾难时进行恢复。

这里提供几个建议：

为了保证有效备份，需要考虑备份的扩展性以及用于备份有效性验证的服务器，还需要配合多种备份机制

建设统一的备份服务器，备份服务器仅从本地机房实例进行数据备份备份异常时，需要有相应的处理机制保障下一次备份能够正常进行逻辑 物理备份 镜像结合进行备份

↓

对于恢复，没有恢复的备份是无意义的，

所以需要

建设统一的恢复验证服务器，用于定期验证备份有效性通过定期恢复演练，确保备份的有效性由于不可能所有备份都通过实际还原的方式进行校验，使用文件MD5对比方式进行每日基础校验

备份恢复工具

MySQL方面各种备份手段：

备份恢复实现方式包含 物理 逻辑 商业软件 虚拟机的整体备份。

常用的备份工具有三个：

逻辑导出：mysqldump，msyqlpump，mydumper

物理导出：xtrabackup。

1.mysqldump 是 MySQL 自带的逻辑备份工具。

备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的SQL语句，当需要还原这些数据时，只要执行这些SQL语句，即可将对应的数据还原。

2.xtrabackup是一款mysql开源备份(物理备份)工具，是由percona公司开发的。

3.mydumper是一个针对MySQL和Drizzle的高性能多线程备份和恢复工具，能多线程进行备份。

1.数据量少于10G以内使用mydumper,mysqldump 进行备份，其他备份建议xtrabackup

2.除了以上场景单表备份，表结构等导出的时候，建议使用逻辑导出。

3.mysqldump是单线程 mydumper是多线程，性能来说mydumper更优。

性能方面：mysqlpump>mydumper>mysqldump 但mysqlpump存在一些bug

4.处置之外MySQL8.0的clone功能确实非常不错的功能，需要脚本配合，应该比xtrabackup优秀，就是业界使用经验太少，后期继续关注

备份策略:

对于不同的数据，可以采取不同的备份策略，结合全备和增量备份的方式，binlog也需要定期进行备份

以下是常用备份策略：

备份方面最佳实践:

使用xtrabackup进行物理备份使用mydumper进行逻辑备份（支持并行逻辑备份恢复）备份文件存储本地 或则 介质为NFS使用binlog2sql进行闪回恢复.对于重要系统，可以使用延迟从库进行备份恢复

条件允许，系统级别每天额外每天进行镜像备份，之后再做去重处理。

备份恢复注意

常见的备份失败问题：

生产案例：

1.xtrabackup备份和恢复的GTID不一致：5.7和8.0都会存在

备份恢复的实例显示已执行过的 GTID xtrabackup_binlog_info 文件记录的信息是 1-9969，

而备份文件显示的是 1-473将该备份恢复至一个新实例并启动该实例，执行 show master status; 查看信息：

分析

xtrabackup_binlog_info 文件中信息是有两种情况获取：全局系统变量 gtid_exected 或则 mysql.gtid_executed

这部分有兴趣的技术同仁，可以深入研究。

解决方案：

备份前 FLUSH LOGS；之后通过show master 和 select * from mysql.gtid_executed; 确认gtid 是否一致。

也可以忽略掉, 通过GTID_PURGED方式处理

2.sql导致失败的案例

堵塞语句kill（从开始执行FLUSH TABLES WITH READ LOCK):kill-long-query-type ，kill-long-queries-timeout长查询的语句：ftwrl-wait-query-type ，ftwrl-wait-timeout ，ftwrl-wait-thresholdno-lock：表示关闭FTWRL的表锁

3.DDL语句导致失败案例

InnoDB: Last flushed lsn: 3375345258517 load_index lsn 3379255303757

InnoDB: An optimized (without redo logging) DDLoperation has been performed. All modified pages may not have been flushed to the disk yet.

PXB will not be able take a consistent backup. Retry the backup operation

xtrabackup在备份innoDB数据是，有2种线程：redo拷贝线程和ibd数据拷贝线程。xtrabackup进程开始执行后，会启动一个redo拷贝的线程，用于从最新的checkpoint点开始顺序拷贝redo.log；再启动ibd数据拷贝线程，进行拷贝ibd数据。

但导致刷新redo 丢失的情况下，那备份就会失败

刷新大量数据，或则 redo刷新跟不上 导致刷新redo丢失的情况下，那备份就会失败

4.大量事务刷新日志&IO性能差

xtrabackup: error: log block numbers mismatch:

xtrabackup: error: expected log block no. 201901064, but got no. 208192508 from the log file.

xtrabackup: error: it looks like InnoDB log has wrapped around before xtrabackup could process all records due to either log copying being too slow, or log files being too small.

xtrabackup: Error: xtrabackup_copy_logfile() failed.

Innodb产生日志的速度远超于Xtrabackup复制的速度，部分Innodb日志被截断，导致备份失败。

5.空间不足

innobackupex: Error writing file '/tmp/xbtempevLQbf' (Errcode: 28 - No space left on device)

xtrabackup: Error: write to logfile failed

xtrabackup: Error: xtrabackup_copy_logfile() failed.

所以备份的时候需要确认空间，mysql的tmp空间

6.备份软件，处理机制不太友好

备份日志里已经报出错误，但xtrabackup线程一直存在。

failed to execute query SET SESSION lock_wait_timeout=31536000,MySQL server has gone away.

MySQL报gone away错误的常见因素

1、MySQL连接超时(受参数wait_timeout和interactive_timeout控制)

2、MySQL连接被KILL

3、MySQL实例重启

7.nfs挂载注意

从数据库服务器备份到NFS挂载，然后使用另一台同样挂载该卷的服务器来准备备份(应用日志)，那么其他服务器对数据的视图可能不一致。这是因为数据可能还没有被刷新到NFS服务器——它可能就在数据库服务器的缓存中。

8.恢复注意

mysql数据目录赋予权限必须清除原先的data目录和binglog信息，不清楚会导致无法正常启动 或则 启动之后的binlog和gtid不一致问题

总结

日常工作中数据备份是非常重要的，操作前做好备份，当碰到问题点的时候，能给你带来意想不到的便利。不要懒惰，通过提供的平台有效地利用多种备份手段，也非常重要

墨天轮原文链接：

作者：崔虎龙

专家专栏 | 崔虎龙 - 墨天轮

作者简介：云和恩墨开源MySQL技术顾问，长期服务于数据中心（银行，金融、游戏、物流）行业，熟悉数据中心运营管理的流程及规范、自动化运维等方面。擅长MySQL，Redis，MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自我学习通过了MySQL OCP5.6 和 MySQL OCP5.7认证证书。