MySQL 备份与恢复

架构师成长历程 06-01 350

前言：

如今各位老铁们对“mysql恢复同步”大概比较珍视，我们都想要知道一些“mysql恢复同步”的相关知识。那么小编也在网摘上网罗了一些对于“mysql恢复同步””的相关内容，希望大家能喜欢，同学们一起来了解一下吧！

数据库的备份与恢复一直都是 DBA 工作中最为重要的部分之一，也是基本工作之一。任何正式环境的数据库都必须有完整的备份计划和恢复测试，本章内容将主要介绍 MySQL

数据库的备份与恢复相关内容。

1.1 数据库备份使用场景

你真的明白了自己所做的数据库备份是要面对什么样的场景的吗？

我想任何一位维护过数据库的人都知道数据库是需要备份的，也知道备份数据库是数据库维护必不可少的一件事情。那么是否每一个人都知道自己所做的备份到底是为了应对哪些场景的呢？抑或者说我们每个人是否都很清楚的知道，为什么一个数据库需要作备份呢？读到这里，我想很多读者朋友都会嗤之以鼻，“备份的作用不就是为了防止原数据丢失吗，这谁不知道？”。确实，数据库的备份很大程度上的作用，就是当我们的数据库因为某些原因而造成部分或者全部数据丢失后，方便找回丢失的数据。但是，不同类型的数据库备份，所能应付情况是不一样的，而且，数据库的备份同时也还具有其他很多的作用。而且我想，每个人对数据库备份的作用的理解可能都会有部分区别。

下面我就列举一下我个人理解的我们能够需要用到数据库备份的一些比较常见的情况吧。

一、数据丢失应用场景

1、人为操作失误造成某些数据被误操作；

2、软件 BUG 造成数据部分或者全部丢失；

3、硬件故障造成数据库数据部分或全部丢失；

4、安全漏洞被入侵数据被恶意破坏；

二、非数据丢失应用场景

5、特殊应用场景下基于时间点的数据恢复；

6、开发测试环境数据库搭建；

7、相同数据库的新环境搭建；

8、数据库或者数据迁移；

上面所列出的只是一些常见的应用场景而已，除了上面这几种场景外，数据库备份还会有很多其他应用场景，这里就不一一列举了。那么各位读者过曾经或是现在所做的数据库备份到底是为了应对以上哪一种(或者几种) 场景？或者说，我们所做的数据库备份能够应对以上哪几种应用场景？不知道这个问题大家是否有考虑过。

我们必须承认，没有哪一种数据库备份能够解决所有以上列举的几种常见应用场景，即使仅仅只是数据丢失的各种场景都无法通过某一种数据库备份完美的解决，当然也就更不用说能够解决所有的备份应用场景了。

比如当我们遇到磁盘故障，丢失了整个数据库的所有数据，并且无法从已经出现故障的硬盘上面恢复出来的时候，我们可能必须通过一个实时或者有短暂时间差的复制备份数据库存在。当然如果没有这样的一个数据库，就必须要有最近时间的整个数据库的物理或者逻辑备份数据，并且有该备份之后的所有物理或者逻辑增量备份，以期望尽可能将数据恢复到出现故障之前最近的时间点。而当我们遇到认为操作失误造成数据被误操作之后，我们需要有一个能恢复到错误操作时间点之前的瞬间的备份存在，当然这个备份可能是整个数据库的备份，也可以仅仅只是被误操作的表的备份。而当我们要做跨平台的数据库迁移的时候，我们所需要的又只能是一个逻辑的数据库备份，因为平台的差异可能使物理备份的文件格式在两个平台上无法兼容。

既然没有哪一种很多中数据库备份能够完美的解决所有的应用场景，而每个数据库环境所需要面对的数据库备份应用场景又可能各不一样，可能只是需要面对很多种场景中的某一种或几种，那么我们就非常有必要指定一个合适的备份方案和备份策略，通过最简单的技术

和最低廉的成本，来满足我们的需求。

1.2 逻辑备份与恢复测试

1.2. 1 什么样的备份是数据库逻辑备份呢？

大家都知道，数据库在返回数据给我们使用的时候都是按照我们最初所设计期望的具有一定逻辑关联格式的形式一条一条数据来展现的，具有一定的商业逻辑属性，而在物理存储的层面上数据库软件却是按照数据库软件所设计的某种特定格式经过一定的处理后存放。

数据库逻辑备份就是备份软件按照我们最初所设计的逻辑关系，以数据库的逻辑结构对象为单位，将数据库中的数据按照预定义的逻辑关联格式一条一条生成相关的文本文件，以达到备份的目的。

1.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 程序的实现原理是通过我们给的参数信息加上数据库中的系统表信息来一个表一个表获取数据然后生成 INSERT 语句再写入备份文件中的。

这样就出现了一个问题，在系统正常运行过程中，很可能会不断有数据变更的请求正在执行，这样就可能造成在 mysqldump 备份出来的数据不一致。也就是说备份数据很可能不是同一个时间点的数据，而且甚至可能都没办法满足完整性约束。这样的备份集对于有些系统来说可能并没有太大问题，但是对于有些对数据的一致性和完整性要求比较严格系统来说问题就大了，就是一个完全无效的备份集。

对于如此场景，我们该如何做？我们知道，想数据库中的数据一致，那么只有两种情况下可以做到。

第一、同一时刻取出所有数据；

第二、数据库中的数据处于静止状态。

对于第一种情况，大家肯定会想，这可能吗？不管如何，只要有两个以上的表，就算我们如何写程序，都不可能昨晚完全一致的取数时间点啊。是的，我们确实无法通过常规方法让取数的时间点完全一致，但是大家不要忘记，在同一个事务中，数据库是可以做到所读取的数据是处于同一个时间点的。所以，对于事务支持的存储引擎，如 Innodb 或者 BDB 等，

我们就可以通过控制将整个备份过程控制在同一个事务中，来达到备份数据的一致性和完整性，而且 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”或者“--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 也已经给我们实现的相应的功能。

在 MySQL 中一般都使用以下两种方法来获得可以自定义分隔符的纯文本备份文件。

1、通过执行 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 输出文件在每条记录结束的时候需要添加什么字符。

如以下示例：

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> exitByeroot@sky:/tmp# cat dump.text350021,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/mysqldumptotal 0root@sky:~# mysqldump -uroot -T/tmp/mysqldump test test_outfile --fields- enclosed-by=\" --fields-terminated-by=,root@sky:~# ls -l /tmp/mysqldumptotal 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.txtroot@sky:~# cat /tmp/mysqldump/test_outfile.txt350021,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 1.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，就会针对每个表都会生成两个相对应的文件。

1.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.2.4 逻辑备份恢复测试

时有听到某某的数据库出现问题，而当其信心十足的准备拿之前所做好的数据库进行恢复的时候才发现自己的备份集不可用，或者并不能达到自己做备份时候所预期的恢复效果。遇到这种情景的时候，恐怕每个人都会郁闷至极的。数据库备份最重要最关键的一个用途就是当我们的数据库出现某些异常状况，需要对数据进行恢复的时候使用的。作为一个维护人员，我们是绝对不应该出现此类低级错误的。那我们到底该如何避免此类问题呢？只有一个办法，那就是周期性的进行模拟恢复测试，校验我们的备份集是否真的有效，是否确实能够按照我们的备份预期进行相应的恢复。

到这里可能有人会问，恢复测试又该如何做呢，我们总不能真的将线上环境的数据进行恢复啊？是的，线上环境的数据确实不能被恢复，但是我们为什么不能在测试环境或者其他的地方做呢？做恢复测试只是为了验证我们的备份是否有效，是否能达到我们的预期。所以在做恢复测试之前我们一定要先清楚的知道我们所做的备份到底是为了应用于什么样的场景的。

就比如我们做了一个全库的逻辑备份，目的可能是为了当数据库出现逻辑或者物理异常的时候能够恢复整个数据库的数据到备份时刻，那么我们恶的恢复测试就只需要将整个逻辑备份进行全库恢复，看是否能够成功的重建一个完整的数据库。至于恢复的数据是否和备份时刻一致，就只能依靠我们自己来人工判断比较。此外我们可能还希望当某一个数据库对象，比如某个表出现问题之后能够尽快的恢复该表数据到备份时刻。那么我们就可以针对单个指定表进行抽样恢复测试。

下面我们就假想数据库主机崩溃，硬件损坏，造成数据库数据全部丢失，来做一次全库恢复的测试示例：

当我们的数据库出现硬件故障，数据全部丢失之后，我们必须尽快找到一台新的主机以顶替损坏的主机来恢复相应的服务。在恢复服务之前，我们首先需要重建损坏的数据库。假设我们已经拿到了一台新的主机， MySQL 软件也已经安装就位，相关设置也都已经调整好，就等着恢复数据库了。

我们需要取回离崩溃时间最近的一次全库逻辑备份文件，复制到准备的新主机上，启动已经安装好的 MySQL。

由于我们有两种逻辑备份格式，每种格式的恢复方法并不一样，所以这里将对两种格式的逻辑备份的恢复都进行示例。

1、如果是 INSERT 语句的逻辑备份

a、准备好备份文件，copy 到某特定目录，如“/tmp”下；

b、通过执行如下命令执行备份集中的相关命令：

mysql -uusername -p < backup.sql

或者先通过 mysql 登录到数据库中，然后再执行如下命令：

root@localhost : (none) 09:59:40> 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 语句备份的恢复完全一样了，这里就不再累述。

1.3 物理备份与恢复测试

前面一节我们了解了如何使用 MySQL 的逻辑备份，并做了一个简单的逻辑备份恢复示例，在这一节我们再一起了解一些 MySQL 的物理备份。

1.3. 1 什么样的备份是数据库物理课备份

在了解 MySQL 的物理备份之前，我们需要先了解一下，什么是数据库物理备份？既然是物理备份，那么肯定是和数据库的物理对象相对应的。就如同逻辑备份根据由我们根据业务逻辑所设计的数据库逻辑对象所做的备份一样，数据库的物理备份就是对数据库的物理对象所做的备份。

数据库的物理对象主要由数据库的物理数据文件、日志文件以及配置文件等组成。在 MySQL 数据库中，除了MySQL 系统共有的一些日志文件和系统表的数据文件之外，每一种存储引擎自己还会有不太一样的物理对象，在之前第一篇的“MySQL 物理文件组成”中我们已经有了一个基本的介绍，在下面我们将详细列出几种常用的存储引擎各自所对应的物理对象 (物理文件)，以便在后面大家能够清楚的知道各种存储引擎在做物理备份的时候到底哪些文件是需要备份的哪些又是不需要备份的。

1.3.2 MySQL 物理备份所需文件

MyISAM 存储引擎

MyISAM 存储引擎的所有数据都存放在 MySQL 配置中所设定的“datadir”目录下。实际上不管我们使用的是 MyISAM 存储引擎还是其他任何存储引擎，每一个数据库都会在 “datadir”目录下有一个文件夹(包括系统信息的数据库 mysql 也是一样)。在各个数据库中每一个 MyISAM 存储引擎表都会有三个文件存在，分别为记录表结构元数据的“.frm”文件，存储表数据的“.MYD”文件，以及存储索引数据的“.MYI”文件。由于 MyISAM 属于非事务性存储引擎，所以他没有自己的日志文件。所以 MyISAM 存储引擎的物理备份，除了备份 MySQL 系统的共有物理文件之外，就只需要备份上面的三种文件即可。

Innodb 存储引擎

Innodb 存储引擎属于事务性存储引擎，而且存放数据的位置也可能与 MyISAM 存储引擎有所不同，这主要取决于我们对 Innodb 的“”相关配置所决定。决定 Innodb 存放数据位置的配置为 “ innodb_data_home_dir ” 、 “ innodb_data_file_path ” 和 “innodb_log_group_home_dir”这三个目录位置指定参数，以及另外一个决定 Innodb 的表空间存储方式的参数“innodb_file_per_table”。前面三个参数指定了数据和日志文件的存放位置，最后一个参数决定 Innodb 是以共享表空间存放数据还是以独享表空间方式存储数据。这几个参数的相关使用说明我们已经在第一篇的“MySQL 存储引擎介绍”中做了相应的解释，在 MySQL 的官方手册中也有较为详细的说明，所以这里就不再累述了。

如果我们使用了共享表空间的存储方式，那么 Innodb 需要备份备份 “ innodb_data_home_dir”和“innodb_data_file_path”参数所设定的所有数据文件， “datadir”中相应数据库目录下的所有 Innodb 存储引擎表的“.frm”文件；

而如果我们使用了独享表空间，那么我们除了备份上面共享表空间方式所需要备份的所有文件之外，我们还需要备份“datadir”中相应数据库目录下的所有“.idb”文件，该文件中存放的才是独享表空间方式下 Innodb 存储引擎表的数据。可能在这里有人文，既然是使用独享表空间，那我们为什么还要备份共享表空间“才使用到”的数据文件呢？其实这是很多人的一个共性误区，以为使用独享表空间的时候 Innodb 的所有信息就都存放在 “datadir”所设定数据库目录下的“.ibd”文件中。实际上并不是这样的，“.ibd”文件中所存放的仅仅只是我们的表数据而已，大家都很清楚， Innodb 是事务性存储引擎，他是需要 undo 和redo 信息的，而不管 Innodb 使用的是共享还是独享表空间的方式来存储数据，与事务相关的 undo 信息以及其他的一些元数据信息，都是存放在“innodb_data_home_dir”

和“innodb_data_file_path”这两个参数所设定的数据文件中的。所以要想 Innodb 的物理备份有效，“innodb_data_home_dir”和“innodb_data_file_path”参数所设定的数据文件不管在什么情况下我们都必须备份。

此外，除了上面所说的数据文件之外， Innodb 还有自己存放 redo 信息和相关事务信息的日志文件在“innodb_log_group_home_dir”参数所设定的位置。所以要想 Innodb 物理备份能够有效使用，我们还比需要备份“innodb_log_group_home_dir”参数所设定的位置的所有日志文件。

NDB Cluster 存储引擎

NDB Cluster 存储引擎(其实也可以说是 MySQL Cluster)的物理备份需要备份的文件主要有一下三类：

1、元数据(Metadata)：包含所有的数据库以及表的定义信息；

2、表数据(Table Records)：保存实际数据的文件；

3、事务日志数据(Transaction Log)：维持事务一致性和完整性，以及恢复过程中所需要的事务信息。

不论是通过停机冷备份，还是通过 NDB Cluster 自行提供的在线联机备份工具，或者是第三方备份软件来进行备份，都需要备份以上三种物理文件才能构成一个完整有效的备份集。当然，相关的配置文件，尤其是管理节点上面的配置信息，同样也需要备份。

1.3.3 各存储引擎常用物理备份方法

由于不同存储引擎所需要备份的物理对象(文件) 并不一样，且每个存储引擎对数据文件的一致性要求也不一样所以各个存储引擎在进行物理备份的时候所使用的备份方法也有区别。当然，如果我们是要做冷备份(停掉数据库之后的备份)，我们所需要做的事情都很简单，那就是直接 copy 所有数据文件和日志文件到备份集需要存放的位置即可，不管是何种存储引擎都可以这样做。由于冷备份方法简单，实现容易，所以这里就不详细说明了。

在我们的实际应用环境中，是很少有能够让我们可以停机做日常备份的情况的，我们只能在数据库提供服务的情况下来完成数据库备份。这也就是我们俗称的热物理备份了。下面我们就针对各个存储引擎单独说明各自最常用的在线(热) 物理备份方法。

MyISAM 存储引擎

上面我们介绍了 MyISAM 存储引擎文件的物理文件比较集中，而且不支持事务没有redo 和 undo 日志，对数据一致性的要求也并不是特别的高，所以 MyISAM 存储引擎表的物理备份也比较简单，只要将 MyISAM 的物理文件copy 出来即可。但是，虽然 MyISAM 存储引擎没有事务支持，对数据文件的一致性要求没有 Innodb 之类的存储引擎那么严格，但是 MyISAM 存储引擎的同一个表的数据文件和索引文件之间是有一致性要求的。当 MyISAM 存储引擎发现某个表的数据文件和索引文件不一致的时候，会标记该表处于不可用状态，并要求你进行修复动作，当然，一般情况下的修复都会比较容易。但是，即使数据库存储引擎本身对数据文件的一致性要求并不是很苛刻，我们的应用也允许数据不一致吗？我想答案肯定是否定

的，所以我们自己必须至少保证数据库在备份时候的数据是处于某一个时间点的，这样就要求我们必须做到在备份 MyISAM 数据库的物理文件的时候让 MyISAM 存储引擎停止写操作，仅仅提供读服务，其根本实质就是给数据库表加锁来阻止写操作。

MySQL 自己提供了一个使用程序 mysqlhotcopy，这个程序就是专门用来备份 MyISAM 存储引擎的。不过如果你有除了 MyISAM 之外的其他非事务性存储引擎，也可以通过合适的参数设置，或者微调该备份脚本，也都能通过 mysqlhotcopy 程序来完成相应的备份任务，基本用法如下：

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/testmysql@sky:/data/mysql/mydata$ ls -l /tmp/backup/total 4drwxr-xr-x 2 mysql mysql 4096 2008-10-19 21:57 testmysql@sky:/data/mysql/mydata$ ls -l /tmp/backup/test

total 39268

-rw-r----- 1

mysql

8658

2008-10-19

21:57

hotcopy_his.frm

-rw-r----- 1

mysql

2008-10-19

21:57

hotcopy_his.MYD

-rw-r----- 1

mysql

1024

2008-10-19

21:57

hotcopy_his.MYI

-rw-r----- 1

... ...

mysql

8586

2008-10-19