mysql跳过安全_【总结】MySQL技术内幕八：备份与恢复

数据库的备份与恢复是一项最基本的操作与工作。在意外情况下（如服务器宕机、磁盘损坏、RAID卡损坏等）要保证数据不丢失，或者是最小程度地丢失。根据自己的业务要求，设计出损失最小、对于数据库影响最小的备份策略。

8.1 备份与恢复概述

按备份的方法分为：

• Hot Backup（热备），也称OnlineBackup（在线备份），指数据库运行中直接备份，对正在运行的数据库操作没有任何的影响。
• Cold Backup（冷备），也称Offline Backup（离线备份），指在数据库停止时进行备份。这种备份最简单，一般只需要复制相关的数据库物理文件即可。
• Warm Backup（温备），在数据库运行中进行备份，但对当前数据库的操作有所影响，如加一个全局读锁以保证备份数据的一致性。

按照备份后文件的内容分为：

• 逻辑备份，指备份出的文件内容是可读的，一般是文本文件，如mysqldump。一般适用于数据库的升级、迁移等工作。缺点是恢复的时间较长。
• 裸文件备份，指复制数据库的物理文件，既可以是在数据库运行中的复制（如ibbackup、xtrabackup这类工具），也可以是在数据库停止运行时直接的数据文件复制。其恢复时间较乱短。

按照备份数据库的内容分为：

• 完全备份，指对数据库进行一个完整的备份。
• 增量备份，指在上次完全备份的基础上，对于更改的数据进行备份。如xtrabackup。
• 日志备份，主要是指对MySQL二进制日志的备份，通过对一个完全备份进行二进制日志的重做（replay）来完成数据库的point-in-time的恢复工作。

数据库备份的一致性，要求在备份的时候数据在这一时间点上是一致的。
对于mysqldump工具，通过添加--single-transaction选项获得InnoDB的一致性备份。
建议任何时候都需要做好远程异地备份，也是容灾的防范。

8.2 冷备

InnoDB的冷备非常简单，只需要备份frm文件、共享表空间文件、独立表空间文件（*.ibd）、重做日志文件。另外建议定期备份配置文件my.cnf，有利于恢复的操作。
由于磁盘空间已满而导致的备份失败经常发生，需要注意。同样建议备份一份到远程机器上。
冷备的优点：

• 备份简单，只要复制相关文件即可。
• 备份文件易于在不同操作系统，不同MySQL版本上进行恢复。
• 恢复相当简单，只需要把文件恢复到指定位置即可。
• 恢复速度快，不需要执行任何SQL语句，也不需要重建索引。

冷备的缺点：

• 冷备的文件通常比逻辑文件大很多，因为表空间中存放着很多其他的数据，如undo段，插入缓冲等信息。
• 冷备也不总是可以轻易地跨平台。操作系统、MySQL的版本、文件大小写敏感和浮点数格式都会成为问题。

8.3 逻辑备份与恢复

8.3.1 mysqldump

mysqldump通常用来完成转存（dump）数据库的备份及不同数据库之间的移植，如从MySQL低版本升级到高版本，又或者从MySQL移植到Oracle、Microsoft SQL Server等。
mysqldump的语法如下：

[args

备份后的文件是文本格式，可以直接查看，内容都是SQL语句。（关于mysqldump更详细的用法这里不再阐述）
恢复很简单：

mysql -uroot -p*** < file_name

如果是通过mysqldump --tab备份的，和SELECT...INTO一样，可以通过 LOAD DATA INFILE 来恢复，后面介绍其用法。
mysqldump可以导出存储过程、导出触发器、导出事件、导出数据，但是却不能导出视图。如果数据库中还使用了视图，还需要导出视图的定义，或者备份视图定义的frm文件，并在恢复时进行导入，这样才能保证mysqldump数据库的完全恢复。

8.3.2 SELECT...INTO OUTFILE

SELECT...INTO语句也是一种逻辑备份的方法，更准确地说是导出一张表中的数据。语法如下：

• FIELDS [TERMINATED BY'string']，表示每个列的分隔符，默认制表符；
• [[OPTIONALLY] ENCLOSED BY'char']，表示对于字符串的包含符；
• [ESCAPED BY'char']，表示转义符；
• [STARTING BY'string']，表示每行的开始符号，默认为空。
• TERMINATED BY'string'，表示每行的结束符号，默认换行符。

简单例子：

通过 LOAD DATA INFILE 进行恢复，语法如下：

示例：

为了加快InnoDB的导入，可以设置忽略对外键的检查。更多用法这里不再阐述。

8.3.3 mysqlimport

mysqlimport是MySQL提供的一个命令行程序，从本质上来说，是LOAD DATA INFILE的命令接口，而且大多数的选项都和LOAD DATA INFILE语法相同。语法如下：

mysqlimport [options] db_name textfile1 [textfile2 ...]

和LOAD DATA INFILE不同的是，mysqlimport命令可以导入多张表。并且通过--user-thread参数并发地导入不同的文件，这里的并发是指并发导入多个文件，不是并发地导入一个文件。这里的并发实际上是同时执行了两句LOAD DTA INFILE并发地导入数据。

8.4 二进制日志备份与恢复

二进制日志非常关键，可以通过它完成point-in-time的恢复工作，replication同样需要二进制日志。默认没启用二进制日志，启用二进制日志时，还需要启用一些其他参数来保证最为安全和正确地记录二进制日志，推荐配置：

[mysqld]
log-bin=mysql-bin
sync_binlog=1
innodb_support_xa=1

在备份二进制日志文件前，可以通过FLUSH LOGS命令来生成一个新的二进制日志文件，然后备份之前的二进制日志。
通过mysqlbinlog恢复二进制日志：

# log_file支持正则匹配，可同时恢复多个文件
mysqlbinlog 

--start-position 和 --stop-position 选项可以指定从二进制日志的某个偏移量来进行恢复，这样可以跳过某些不正确的语句。
--start-datetime 和 --stop-datetime 选项可以指定从二进制日志的某个时间点来进行恢复。

8.5 热备

8.5.1 ibbackup

ibbackup是官方提供的热备工具，可以同时备份MyISAM和InnoDB表。对于InnoDB备份工作原理如下：
1）记录备份开始时，重做日志文件检查点的LSN。
2）复制共享表空间文件以及独立表空间文件。
3）记录复制完表空间文件后，重做日志文件检查点的LSN。
4）复制在备份时产生的重做日志。
事务性数据库的热备的原理都大致和上述相同。备份时不会对数据库本身有任何影响，操作的只是复制数据库文件，不会阻塞数据库任何操作。
ibbackup的优点如下：

• 在线备份，不阻塞任何的SQL语句。
• 备份性能好，备份的实质是复制数据库文件和重做日志文件。
• 支持压缩备份，通过选项，可以支持不同级别的压缩。
• 跨平台支持，ibbackup可以运行在Linux、Windows以及主流的UNIX系统平台上。

ibbackup对InnoDB表的恢复步骤为：

• 恢复表空间文件。
• 应用重做日志文件。

ibbackup提供了一种高性能的热备方式，是InnoDB存储引擎备份的首选方式。不过它是收费软件，对应开源且免费的热备工具——XtraBackup。

8.5.2 XtraBackup

XtraBackup备份工具是由Percona公司开发的开源热备工具。支持MySQL5.0以上的版本。用法：

| --prepare 

xtrabackup首先记录了重做日志的位置，然后备份InnoDB表的物理文件，即复制共享表空间和独立表空间，最后记录备份完成后的重做日志位置。

8.5.3 XtraBackup实现增量备份

XtraBackup工具支持增量备份的工作原理：
1）首选完成一个全备，并记录下此时检查点的LSN。
2）在进行增量备份时，比较表空间中每个页的LSN是否大于上次备份时的LSN，如果是，则备份该页，同时记录当前检查点的LSN。
备份和恢复大致的过程：

# full backup
./xtrabackup --backup --target-dir

8.6 快照备份

MySQL本身并不支持快照功能，因此快照备份是指通过文件系统支持的快照功能对数据库进行备份。备份的前提是将所有数据库文件放在同一文件分区（LVM的逻辑卷）中，然后对该分区进行快照操作。
支持快照功能的文件系统和设备包括FreeBSD的UFS文件系统，Solaris的ZFS文件系统，GNU/Linux的逻辑管理器（Logical Volume Manager，LVM）等。这里以LVM为例进行介绍，UFS和ZFS的快照实现大致和LVM相似。

8.6.1 LVM快照原理

LVM是LINUX系统下对磁盘分区进行管理的一种机制。LVM在硬盘和分区之上建立一个逻辑层，来提高磁盘分区管理的灵活性。LVM可以轻松管理磁盘分区和调整卷组的大小，对磁盘存储按照组的方式进行命名、管理和分配。
LVM将若干个磁盘分区连接为一个整块的卷组（Volume Group），形成一个存储池。在卷组上随意创建逻辑卷（Logical Volumes），然后在逻辑卷上创建文件系统。
LVM工作原理：

物理卷到逻辑卷的映射：

常用命令：

• pvdisplay和pvcreate：查看和创建物理卷
• vgdisplay和vgcreate：查看和创建卷组
• lvdisplay和lvcreate：查看和创建逻辑卷

LVM使用了写时复制（Copy-on-write）技术来创建快照。当创建一个快照时，仅复制原始卷中数据的元数据（meta data），并不会有数据的物理操作，因此快照的创建过程是非常快的。当快照创建完成，原始卷上有写操作时，快照会跟踪原始卷块的改变，将要改变的数据在改变之前复制到快照预留的空间里，这就是写时复制。
而对于快照的读取操作，如果读取的数据块是创建快照后没有修改过的，那么会将读操作直接重定向到原始卷上，如果要读取的是已经修改过的块，则将读取保存在快照中该块在原始卷上改变之前的数据。因此，采用写时复制机制保证了读取快照时得到的数据与快照创建时一致。
LVM快照读取：

8.6.2 快照与备份的比较

快照，是数据存储的某一时刻的状态记录。
备份，是数据存储的某一个时刻的副本。
这两个概念完全不同。
快照和备份的区别：

• 备份的数据安全性更好：如果原始数据损坏（例如物理介质损坏，或者绕开了快照所在层的管理机制对锁定数据进行了改写），快照回滚是无法恢复出正确的数据的，而备份可以。
• 快照的速度比备份快得多：生成快照的速度比备份速度快的多。也因为这个原因，为了回避因为备份时间带来的各种问题（例如IO占用、数据一致性等）很多备份软件是先生成快照，然后按照快照所记录的对应关系去读取底层数据来生成备份。
• 占用空间不同：备份会占用双倍的存储空间，而快照所占用的存储空间则取决于快照的数量以及数据变动情况。极端情况下，快照可能会只占用1%不到的存储空间，也可能会占用数十倍的存储空间。

8.7 复制

8.7.1 复制的工作原理

复制（replication）是MySQL提供的一种高可用高性能的解决方案。
复制工作原理的3个步骤：
1）主服务器（master）把数据更改记录到二进制日志（binlog）中。
2）从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relay log）中。
3）从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。
如图：

复制是一个完全备份加上二进制日志备份的还原。二进制日志的还原操作基本上实时在进行中，但不是完全实时地进行同步，而是异步实时。因为主从服务器之间的存在延时，如果主服务器压力越很大，延时也会越较大。
从服务器有2个线程，一个是I/O线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是SQL线程，复制执行中继日志。
命令 SHOW MASTER STATUS 可以查看主服务器中二进制日志的状态。
命令 SHOW SLAVE STATUS 可以查看当前的运行状态，主要变量见下表。

根据上面的两个命令查出来的信息，主服务上的Position减去从服务器上的Read_Master_Log_Pos就是I/O线程的延迟。
为确保从服务器上的数据尽可能接近主服务器的状态，需要监控复制的以下信息：

• 从服务器上I/O线程和SQL线程运行状态
• 从服务器和主服务器之间的延迟

8.7.2 快照+复制的备份架构

复制的主要作用：

• 数据分布。由于MySQL提供的复制并不需要很大的带宽要求，因此可以在不同的数据中心之间实现数据的复制。
• 读取的负载平衡。通过建立多个从服务器，可将读取平均地分布到这些从服务器中，以减少了主服务器的压力。DNS的Round-Robin和Linux的LVS等可以实现负载平衡（很多公司有自己的中间件）。
• 数据库备份。复制对备份很有帮助，但是从服务器不是备份，不能完全代替备份。
• 高可用性和故障转移。通过复制建立的从服务器有助于故障转移，减少故障的停机时间和恢复时间。

复制不能完全作为备份，比如DROP DATABASE或DROP TABLE的误操作也会导致从服务器上的数据被删除。比较好的解决方案是，在从服务器上做快照备份，当发生类似误操作时，可以根据快照恢复。
快照+复制的备份架构：

一般情况下，从服务启用read-only选项，从服务器只提供读服务。有如下理由：

• 实际应用中，通常读请求量远大于写的请求量。
• 保证从服务器上的数据仅与主服务器进行同步，避免其他线程修改数据。

参考

• 《MySQL技术内幕：InnoDB存储引擎（第2版）》
• https://dev.mysql.com/doc/refman/5.7/en/select-into.html
• https://dev.mysql.com/doc/refman/5.7/en/load-data.html
• 源码：MariaDB 10.4.12