这是学习笔记的第 1969 篇文章

MySQL备份恢复

说到备份恢复，不管你对心理学是否感兴趣，建议你要理解下墨菲定律：如果事情有变坏的可能，不管这种可能性有多小，它总会发生。

所以一旦灾难发生，尤其是发生自然灾害，在不可抗因素的情况下，备份恢复往往是最后的救命稻草。

而数据库的备份恢复有多重要呢？来看一下下面的数据。

据美国德克萨斯州大学的调查显示，只有6%的公司可以在数据丢失后生存下来，43%的公司会彻底关门，51%的公司会在两年之内消失。

Gartner公司的一项调查表明，在灾难之后，如果无法在14天内恢复信息作业，有75%的公司业务会完全停顿，43%的公司再也无法重新开业，20%的企业在两年之内被迫宣告破产。

在互联网业务高速的发展中，数据的价值和运营能力被极大的发挥出来，就好比一辆在偏远地区高速行驶的汽车，如果出现了爆胎，但是没有备胎，那影响是巨大的。

所以作为DBA,作为数据保障的最后一道防线，我们要完善备份恢复，在有限的时间内能够快速恢复数据，保障业务持续可用是我们始终恪守的服务底线。

本小节我们会着重介绍下常见的备份恢复工具，并总结一些备份恢复的小技巧，试图通过一些案例的方式来让备份恢复工作具有实践性。

1  数据安全警示录

在开始备份恢复之前，我们需要思考一个问题，为什么会有故障？

如果服务器发生了宕机，我们有条不紊的恢复，那么这种故障是可控的，毕竟硬件故障是不可避免的，我们需要在服务可持续方向多下功夫，尽可能减少业务层的影响。

而另外一类故障是不可控的，因为系统层的工作相对是底层的操作，一旦出现问题，影响范围就会无限放大。

如果发生了服务器宕机，会存在诸多的原因，有硬件，系统，软件层面，当然也包括人为故障，通过行业内的故障分析数据，系统层的故障整体会有如下的比例显示（仅供参考）。

系统故障比例

  其中硬件故障占了大部分，基本是二八的比例分布，而硬件故障中主板故障造成的比例极高，接下来是内存故障，主板和内存都是一些不可控的硬件因素，从这个比例可以看出服务器发生硬件的概率还是很高的。 

 再来说下人为故障，根据数据中心性能研究机构纽约正常运行时间学会（UPTIMEINSTITUTE）提供的数据显示，他们分析了4500起数据中心事故，其中包含400次完全宕机事件，发现70%以上的数据中心故障都是人为失误导致。

  最后需求强调下：备份重于一切，定期的恢复演练重于备份。如果数据恢复不了，备份就没有任何意义。

2 常规备份方案

工欲善其事，必先利其器，我们需要对备份恢复工具有一个较为清晰的认识，我整理了如下的一些工具和技巧总结，接下来的内容整体会通过三个维度来展开，分别是数据备份恢复（库级别），数据导入导出（表级别）和日志恢复（日志级别）。

从备份类型来说，可以分为冷备份和热备份。

 冷备份主要使用系统命令完成，是基于物理文件的复制，比如命令 cp,特点是快速、事务一致；但是重要的一点是需要停服务,适用的场景会相对较少。

而热备份可以理解是在线备份，不会中断已有的业务访问，我们绝大多数的场景涉及的都是热备份，而接下来所说的备份工具也都是基于热备份的前提下。

对于备份工具我整理了如下的表格，从各个层面来看没有最好的工具，从各个维度来看都是一种平衡。

（1） 备份工具1：mysqldump

对于备份工具mysqldump，虽然看上去性能没有那么高，但是对于数据量较小的环境来说还是比较适用的，而在工作中，对于mysqldump的学习应该是我们的重点。

l --single-transaction选项

在备份时，是默认启用—lock-all-tables选项，所以要明确的一点是开启single-transaction选项，保证在一个事务中所有相同的查询读取到同样的数据，只在dump开始时短暂获取global read lock，否则在备份中全程锁表。

如下命令是备份数据库mobile_billing 生成转储文件test.sql

mysqldump --single-transaction --databases mobile_billing > test.sql 

究其原因，我们可以使用general log看到开启single-transaction时会设置会话事务隔离级别为RR(Repeatable read),同时会开启一个事务，设置为一致性快照，只在dump开始时短暂获取global read lock；

Query  SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

Query  START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 

           注：在备份过程中，要确保没有其他连接在使用ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE等语句，否则会出现不正确的内容或者失败。

l --master-data 选项

在备份时，如果要生成整个备份文件的检查点，可以使用master-data选项，在做主从复制时是一种有效的参考，通常会使用1和2这两个选项，它们的差异很简单，一个是执行（参数值1），一个是不执行（参数值2），

如下是两个参数启用后在dump文件中的相关内容：

参数值为2生成的dump相关语句：

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000033', MASTER_LOG_POS=943935226;

参数值为1生成的dump相关语句：

CHANGE MASTER TO MASTER_LOG_FILE='binlog.000033', MASTER_LOG_POS=943935226; 

l --add-drop-database选项

在导入数据时，如果数据库存在，通常有两种策略，一种是使用drop database if exists选项，另一种是直接忽略该操作，对于这类操作，mysqldump提供了丰富的选项，对于表的操作是默认开启了drop table if exists的处理方式，而对数据库是直接忽略此操作，我们可以设定这些已有的默认值，对于实际的工作环境进行选用。

Variables                     Value

--------------------------------- ----

all-databases                     FALSE

all-tablespaces                   FALSE

no-tablespaces                    FALSE

add-drop-database              FALSE

add-drop-table                    TRUE

注：对于表数据的导入，在一些关键操作时，在导入前看一下是否启用了drop选项，如果是线上核心业务，假设表test有100数据，dump文件有10条，如果稀里糊涂导入，表里就只剩下10条数据了，对于职业生涯来说是一种阴影。

l --triggers --routines --events

在数据导出时默认是不会备份触发器和存储过程，事件的，如果需要，我们启用即可。

此外mysqldump作为一款逻辑备份工具，也提供了丰富的定制功能。

l --order-by-primary 选项

这个选项属于MySQL很有特色的一个功能，能够根据主键值来进行排序

l  ----skip-extended-insert 选项

默认是使用insert into xxx values(xx)(xx)的形式。

如果要得到一行数据对应一条insert语句的形式，可以使用这个选项，生成的语句下面的形式：

INSERT INTO `test` VALUES (1,'1');

INSERT INTO `test` VALUES (2,'2');

l  --complete-insert 选项

如果对insert语句还不够满意，我们想生成完整的字段列表，可以使用该选项，生成语句类似下面的形式：

 INSERT INTO `test` (`id`, `name`) VALUES (1,'aa'),(2,'bb'); 

 或者结合--skip-extended-insert生成多条语句：

INSERT INTO `test` (`id`, `name`) VALUES (1,'aa');

INSERT INTO `test` (`id`, `name`) VALUES (2,'bb'); 

l  --replace 选项

可以把insert转化为replace语句，或者结合--skip-extended-insert来完成，这样一来就会生成若干条replace语句，可以实现操作的幂等性。

REPLACE INTO `test` VALUES (1,'aa');

REPLACE INTO `test` VALUES (2,'bb');