mysql的备份与修复

部里讨论新网站的数据库平台时，同事提及了mysql常用的备份策略（导出sql脚本）可能在恢复的时候出现问题，需要再仔细考察一下mysql的备份与恢复功能。上午抽空看了一下mysql手册，做一下笔记。

1、MyISAM 表类型的数据库以文件的形式保存，所以直接拷贝备份与恢复即可。需要注意的是，如果要保证备份的一致性，要先对相关表执行LOCK TABLES操作（只需读锁定即可），然后对表执行FLUSH TABLES操作（用于确保在备份之前所有内存中的数据被写入硬盘）。这种方式通过shell比较容易实现定期备份。

2、导出文本文件备份（sql级脚本）：

1. SELECT * INTO OUTFILE 'file_name' FROM tbl_name；

这样会在服务器上生成备份文件，需要注意的是它不能覆盖已有文件，所以输出的’file_name’要确保不重名。恢复备份可以使用 LOAD DATA INFILE 操作或 mysqlimport 工具。

3、使用 mysqldump 进行sql级别的备份：

1. shell>  mysqldump --tab=/path/to/some/dir --opt db_name

mysqldump 的一些主要参数（全部参数）：

* –compatible=name
导出的数据将和哪种数据库或哪个旧版本的 MySQL 服务器相兼容,值可以为 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等，要使用几个值，用逗号将它们隔开。

* –complete-insert，-c
导出的数据采用包含字段名的完整 INSERT 方式，也就是把所有的值都写在一行。这么做能提高插入效率，但是可能会受到 max_allowed_packet 参数的影响而导致插入失败。需要谨慎使用该参数。

* –default-character-set=charset
指定导出数据时采用何种字符集，如果数据表不是采用默认的 latin1 字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。

* –disable-keys
告诉 mysqldump 在 INSERT 语句的开头和结尾增加 /*!40000 ALTER TABLE table DISABLE KEYS */; 和 /*!40000 ALTER TABLE table ENABLE KEYS */; 语句，这能大大提高插入语句的速度，因为它是在插入完所有数据后才重建索引的。该选项只适合 MyISAM 表。

* –extended-insert = true|false
默认情况下，mysqldump 开启 –complete-insert 模式，因此不想用它的的话，就使用本选项，设定它的值为 false 即可。

* –hex-blob
使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项。影响到的字段类型有 BINARY、VARBINARY、BLOB。

* –lock-all-tables，-x
在开始导出之前，提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭 –single-transaction 和 –lock-tables 选项。

* –lock-tables
它和 –lock-all-tables 类似，不过是锁定当前导出的数据表，而不是一下子锁定全部库下的表。本选项只适用于 MyISAM 表，如果是 Innodb 表可以用 –single-transaction 选项。

* –no-create-info，-t
只导出数据，而不添加 CREATE TABLE 语句。

* –no-data，-d
不导出任何数据，只导出数据库表结构。

* –opt
这只是一个快捷选项，等同于同时添加 –add-drop-tables –add-locking –create-option –disable-keys –extended-insert –lock-tables –quick –set-charset 选项。本选项能让 mysqldump 很快的导出数据，并且导出的数据能很快导回。该选项默认开启，但可以用 –skip-opt 禁用。注意，如果运行 mysqldump 没有指定 –quick 或 –opt 选项，则会将整个结果集放在内存中。如果导出大数据库的话可能会出现问题。

* –quick，-q
该选项在导出大表时很有用，它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。

* –routines，-R
导出存储过程以及自定义函数。

* –single-transaction
该选项在导出数据之前提交一个 BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 InnoDB 和 BDB。本选项和 –lock-tables 选项是互斥的，因为 LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用 –quick 选项。

* –triggers
同时导出触发器。该选项默认启用，用 –skip-triggers 禁用它。

例：

1. #这个语句用于MyISAM类型表，如果是事物表如Innodb，请将锁定表的-x参数换成--single-transaction
2. mysqldump -uyejr -pyejr --default-character-set=utf8 --opt --extended-insert=false --triggers -R --hex-blob -x db_name > db_name.sql

恢复的话通过mysql客户端导入sql脚本即可：

1. mysql -uyejr -pyejr db_name < db_name.sql

4、使用 mysqlhotcopy 对MyISAM 表类型的数据库进行备份：

1. shell> mysqlhotcopy db_name /path/to/some/dir

mysqlhotcopy是一个 PERL 程序，通过 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库，是备份数据库或单个表的最快的途径，支持一次性拷贝多个数据库，同时还支持正则表达，但只能用于备份 MyISAM类型数据库。

例：

1. #单个数据库
2. mysqlhotcopy -h=localhost -u=yejr -p=yejr db_name /tmp

1. #多个数据库
2. mysqlhotcopy -h=localhost -u=yejr -p=yejr db_name_1 ... db_name_n /tmp

1. #正则表达式
2. mysqlhotcopy -h=localhost -u=yejr -p=yejr db_name./regex/ /tmp

恢复直接将备份文件拷贝到 mysqld 指定的 datadir 即可。
例：

1. root#cp -rf db_name /usr/local/mysql/data/
2. root#chown -R nobody:nobody /usr/local/mysql/data/ (将 db_name 目录的属主改成 mysqld 运行用户)

5、启用二进制日志（The Binary Log）实现增量备份

二进制日志包含了更新日志中的所有可用信息，包括了所有更新了数据或者已经潜在更新了数据（如没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改，记录更新数据的语句的执行时间信息，但不包含没有修改任何数据的语句（查询日志）。这应该是要采用的备份方法，比较灵活方便，同时它还支持主从服务器备份。手册上说启用二进制日志会使得性能有1%的下降，对我们的小应用来说，实在算不上什么，呵呵。

不过，这个方式涉及到的方面有点多，我还在啃文档，可能还需要做一下实验，才能把它摸透，暂时不做笔记了。

本文除了参考mysql官方网站外，还参考并引用了叶金荣先生的《MySQL 备份和恢复》一文中许多内容。

（注：本笔记讨论的是mysql 5.1）