MySQL 数据库回档方案

操作场景

对于自建数据库 MySQL，在误操作造成数据损坏时，进行数据修复相对来说是比较麻烦的。在公有云上的云数据 MySQL，基本上都会提供数据回档的功能，只需要在控制台简单操作即可。

这里参考了腾讯云数据库的回档方案，结合公司当数据库集群架构以及数据库备份方案制定了较为简单、安全的 MySQL 的回档方案：

此回档方案只支持对数据库或表进行回档操作，回档是基于数据备份 + 日志备份（binlog），可进行实时数据回档。

自建数据库 MySQL 回档通过定期全量物理热备（这里使用XtraBackup工具进行全备）和 binlog 日志重建，将数据库或表回档到指定时间，期间原有数据库或表的访问不受影响，回档操作会产生新的数据库或表至原实例中。回档完后，在原实例中可以看到原来的数据库或表，以及新建的数据库或表。

XtraBackup 工具使用请参考：《MySQL 备份与恢复》

功能原理

回档基于最近一次备份文件 + 对应的 binlog回档到指定时间点。

备份系统每天会从 MySQL 备机导出数据到备份系统。
回档时，首先需要新建一台回档实例，然后从备份系统导出备份数据并导入临时实例（根据回档方式导入不同数据）。
从回档后的实例中导出数据库，将导出的数据导入到 MySQL 原实例。

恢复步鄹

1.通过XtraBackup工具备份的全备文件中的 xtrabackup_binlog_info、xtrabackup_info 文件定位到备份结束的 binlog 文件以及 binlog position。
2.通过上面一个步鄹得到的binlog文件以及binlog position，对binlog文件进行解析，找出全备结束后对应的时间点。
3.在临时实例中恢复全量备份文件。（这里建议新安装一个与生产环境同版本的MySQL服务）
4.通过 binglog 进行增量恢复，以步鄹2的获取的时间点作为 start-time，开发需要恢复的时间点作为 stop-time 进行增量恢复。
5.导出临时实例中需要恢复的库的 mysqldump 文件。（此处通过 mysqldump 命令进行导出）
6.修改导出的 SQL 文件中的库名为 DBname_recovery，反复确认_
7.把上一步鄹得到的sql文件导入线上的mysql实例中，得到一个 DBname_recovery 的新库。
8.开发根据此库和当前的实例中的库来进行数据修复。

恢复实战

定位备份结束的时间点

获取XtraBackup备份结束后对应的binlog文件名：

# 提供两种获取方法
xtrabackup_end_binlog=`awk 'NR==1{print $1}' xtrabackup_binlog_info`
xtrabackup_end_binlog=`grep binlog_pos xtrabackup_info | grep -Po "(?<=filename ').*(?=', position)"`

获取XtraBackup备份结束后binglog中对应的 position：

# 提供两种获取方法
xtrabackup_end_pos=`awk 'NR==1{print $2}' xtrabackup_binlog_info`
xtrabackup_end_pos=`grep binlog_pos xtrabackup_info | grep -Po "(?<=, position ').*(?=', GTID of)"`

根据获得的binlog文件名和position，通过解析binlog日志定位出备份结束时刻的时间点：

$ mysqlbinlog --base64-output=decode-rows -vvv ./mysql-bin.000339 | grep -A1 '# at 18557859' | awk -F'[# ]+' 'NR>1{ print $2,$3}'

恢复全量备份到临时实例

解压全量备文件，并对备份进行 apply-log 操作：

$ innobackupex --apply-log /opt/mysql_full_backup/mysql-prod_full_2021-12-22_05-00-01/
......
211222 17:18:46 completed OK!     #看到类似的输出信息，表示--apply-log操作执行成功！

备份临时实例mysql的data目录，并清空data目录（先停止mysql实例）：

$ cp /data/server/mysql/data /data/server/mysql/data-bak
$ rm -rf /data/server/mysql/data

恢复数据，对备份目录执行–copy-back操作，把备份文件复制到之前清空的数据目录下：

$ innobackupex --defaults-file=/etc/my.cnf --copy-back /opt/mysql_full_backup/mysql-prod_full_2021-12-22_05-00-01/
......
211222 17:31:17 completed OK!     #看到类似的输出信息，表示--copy-back操作执行成功！

修改数据目录的属主、属组：

$ chown -R mysql:mysql /data/server/mysql/

启动数据库

$ systemctl start mysql.service

恢复增量数据到临时实例

增量数据恢复是基于binlog进行恢复，可以恢复到指定的任意时刻。

截取全备后的binlog偏移位至上午8点的binlog数据：

$ mysqlbinlog --skip-gtids --start-position=18557859 --stop-datetime='2021-12-22 08:00:00' /opt/mysql-data-recovery/mysql-bin.000339 > /tmp/incr_binlog.sql

注意：

在这里截取binlog内容时，起始位置使用的是从xtrabackup_info文件中获取的binlog偏移位，如果想使用此偏移位对应的时间作为起始位置，则需要根据这个position在binlog中定位到具体时间（对应的参数应该为 --start-datetime）。在文章上部分内容也有提及如何获取 position 对应的时间点。

进入mysql，导入此区间段的SQL数据：

# 方式一：在Linux系统命令行直接导入（推荐）
$ mysql < /tmp/incr_binlog.sql

# 方式二：进入数据库中执行source命令
> source /tmp/incr_binlog.sql;

恢复数据到原MySQL实例

先与开发沟通确认当前临时实例中的数据是否准确，然后对需要回档的数据库重命名为 DBname_recovery

注意：重命名可以根据需求更改，千万不要与线上MySQL实例中的数据库同名。

在临时MySQL实例中创建一个 DBname_recovery 的新库：

CREATE DATABASE DBname_recovery CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

重命名数据库，根据重命名表名来实现：

$ for table in `mysql -s -N -e "show tables from DBname;"`; do mysql -e "RENAME TABLE DBname.$table TO DBname_recovery.$table;"; done

导出重命名后的库：

$ mysqldump -E -R --set-gtid-purged=OFF --triggers --single-transaction --master-data=2 -B DBname_recovery > /tmp/DBname_recovery.sql

检查导出文件中的内容，确保 CREATE DATABASE ...; 和 USE ...; 两条SQL中的库名为 DBname_recovery ，避免在向原MySQL实例导入数据时造成原实例数据脏乱。

$ grep -E 'CREATE DATABASE |USE ' /tmp/DBname_recovery.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `DBname_recovery` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `DBname_recovery`;

向原MySQL实例导入回档数据：

$ mysql -u$DBUser -p$DBPasswd -h $DBHost -P$DBPort < ./DBname_recovery.sql

数据验证

待数据导入完成后，检查原 MySQL 实例是否成功导入需要恢复的数据。

之后的工作就交给开发来进行数据核对、修补等。

总结

早在多年前就有很多开源的 MySQL 数据闪回工具诞生，比如主流的 binlog2sql 、flashback等。这些工具主要用于直接在线上数据库进行数据恢复操作，这对维护人员的经验有一定要求，风险性较高。并且这些闪回工具有一定的局限性，比如仅支持 DML 闪回，如果要实现 DDL 闪回，则需要修改 MySQL 源码。

相对于这些闪回工具，本文的数据回档方法是在线下环境把回档好的数据导入线上MySQL，避免直接操作线上MySQL，风险性较低，并且对维护人员来说操作难度不会太高。因为是基于全量备份 + binlog 的方式，所以进行数据库恢复时，不用担心 DDL 等无法恢复的问题。

数据恢复所需时间参考：

全量备份 43GB，--copy-back 需要 6分钟 左右。

binlog 1GB，数据恢复需要 30分钟 左右

参考