MySQL数据库备份与恢复（1） -- mysqldump

mysqldump 客户端是由 Igor Romanenko 编写的数据库备份程序。可以用它将整个或部分数据库导出备份或直接导出到另外一个数据库中（不一定非得是 MySQL 数据库）。一般来说，生成的转储文件会包含创建表或插入数据的 SQL ，或者两者兼有。另外， mysqldump 还可以生成 CSV 文件，带分隔符的文本文件或者 XML 。

注： mysqldump 是一个备份程序，并不用作恢复。

 

（如果你所创建的表都是 MyISAM 类型，推荐使用 mysqlhotcopy 进行备份和恢复，速度会比 mysqldump 更快。）

 

使用 mysqldump 一般归为三种形式：

 

shell> mysqldump [ options ] db_name [ tbl_name ... ]
shell> mysqldump [ options ] --database db_name ...
shell> mysqldump [ options ] -all-databases

 

如果 db_name 后未接表名，或使用 --databases 或 --all-database 选项， 整个库会被转储（ mysqldump 默认不会转储 INFORMATION_SCHEMA ）。

 

options 都是可选参数，有很多，一般使用默认值即可。其中有两项： --user 和 --password 用来指定连接数据库的用户名和密码。一般用法如下：

 

shell> mysqldump --user=user_name --password[=password] db_name > backup-file.sql 

 

user_name 和 password 替换成具体的用户名和密码。注意，密码可不在命令行中提供，稍后 MySQL 会提示输入密码。所以也可写成这样：

 

shell> mysqldump --user=user_name --password db_name > backup-file.sql 

也可以将 --user 和 --password 替换成 -u 和 -p，用法如下：

 

shell> mysqldump -u<user_name> -p[<password>] db_name > backup-file.sql 

同样，将 <user_name> 和 <password> 用真实值替换。

 

 

在做数据库恢复时，使用 mysql 命令，用法如下：

 

shell> mysql -u<user_name> -p db_name < backup-file.sql

注意：此处使用的是 mysql 而不是 mysqldump，因为 mysqldump 是用来做导出的。

 

在导入时，有可能会出现如下错误：

 

ERROR 2006 (HY000) at line <line_num> : MySQL server has gone away

这是因为在导入数据时，往往会把一张表的数据写成一个很大的 insert 语句，导致此 insert 语句超过了缓冲区的大小。一般有两个办法来解决这个问题：（1）把一个大的 SQL 语句拆成几个小的， 网上有这样的工具，如 SplitInsert；（2）加大缓冲区的容量，进入 MySQL 的安装目录，打开 my.ini ，增加或修改以下配置项：

 

max_allowed_packet=20M

值的大小取决于数据的多少，修改后需要重启数据库。

 

 

小技巧：

 

生成 backup-file.sql 后可手动在文件的最前处加入：

 

SET AUTOCOMMIT=0;
SET FOREIGH_KEY_CHECKS=0;

在文件的最后位置加入：

 

SET FOREIGN_KEY_CHECKS=1;
COMMIT;
SET AUTOCOMMIT=1;

这样在执行导入时速度会快非常多。

 

如果在 linux 上可以更加简便：

 

shell> echo 'SET AUTOCOMMIT=0;
SET FOREIGN_KEY_CHECKS=0;
' > pre.sql

shell> echo 'SET FOREIGN_KEY_CHECKS=1;
COMMIT;
SET AUTOCOMMIT=1;
' > post.sql

shell> cat pre.sql backup-file.sql post.sql | mysql --user=user_name --password db_name