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

一、什么样的备份是数据库物理课备份

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

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

二、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 是以共享表空间存放数据还是以独享表空间方式存储数据。

如果我们使用了共享表空间的存储方式，那么 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）的物理备份需要备份的文件主要有一下三类：

元数据（Metadata）：包含所有的数据库以及表的定义信息；
表数据（Table Records）：保存实际数据的文件；
事务日志数据（Transaction Log）：维持事务一致性和完整性，以及恢复过程中所需要的事务信息。

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

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

由于不同存储引擎所需要备份的物理对象（文件）并不一样，且每个存储引擎对数据文件的一致性要求也不一样所以各个存储引擎在进行物理备份的时候所使用的备份方法也有区别。当然，如果我们是要做冷备份（停掉数据库之后的备份），我们所需要做的事情都很简单，那就是直接 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/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
-rw-r----- 1 mysql mysql 20999600 2008-10-19 21:57 test_myisam.MYD
-rw-r----- 1 mysql mysql 10792960 2008-10-19 21:57 test_myisam.MYI
-rw-r----- 1 mysql mysql 8638 2008-10-19 21:57 test_outfile.frm
-rw-r----- 1 mysql mysql 2400 2008-10-19 21:57 test_outfile.MYD
-rw-r----- 1 mysql mysql 1024 2008-10-19 21:57 test_outfile.MYI
... ...

然后再在之前的执行锁定命令的 session 中解锁

root@localhost : test 10:00:57> unlock tables;
Query OK, 0 rows affected (0.00 sec)

这样就完成了一次物理备份，而且大家也从文件列表中看到了，备份中还有 CSV 存储引擎的表。

②Innodb 存储引擎

Innodb 存储引擎由于是事务性存储引擎，有 redo 日志和相关的 undo 信息，而且对数据的一致性和完整性的要求也比 MyISAM 要严格很多，所以 Innodb 的在线（热）物理备份要比 MyISAM 复杂很多，一般很难简单的通过几个手工命令来完成，大都是通过专门的 Innodb 在线物理备份软件来完成。

Innodb 存储引擎的开发者（Innobase 公司）开发了一款名为 ibbackup 的商业备份软件，专门实现 Innodb 存储引擎数据的在线物理备份功能。该软件可以在 MySQL 在线运行的状态下，对数据库中使用 Innodb 存储引擎的表进行备份，不过仅限于使用 Innodb 存储引擎的表。

由于这款软件并不是开源免费的产品，我个人也很少使用，主要也是下载的试用版试用而已，所以这里就不详细介绍了，各位读者朋友可以通过 Innobase 公司官方网站获取详细的使用手册进行试用

③NDB Cluster 存储引擎

NDB Cluster 存储引擎也是一款事务性存储引擎，和 Innodb 一样也有 redo 日志。NDB Cluter 存储引擎自己提供了备份功能，可以通过相关的命令实现。当然，停机冷备的方法也是有效的。

在线联机备份步骤如下：

连接上管理服务器；
在管理节点上面执行 “START BACKUP” 命令；
在管理节点上发出备份指令之后，管理节点会通知所有数据节点开始进行备份，并反馈通知结果。
管理节点在通知发出备份指令之前会生成一个备份号来唯一定位这次备份所产生的备份集。当各数据节点收到备份指令之后，就会开始进行备份操作。
当所有数据节点都完成备份之后，管理节点才会反馈“备份完成”的信息给客户端。

由于 NDB Cluster 的备份，备份指令是从管理节点发起，且并不会等待备份完成就会返回，所以也没办法直接通过 “Ctrl + c” 或者其他方式来中断备份进程，所以 NDB Cluster 提供了相应的命令来中断当前正在进行的备份操作，如下：

登录管理节点
执行 “ABORT BACKUP backup_id”，命令中的 backup_id 即之前发起备份命令的时候所产生的备份号。
管理结带你上会用消息“放弃指示的备份 backup_id”确认放弃请求，注意，则时候其实并没有收到数据节点对请求的实际回应。
然后管理节点才会将中断备份的指令发送到所有数据节点上面，然后当各个数据节点都中断备份并删除了当前产生的备份文件之后，才会返回“备份 backup_id 因＊＊＊而放弃”。至此，中断备份操作完成。

通过 NDB Cluster 存储引擎自己的备份命令来进行备份之后，会将前面所提到的三种文件存放在参与备份的节点上面，且被存放在三个不同的文件中，类似如下：

BACKUP-backup_id.node_id.ctl，内容包含相关的控制信息和元数据的控制文件。每个节点均会将相同的表定义（对于 Cluster 中的所有表）保存在自己的该文件中。
BACKUP-backup_id-n.node_id.data，数据备份文件，被分成多个不同的片段来保存，在备份过程中，不同的节点将保存不同的备份数据所产生的片段，每个节点保存的文件都会有信息指明数据所属表的部分，且在备份片段文件最后还包含了最后的校验信息，以确保备份能够正确恢复。
BACKUP-backup_id.node_id.log，事务日志备份文件中仅包含已提交事务的相关信息，且仅保存已在备份中保存的表上的事务，各个阶段所保存的日志信息也不一样，因为仅仅针对各节点所包含的数据记录相关的日志信息。

上面的备份文件命名规则中，backup_id 是指备份号，不同的备份集会针对有一个不同的备份号，node_id 则是指明该备份文件属于哪个数据节点，而在数据文件的备份文件中的 n 则是指明片段号。

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

和之前逻辑备份一样，光有备份是没有意义的，还需要能够将备份有效的恢复才行。物理备份和逻辑备份相比最大的优势就是恢复速度快，因为主要是物理文件的拷贝，将备份文件拷贝到需要恢复的位置，然后进行简单的才做即可。

①MyISAM 存储引擎

MyISAM 存储引擎由于其特性，物理备份的恢复也比较简单。

如果是通过停机冷备份或者是在运行状态通过锁定写入操作后的备份集来恢复，仅仅只需要将该备份集直接通过操作系统的拷贝命令将相应的数据文件复制到对应位置来覆盖现有文件即可。

如果是通过 mysqlhotcopy 软件来进行的在线热备份，而且相关的备份信息也记录进入了数据库中相应的表，其恢复操作可能会需要结合备份表信息来进行恢复。

②Innodb 存储引擎

对于冷备份，Innodb 存储引擎进行恢复所需要的操作和其他存储引擎没有什么差别，同样是备份集文件（包括数据文件和日志文件）复制到相应的目录即可。但是对于通过其他备份软件所进行的备份，就需要根据备份软件本身的要求来进行了。比如通过 ibbackup 来进行的备份，同样也需要通过他来进行恢复才可以，具体的恢复方法请通过该软件的使用手册来进行，这里就不详细介绍了。

③NDB Cluster 存储引擎

对于停机冷备，恢复方法和其他存储引擎也没有太多区别，只不过有一点需要特别注意的就是恢复的时候必须要将备份集中文件恢复到对应的数据节点之少，否则无法正确完成恢复过程。

而通过 NDB Cluster 所提供的备份命令来生成的备份集，需要使用专用的备份恢复软件 ndb_restore 来进行。ndb_restore 软件将从备份集中读取出备份相关的控制信息，而且 ndb_restore 软件必须在单独的数据节点上面分别进行。所以当初备份进行过程中有多少数据节点，现在就需要运行多少次 ndb_restore。而且，首次通过 ndb_restore 来进行恢复的话，还必须恢复元数据，也就是会重建所有的数据库和表。

Mysql必知必会：物理备份与恢复测试

一、什么样的备份是数据库物理课备份

二、MySQL 物理备份所需文件

①MyISAM 存储引擎

②Innodb 存储引擎

③NDB Cluster 存储引擎

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

①MyISAM 存储引擎

②Innodb 存储引擎

③NDB Cluster 存储引擎

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

①MyISAM 存储引擎

②Innodb 存储引擎

③NDB Cluster 存储引擎