第 5 章 MySQL 备份与恢复
前言
数据库的备份与恢复一直都是DBA 工作中最为重要的部分之一，也是基本工作之一。
任何正式环境的数据库都必须有完整的备份计划和恢复测试，本章内容将主要介绍MySQL
数据库的备份与恢复相关内容。
5.1 数据库备份使用场景
一、数据丢失应用场景
1、人为操作失误造成某些数据被误操作；
2、软件BUG 造成数据部分或者全部丢失；
3、硬件故障造成数据库数据部分或全部丢失；
4、安全漏洞被入侵数据被恶意破坏；
二、非数据丢失应用场景
5、特殊应用场景下基于时间点的数据恢复；
6、开发测试环境数据库搭建；
7、相同数据库的新环境搭建；
8、数据库或者数据迁移；
5.2 逻辑备份与恢复测试
5.2.1 什么样的备份是数据库逻辑备份呢？
大家都知道，数据库在返回数据给我们使用的时候都是按照我们最初所设计期望的具有
一定逻辑关联格式的形式一条一条数据来展现的，具有一定的商业逻辑属性，而在物理存储
的层面上数据库软件却是按照数据库软件所设计的某种特定格式经过一定的处理后存放。
数据库逻辑备份就是备份软件按照我们最初所设计的逻辑关系，以数据库的逻辑结构对
象为单位，将数据库中的数据按照预定义的逻辑关联格式一条一条生成相关的文本文件，以
达到备份的目的。
5.2.2 常用的逻辑备份
逻辑备份可以说是最简单，也是目前中小型系统最常使用的备份方式。在MySQL 中我们
常用的逻辑备份主要就是两种，一种是将数据生成可以完全重现当前数据库中数据的
INSERT 语句，另外一种就是将数据通过逻辑备份软件，将我们数据库表数据以特定分隔符
进行分隔后记录在文本文件中。
1、生成INSERT 语句备份
两种逻辑备份各有优劣，所针对的使用场景也会稍有差别，我们先来看一下生成INSERT
语句的逻辑备份。
在MySQL 数据库中，我们一般都是通过MySQL 数据库软件自带工具程序中的mysqldump
来实现声称INSERT 语句的逻辑备份文件。其使用方法基本如下：
Dumping definition and data mysql database or table
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
由于mysqldump 的使用方法比较简单，大部分需要的信息都可以通过运行“mysqldump -
-help”而获得。这里我只想结合MySQL 数据库的一些概念原理和大家探讨一下当我们使用
mysqldump 来做数据库逻辑备份的时候有些什么技巧以及需要注意一些什么内容。
对于如此场景，我们该如何做？我们知道，想数据库中的数据一致，那么只有两种情况
下可以做到。
第一、同一时刻取出所有数据；
第二、数据库中的数据处于静止状态。
在同一个事务中，数据库是可以做到所读取的数据是处于同一个时间点的。
我们就可以通过控制将整个备份过程控制在同一个事务中，来达到备份数据的一致性和完整
性，而且mysqldump 程序也给我们提供了相关的参数选项来支持该功能，就是通过“--
single-transaction”选项，可以不影响数据库的任何正常服务。
对于第二种情况我想大家首先想到的肯定是将需要备份的表锁定，只允许读取而不允许
写入。是的，我们确实只能这么做。我们只能通过一个折衷的处理方式，让数据库在备份过
程中仅提供数据的查询服务，锁定写入的服务，来使数据暂时处于一个一致的不会被修改的
状态，等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”或者“--masterdata＝
2”，则CHANGE_MASTER 语句会以注释的形式存在。这个选项在实施slave 的在线搭建
的时候是非常有用的，即使不是进行在线搭建slave，也可以在某些情况下做恢复的过程中
通过备份的binlog 做进一步恢复操作。
在某些场景下，我们可能只是为了将某些特殊的数据导出到其他数据库中，而又不希望
通过先建临时表的方式来实现，我们还可以在通过mysqldump 程序的“—where='wherecondition'”
来实现，但只能在仅dump 一个表的情况下使用。
其实除了以上一些使用诀窍之外，mysqldump 还提供了其他很多有用的选项供大家在不
同的场景下只用，如通过“--no-data”仅仅dump 数据库结构创建脚本，通过“--no-createinfo”
去掉dump 文件中创建表结构的命令等等，感兴趣的读者朋友可以详细阅读mysqldump
程序的使用介绍再自行测试。
2、生成特定格式的纯文本备份数据文件备份
除了通过生成INSERT 命令来做逻辑备份之外，我们还可以通过另外一种方式将数据库
中的数据以特定分隔字符将数据分隔记录在文本文件中，以达到逻辑备份的效果。这样的备
份数据与INSERT 命令文件相比，所需要使用的存储空间更小，数据格式更加清晰明确，编
辑方便。但是缺点是在同一个备份文件中不能存在多个表的备份数据，没有数据库结构的重
建命令。对于备份集需要多个文件，对我们产生的影响无非就是文件多了维护和恢复成本增
加，但这些基本上都可以通过编写一些简单的脚本来实现
那我们一般可以使用什么方法来生成这样的备份集文件呢，其实MySQL 也已经给我们实
现的相应的功能。
在MySQL 中一般都使用以下两种方法来获得可以自定义分隔符的纯文本备份文件。
1、通过执行SELECT ... TO OUTFILE FROM ...命令来实现
在MySQL 中提供了一种SELECT 语法，专供用户通过SQL 语句将某些特定数据以指定格
式输出到文本文件中，同时也提供了实用工具和相关的命令可以方便的将导出文件原样再导
入到数据库中。正不正是我们做备份所需要的么？
该命令有几个需要注意的参数如下：
实现字符转义功能的“FIELDS ESCAPED BY ['name']” 将SQL 语句中需要转义的字符
进行转义；
该命令有几个需要注意的参数如下：
实现字符转义功能的“FIELDS ESCAPED BY ['name']” 将SQL 语句中需要转义的字符
进行转义；
可以将字段的内容“包装”起来的“FIELDS [OPTIONALLY] ENCLOSED BY 'name'”，如
果不使用“OPTIONALLY”则包括数字类型的所有类型数据都会被“包装”，使用“OPTIONALLY”
之后，则数字类型的数据不会被指定字符“包装”。
通过"FIELDS TERMINATED BY"可以设定每两个字段之间的分隔符；
而通过“LINES TERMINATED BY”则会告诉MySQL 输出文件在每条记录结束的时候需要
添加什么字符。
如以下示例：
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"
2、通过mysqldump 导出
可能我们都知道mysqldump 可以将数据库中的数据以INSERT 语句的形式生成相关备份
文件，其实除了生成INSERT 语句之外，mysqldump 还同样能实现上面“SELECT ... TO
OUTFILE FROM ...”所实现的功能，而且同时还会生成一个相关数据库结构对应的创建脚本。
如以下示例：
root@sky:~# ls -l /tmp/mysqldump
total 0
root@sky:~# mysqldump -uroot -T/tmp/mysqldump test test_outfile --fieldsenclosed-
by=\" --fields-terminated-by=,
root@sky:~# ls -l /tmp/mysqldump
total 8
-rw-r--r-- 1 root root 1346 2008-10-14 22:18 test_outfile.sql
-rw-rw-rw- 1 mysql mysql 2521 2008-10-14 22:18 test_outfile.txt
root@sky:~# cat /tmp/mysqldump/test_outfile.txt
350021,21,"A","abcd"
350022,22,"B","abcd"
350023,23,"C","abcd"
350024,24,"D","abcd"
350025,25,"A","abcd"
root@sky:~# cat /tmp/mysqldump/test_outfile.sql
-- MySQL dump 10.11
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.0.51a-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `test_outfile`
--
DROP TABLE IF EXISTS `test_outfile`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `test_outfile` (
`id` int(11) NOT NULL default '0',
`t_id` int(11) default NULL,
`a` char(1) default NULL,
`mid` varchar(32) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2008-10-14 14:18:23
这样的输出结构对我们做为备份来使用是非常合适的，当然如果一次有多个表需要被
dump，就会针对每个表都会生成两个相对应的文件。
5.2.3 逻辑备份恢复方法
1、INSERT 语句文件的恢复：
对于INSERT 语句形式的备份文件的恢复是最简单的，我们仅仅只需要运行该备份文件
中的所有（或者部分）SQL 命令即可。首先，如果需要做完全恢复，那么我们可以通过使用
“mysql < backup.sql”直接调用备份文件执行其中的所有命令，将数据完全恢复到备份时
候的状态。如果已经使用mysql 连接上了MySQL，那么也可以通过在mysql 中执行“source
/path/backup.sql”或者“\. /path/backup.sql”来进行恢复。
2、纯数据文本备份的恢复：
如果是上面第二中形式的逻辑备份，恢复起来会稍微麻烦一点，需要一个表一个表通过
相关命令来进行恢复，当然如果通过脚本来实现自动多表恢复也是比较方便的。恢复方法也
有两个，一是通过MySQL 的“LOAD DATA INFILE”命令来实现，另一种方法就是通过MySQL
提供的使用工具mysqlimport 来进行恢复。
逻辑备份能做什么？不能做什么？
在清楚了如何使用逻辑备份进行相应的恢复之后，我们需要知道我们可以利用这些逻辑
备份做些什么。
1、通过逻辑备份，我们可以通过执行相关SQL 或者命令将数据库中的相关数据完全恢
复到备份时候所处的状态，而不影响不相关的数据；
2、通过全库的逻辑备份，我们可以在新的MySQL 环境下完全重建出一个于备份时候完
全一样的数据库，并且不受MySQL 所处的平台类型限制；
3、通过特定条件的逻辑备份，我们可以将某些特定数据轻松迁移（或者同步）到其他
的MySQL 或者另外的数据库环境；
4、通过逻辑备份，我们可以仅仅恢复备份集中的部分数据而不需要全部恢复。
在知道了逻辑备份能做什么之后，我们必须还要清楚他不能做什么，这样我们自己才能
清楚的知道这样的一个备份能否满足自己的预期，是否确实是自己想要的。
1、逻辑备份无法让数据恢复到备份时刻以外的任何一个时刻；
2、逻辑备份无法
由于我们有两种逻辑备份格式，每种格式的恢复方法并不一样，所以这里将对两种格式
的逻辑备份的恢复都进行示例。
1、如果是INSERT 语句的逻辑备份
a、准备好备份文件，copy 到某特定目录，如“/tmp”下；
b、通过执行如下命令执行备份集中的相关命令：
mysql -uusername -p < backup.sql
或者先通过mysql 登录到数据库中，然后再执行如下命令：
root@localhost : (none) 09:59:40> source /tmp/backup.sql
c、再到数据库中检查相应的数据库对象，看是否已经齐全；
d、抽查几个表中的数据进行人工校验，并通知开启应用内部测试校验，当所有校验都
通过之后，即可对外提供服务了。
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 语句备份的恢复完全一样了，这里就不再累述。
5.3 物理备份与恢复测试
数据库的物理备份就是对数据库的物理对象所做的备份。
数据库的物理对象主要由数据库的物理数据文件、日志文件以及配置文件等组成。在
MySQL 数据库中，除了MySQL 系统共有的一些日志文件和系统表的数据文件之外，每一种存
储引擎自己还会有不太一样的物理对象，在之前第一篇的“MySQL 物理文件组成”中我们已
经有了一个基本的介绍，在下面我们将详细列出几种常用的存储引擎各自所对应的物理对象
（物理文件），以便在后面大家能够清楚的知道各种存储引擎在做物理备份的时候到底哪些
文件是需要备份的哪些又是不需要备份的。
5.3.2 MySQL物理备份所需文件
不论是通过停机冷备份，还是通过NDB Cluster 自行提供的在线联机备份工具，或者
是第三方备份软件来进行备份，都需要备份以上三种物理文件才能构成一个完整有效的备份
集。当然，相关的配置文件，尤其是管理节点上面的配置信息，同样也需要备份。
5.3.3 各存储引擎常用物理备份方法
冷备份：关机拷贝。
热备份：开机执行文件拷贝，必须加锁保证表和索引的一致性。
mysqlhotcopy db_name[./table_regex/] [new_db_name | directory]
从上面的基本使用方法我们可以看到，mysqlhotcopy 出了可以备份整个数据库，指定
的某个表，还可以通过正则表达式来匹配某些表名来针对性的备份某些表。备份结果就是指
定数据库的文件夹下包括所有指定的表的相应物理文件。
mysqlhotcopy 是一个用perl 编写的使用程序，其主要实现原理实际上就是通过先LOCK
住表，然后执行FLUSH TABLES 动作，该正常关闭的表正常关闭，将该fsync 的数据都fsync，
然后通过执行OS 级别的复制（cp 等）命令，将需要备份的表或者数据库的所有物理文件都
复制到指定的备份集位置。
此外，我们也可以通过登录数据库中手工加锁，然后再通过操作系统的命令来复制相关
文件执行热物理备份，且在完成文件copy 之前，不能退出加锁的session（因为退出会自
动解锁），如下：
root@localhost : test 08:36:35> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
不退出mysql，在新的终端下做如下备份：
mysql@sky:/data/mysql/mydata$ cp -R test /tmp/backup/test
mysql@sky:/data/mysql/mydata$ ls -l /tmp/backup/
total 4
drwxr-xr-x 2 mysql mysql 4096 2008-10-19 21:57 test
mysql@sky:/data/mysql/mydata$ ls -l /tmp/backup/test
total 39268
-rw-r----- 1 mysql mysql 8658 2008-10-19 21:57 hotcopy_his.frm
-rw-r----- 1 mysql mysql 36 2008-10-19 21:57 hotcopy_his.MYD
-rw-r----- 1 mysql mysql 1024 2008-10-19 21:57 hotcopy_his.MYI
-rw-r----- 1 mysql mysql 8586 2008-10-19 21:57 memo_test.frm
... ...
-rw-rw---- 1 mysql mysql 8554 2008-10-19 22:01 test_csv.frm
-rw-rw---- 1 mysql mysql 0 2008-10-19 22:01 test_csv.MYD
-rw-rw---- 1 mysql mysql 1024 2008-10-19 22:01 test_csv.MYI
-rw-r----- 1 mysql mysql 8638 2008-10-19 21:57 test_myisam.frm
然后再在之前的执行锁定命令的session 中解锁
root@localhost : test 10:00:57> unlock tables;
Query OK, 0 rows affected (0.00 sec)
这样就完成了一次物理备份，而且大家也从文件列表中看到了，备份中还有CSV 存储引
擎的表。
5.3.4 各存储引擎常用物理备份恢复方法
和之前逻辑备份一样，光有备份是没有意义的，还需要能够将备份有效的恢复才行。物
理备份和逻辑备份相比最大的优势就是恢复速度快，因为主要是物理文件的拷贝，将备份文
件拷贝到需要恢复的位置，然后进行简单的才做即可。
5.5 备份策略的设计思路
1、对于较为核心的在线应用系统，比需要有在线备用主机通过MySQL 的复制进行相
应的备份，复制线程可以一直开启，恢复线程可以每天恢复一次，尽量让备机的数
据延后主机在一定的时间段之内。这个延后的时间多长合适主要是根据实际需求决
定，一般来说延后一天是一个比较常规的做法
2、对于重要级别稍微低一些的应用，恢复时间要求不是太高的话，为了节约硬件成本，
不必要使用在线的备份主机来单独运行备用MySQL，而是通过每一定的时间周期内
进行一次物理全备份，同时每小时（或者其他合适的时间段）内将产生的二进制日
志进行备份。这样虽然没有第一种备份方法恢复快，但是数据的丢失会比较少。恢
复所需要的时间由全备周期长短所决定。
3、而对于恢复基本没有太多时间要求，但是不希望太多数据丢失的应用场景，则可以
通过每一定时间周期内进行一次逻辑全备份，同时也备份相应的二进制日志。使用
逻辑备份而不使用物理备份的原因是因为逻辑备份实现简单，可以完全在线联机完
成，备份过程不会影响应用提供服务。
4、对于一些搭建临时数据库的备份应用场景，则仅仅只需要通过一个逻辑全备份即可
满足需求，都不需要用二进制日志来进行恢复，因为这样的需求对数据并没有太苛
刻的要求
5.6 小结
总的来说，MySQL 的备份与恢复都不是太复杂，方法也比较单一。姑且不说逻辑备份，
对于物理备份来说，确实是还不够完善。缺少一个开源的比较好的在线热物理备份软件，一
直是MySQL 一个比较大的遗憾，也是所有MySQL 使用者比较郁闷的事情。
当然，没有开源的备份软件使用，非开源的商业软件也还是有的，如比较著名的Zmanda
备份恢复软件，功能就比较全面，使用也不太复杂，在商业的MySQL 备份恢复软件市场上
有较高的占有率。而且，Zmanda 同时还提供社区版本的免费下载使用。
不过，稍微让人有所安慰的是MySQL 在实际应用场景中大多是有一台或者多台Slave
机器来作为热备的。在需要进行备份的时候通过Slave 来进行备份也不是太难，而且通过
暂时停止Slave 上面的SQL 线程，即可让Slave 机器停止所有数据写入操作，然后就可
以进行在线进行备份操作了。所以即使买不起商用软件或者不太想买关系也不是太大。
