MySQL参考手册第7章：数据库的备份与恢复

1 备份与恢复类型

1.1 物理备份与逻辑备份

物理备份复制存储数据内容的目录和文件，这种方式适合大型及重要的企业数据库。

• 输出压缩比大
• 备份方式比较快
• 备份时需要锁定相应的库、表
• 可以备份对应的日志和配置文件
• 备份只能在具有相同物理特性的设备上使用
• 备份包含了精确的数据库目录和文件的复本

逻辑备份保存生成数据库及数据的SQL语句，这种方式适合少量数据的数据库。

• 输出压缩比小
• 备份过程相对较慢
• 可以指定服务器级、库、表级进行备份，与存储引擎无关
• 备份时不会备份配置、日志等内容
• 可使用mysqldump或select..into outfile进行备份
• 可使用mysqlimport ,load data infile等进行恢复

1.2 在线与离线备份

在线备份发生在服务器运行时

• 在线备份不影响用户使用
• 必须关注合适的锁机制避免新数据造成不一致

离线备份发生在服务器关闭时

• 备份需要停机，会影响用户使用，因而通常在从库上进行离线备份
• 备份相对简单，不受活动用户干扰

1.3 本地与远程备份

本地备份运行与本地服务器上，而远程备份则在另外一台设备上实施。

• mysqldump支持本地和远程备份
• mysqlhotcopy只能进行本地备份
• select...into outfile可以通过本地或远程客户端发起，但数据将备份在本地
• 物理备份通常发起于本地，但数据可以备份到远程设备上

1.4 全备份与增量备份

全量备份包含了指定时间点时的所有数据，增量备份只包含给定时间段内的部分备份。

1.5 全量还原与增量还原

全量还原指从一个全备份进行还原。如果此次还原仍不完整，则还需要进行增量备份的还原。

1.6 其他

对于相关需求如备份调度、压缩及加密会针对不同的需求进行。

2 数据库备份方法

2.1 MySQL企业版的备份

企业版提供了强大的在线物理备份功能，对于企业级用户是一个非常好的选择。

2.2 使用mysqldump与mysqlhotcopy等工具

对于MyISAM引擎，可以使用mysqlhotcopy进行在线热备份。

对于InnoDB引擎，可以使用mysqldump --single-transaction进行在线热备份。

2.3 复制文件进行备份

对于MyISAM引擎的表，可在锁定表后备份.frm .MYD和.MYI文件。

2.4 制作基于文本的备份

可以使用select * into outfile 'filename'在服务器上创建表的数据备份。

2.5 增量备份

做增量备份需要服务器上启用log-bin选项。

2.6 使用文件系统快照

如果使用Veritas文件系统，可以使用快照来备份数据。

首先锁定表，flush tables with read lock

然后创建快照，mount vxfs snapshot

解锁 unlock tables

复制快照中数据

卸载snapshot

3 备份与恢复示例

假设备份名为test的数据库，当前时间为$date

mysqldump --single-transaction --flush-logs --add-drop-database --master-data=2 [--databases] test >$date.sql

在全量备份以后，可以定时将后续新生成的binlog文件作为增量进行备份保存

当需要恢复的时

mysql -D test < $date.sql            //全量还原

查看$date.sql，获取LSN的值为N

mysqlbinlog -d test --disable-log-bin --start-pos=N mysql-bin.0000*  |mysql  //增量还原

对于mysqldump与mysqlbinlog的使用，参考 第4章。

4 MyISAM表的维护与崩溃恢复

使用myisamchk -m table对表文件进行检查

若检查出有问题myisamchk -r table