基于MySQL数据库备份恢复工具的研究与实践

数据库备份是指对数据库中存储的业务表数据、数据库元数据等电子数据通过外部文件进行存储的技术；数据库恢复是指通过外部文件，对数据库中丢失的电子数据进行抢救和恢复的技术。

在金融科技领域，系统对数据的可靠性要求极为严苛，如果发生意外的宕机与数据丢失其损失会非常严重。因此，系统需要针对业务的具体需求制定相应的数据库备份与恢复策略，只有这样才可以保证系统数据的安全性与可用性。

本文介绍了MySQL数据库的几种常用的数据库备份与恢复工具，对其使用方式、使用场景进行了详细的阐述。随后根据行内对MySQL数据库的相关技术要求，本文详细地介绍了数据库备份与恢复工具在行内现有的MySQL数据库技术体系下的实践。

关键字： MySQL、xtrabackup、数据库备份、数据库恢复

本文重点讨论在行内技术规范下，MySQL数据库场景下的备份与恢复操作。行内MySQL数据库的备份与恢复的示意图如图1-1所示：

虽然数据库备份与恢复往往在整个系统的开发、测试与应用当中所占的工作量较少，但数据库备份与恢复是数据库使用过程中不可忽视的重要环节。其原因主要有两点：

1.在生产环境下，数据的安全性至关重要，任何形式的数据丢失都可能导致非常严重的生产事故。导致数据库中数据丢失的原因可能是程序错误、人为操作错误、磁盘故障、不可抗灾难（如火灾、地震）等。

在数据发生丢失后，需要使用最近的备份文件对数据进行恢复，从而将数据丢失造成的损失降到最低；

2.在行内的技术规范下，MySQL数据库采用的是MySQL Group Replication（MGR）架构或主从架构。使用数据库备份与恢复技术，可以在已有备份数据的基础上快速重建MGR集群，修复损坏的MGR集群；也可以快速重建主从数据库节点。

根据备份过程中是否需要停库、是否需要阻塞用户操作，数据库备份可以分为冷备份、热备份与温备份。其具体方式如下：

1.冷备份：冷备份过程中数据库需要停库，此时数据库无法对外提供服务，用户无法查询、修改数据库；

2.热备份：热备份过程中数据库不需要停库，此时数据库不会对被备份的表进行锁表，用户可以查询、修改数据；

3.温备份：温备份过程中数据库不需要停库，此时数据库会对被备份的表进行锁表，用户可以进行查询操作，但用户不能进行修改操作；

其中，冷备份、热备份、温备份的区别与特性如图1-2所示：

根据备份的目标是数据库原始文件还是逻辑表数据，备份可以分为物理备份与逻辑备份。其具体方式如下：

1.物理备份：备份的目标是存储数据库内容的目录和文件的原始副本，即保存数据库的原始文件。物理备份适用于在发生问题时快速回复大型重要数据库；

2.逻辑备份：备份的目标是表的逻辑结构（建表语句、建库语句）与表的逻辑内容（INSERT语句、分割符文本文件），即保存数据库的逻辑内容。逻辑备份适用于数据值或表结构较小的数据库，或在不同的计算机体系结构上重新创建数据；

在行内的技术规范下，有多种备份工具适用于MySQL数据库场景下的备份操作。表2-1列举了行内MySQL数据库使用到的备份工具。

xtrabackup是由Percona公司开发的一个用于MySQL数据库物理备份、热备份的工具。

xtrabackup支持一般备份工具都不支持的增量备份功能，其可以选择进行完整备份，也可以选择在已有数据的基础上进行增量备份。

xtrabackup具有备份速度快、备份数据可靠、热备份无需锁表、还原速度快等特点。xtrabackup命令的备份使用如下所示：

xtrabackup [options]


一般选项：


--user=user_name : 备份使用的用户


--password=password : 备份用户的密码


--host=host_name : 备份目标数据库的地址


--port=port_name : 备份目标数据库的端口


--defaults-file=mycnf_dir : 指定数据库配置文件路径


--incremental-basedir=base_dir : 增量备份时，指定上次备份的路径


--target-dir=tgt_dir : 指定备份结果存放目录


--parallel=# : 指定并行的线程数#


--databases=dbname1[.tbl_name] dbname2[.tbl_name]... : 
备份指定数据库，多个数据库空格隔开，不加该参数则备份所有库


执行动作选项：


--backup : 执行备份
复制

xtrabackup对某个MySQL数据库的全量备份的命令示例如下，其中红色加粗字段需根据实际情况进行替换：

xtrabackup \


--defaults-file=/etc/my.cnf \


--user=user_name \


--password=password \


--backup \


--target-dir=/mysqldata/backup/test
复制

xtrabackup对某个MySQL数据库在已有备份基础上进行增量备份的命令实例如下，其中红色加粗字段需根据实际情况进行替换：  

xtrabackup \


--defaults-file=/etc/my.cnf \


--user=user_name \


--password=password \


--backup \


--target-dir=/opt/inc \


--incremental-basedir=/opt/base 
复制

mysqldump是MySQL数据库自带的逻辑备份、温备份工具。其备份原理是通过协议连接MySQL服务器，将需要备份的数据查询出来，将查询出来的数据转化为对应的INSERT语句或文本数据。mysqldump命令的使用如下所示：

mysqldump [options] 数据库名 [表名] [> 导出文件名]


常用选项：


--user=user_name, -uuser_name : 备份使用的用户


--password=[password], -p[password] : 备份用户的密码


--host=host_name, -h host_name : 备份目标数据库的地址


--port=port_name, -P port_name: 备份目标数据库的端口


--databases db_name, -B db_name : 指定需要备份的数据库


--tables tbl_names : 指定需要备份的表，多个表使用空格隔开


--no-create-info, -t : 导出的文件中没有建表信息


--no-data, -d : 导出的文件中没有数据


--tab=dir_name, -T dir_name : 将表中数据导出为制表符分割的文本文件，dir_name指定导出的目录，该参数同时还会在dir_name下创建一个tbl_name.sql的文件来记录建表的SQL语句


--fields-terminated-by=... : 配合--tab参数使用，指定文本文件的分隔符
复制

mysqldump可以在一个.sql文件中导出CREATE TABLE建表语句和INSERT插入数据语句。其使用示例如下所示，其中红色加粗字段需根据实际情况进行替换：

mysqldump \


-uuser_name \


-ppassword \


--databases db_name \


--tables tbl_name > XXX.sql
复制

mysqldump可以在指定目录下导出文本格式数据文件以及保存CREATE TABLE建表语句的.sql文件。其使用示例如下所示，其中红色加粗字段需根据实际情况进行替换：

mysqldump \


-uuser_name \


-ppassword \


--databases db_name \


--tables tbl_name \


-T /path/to/dump --fields-terminated-by=','
复制

mysqldump在备份数据时，会发生锁表的情况。如表2-2所示，根据在MGR集群的主节点备份还是在从节点备份，会导致备份期间不同的读写权限。

exp2txt.sh脚本是一种逻辑备份、温备份工具脚本。其备份原理是通过协议连接MySQL服务器，将需要备份的数据查询出来，将查询出来的数据转化为文本数据。exp2txt.sh脚本的的使用需要根据备份需求修改红色加粗字段：

#!/bin/bash


... ...


# 每个批次导出的行数，根据实际设置，该值越大，导出速度越快，越影响


# 数据库的性能


limit=limit_num


... ...  


# 备份用户的用户名、密码


user=user_name


password=password


# 目标MySQL服务器的IP、端口


host=host_name


port=port_name


# 指定需要备份的数据库、表


schema=db_name


table=tbl_name


... ...  


# 指定文本文件存放位置


output_datafile=/path/to/output_file
复制

由于exp2txt.sh脚本基于MySQL查询操作设计，因此exp2txt.sh脚本支持异地备份文本文件。

与mysqldump类似地，exp2txt.sh脚本也属于温备份工具，当从主节点进行备份时，会阻塞用户对MySQL数据库的修改操作。

batch_export.sh脚本是一种逻辑备份、温备份工具脚本。其备份原理是通过协议连接MySQL服务器，将需要备份的数据查询出来，将查询出来的数据转化为文本数据。

batch_export.sh脚本的的使用与exp2txt.sh脚本完全一样。当导出的数据中含有制表符等特殊字符时，exp2txt.sh脚本无法正常备份数据，此时应该使用batch_export.sh脚本。

由于batch_export.sh脚本基于MySQL查询操作设计，因此batch_export.sh脚本支持异地备份文本文件。

与mysqldump类似地，batch_export.sh脚本也属于温备份工具，当从主节点进行备份时，会阻塞用户对MySQL数据库的修改操作。

xtrabackup提供使用备份文件进行数据恢复的功能，在进行恢复时，需要使用xtrabackup备份产生的备份文件。需要特别注意的是，使用xtrabackup进行数据恢复时，需要停库。xtrabackup命令的恢复使用如下所示：

xtrabackup [options]


一般选项：


--defaults-file=mycnf_dir : 指定数据库配置文件路径


--target-dir=tgt_dir : 指定备份文件存放的目录


--parallel=# : 指定并行的线程数#


--databases=dbname1[.tbl_name] dbname2[.tbl_name]... : 
指定进行恢复的数据库，多个数据库空格隔开，不加该参数则恢复所有库


执行动作选项：


--prepare : 执行恢复前准备操作


--apply-log : 通过应用日志，使数据文件处于一致状态。该选项会保证在数据文件中回滚尚未提交的事务，保证已提交的事务同步到数据文件中


--redo-only : 该选项在使用--apply-log选项的情况下，会在准备阶段不回滚尚未提交的事务


--copy-back : 执行数据恢复
复制

要进行两步操作。首先，需要对备份文件进行恢复前的准备操作，在此期间会通过日志文件来保证数据文件的一致性。准备操作的命令示例如下，其中红色加粗字段需根据实际情况进行替换：

xtrabackup \


--defaults-file=/etc/my.cnf \


--prepare \


--apply-log-only \


--target-dir=/mysqldata/backup/test
复制

在准备操作执行结束后，使用如下命令执行数据恢复操作，其中红色加粗字段需根据实际情况进行替换：

xtrabackup \


--defaults-file=/etc/my.cnf \


--copy-back \


--target-dir=/mysqldata/backup/test
复制

MySQL Shell是MySQL 5.7.12之后提供的一种功能强大的MySQL客户端工具。MySQL Shell在原有SQL语句的基础上，扩展支持JavaScript和Python脚本。

本文主要关注MySQL Shell在数据备份与恢复方面的使用，用户可以使用MySQL Shell从文本文件恢复MySQL数据库。MySQL Shell在备份恢复时命令格式如下所示：

mysqlsh [一般选项] -- util import-table 文本文件名 [恢复选项]


一般选项：


--user=user_name, -u user_name : 恢复使用的用户


--password=[password], -p[password] : 恢复用户的密码


--uri str : 指定连接数据库的URI


--host=host_name, -h host_name : 恢复目标数据库的地址


--port=port_name, -P port_name : 恢复目标数据库的端口


--ssl-mode=mode : 与数据库建立的连接是否使用SSL/TLS加密


恢复选项：


--schema=db_name, -D db_name : 指定需要恢复的数据库


--table=tbl_name : 指定需要恢复的表


--fieldsTerminatedBy=$'char' : 指定字段间分隔符


--linesTerminatedBy=$'char' : 指定每条数据间换行符


--threads=thrd_num : 指定恢复使用的最大工作线程数，默认8


--bytes-per-chunk=size : 指定工作线程每次运行导入的数据量，该参数最大不应该超过145M，默认50M
复制

MySQL Shell可以将已有文本文件时间恢复到特定表中。其使用示例如下所示，其中红色加粗字段需根据实际情况进行替换：

mysqlsh \


--uri user_name@host_name:port_name -ppassword \


--ssl-mode=DISABLED \


-- util import-table /path/to/data_file \


--schema=db_name --table=tbl_name \


--fieldsTerminatedBy=$',' \


--linesTerminatedBy=$'\n' \


--bytes-per-chunk=80M
复制

可以使用MySQL客户端的source命令导入已有的.sql语句文件。具体使用方法为MySQL客户端连接MySQL服务器，使用在指定数据库下使用source命令导入数据。具体使用方法如下所示，其中红色加粗字段需根据实际情况进行替换：

mysql -uuser_name -ppassword


mysql> use db_name;


Reading table information for completion of table and column names


You can turn off this feature to get a quicker startup with -A


Database changed


mysql> source XXX.sql;
复制

可以使用MySQL客户端重定向命令‘<’导入已有的.sql语句文件。该重定向命令的具体使用方法如下所示，其中红色加粗字段需根据实际情况进行替换：

mysql -uuser_name -ppassword \


-hhost_name -Pport_name \


db_name < XXX.sql
复制

本文在两地三中心环境下对比各种备份恢复工具的性能，本文使用的测试MySQL数据库构架采用一主五从六节点的主从部署构架。

其中，上海本部机房、上海同城机房间的MySQL使用半同步复制，北京机房的MySQL使用异步复制。本文测试对比使用的MySQL架构如图4-1所示：

值得注意的是，本文使用使用北京机房的从节点进行备份测试，在真实情况下，如果不存在异地从节点，则推荐从同城机房的从节点进行备份。本文使用的数据节点为真实物理机上搭建的主从架构集群。表4-1展示了各台物理机的详细软硬件配置：

为了充分对比四款备份工具（1款物理热备工具、3款逻辑温备工具）的备份速度，本文在特定测试数据量下对这四款备份工具的表现进行了对比。

表4-2展示了四款备份工具在一亿条测试数据量下备份速度的情况。由于有些备份工具需要指定一些特定参数，本文在表4-2中列举了这些参数的常见配置大小。

根据上述测试结果，可以发现物理热备份工具xtrabackup在备份数据时，性能效率方面明显优于三款逻辑温备份工具。

同时，可以观察得出，mysqldump在备份格式为文本格式数据文件的时候，备份速度要明显优于备份格式为SQL语句格式数据文件。

在所有备份工具当中，两款脚本备份工具的备份速度明显差于其他备份工具。但是，两款脚本备份工具支持异地备份，这是其他备份工具所不具备的。

本文使用备份数据集，在MGR集群场景下进行数据恢复。对比四种恢复工具的恢复性能。图4-2展示了恢复工具性能测试使用的MGR集群的构架场景。

由于MGR的主节点有读写权限，从节点仅有只读权限，因此本文在MGR的主节点进行数据恢复测试。本文在恢复测试场景下使用的数据节点为虚拟机上搭建的MGR集群。表4-3展示了各台虚拟机的详细软硬件配置：

在上述MGR集群架构以及软硬件配置下，使用特定的数据集测试四款恢复工具的性能。表4-4展示了四款恢复工具在一亿条测试数据量下恢复速度的情况。由于有些恢复工具需要指定一些特定参数，本文在表4-4中列举了这些参数的常见配置大小。

根据上述测试结果，可以发现xtrabackup在恢复数据时，性能效率方面明显优于三款恢复工具。同时，可以观察得出，mysqlsh命令的“-byte_per-chunk”参数并非越大越好，在现有环境下，80M时恢复性能最好。

长按二维码，关注我们吧！