MySQL备份恢复体系(一)

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');

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2019-05-04

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券