MySQL MyISAM和InNodb备份与恢复技巧

1. 为什么要备份数据库

对数据库来说，最重要也最容易被忽视的就是备份。由于不可预测性，偶然的事件可能会导致非常惨重的损失。

数据越是重要，数据的变化越频繁，备份越发需要经常进行。

备份周期根据不同业务的需要可以调整，但是不能忽视备份。

备份时最好也备份my.cnf或my.ini，这样可以保存你以前的配置参数。

2. MyISAM 表备份/恢复策略

2.1. 文件热备份

2.1.1. 拷贝文件

因为MySQL表保存为文件方式，很容易备份。要想保持备份的一致性，对相关表执行LOCK TABLES操作，然后对表执行FLUSH TABLES。

你只需要读锁定;这样当你复制数据库目录中的文件时，允许其它客户继续查询表。需要FLUSH TABLES语句来确保开始备份前将所有激活的索引页写入硬盘。

标准流程：锁表-》刷新表到磁盘-》拷贝文件-》解锁。

2.1.2. 使用sql 语句备份

如果你想要进行SQL级别的表备份，你可以使用SELECT INTO ...OUTFILE或BACKUP TABLE。对于SELECT INTO ...OUTFILE， 输出的文件不能先存在。

对于BACKUP TABLE也如此，因为覆盖完整的文件会有安全风险。

这两种备份方法，如果输出文件有重名的话，最好把重名文件移除。

BACKUP TABLE备份时注意输出目录的权限，改方法只是备份MYD和frm文件，不备份索引。

2.1.3. 使用mysqlhotcopy 备份

mysqlhotcopy 是一个 Perl脚本，最初由Tim Bunce编写并提供。它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库目录所在的机器上。mysqlhotcopy 只用于备份MyISAM。它运行在Unix和NetWare中。

shell> mysqlhotcopy db_name [/path/to/new_directory ]

shell> mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

备份给定数据库中的匹配正则表达式的表：

shell> mysqlhotcopy db_name./regex/

加上发音符(‘~’)前缀，表名的正则表达式可以被否定：

shell> mysqlhotcopy db_name./~regex/

mysqlhotcopy支持下面的选项：

· ---help，-?

显示帮助消息并退出。

· --allowold

如果目标存在不放弃(加上一个_old后缀重新命名它)。

· --checkpoint=db_name.tbl_name

在指定的db_name.tbl_name插入检查点条目。

· ---debug

启用调试输出。

· --dryrun，-n

报告动作而不执行它们。

· --flushlog

所有表锁定后刷新日志。

· --keepold

完成后不删除以前(重新命名的)的目标。

· -- method=command

复制方法(cp或scp)。

· --noindices

备份中不包括全部索引文件。这样使备份更小、更快。可以在以后用myisamchk -rq重新构建索引。

· --password=password，-p password

当连接服务器时使用的密码。请注意该选项的密码值是不可选的，不象其它MySQL程序。

· --port=port_num，-P port_num

当连接本地服务器时使用的TCP/IP端口号。

· --quiet，-q

除了出现错误时保持沉默。

· --regexp=expr

复制所有数据库名匹配给出的正则表达式的数据库。

· --socket=path，-S path

用于连接的Unix套接字文件。

· --suffix=str

所复制的数据库名的后缀。

· --tmpdir=path

临时目录(代替/tmp)。

· --user=user_name，-u user_name

当连接服务器时使用的MySQL用户名。

mysqlhotcopy从选项文件读取[client]和[mysqlhotcopy]选项组。

因为mysqlhotcopy一般是用来做完全备份，所以推荐使用—flushlog选项来产生增量更新日志。

2.1.4. 使用mysqldump 备份

可以备份表结构和数据，可以同时支持MyISAM和InnoDB引擎数据库。

mysqldump可以备份单个表、单个库或所有库。

Mysqldump 还可以只导出表结构。

mysqldump是逻辑备份，输出的是sql语句文件，还可以输出其他数据库兼容的格式。

有3种方式来调用mysqldump：

shell> mysqldump [options] db_name [tables]

shell> mysqldump [options] ---database DB1 [DB2 DB3...]

shell> mysqldump [options] --all--database

如果没有指定任何表或使用了---database或--all--database选项，则转储整个数据库。

要想获得你的版本的mysqldump支持的选项，执行mysqldump ---help。

如果运行mysqldump没有--quick或--opt选项，mysqldump在转储结果前将整个结果集装入内存。如果转储大数据库可能会出现问题。该选项默认启用，但可以用--skip-opt禁用。

mysqldump支持下面的选项：

· ---help，-?

显示帮助消息并退出。

· --add-drop--database

在每个CREATE DATABASE语句前添加DROP DATABASE语句。

· --add-drop-tables

在每个CREATE TABLE语句前添加DROP TABLE语句。

· --add-locking

用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。

· --all--database，-A

转储所有数据库中的所有表。与使用---database选项相同，在命令行中命名所有数据库。

· --allow-keywords

允许创建关键字列名。应在每个列名前面加上表名前缀。

· ---comments[={0|1}]

如果设置为 0，禁止转储文件中的其它信息，例如程序版本、服务器版本和主机。--skip—comments与---comments=0的结果相同。 默认值为1，即包括额外信息。

· --compact

产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skip-disable-keys和--skip-add-locking选项。