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

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 存储引擎自己提供了备份功能，可以通过相关的命令实现。当然，停机冷备的方法也是有效的。

在线联机备份步骤如下：

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

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

1. 登录管理节点
2. 执行 “ABORT BACKUP backup_id”，命令中的 backup_id 即之前发起备份命令的时候所产生的备份号。
3. 管理结带你上会用消息“放弃指示的备份 backup_id”确认放弃请求，注意，则时候其实并没有收到数据节点对请求的实际回应。
4. 然后管理节点才会将中断备份的指令发送到所有数据节点上面，然后当各个数据节点都中断备份并删除了当前产生的备份文件之后，才会返回“备份 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 来进行恢复的话，还必须恢复元数据，也就是会重建所有的数据库和表。