MySQL备份与恢复
数据库的备份与恢复一直都是 DBA 工作中最为重要的部分之一,也是基本工作之一。
任何正式环境的数据库都必须有完整的备份计划和恢复测试,本章内容将主要介绍 MySQL
数据库的备份与恢复相关内容。
1.数据库备份使用场景
你真的明白了自己所做的数据库备份是要面对什么样的场景的吗?
我想任何一位维护过数据库的人都知道数据库是需要备份的,也知道备份数据库是数据
库维护必不可少的一件事情。那么是否每一个人都知道自己所做的备份到底是为了应对哪
些场景的呢?或者说我们每个人是否都很清楚的知道,为什么一个数据库需要作备份呢?读
到这里,我想很多读者朋友都会嗤之以鼻,“备份的作用不就是为了防止原数据丢失吗,这谁不
知道?”。确实,数据库的备份很大程度上的作用,就是当我们的数据库因为某些原因而造成部
分或者全部数据丢失后,方便找回丢失的数据。但是,不同类型的数据库备份,所能应付情况是
不一样的,而且,数据库的备份同时也还具有其他很多的作用。而且我想,每个人对数据库备份
的作用的理解可能都会有部分区别。
下面我就列举一下我个人理解的我们能够需要用到数据库备份的一些比较常见的情况
吧。
一、数据丢失应用场景
1、人为操作失误造成某些数据被误操作;
2、软件 BUG 造成数据部分或者全部丢失;
3、硬件故障造成数据库数据部分或全部丢失;
4、安全漏洞被入侵数据被恶意破坏;
二、非数据丢失应用场景
5、特殊应用场景下基于时间点的数据恢复;
6、开发测试环境数据库搭建;
7、相同数据库的新环境搭建;
8、数据库或者数据迁移;
上面所列出的只是一些常见的应用场景而已,除了上面这几种场景外,数据库备份还会
有很多其他应用场景,这里就不一一列举了。那么各位读者过曾经或是现在所做的数据库备
份到底是为了应对以上哪一种(或者几种)场景?或者说,我们所做的数据库备份能够应对以上
哪几种应用场景?不知道这个问题大家是否有考虑过。
我们必须承认,没有哪一种数据库备份能够解决所有以上列举的几种常见应用场景,即使
仅仅只是数据丢失的各种场景都无法通过某一种数据库备份完美的解决,当然也就更不用说
能够解决所有的备份应用场景了。
比如当我们遇到磁盘故障,丢失了整个数据库的所有数据,并且无法从已经出现故障的
硬盘上面恢复出来的时候,我们可能必须通过一个实时或者有短暂时间差的复制备份数据库
存在。当然如果没有这样的一个数据库,就必须要有最近时间的整个数据库的物理或者逻辑
备份数据,并且有该备份之后的所有物理或者逻辑增量备份,以期望尽可能将数据恢复到出
现故障之前最近的时间点。而当我们遇到认为操作失误造成数据被误操作之后,我们需要有
一个能恢复到错误操作时间点之前的瞬间的备份存在,当然这个备份可能是整个数据库的备
份,也可以仅仅只是被误操作的表的备份。而当我们要做跨平台的数据库迁移的时候,我们
所需要的又只能是一个逻辑的数据库备份,因为平台的差异可能使物理备份的文件格式在两
个平台上无法兼容。
既然没有哪一种数据库备份能够完美的解决所有的应用场景,而每个数据库环境所需要
面对的数据库备份应用场景又可能各不一样,可能只是需要面对很多种场景中的某一种或几
种,那么我们就非常有必要指定一个合适的备份方案和备份策略,通过最简单的技术和最低廉
的成本,来满足我们的需求。
2.逻辑备份与恢复测试
(1)什么样的备份是数据库逻辑备份呢?
大家都知道,数据库在返回数据给我们使用的时候都是按照我们最初所设计期望的具有
一定逻辑关联格式的形式一条一条数据来展现的,具有一定的商业逻辑属性,而在物理存储的
层面上数据库软件却是按照数据库软件所设计的某种特定格式经过一定的处理后存放。
数据库逻辑备份就是备份软件按照我们最初所设计的逻辑关系,以数据库的逻辑结构对
象为单位,将数据库中的数据按照预定义的逻辑关联格式一条一条生成相关的文本文件,以
达到备份的目的。
(2)常用的逻辑备份
逻辑备份可以说是最简单,也是目前中小型系统最常使用的备份方式。在 MySQL 中我们
常用的逻辑备份主要就是两种,一种是将数据生成可以完全重现当前数据库中数据的INSERT
语句,另外一种就是将数据通过逻辑备份软件,将我们数据库表数据以特定分隔符进行分隔后
记录在文本文件中。
1、生成 INSERT 语句备份
两种逻辑备份各有优劣,所针对的使用场景也会稍有差别,我们先来看一下生成 INSERT
语句的逻辑备份。
在 MySQL 数据库中,我们一般都是通过 MySQL 数据库软件自带工具程序中的 mysqldump
来实现声称 INSERT 语句的逻辑备份文件。其使用方法基本如下:
当你在命令行下敲mysqldump，后面不加如何参数，将出现如下的提示信息：
[root@lx203 ~]# mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
上面的提示信息给出了3种使用方法，下面我们将一一介绍：
1）mysqldump [OPTIONS] database [tables]
这是最常见的使用方法，给出一个数据库名，在它后面给出一个或多个表名，表示导出该数据库下指定的这几个表。
2）mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
第二种方法使用了--databases参数，它将导出列出的数据库里的所有表
3）mysqldump [OPTIONS] --all-databases [OPTIONS]
--all-databases参数表示导出所有数据库里的所有表，包括mysql数据库里的权限表，所以一定要谨慎使用.
由于 mysqldump 的使用方法比较简单,大部分需要的信息都可以通过运行“mysqldump -
-help”而获得。这里我只想结合 MySQL 数据库的一些概念原理和大家探讨一下当我们使用
mysqldump 来做数据库逻辑备份的时候有些什么技巧以及需要注意一些什么内容。
我们都知道,对于大多数使用数据库的软件或者网站来说,都希望自己数据库能够提供
尽可能高的可用性,而不是时不时的就需要停机停止提供服务。因为一旦数据库无法提供服
务,系统就无法再通过存取数据来提供一些动态功能。所以对于大多数系统来说如果要让每
次备份都停机来做可能都是不可接受的,可是 mysqldump 程序的实现原理是通过我们给的参
数信息加上数据库中的系统表信息来一个表一个表获取数据然后生成 INSERT 语句再写入备
份文件中的。这样就出现了一个问题,在系统正常运行过程中,很可能会不断有数据变更的
请求正在执行,这样就可能造成在 mysqldump 备份出来的数据不一致。也就是说备份数据很
可能不是同一个时间点的数据,而且甚至可能都没办法满足完整性约束。这样的备份集对于
有些系统来说可能并没有太大问题,但是对于有些对数据的一致性和完整性要求比较严格系
统来说问题就大了,就是一个完全无效的备份集.
对于如此场景,我们该如何做?我们知道,想数据库中的数据一致,那么只有两种情况
下可以做到。
第一、同一时刻取出所有数据;
第二、数据库中的数据处于静止状态。
对于第一种情况,大家肯定会想,这可能吗?不管如何,只要有两个以上的表,就算我
们如何写程序,都不可能完全一致的取数时间点啊。是的,我们确实无法通过常规方法
让取数的时间点完全一致,但是大家不要忘记,在同一个事务中,数据库是可以做到所读取
的数据是处于同一个时间点的。所以,对于事务支持的存储引擎,如 Innodb 或者 BDB 等；
我们就可以通过控制将整个备份过程控制在同一个事务中,来达到备份数据的一致性和
完整性,而且 mysqldump 程序也给我们提供了相关的参数选项来支持该功能,就是通过
“--single-transaction”选项,可以不影响数据库的任何正常服务。
如：mysqldump -h192.168.0.119 -uroot -p123456 --single-transaction xmen>/tmp/backup.sql
对于第二种情况我想大家首先想到的肯定是将需要备份的表锁定,只允许读取而不允许
写入。是的,我们确实只能这么做。我们只能通过一个折衷的处理方式,让数据库在备份过
程中仅提供数据的查询服务,锁定写入的服务,来使数据暂时处于一个一致的不会被修改的
状态,等 mysqldump 完成备份后再取消写入锁定,重新开始提供完整的服务。mysqldump 程
序自己也提供了相关选项如“--lock-tables”和“--lock-all-tables”,在执行之前会锁定表,执行结
束后自动释放锁定。这里有一点需要注意的就是,“--lock-tables”并不是一次性将需要 dump
的所有表锁定,而是每次仅仅锁定一个数据库的表,如果你需要 dump 的分别在多个不同的数
据库中,一定要使用“--lock-all-tables”才能确保数据的一致完整性。
当通过 mysqldump 生成 INSERT 语句的逻辑备份文件的时候,有一个非常有用的选项可
以供我们使用,那就是“--master-data[=value]”。当添加了“--master-data=1”的时候,
mysqldump 会将当前 MySQL 使用到 binlog 日志的名称和位置记录到 dump 文件中,并且是被
以 CHANGE_MASTER 语句的形式记录,如果仅仅只是使用“--master-data”或者“--master-
data=2”,则 CHANGE_MASTER 语句会以注释的形式存在。这个选项在实施 slave 的在线搭建
的时候是非常有用的,即使不是进行在线搭建 slave,也可以在某些情况下做恢复的过程中
通过备份的 binlog 做进一步恢复操作。
在某些场景下,我们可能只是为了将某些特殊的数据导出到其他数据库中,而又不希望
通过先建临时表的方式来实现,我们还可以在通过 mysqldump 程序的“—where='where-
condition'”来实现,但只能在仅 dump 一个表的情况下使用。
其实除了以上一些使用诀窍之外,mysqldump 还提供了其他很多有用的选项供大家在不
同的场景下只用,如通过“--no-data”仅仅 dump 数据库结构创建脚本,通过“--no-create-
info”去掉 dump 文件中创建表结构的命令等等,感兴趣的读者朋友可以详细阅读 mysqldump
程序的使用介绍再自行测试。
2、生成特定格式的纯文本备份数据文件备份
除了通过生成 INSERT 命令来做逻辑备份之外,我们还可以通过另外一种方式将数据库
中的数据以特定分隔字符将数据分隔记录在文本文件中,以达到逻辑备份的效果。这样的备
份数据与 INSERT 命令文件相比,所需要使用的存储空间更小,数据格式更加清晰明确,编
辑方便。但是缺点是在同一个备份文件中不能存在多个表的备份数据,没有数据库结构的重
建命令。对于备份集需要多个文件,对我们产生的影响无非就是文件多了维护和恢复成本增
加,但这些基本上都可以通过编写一些简单的脚本来实现.
那我们一般可以使用什么方法来生成这样的备份集文件呢,其实 MySQL 也已经给我们实
现的相应的功能。
SELECT ... TO OUTFILE FROM ..
在 MySQL 中提供了一种 SELECT 语法,专供用户通过 SQL 语句将某些特定数据以指定格
式输出到文本文件中,同时也提供了实用工具和相关的命令可以方便的将导出文件原样再导
入到数据库中。这不正是我们做备份所需要的么?
该命令有几个需要注意的参数如下:
实现字符转义功能的“FIELDS ESCAPED BY ['name']” 将 SQL 语句中需要转义的字符进行转义;
可以将字段的内容“包装”起来的“FIELDS [OPTIONALLY] ENCLOSED BY 'name'”,如果不使用
“OPTIONALLY”则包括数字类型的所有类型数据都会被“包装”,使 用“OPTIONALLY”
之后,则数字类型的数据不会被指定字符“包装”。
通过"FIELDS TERMINATED BY"可以设定每两个字段之间的分隔符;
而通过“LINES TERMINATED BY”则会告诉 MySQL 输出文件在每条记录结束的时候需要
添加什么字符。（如\n换行）
如以下示例:
root@localhost : test 10:02:02> SELECT * INTO OUTFILE '/tmp/dump.text'
-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> FROM test_outfile limit 100;
Query OK, 100 rows affected (0.00 sec)
root@localhost : test 10:02:11> exit
Bye
root@sky:/tmp# cat dump.text
350021,21,"A","abcd"
350022,22,"B","abcd"
350023,23,"C","abcd"
350024,24,"D","abcd"
350025,25,"A","abcd"
... ...
mysql 默认文件路径保存在/tmp中
也可使用命令查找：sudo find / -name 'dump.text'查找文件名
3.逻辑备份恢复方法
仅仅有了备份还是不够啊,我们得知道如何去使用这些备份,现在我们就看看上面所做
的逻辑备份的恢复方法:
由于所有的备份数据都是以我们最初数据库结构的设计相关的形式所存储,所以逻辑备
份的恢复也相对比较简单。当然,针对两种不同的逻辑备份形式,恢复方法也稍有区别。下
面我们就分别针对这两种逻辑备份文件的恢复方法做一个简单的介绍。
1、INSERT 语句文件的恢复:
对于 INSERT 语句形式的备份文件的恢复是最简单的,我们仅仅只需要运行该备份文件
中的所有(或者部分)SQL 命令即可。首先,如果需要做完全恢复,那么我们可以通过使用
“mysql < backup.sql”直接调用备份文件执行其中的所有命令,将数据完全恢复到备份时
候的状态。如果已经使用 mysql 连接上了 MySQL,那么也可以通过在 mysql 中执行“
source /path/backup.sql”或者“\. /path/backup.sql”来进行恢复。
如果未连接mysql可这样恢复：
mysql -uroot -phello1234 db_name < /tmp/backup.sql
2、纯数据文本备份的恢复:
如果是上面第二中形式的逻辑备份,恢复起来会稍微麻烦一点,需要一个表一个表通过
相关命令来进行恢复,当然如果通过脚本来实现自动多表恢复也是比较方便的。恢复方法也
有两个,一是通过 MySQL 的“LOAD DATA INFILE”命令来实现,另一种方法就是通过 MySQL
提供的使用工具 mysqlimport 来进行恢复。
逻辑备份能做什么?不能做什么?
在清楚了如何使用逻辑备份进行相应的恢复之后,我们需要知道我们可以利用这些逻辑
备份做些什么。
1、通过逻辑备份,我们可以通过执行相关 SQL 或者命令将数据库中的相关数据完全恢
复到备份时候所处的状态,而不影响不相关的数据;
2、通过全库的逻辑备份,我们可以在新的 MySQL 环境下完全重建出一个于备份时候完
全一样的数据库,并且不受 MySQL 所处的平台类型限制;
3、通过特定条件的逻辑备份,我们可以将某些特定数据轻松迁移(或者同步)到其他
的 MySQL 或者另外的数据库环境;
4、通过逻辑备份,我们可以仅仅恢复备份集中的部分数据而不需要全部恢复。
在知道了逻辑备份能做什么之后,我们必须还要清楚他不能做什么,这样我们自己才能
清楚的知道这样的一个备份能否满足自己的预期,是否确实是自己想要的。
1、逻辑备份无法让数据恢复到备份时刻以外的任何一个时刻;
4.逻辑备份恢复测试
时有听到某某的数据库出现问题,而当其信心十足的准备拿之前所做好的数据库进行恢
复的时候才发现自己的备份集不可用,或者并不能达到自己做备份时候所预期的恢复效果。
遇到这种情景的时候,恐怕每个人都会郁闷至极的。数据库备份最重要最关键的一个用途就
是当我们的数据库出现某些异常状况,需要对数据进行恢复的时候使用的。作为一个维护人
员,我们是绝对不应该出现此类低级错误的。那我们到底该如何避免此类问题呢?只有一个
办法,那就是周期性的进行模拟恢复测试,校验我们的备份集是否真的有效,是否确实能够
按照我们的备份预期进行相应的恢复。
到这里可能有人会问,恢复测试又该如何做呢,我们总不能真的将线上环境的数据进行
恢复啊?是的,线上环境的数据确实不能被恢复,但是我们为什么不能在测试环境或者其他
的地方做呢?做恢复测试只是为了验证我们的备份是否有效,是否能达到我们的预期。所以
在做恢复测试之前我们一定要先清楚的知道我们所做的备份到底是为了应用于什么样的场
景的。就比如我们做了一个全库的逻辑备份,目的可能是为了当数据库出现逻辑或者物理异
常的时候能够恢复整个数据库的数据到备份时刻,那么我们做的恢复测试就只需要将整个逻
辑备份进行全库恢复,看是否能够成功的重建一个完整的数据库。至于恢复的数据是否和备
份时刻一致,就只能依靠我们自己来人工判断比较。此外我们可能还希望当某一个数据库对
象,比如某个表出现问题之后能够尽快的恢复该表数据到备份时刻。那么我们就可以针对单
个指定表进行抽样恢复测试。
下面我们就假想数据库主机崩溃,硬件损坏,造成数据库数据全部丢失,来做一次全库
恢复的测试示例:
当我们的数据库出现硬件故障,数据全部丢失之后,我们必须尽快找到一台新的主机以
顶替损坏的主机来恢复相应的服务。在恢复服务之前,我们首先需要重建损坏的数据库。假
设我们已经拿到了一台新的主机,MySQL 软件也已经安装就位,相关设置也都已经调整好,
就等着恢复数据库了。
我们需要取回离崩溃时间最近的一次全库逻辑备份文件,复制到准备的新主机上,启动
已经安装好的 MySQL。
由于我们有两种逻辑备份格式,每种格式的恢复方法并不一样,所以这里将对两种格式
的逻辑备份的恢复都进行示例。
1、如果是 INSERT 语句的逻辑备份
a、准备好备份文件,copy 到某特定目录,如“/tmp”下;
b、通过执行如下命令执行备份集中的相关命令:
mysql -uusername -p < backup.sql
或者先通过 mysql 登录到数据库中,然后再执行如下命令:
mysql> source /tmp/backup.sql
c、再到数据库中检查相应的数据库对象,看是否已经齐全;
d、抽查几个表中的数据进行人工校验,并通知开启应用内部测试校验,当所有校验都
通过之后,即可对外提供服务了。
当然上面所说的步骤都是在默认每一步都正常的前提下进行的,如果发现某一步有问
题。假若在 b 步骤出现异常,无法继续进行下去,我们首先需要根据出现的错误来排查是否
是我们恢复命令有错?是否我们的环境有问题等?等等。如果我们确认是备份文件的问题,
那么说明我们的这个备份是无效的,说明测试失败了。如果我们恢复过程很正常,但是在校
验的时候发现缺少数据库对象,或者某些对象中的数据不正确,或者根本没有数据。同样说
明我们的备份级无法满足预期,备份失败。当然,如果我们是在实际工作的恢复过程中遇到
类似情况的时候,如果还有更早的备份集,我们必须退一步使用更早的备份集做相同的恢复
操作。虽然更早的备份集中的数据可能会有些失真,但是至少可以部分恢复,而不至于丢失
所有数据。
2、如果我们是备份的以特殊分隔符分隔的纯数据文本文件
a、第一步和 INSERT 备份文件没有区别,就是将最接近崩溃时刻的备份文件准备好;
b、通过特定工具或者命令将数据导入如到数据库中:
由于数据库结构创建脚本和纯文本数据备份文件分开存放,所以我们首先需要执行数据
库结构创建脚本,然后再导入数据。结构创建脚本的方法和上面第一种备份的恢复测试中的
b 步骤完全一样。
有了数据库结构之后,我们就可以导入备份数据了,如下:
mysqlimport --user=name --password=pwd test --fields-enclosed-by=\" --
fields-terminated-by=, /tmp/test_outfile.txt
或者
LOAD DATA INFILE '/tmp/test_outfile.txt' INTO TABLE test_outfile FIELDS
TERMINATED BY '"' ENCLOSED BY ',';
后面的步骤就和备份文件为 INSERT 语句备份的恢复完全一样了,这里就不再累述。