第8章 数据库备份及恢复

一.数据库备份概述

1-1备份的分类的维度

备份时数据库的状态：备份时这个数据库是被锁了别人都不能用，还是不锁别人都能用的情况下进行动态的备份；

备份文件的格式：备份的文件是什么格式的；

备份文件的内容：是备份了全量数据库还是增量数据库。

1-2备份时数据库的状态

Hot Backup（热备份）：数据库在正常运行中，别人都可以读写。这种方式对业务的侵入是最小的，尽管现在的热备方法很难保证一点都不影响业务；

Cold Backup（冷备份）：数据库完全停止（将mysqld服务stop掉）。这种方式对业务的侵入是最大的，业务既不能读也不能写，现在基本上都不使用这种方式；

Warn Backup（温备份）：数据库未完全停止，数据库可以读但是不能写。

1-3备份文件的格式

逻辑备份：输出文本或者SQL语句；

物理（裸文件）备份：备份数据库底层文件。

1-4备份的内容

完全备份：备份完整的数据；

增量备份：备份数据差异；

日志备份：备份Binlog。

1-5常用的备份工具

1.mysqldump

逻辑备份（备份的结果是SQL文件），热备份，全量备份。

2.xtrabackup

物理备份（备份的是表空间文件及元数据文件），热备份，全量+增量备份。

二.使用OUTFILE命令备份

OUTFILE是MySQL原生的SQL指令（相当于SELECT）;

最原始的逻辑备份方式；

备份的功能和效果取决于如何写SQL语句。

2-1OUTFILE使用方式

①首先，MySQL都有一个可安全操作的文件目录，如果想包OUTFILE的内容导出为文件的话，需要看一下MySQL Server可以操作系统中哪些文件路径：

SHOW VARIABLES LIKE '%secure%';

②使用INTO OUTFILE指令将查询结果导出至文件

SELECT * INTO OUTFILE '/var/lib/mysql-files/out_file_test' FROM t3;

2-2使用OUTFILE备份同一时间点的数据

如果想一次性备份所有的表，但担心在备份t1这张表的过程中，t2表中的数据被更新了，这两个表的数据就不一致了。如果想要一个一致性的视图，即同一个时刻的所有表的内容，OUTFILE通过事务也能做到。因为InnoDB是MVCC功能，且MySQL默认的隔离级别是可重复读。

这样备份的t1和t2表的内容是BEGIN这一刻的数据。

导出的数据很简易，分隔符合换行符都不明显，可以指定字段分隔符合换行符：

SELECT * INTO OUTFILE '/var/lib/mysql-files/t2-out' FIELDS TERMINATED BY ',' FROM t2;

2-3OUTFILE的注意事项

在InnoDB事务下，可以做到一致性视图；

修改分隔符：fields terminated by

修改换行符：lines terminated by

2-4OUTFILE的缺陷

输出的文本比较简略；

输出的文件不能直接用于数据库恢复。

三.使用mysqldump备份

mysqldump是一个常用的mysql备份工具。

3-1OUTFILE如何改进

自动发送SELECT语句，不需要手动发送；

自动开启事务。在InnoDB这类支持MVCC的数据库引擎上，自动把隔离级别切换到REPEATABLE READ隔离级别上，这样就可以实现从备份第一张到最后一张表展示的都是同一个时刻的数据。备份结束后自动提交事务。

输出INSERT语句，可以直接用于还原。

3-2mysqldump特点

非常常用的MySQL逻辑备份工具；

MySQL Server自带的，安装了MySQL后就可以使用；

输出的备份内容为SQL语句，平衡了阅读和还原；

SQL语句占用空间小。InnoDB的索引和日志不导出（与要备份的数据无关）。

3-3mysqldump原理

mysqldump向MySQL中发查询表所有数据的SQL指令，然后将查询的结果存储为INSERT语句存放到文件中。

mysqldump使用以下语句对数据进行备份：

SELECT SQL_NO_CACHE FROM `t`;

• SQL_NO_CACHE查询出的数据不会进入SQL缓存；
• 因为是查询所有数据，所以缓存没意义且还会占用MySQL Server的内存空间。

3-4mysqldump使用方法

1.备份

mysqldump使用以下语句对数据进行备份（无需进入MySQL客户端）：

mysqldump -uroot -p123456 --databases d1 --single-transaction >test.sql

--single-transaction：使用可重复读这个隔离级别进行备份，保证备份第一个表到备份最后一个表，备份的数据都是同一个时刻的。

密码也可隐藏输入：

2.还原

进入MySQL客户端，使用SOURCE命令还原（无需使用 use命令切换数据库，mysqldump备份的结果中有重建数据库的语句）：

SOURCE /data/mysqlbackup/sakila.sql;

sakila.sql中的部分内容：

3-5mysqldump的注意事项

1.使用的是InnoDB存储引擎

--single-transaction：在可重复读的隔离级别下进行（InnoDB）;

2.使用的是MyISAM存储引擎

--lock-all-tables：使用FTWRL锁（全局读锁）所有表（MyISAM）;

--lock-tables：使用READ LOCAL锁当前库的表（MyISAM），牺牲数据的一致性，备份到哪张表时，锁哪张表；

3-6mysqldump的缺点

1. 导出的是逻辑数据，备份较慢。原理是发送SELECT * FROM table到MySQL Server，它解析、查询、返回数据并处理为INSERT语句到文本文件中；
2. 还原时需要执行SQL指令，速度也比较慢。

四.增量备份

4-1思路

redolog和binlog忠实地记录了MySQL数据额变化；但binlog更合适，因为redolog是InnoDB独有的，而数据库的存储引擎不只有InnoDB;

mysqldump全量备份后，可以用binlog作为增量备份；

mysqldump做全量备份时，切换新的binlog文件；

从零还原时，采用全量还原+binlog增量还原。

4-2 开启binlog

MySQL的binglog日志不是默认开启的，需要手动开启：

①在配置文件/etc/my.cnf中新增：

log-bin=mysql-bin
server-id=1
binlog_format=ROW

②使用systemctl restart mysqld.service命令重启MySQL，重启后进入MySQL客户端查看是否设置成功：

show variables like '%log_bin%';

4-3使用binlog增量备份存在的问题

查看binlog，binlog在/var/lib/目录下：

存在的问题：

1. mysql-bin.000002是最新的binlog，不能将其直接拷贝走，因为这个binlog还在持续被写；
2. 如果正在写mysql-bin.000002的时候进行全量备份，不能认为mysql-bin.000002就是增量，因为其中有一部分在全量备份开始之前就已经存在了。

4-3备份

1.mysqldump全量备份

mysqldump使用以下语句对数据进行全量备份：

mysqldump -uroot -p123456 --databases sakila --single-transaction --flush-logs --master-data=2 >/data/mysql/backup/sakila.sql;

• --flush-logs：开始备份的时刻，切换binlog文件；
• --master-data=2：记录切换后的binlog文件名，=2表示以注释的形式写到备份文件中去，不会干扰到备份文件中SQL的正常执行。

执行全量备份命令（开始备份的时刻切换binlog日志文件）：

查看备份生成的文件sakila.sql:

记录了切换后的binlog文件为：mysql-bin.000004

2.binlog增量备份

①需要增量备份时，需先切换binlog文件。因为正在写的那个binlog文件如果直接被拷贝走，那么拷贝得到的文件可能是不完整的。

mysqladmin -uroot -p123456 flush-logs

②将在开始全量备份时刻到执行如上flush-logs命令的时刻之间所有新增的binlog文件备份（手动拷贝）。

执行flush-logs命令后：

binlog切换到了mysql-bin.000005，mysql-bin.000004就是一个完整的文件。

4-4恢复

首先恢复旧的全量备份

source /data/mysql/backup/sakila.sql;

然后将binlog增量还原至数据库

mysqlbinlog mysql-bin.000004(多个文件用空格分开)|mysql -uroot -p123456;

恢复全量备份（在MySQL客户端里）：

恢复增量备份（退出客户端）：

 

五.物理备份（针对InnoDB）

逻辑备份（如mysqldump）对数据库的性能是有影响的。因为逻辑备份最底层的原理就是向数据库发送SQL指令，只要是SQL指令就需要经历SQL的解析、优化、执行，到存储引擎去磁盘查询，将数据聚合起来并格式化，然后把数据返回到备份工具并落在磁盘上这个过程。

5-1为什么需要物理备份

直接备份InnoDB底层数据文件；

导出不需要转换速度快；

工作时对数据库的压力较小；

更容易实现增量备份。InnoDB的页上有元数据记录着这个页什么时候被什么事务更新的，XtraBackup在备份时，就会根据页上的元数据来判断这个页是否有变化，如果有变化则视为增量。

1.直接备份裸文件

理论上可行，但有很多问题：

• 要同时备份frm文件，ibd文件，binlog文件，redolog文件等。因为这些文件在运行时要求一致；
• 在不同版本的数据库和操作系统上可能有兼容问题；
• 必须冷备份，影响业务。

5-2物理+热+全量备份

sakila数据库中actor表的底层数据：

其中ibd文件保存的是表的数据，但这里面的数据不是最新的，因为可能有一部分数据还在redolog中（表名内存中有脏页还未刷盘）。

如果actor.ibd文件比较大，那么所需拷贝时间会较长，在这个过程中，这个表可能又有更新到放到redolog中。所以在备份ibd文件的时候，需要监听redolog中新增的内容，待备份结束后，再将redolog中在备份期间更新的内容落盘。

思路：利用redolog。备份ibd文件+备份再备份ibd文件期间的redolog。

1. 启动redolog监听线程，开始收集redolog;
2. 拷贝ibd数据文件；
3. 停止收集redolog；
4. 加FTWRL锁，然后拷贝元数据frm文件。（所以这个物理热备期间实际上是有很短一段时间是物理温备（只读））。

5-3物理+热+增量备份

思路：和全量备份相同。

如何确定增量：根据每个页的LSN号（记录了页在哪个事务做了更新），确定变化的页。如果页是在全量备份事务前做的更新就不认为这个页是增量，否则就认为是增量。

5-4实现物理恢复

思路：与mysqld crash崩溃恢复流程相似。

1. 还原ibd文件，重放redolog文件；

5-5物理备份和恢复实践

1.ibbackup工具

现名为MySQL Enterprise Backup，InnoDB官方出品（收费）；

实现了上述功能，性能优秀：

2.XtraBackup工具

Percona公司开发的开源版本，实现了ibbackup所有功能；

XtraBackup 8.0适用于MySQL8.0；

XtraBackup 2.4适用于MySQL5.1,5.5,5.6,5.7。

3.XtraBackup安装方法

下载：

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm

安装（不检查依赖强制安装）：

rpm -ivh --nodeps --force percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm

4.XtraBackup全量备份使用方法

备份：

innobackupex --user=root --password=123456 data/mysql/backup/xtrabackup/

出现如下问题：