MySQL的数据备份与恢复

概要

MySQL中备份数据的工具有

• mysqldump (MDP)
• percona Xtrabackup (XBK)
• MySQL Enterprise Backup (MEB)  
• mysqlbinlog

其中MEB是MySQL官方的备份工具，是收费的。由于本身条件有限，没有使用过这款工具，故在这里就不做讲解。

备份方式

• 逻辑备份

全量备份：使用 mysqldump 可以实现数据的全量备份，但是无法实现增量备份

增量备份：依赖 binlog 可以实现增量备份

Tip：使用 binlog 进行增量备份的步骤： 1、mysql>flush logs;        //滚动日志，产生一个新的 binlog   2、在Linux中将除了新产生的 binlog 之外的增量 binlog 文件使用"cp"命令拷贝备份

•  物理备份

全量备份：使用 XBK工具 可以实现数据的全量的物理备份

增量备份：使用 XBK工具 也可以实现数据的增量的物理备份

逻辑备份和物理备份的区别：

• 逻辑备份：逻辑备份是备份SQL语句，在恢复的时候执行备份中的SQL语句，从而实现数据库数据的重现
• 物理备份：物理备份就是备份数据文件了，比较形象点就是cp数据文件，但真正备份的时候自然不是cp这么简单

 

逻辑备份(mysqldump)

优点：

1、恢复简单

2、与存储引擎无关，因为备份的是SQL语句，所以消除了底层数据存储的差异

3、有助于避免数据损坏。因为若是磁盘驱动器有故障而要复制原始文件时，此时将得到一个损坏的备份

缺点：

1、必须有数据库服务器完成逻辑工作，需要更多的CPU周期

2、逻辑备份还原速度慢：因为需要MySQL加载和解释SQL语句、转化存储格式、重建引擎

 

物理备份(xtrabackup)

优点：

1、备份和恢复非常快（因为不需要执行任何的SQL语句，不要构建索引）

2、增量备份

3、流式备份

4、（解）压缩算法相比逻辑备份的zlib库更快

5、自身多线程支持

 缺点：

1、不够灵活，无法单独备份指定表、指定库

2、无法同逻辑备份那样 grep 特定业务存在的字符串

3、空间损耗，且不支持逻辑备份恢复时的碎片整理功能

4、依赖于备份过程中生成的 redo log 大小，若过大就会极大的影响备份和恢复速度

5、逻辑备份在不同的MySQL版本中，兼容性好；而xtrabackup作为物理备份方案，对MySQL的版本依赖性较强，扩展性较差

 

 备份类型

• 热备份：在业务运行时进行数据备份，对于业务影响小。（InnoDB存储引擎支持热备）
• 温备份：虽然也是在业务运行期间进行数据备份，但是备份时会长时间锁表。（MyISAM存储引擎支持温备）
• 冷备份：需要业务关闭才能备份

 

mysqldump逻辑备份

mysqldump备份属于热备份，但是并不是正真的热备份，只能是快照备份。快照备份也不会锁表，和真正的热备份的区别在于：快照备份只能备份快照之前的数据，在备份过程中产生的数据则无法备份，而真正的热备份可以把备份过程中产生的数据也进行备份。

比如我们想备份MySQL中某个数据库下的某些表：

[root@db01 backup]# mysqldump -uroot -p123 world city country > /backup/tab.sql

表示要备份world数据库中的city表和country表

TIP：若是想把数据库中的数据都按照表为单位进行备份，可通过 information_schema进行语句拼接，从而减少工作量。例如：

mysql>select concat("mysqldump -uroot -p123456"," ",table_schema," ",table_name," ",">/tmp/",table_name,".sql") from information_schema.tables where table_name='world' into outfile '/tmp/discard.sql';

使用"into outfile"可以将输出在屏幕上的查询结果输出到Linux系统的文件中，然后在Linux命令中执行"source /tmp/discard.sql"命令就可让里面拼接的语句开始执行。

若是把mysql数据库中表中的数据导入到linux文件中，一般文件后缀名为".csv"，因为使用".csv"后缀名，将文件传送到windows时，就可以直接使用Excel打开（当然也可以使用".xlsx"后缀名），此方式多用于数据迁移。

TIP：在5.7版本中若是想从MySQL中直接把数据输出到linux文件中，需要在配置文件中配置"secure-file-priv"参数，此参数表示安全路径，只有安全路径，"into outfile"才能成功将MySQL中的数据导入linux中。此参数后面可跟指定的路径，也可以什么不跟，表示所有路径都是安全路径。

1、基础备份参数

-A：将整个MySQL数据库中数据进行备份

例：[root@db01 backup]# mysqldump -uroot -p123 -A  >/backup/full.sql 

-B：备份MySQL中指定的某些数据库中的所有数据

例：[root@db01 backup]# mysqldump -uroot -p123 -B world oldguo wordpress >/backup/db.sql

 

2、特殊备份参数

-R：备份存储过程和函数；MySQL中的存储过程和函数相当于Linux中的脚本，是工作人员自定义的函数

-E：备份事件；此事件相当于Linux中的crontab计划任务，是工作人员自定义的

--triggers：备份触发器

--single-transaction：对于InnoDB的表，进行一致性快照备份，不锁表

--master-data=2 此参数有两个功能：

1. 记录备份时刻的 binlog 的position信息和文件名信息，用于数据恢复是，截取 binlog
2. 自动锁表，操作完成后会自动解锁。（配合"--single-transaction"参数使用时，对于InnoDB存储引擎的表可以不锁表备份，是通过MVCC功能进行快照备份；不配合"--single-transaction"参数使用时就是全局锁表，进行温备份）

3、扩展参数

--set-gtid-purged参数：默认值为AUTO/ON，主要在构建主从时使用。 使用 mysqldump进行数据备份时，若是使用此参数的默认值，备份文件中就有一行GTID的信息，如：

SET @@GLOBAL.GTID_PURGED='aa648280-a6a6-11e9-949f-000c294a1b3b:1-11';

此条语句指定了备份的数据所对应得GTID号得范围。构建主从时，由于可能主库已经存在数据，所以需要先备份，再恢复到从库，因为备份文件中有了这条语句，从库在同步主库的数据时，就会从GTID号为11的地方开始同步；若是备份文件中没有这条语句，从库就会从头开始同步主库的数据。从头开始同步数据就会和从库中已有的数据发生冲突，因为从库中已经有这部分数据了，最后就会致使主从构建失败。

设置 --set-gtid-purged=off 时，备份的文件中就不会有上面那一行的GTID信息。此种设置仅用于做普通的本机恢复备份。

--max_allowed_packet参数：此参数用于控制MySQL服务端和客户端之间一次传送过程中最大允许数据包的大小。（进行逻辑备份时，存储引擎层将磁盘中的数据提出给SQL层，SQL层将数据结构化成SQL语句，此过程也可称为MySQL服务器端和客户端的一次数据传输）

例：mysqldump -uroot -p123 -A  -R  --max_allowed_packet=128M --triggers --set-gtid-purged=OFF --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

模拟演练

1、准备数据

mysql>create database backup;

mysql>use backup;

mysql>create table t1 (id int);

mysql>insert into t1 values(1),(2),(3);

mysql>commit;

2、模拟周二 23:00对数据库进行了全备

[root@db01 backup]#mysqldump -uroot -p123 -A  -R  --triggers --set-gtid-purged=OFF --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

TIP：使用mysqldump进行备份时，若是一台主机上有多个实例，可以使用"-S"参数来指定备份哪个实例中的数据

3、模拟周二 23:00 到周三 10 点之间的数据变化

mysql>use backup;

mysql>insert into t1 values(11),(22),(33);

mysql>commit;

mysql>create table t2 (id int);

mysql>insert into t2 values(11),(22),(33);

mysql>commit;

4、故障模拟

mysql>drop database backup;     //backup数据库被删除了

5、在一台主机上准别另外一个MySQL实例作为临时数据库，进行数据恢复

[root@db01 ~]#systemctl start mysqld3307       //启动临时数据

[root@db01 backup]#gunzip full_2021-03-10.sql.gz        //对周二23点做的全备文件进行解压   （gzip：压缩）     

6、截取二进制日志

由于我们在全备时，使用了"--master-data=2"的参数，所以在全备文件full_2021-03-10.sql中会记录着这样一行信息：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=753;

MASTER_LOG_POS=753代表着我们进行全备时，起始的position。所以我们只需将从此起始位置到全备文件最后的position之间所有的二进制日志截取就可以了。

[root@db01 backup]#mysqlbinlog --skip-gtids   --start-position=753 --stop-position=1519  /data/binlog/mysql-bin.000002 >/backup/bin.sql      //使用"--skip-gtids"参数，是因为我之前已经启用了GTID，不加此参数，直接截取事件号作为备份数据的话，数据恢复的时候会出错。

7、恢复数据到临时数据库

[root@db01 backup]#mysql -S /data/3307/mysql.sock

mysql>set sql_log_bin=0;

mysql>source /backup/full_2019-07-15.sql;

mysql>source /backup/bin.sql;

8、将故障表导出并恢复到生产

[root@db01 ~]# mysqldump   -S /data/3307/mysql.sock -B  backup  >/backup/bak.sql

[root@db01 ~]# mysql -uroot -p

mysql>set sql_log_bin=0;

mysql>source /backup/bak.sql;

mysql>set sql_log_bin=1;

 

物理备份

物理全备

XBK不是MySQL自带的备份工具，所以需要先进行安装才能使用。

1、下载percona-xtrabackup软件包

[root@db01 backup]#wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

我这里将 percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm 软件包下载到本地的backup目录下。

2、安装

[root@db01 backup]# yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

安装结束后，系统会提示 percona-xtrabackup 服务还需要一些依赖包，将这些依赖包也一并安装才能正常使用 percona-xtrabackup 服务。

[root@db01 backup]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev

 

TIP：若是这些依赖包无法安装，或者因为 yum 仓库没有相应的依赖包，可以通过更新 yum 仓库来解决问题

 [root@db01 ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo

备份核心理念

1. 针对非InnoDB存储引擎，进行锁表备份，然后copy所有的非InnoDB存储引擎的表文件
2. 针对InnoDB存储引擎，立即触发CKPT，将内存中的脏页刷新到磁盘中，然后copy所有InnoDB存储引擎的表文件（ibdata1、ibd、frm），并且将备份过程中新的数据变化相关redo bufffer中的数据一起备份走，从而实现真正的热备份
3. 在恢复数据时，xbk会调用InnoDB存储引擎的CSR过程，将数据和redo的LSN号追平，然后进行数据一致性恢复

模拟演练

1、进行物理全备

[root@db01 ~]# innobackupex  --user=root --password=123  --no-timestamp /backup/full

"--no-timestamp"参数：该选项表示不要创建一个时间戳目录来存储备份，指定到自己想要的备份文件夹

2、利用全备进行数据恢复

[root@db01 ~]# pkill mysqld

[root@db01 ~]# \rm -rf /data/mysql/data/*        //模拟故障，数据库中的数据全部丢失了

[root@db01 ~]# innobackupex --apply-log /backup/full/         //进行数据恢复时，使用 --apply-log 参数来模拟MySQL中的CSR过程，即通过回滚未提交的事务及同步已经提交的事务至数据文件，使数据文件处于一致性状态

[root@db01 full]# cp -a /backup/full/* /data/mysql/data/       //通过上一步将数据文件和redo log中的LSN号追平后，就可以将备份文件拷贝到MySQL的数据目录下了

[root@db01 full]# chown -R mysql.mysql /data/mysql/data/*     //修改拷贝过去的文件的属主、属组

[root@db01 full]# systemctl start mysqld             //重启数据库

TIP：percona-xtrabackup 服务中我们主要使用 innobackupex 工具对MySQL数据库中的数据进行备份。

备份中产生的文件介绍

xtrabackup_binlog_info：记录备份时刻的二进制日志信息，可以作为 binlog 截取的起点。

xtrabackup_checkpoints：

           from ：备份中包含的LSN号的起点。全备时，值为0；增量备份时，值为上次备份结束的位置

           to ：ckpt 时的LSN。备份结束时的LSN，也是下次增量备份的起始位置

           last_lsn ：mysql 5.7版本会在"to"LSN号之后额外在增加9个LSN号留作redo维护使用，所以当"to"LSN号和"last"LSN号相差9时，就说明备份的过程中没有发生其它数据的变化

 

物理增量备份 

增量备份需要依赖于全备。

1、模拟数据

mysql>create database full charset utf8mb4;

mysql>use full;

mysql>create table t1 (id int);

mysql>insert into t1 values(1),(2),(3);

mysql>commit;

2、进行周日的全备

[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /backup/full

3、模拟周一数据的变化

mysql>create database inc1 charset utf8mb4;

mysql>use inc1;

mysql>create table t1 (id int);

mysql>insert into t1 values(1),(2),(3);

mysql>commit;

4、进行周一的增量备份

[root@db01 ~]#innobackupex   --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full  /backup/inc1 

TIP：

--incremental     增量备份的开关

--incremental-basedir        指定基于哪个备份进行增量备份

/backup/inc1      增量备份的位置点

TIP：可以通过检查备份文件的LSN号，就会发现增量备份LSN的起始位置，就是上次备份LSN号的结束点：

[root@db01 backup]# cat /backup/full/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 217478672
last_lsn = 217478681
compact = 0
recover_binlog_info = 0
[root@db01 backup]# cat /backup/inc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 217478672
to_lsn = 217484653
last_lsn = 217484662
compact = 0
recover_binlog_info = 0

5、模拟周二的数据变化

mysql>create database inc2 charset utf8mb4;

mysql>use inc2;

mysql>create table t1 (id int);

mysql>insert into t1 values(1),(2),(3);

mysql>commit;

6、周二的增量备份

[root@db01 ~]# innobackupex   --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2 

7、周三的数据变化

mysql>create database inc3 charset utf8mb4;

mysql>use inc3;

mysql>create table t1 (id int);

mysql>insert into t1 values(1),(2),(3);

mysql>commit;

8、模拟周三上午10数据库崩溃

[root@db01 ~]# pkill mysqld 
[root@db01 ~]# \rm -rf /data/mysql/data/*

数据恢复思路

1. 停业务，挂维护页
2. 查找可用备份并处理备份：full+inc1+inc2
3. binlog：inc2 到故障时间点的binlog
4. 恢复全备+增量+binlog
5. 验证数据
6. 起业务，撤维护页

一、数据恢复前准备（要使用绝对路径）

1、整理full

[root@db01 ~]# innobackupex --apply-log --redo-only  /backup/full

--redo-only：不加"--redo-only"时，使用"--apply-log"参数进行整理数据时，就会模拟CSR，进行redo、undo操作。若是使用了"--redo-only"参数，则说明进行数据整理时，只做redo操作，不做undo操作。

--redo-only参数用在整理基文件全备和所有增量合并时，不可以用在最后一次增量数据的整理。
原因： 当使用"--apply-log"进行数据整理时，就会模仿CSR过程，执行redo和undo操作。因为在热备的过程中，可能会存在未提交的事务（别的事务提交，把此事务一起刷新到磁盘），所以若是执行undo操作的话，LSN号就会发生改变。但是两个备份文件的拼接需要上一个备份文件的"last"LSN号和下一个"from"LSN号能够对接上，所以只能执行redo操作，不能执行undo操作。但是最后一个备份文件的拼接时，因为不需要再拼接备份文件，所以可以执行undo操作。所以就不需要"--redo-only"参数。

2、合并inc1到full，并整理备份

[root@db01 ~]#innobackupex --apply-log --redo-only  --incremental-dir=/backup/inc1 /backup/full 

3、合并inc2到full，并整理备份

[root@db01 ~]#innobackupex --apply-log  --incremental-dir=/backup/inc2 /backup/ful

最后一次增量，无"--redo-only"

4、最后整理整体的数据

[root@db01 ~]# innobackupex --apply-log  /backup/full

二、截取二进制

1、通过查看"xtrabackup_binlog_info"来获取要截取的二进制的起始位置

[root@db01 ~]#cat /backup/inc2/xtrabackup_binlog_info

mysql-bin.000031    1997     e16db3fd-a6e8-11e9-aee9-000c294a1b3b:1-9

因为我们是在周三发生数据崩溃，周二的时候我们有做增量备份，所以我们要知道周二的时候备份到了哪里。

从查看的结果中我们发现，周二增量备份时，已经备份到了 mysql-bin.000031文件，事件号为1997的位置，或者gtid号为9的位置了。

2、获取要截取的二进制日志位置为终点

[root@db01 ~]#mysqlbinlog /data/binlog/mysql-bin.000031 |grep 'SET'

...省略...

SET @@SESSION.GTID_NEXT= 'e16db3fd-a6e8-11e9-aee9-000c294a1b3b:12'/*!*/;

同过查看 mysql-bin.000031文件，我们可以发现，GTID号已经到了12了，所以只需要截取GTID号10~12的二进制日志数据即可。

 

TIP：在查看 xtrabackup_binlog_info 文件时，可能会出现多个GTID号，可通过查看mysql-bin.xxxxxx文件，来获取该选择使用哪个GTID号

3、截取二进制日志数据

[root@db01 ~]#mysqlbinlog --skip-gtids --include-gtids='e16db3fd-a6e8-11e9-aee9-000c294a1b3b:10-12' /data/binlog/mysql-bin.000031>/backup/binlog.sql

4、恢复数据

[root@db01 /]# cp -a  /backup/full/* /data/mysql/data/
[root@db01 /]# chown -R mysql. /data/
[root@db01 /]# systemctl start mysqld

[root@db01 /]#mysql -uroot -p111111
mysql> set sql_log_bin=0;
mysql> source /backup/binlog.sql
mysql>set sql_log_bin=1;