MySQL 数据库回档方案

操作场景

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

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

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

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

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

功能原理

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

mysql-data-archiving.png

  1. 备份系统每天会从 MySQL 备机导出数据到备份系统。
  2. 回档时,首先需要新建一台回档实例,然后从备份系统导出备份数据并导入临时实例(根据回档方式导入不同数据)。
  3. 从回档后的实例中导出数据库,将导出的数据导入到 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.sqlCREATE 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分钟 左右

参考

点赞

发表回复