《MySQL 的备份和恢复机制》由会员分享，可在线阅读，更多相关《MySQL 的备份和恢复机制（7页珍藏版）》请在金锄头文库上搜索。
1、本文讨论MySQL的备份和恢复机制，以及如何维护数据表，包括最主要的两种 表类型：MylSAM和Innodb，文中设计的MySQL版本为5.0.22。目前MySQL支持的免费备份工具有：mysqldump、mysqlhotcopy，还可以用SQL 语法进行备份：BACKUP TABLE或者SELECT INTO OUTFILE，又或者备份二进制 日志（binlog），还可以是直接拷贝数据文件和相关的配置文件。MylSAM表是 保存成文件的形式，因此相对比较容易备份，上面提到的几种方法都可以使用。 Innodb所有的表都保存在同一个数据文件ibdata1中（也可能是多个文件，或 者是独立的表空间
2、文件），相对来说比较不好备份，免费的方案可以是拷贝数据 文件、备份binlog，或者用mysqldump。1、mysqldump1.1备份mysqldump是采用SQL级别的备份机制，它将数据表导成SQL脚本文件，在不 同的MySQL版本之间升级时相对比较合适，这也是最常用的备份方法。现在来讲一下mysqldump的一些主要参数：1. -compatible=name它告诉mysqldump，导出的数据将和哪种数据库或哪个旧版本的MySQL 服务器相兼容。值可以为 ansi、mysql323、mysql40、postgresql、oracle、 mssql、 db2、 maxdb、 no_ke
3、y_options、 no_tables_options、 no_field_options等，要使用几个值，用逗号将它们隔开。当然了，它 并不保证能完全兼容，而是尽量兼容。2. -complete-insert， -c导出的数据采用包含字段名的完整INSERT方式，也就是把所有的值都写 在一行。这么做能提高插入效率，但是可能会受到max_allowed_packet 参数的影响而导致插入失败。因此，需要谨慎使用该参数，至少我不推荐。3. -default-character-set二charset指定导出数据时采用何种字符集，如果数据表不是采用默认的latin1字 符集的话，那么导出时必须指
4、定该选项，否则再次导入数据后将产生乱码 问题。4. -disable-keys告诉mysqldump在INSERT语句的开头和结尾增加/*!40000 ALTERTABLE table DISABLE KEYS */;和 /*!40000 ALTER TABLE table ENABLEKEYS */;语句，这能大大提高插入语句的速度，因为它是在插入完所有 数据后才重建索引的。该选项只适合MylSAM表。5. -extended-insert = true|false默认情况下，mysqldump开启-complete-insert模式，因此不想用它 的的话，就使用本选项，设定它的值为fals
5、e即可。6. -hex-blob使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用 本选项。影响到的字段类型有BINARY、VARBINARY、BLOB。7. -lock-all-tables， -x在开始导出之前，提交请求锁定所有数据库中的所有表，以保证数据的一 致性。这是一个全局读锁，并且自动关闭-single-transaction和 -lock-tables 选项。8. -lock-tables它和-lock-all-tables类似，不过是锁定当前导出的数据表，而不是 一下子锁定全部库下的表。本选项只适用于MyISAM表，如果是Innodb 表可以用-single-tr
6、ansaction 选项。9. -no-create-info， -t只导出数据，而不添加CREATE TABLE语句。10. -no-data，-d不导出任何数据，只导出数据库表结构。11. opt这只是一个快捷选项，等同于同时添加-add-drop-tables-add-locking -create-option -disable-keys -extended-insert -lock-tables -quick-set-charset 选项。本选项能让 mysqldump 彳艮 快的导出数据，并且导出的数据能很快导回。该选项默认开启，但可以用 -skip-opt禁用。注意，如果运行my
7、sqldump没有指定-quick或 -opt选项，则会将整个结果集放在内存中。如果导出大数据库的话可能 会出现问题。12. quick， -q该选项在导出大表时很有用，它强制mysqldump从服务器查询取得记录 直接输出而不是取得所有记录后将它们缓存到内存中。13. -routines， -R导出存储过程以及自定义函数。14. -single-transaction该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN不会阻塞任何 应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 InnoDB 和 BDB。本选项和-lock-tables选项是互斥的，因为LOCK
8、TABLES会使任何挂 起的事务隐含提交。要想导出大表的话，应结合使用-quick选项。15. -triggers同时导出触发器。该选项默认启用，用-skip-triggers禁用它。其他参数详情请参考手册，我通常使用以下SQL来备份MyISAM表：/usr/local/mysql/bin/mysqldump -uyejr -pyejr-default-character-set=utf8 -opt -extended-insert二false /-triggers -R -hex-blob -x db_name db_name.sql使用以下SQL来备份Innodb表：/usr/local/
9、mysql/bin/mysqldump -uyejr -pyejr-default-character-set=utf8 -opt -extended-insert二false /-triggers -R -hex-blob -single-transaction db_name db_name.sql1.2还原用mysqldump备份出来的文件是一个可以直接倒入的SQL脚本，有两种方法可 以将数据导入。1. 直接用mysql客户端例如：/usr/local/mysql/bin/mysql -uyejr -pyejr db_name FLUSH TABLES和cp或scp来快速备份数据库。它是
10、备份数据库或单个表的最 快的途径，但它只能运行在数据库文件（包括数据表定义文件、数据文件、索引 文件）所在的机器上。mysqlhotcopy只能用于备份MyISAM，并且只能运行在 类 Unix和NetWare系统上。mysqlhotcopy支持一次性拷贝多个数据库，同时还支持正则表达。以下是几个 例子：root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p二yejr db_name /tmp （把数据库目录db_name拷贝到/tmp下） root#/usr/local/mysql/bin/mysqlhotcopy -h=
11、localhost -u二yejr -p二yejr db_name_1 . db_name_n /tmproot#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u二yejr -p二yejr db_name./regex/ /tmp更详细的使用方法请查看手册，或者调用下面的命令来查看mysqlhotcopy的帮 助：perldoc /usr/local/mysql/bin/mysqlhotcopy注意，想要使用 mysqlhotcopy，必须要有 SELECT RELOAD（要执行 FLUSH TABLES） 权限，并且还必须要能够有读取data
12、dir/db_name目录的权限。2.2还原mysqlhotcopy备份出来的是整个数据库目录，使用时可以直接拷贝到mysqld 指定的datadir （在这里是/usr/local/mysql/data/）目录下即可，同时要注意 权限的问题，如下例：root#cp -rf db_name /usr/local/mysql/data/root#chown -R nobody:nobody /usr/local/mysql/data/ （将 db_name 目录的 属主改成mysqld运行用户）3、SQL语法备份3.1备份BACKUP TABLE语法其实和mysqlhotcopy的工作原理差不多
13、，都是锁表，然后 拷贝数据文件。它能实现在线备份，但是效果不理想，因此不推荐使用。它只拷 贝表结构文件和数据文件，不同时拷贝索引文件，因此恢复时比较慢。例子：BACK TABLE tbl_name TO /tmp/db_name/;注意，必须要有FILE权限才能执行本SQL，并且目录/tmp/db_name/必须能 被mysqld用户可写，导出的文件不能覆盖已经存在的文件，以避免安全问题。SELECT INTO OUTFILE则是把数据导出来成为普通的文本文件，可以自定义字段 间隔的方式，方便处理这些数据。例子：SELECT INTO OUTFILE /tmp/db_name/tbl_name
14、.txt FROM tbl_name;注意，必须要有FILE权限才能执行本SQL，并且文件 /tmp/db_name/tbl_name.txt必须能被mysqld用户可写，导出的文件不能覆盖 已经存在的文件，以避免安全问题。3.2恢复用BACKUP TABLE方法备份出来的文件，可以运行RESTORE TABLE语句来恢复 数据表。例子：RESTORE TABLE FROM /tmp/db_name/;权限要求类似上面所述。用SELECT INTO OUTFILE方法备份出来的文件，可以运行LOAD DATA INFILE语 句来恢复数据表。例子：LOAD DATA INFILE /tmp/d
15、b_name/tbl_name.txt INTO TABLE tbl_name;权限要求类似上面所述。倒入数据之前，数据表要已经存在才行。如果担心数据 会发生重复，可以增加REPLACE关键字来替换已有记录或者用IGNORE关键字 来忽略他们。4、启用二进制日志(binlog)采用binlog的方法相对来说更灵活，省心省力，而且还可以支持增量备份。启用binlog时必须要重启mysqldo首先，关闭mysqld，打开f，加入 以下几行： server-id = 1log-bin = binloglog-bin-index = binlog.index然后启动mysqld就可以了。运行过程中会产生binlog.000001以及 binlog.index，前面的文件是
