周一故事会之关于MySQL备份及恢复的二三事

MySQL常用的备份工具有基于物理备份的Xtrabackup和基于逻辑备份的mysqldump；其中Xtrabackup主要特点是备份过程快速、可靠以及数据恢复速度快。

Mysqldump的主要特点在于可以远程主机进行备份。下面主要介绍使用Xtrabackup对mysql实例进行全量、增量备份。对于库、表全量备份使用mysqldump进行备份，另外结合binlog文件实现库、表的增量备份。

一、使用xtrabackup对mysql全节点备份及恢复

由于Xtrabackup支持备份innodb表，实际生产环境中我们使用的工具是innobackupex，它是对xtrabackup的一层封装。innobackupex脚本用来备份非InnoDB表，同时会调用xtrabackup命令来备份InnoDB表，innobackupex的基本流程如下：

1）开启redo日志拷贝线程，从最新的检查点开始顺序拷贝redo日志；

2）开启ibd文件拷贝线程，拷贝innodb表的数据；

3）ibd文件拷贝结束，通知调用FTWRL，获取一致性位点；

4）备份非innodb表(系统表)和frm文件；

5)由于此时没有新事务提交，等待redo日志拷贝完成；

6)最新的redo日志拷贝完成后，相当于innodb表和非innodb表数据都是最新的；

7)获取binlog位点，此时数据库的状态是一致的；

8)释放锁，备份结束；

1.1基于xtrabackup全量备份

Mysql全节点全量备份：

innobackupex --defaults-file=/data/mysql/3306/run/3306.cnf--use-memory=128M --host=172.31.10.18 --user=bakuser--password=123456 --port=3306 /backup/mysql/full/

其中，--host指定连接数据库的地址，--user指定连接数据库的用户名，--password指定连接数据库的密码，--defaults-file指定数据库的配置文件，innobackupex要从其中获取datadir等信息；/backup/mysql/full/+创建一个时间戳命名的文件夹来指定备份数据存放地址；若对库、表备份时需添加—include参数指定要备份的数据库、表，如备份test库下的2个表：test1和test2则include="test.test1test.test2"。

1.2基于xtrabackup增量备份

Mysql全节点增量备份或累计增量备份：

innobackupex --defaults-file=/data/mysql/3306/run/3306.cnf--use-memory=128M --host=172.31.10.18 --user=bakuser--password=123 --port=3306 --incremental /backup/mysql/incre/--incremental-basedir /backup/mysql/full/

其中，--incremental指明是增量备份，--incremental-basedir指定上次完整备份或者增量备份文件的位置。增量备份其实只针对的是InnoDB，对于MyISAM来说，还是完整备份。

1.3 mysql全节点全量备份数据恢复

mysql全节点全量备份数据恢复具体步骤：

关闭数据库

mysqladmin-P3306 -S /data/mysql/3306/data/mysql.sock -u root -p123456 shutdown

备份目前数据库文件及更改权限

mv/data/mysql/3306/data /data/mysql/3306/data_bak

chown -Rmysql:mysql /data/mysql/3306/data

3)备份数据恢复

a．将备份文件中的日志应用到备份文件中的数据文件上

innobackupex--apply-log /backup/mysql/full/2017-12-09_13-52-52/

b．-apply-log指明是将日志应用到数据文件上，完成之后将备份文件中的数据恢复到数据库中：

innobackupex--defaults-file=/data/mysql/3306/run/3306.cnf --copy-back --rsync /backup/mysql/full/2017-12-09_13-52-52/

4)启动mysql(使用mysql账号)

mysqld_safe--defaults-file=/data/mysql/3306/run/3306.cnf --datadir=/data/mysql/3306/data--user=mysql &

1.4 mysql全节点增量备份数据恢复

在全量备份和增量备份文件中都有一个文件xtrabackup_checkpoints会记录备份完成时检查点的LSN。在进行新的增量备份时，XtraBackup会比较表空间中每页的LSN是否大于上次备份完成的LSN，如果是，则备份该页，并记录当前检查点的LSN。对mysql进行增量备份数据恢复时需要先查看全量备份的xtrabackup_checkpoints和增量备份数据的xtrabackup_checkpoints是否连接上，若连接不上，则无法进行增量数据恢复；

注：xtrabackup_checkpoints——备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；

mysql全节点增量、累计增量备份数据恢复具体步骤：

1)关闭数据库

mysqladmin-P3306 -S /data/mysql/3306/data/mysql.sock -u root -p123456 shutdown

备份目前数据库文件及更改权限

mv /data/mysql/3306/data/data/mysql/3306/data_bak

chown -Rmysql:mysql /data/mysql/3306/data

3)备份数据恢复

a、先恢复基础的“全量备份”

innobackupex --defaults-file=/data/mysql/3306/run/3306.cnf--apply-log --redo-only /backup/mysql/full/

b、再恢复离全量备份最近的一次增量。如果是最后一次增量，不需要添加"--redo-only"

innobackupex --defaults-file=/data/mysql/3306/run/3306.cnf--apply-log /backup/mysql/full/ --incremental-dir=/backup/mysql/incre/

c、最后把所有合在一起的完全备份整体进行一次apply操作，回滚未提交的数据

innobackupex --defaults-file=/data/mysql/3306/run/3306.cnf--apply-log /backup/mysql/full/

4)备份文件拷贝（使用mysql系统账户）

innobackupex --defaults-file=/data/mysql/3306/run/3306.cnf--copy-back --rsync /backup/mysql/full/

5)启动mysql(使用mysql账号)

mysqld_safe--defaults-file=/data/mysql/3306/run/3306.cnf --datadir=/data/mysql/3306/data--user=mysql &

1.5 mysql库、表全量备份数据恢复

1）关闭数据库

mysqladmin –P3306-S /data/mysql/3306/data/mysql.sock -u root -p123456 shutdown

2）将数据库备份数据拷贝到data目录

cp -rf/backup/mysql/full/2017-12-11_14-46-41/* /data/mysql/3306/data/

3）赋权

chown -Rmysql:mysql /data/mysql/3306/data

4）启动mysql

sudomysqld_safe --defaults-file=/data/mysql/3306/run/3306.cnf --datadir=/data/mysql/3306/data--user=mysql &

1.6基于xtrabackup库、表备份数据恢复问题及分析

问题复现具体步骤：

1）备份testdb1库下的table1表

innobackupex --defaults-file=/data/mysql/3306/run/3306.cnf--use-memory=128M --host=172.31.10.18 --user=bakuser --password=123456--port=3306 --include='testdb1.table1' /backup/mysql/full/

2）testdb1库下新增table2表，在testdb2库下新增table3表

3）使用上述的mysql库、表全量备份数据恢复方式对table1进行数据恢复；

4）查看数据，table1表数据恢复正常，table2、table3表无法打开；

分析导致原因：由于innobackup采取物理备份的方式进行备份，如果在备份之后新增了表，直接在原数据库上还原数据时对于tablespace的操作会导致这些新增表无法打开，需要在全新的环境中进行相应的数据还原操作并将还原结果逻辑导入原数据库。

二、binlog+mysqldump库、表备份及恢复

mysqldump备份的原理是通过设置READ LOCK获取数据库全局锁后，RR事务隔离级别下记录当前的日志文件名和日志位置position，然后释放掉全局锁。接下来创建一个事务的回滚点，所有数据的获取都是获取的是这个sp回滚点数据。最后释放掉回滚点sp。当然，对于MyISAM存储引擎，备份是直接锁全表的。备份的基本流程如下：

1）调用FTWRL(flush tables with read lock)，全局禁止读写；

2）开启快照读，获取此时的快照(仅对innodb表起作用)；

3）备份非innodb表数据(*.frm,*.myi,*.myd等)；

4）非innodb表备份完毕后，释放FTWRL锁；

5）逐一备份innodb表数据；

6）备份完成。

2.1基于mysqldump库、表全量备份

mysqldump备份指令的具体使用：

1）单库备份直接写入数据库名称

mysqldump -h 172.31.10.18–p3306 -u'bakuser' -p'123456' -R testdb1 --single-transaction--log-error=/data/log/a.log --master-data=2 > /data/dump/ testdb1.sql

命令指备份testdb1库到testdb1.sql文件

2）多库备份使用--databases参数

mysqldump -h 172.31.10.18- p3306 -u'bakuser' -p'123456' -R --databases testdb1 testdb2 --single-transaction--log-error=/data/log/a.log --master-data=2 > /data/dump/testdbs.sql

命令指备份testdb1和testdb2数据库到testdbs.sql文件

3）单库下多表备份使用库名+表1名+表2名

mysqldump -h 172.31.10.18- p3306 -u'bakuser' -p'123456' -R testdb1 table1 table2 --single-transaction --log-error=/data/log/a.log--master-data=2 > /data/dump/tables.sql

命令指备份testdb1中的table1和table2表到tables.sql文件

4）全库备份使用--all-databases参数

mysqldump -h 172.31.10.18- p3306 -u'bakuser' -p'123456' -R --all-databases --single-transaction--log-error=/data/log/a.log --master-data=2 > /data/dump/alldbs.sql

注：命令指备份所有数据库到alldbs.sql文件，包括系统库mysql，但是不包括系统库sys（5.7版本新增）、performance_schema和information_schema。--single-transaction获取InnoDB表的一致性备份，--master-data=2主要用于记录一致性备份的位点。

备份成功后具体备份文件内容如下所示：

其中包括MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=2304763两个参数，指定了Mysql数据备份时的binlog文件位置MASTER_LOG_FILE和具体物理位置MASTER_LOG_POS。

Mysqldump记录上述两个参数的意义在于使用binlog进行恢复时提供起始位置信息。

2.2使用Binlog+mysqldump恢复

通过mysqldump产生的目标文件中的binlog文件位置MASTER_LOG_FILE和具体物理位置MASTER_LOG_POS信息协助进行mysqlbinlog恢复。使用mysql自动的source命令进行数据恢复，命令格式：source PQTH（需要指定绝对路径）；

1、库、表的全量备份恢复：

使用备份文件对库、表进行全量备份恢复：

2、在库、表全量备份的基础上结合binlog进行增量数据恢复：

1）查看binlog的end_log_pos终点位置；

show binlog events in'mysql-bin.000006';

2）导出对应备份文件指定的binlog文件开始位置和终止位置有关库testdb1操作的binlog内容到testdb.sql文件中；

3）使用库备份数据库全量恢复

4）使用导出有关testdb1库操作的binlog文件，进行基于testdb1库增量数据恢复；

注：使用source恢复之前不需要删表，会直接覆盖现有数据表，由于.sql文件中包含if exist，drop的判断到操作；对于表的数据恢复和库的数据恢复步骤是一样的，不同点在于恢复表数据时需要先切换到该表对应的库下进行数据恢复；对binlog文件内容过滤只支持到库，表增量恢复时需手动删除该表对应库下其他表的操作对应binlog的内容，否则会影响同数据库下其他表的内容；