本文讨论了mysql的备份和恢复机制，以及如何维护数据表，包括两种主要的表类型：MyISAM和Innodb。本文设计的MySQL版本是5.0.22。
目前MySQL支持的免费备份工具有mysqldump和mysqlhotcopy。它们也可以通过SQL语法进行备份：备份表或SELECT INTO OUTFILE，或备份二进制日志，或直接复制数据文件和相关配置文件。MyISAM表保存为文件，所以相对容易备份。可以使用上述方法。Innodb的所有表都保存在同一个数据文件ibdata1中(也可能是多个文件或者独立的表空间文件)，相对来说比较难备份。免费方案可以是复制数据文件、备份binlog或使用mysqldump。
1、mysqldump
1.1备份
Mysqldump是一种SQL级别的备份机制，它将数据表引导到SQL脚本文件中。在不同MySQL版本之间升级时相对合适，这也是最常用的备份方式。现在来说说mysqldump的一些主要参数：
- compatible=name它告诉mysqldump导出的数据将与哪个数据库或MySQL服务器的旧版本兼容。值可以是ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options等。要使用多个值，请用逗号分隔它们。当然，并不保证完全兼容，而是尽可能兼容。
-complete-INSERT，c导出的数据采用包括字段名在内的完全插入方式，即所有值都写在一行。这样可以提高插入效率，但是可能会受到max_allowed_packet参数的影响，导致插入失败。所以这个参数需要谨慎使用，至少我不推荐。
-default-character-set=charset指定导出数据时使用的字符集。如果数据表不使用默认的latin1字符集，则导出时必须指定该选项，否则再次导入数据后会出现乱码。
- disable-keys告诉mysqldump在INSERT语句的开头和结尾添加/*！40000 ALTER TABLE表禁用键*/；和/*！40000 ALTER TABLE表启用键*/；语句，这可以大大提高插入语句的速度，因为它会在插入所有数据后重建索引。此选项仅适用于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表，而- single-transaction选项可用于Innodb表。
-no - 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从服务器查询中获取记录并直接输出，而不是在获取所有记录后缓存在内存中。
-例程，-R导出存储过程和自定义函数。
- single-transaction该选项在导出数据之前提交BEGIN SQL语句。BEGIN不会阻止任何应用程序，并且可以保证导出时数据库的一致性。它只适用于事务表，如InnoDB和BDB。该选项和-lock-tables选项是互斥的，因为锁表将隐式提交任何挂起的事务。如果要导出一个大表，应该同时使用- quick选项。
- triggers同时导出触发器。默认情况下启用该选项，使用- skip-triggers禁用它。
其他参数详情请参考手册。我通常使用以下SQL来备份MyISAM表：
/usr/local/MySQL/bin/MySQL dump-uyejr-pye Jr
-default-character-set=utf8-opt-extended
-insert=false \-triggers-R-hex-blob-x数据库名称
》db_name.sql
使用以下SQL来备份Innodb表：
/usr/local/MySQL/bin/MySQL dump-uyejr-pye Jr-default
-character-set=utf8-opt-extended-insert=
false \ - triggers -R - hex-blob -单一事务数据库名称
》db_name.sql
1.2缩减
mysqldump备份的文件是一个SQL脚本，可以直接倒出来。有两种方法可以导入数据。
直接使用mysql客户端，例如：
/usr/local/MySQL/bin/MySQL-uyejr-pye Jr db _ name《db _ name . SQL
使用源语法。其实这并不是一个标准的SQL语法，而是mysql客户端提供的一个函数，比如：SOURCE/tmp/db _ name . SQL；
这里需要指定文件的绝对路径，并且必须是运行mysqld的用户(例如nobody)有权限读取的文件。
2、mysqlhotcopy
2.1备份
Mysqlhotcopy是一个PERL程序，最初由Tim Bunce编写。它使用锁表、刷新表和cp或scp来快速备份数据库。这是备份数据库或单个表的最快方法，但它只能在数据库文件(包括数据表定义文件、数据文件和索引文件)所在的计算机上运行。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=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，
您必须具有选择和重新加载(刷新表)的权限，并且还必须具有读取datadir/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备份
其实备份表的语法类似于mysqlhotcopy的工作原理，就是锁定表，然后复制数据文件。可以实现在线备份，但是效果不理想，不推荐。它只复制表结构文件和数据文件，不同时复制索引文件，所以恢复比较慢。示例：
将表tbl_name返回到“/tmp/db _ name/”；
注意，要执行这个SQL，您必须拥有文件权限，并且目录/tmp/db_name/必须对mysqld用户是可写的。导出的文件不能覆盖现有文件，以避免安全问题。
SELECT INTO OUTFILE是将数据导出到一个普通的文本文件中，可以自定义字段间隔，方便对这些数据的处理。
示例：
SELECT INTO OUTFILE '/tmp/db _ name/TBL _ name . txt ' FROM TBL _ name；
注意，要执行这个SQL，您必须拥有文件权限，并且文件/tmp/db_name/tbl_name.txt必须是mysqld用户可写的。导出的文件不能覆盖现有文件，以避免安全问题。
3.2恢复
使用备份表方法，可以运行RESTORE TABLE语句来还原数据表。示例：从“/tmp/db _ name/”中还原表；权限要求与上面描述的类似。
您可以运行LOAD DATA INFILE语句来还原由SELECT INTO OUTFILE方法备份的文件的数据表。示例：
将文件“/tmp/db_name/tbl_name.txt”中的数据加载到表tbl _ name
权限要求与上面描述的类似。在注入数据之前，数据表必须已经存在。如果担心数据重复，可以添加REPLACE关键字来替换现有记录，或者用IGNORE关键字忽略它们。
补充：
shell》MySQL dump-quick db _ name gzip》db _ name . contents . gz
(本例中创建的文件是压缩格式的)。
恢复/转移到另一个站点的命令如下：
shell》gunzip《db _ name . contents . gz MySQL db _ name
上述命令适用于装有*nix操作系统的机器。
4.启用binlog。
binlog方法相对灵活、省力，并且可以支持增量备份。
启用binlog时，必须重新启动mysqld。首先，关闭mysqld，打开my.cnf，添加以下几行：
server-id=1 log-bin=binloglog-bin-index=bin log . index
然后启动mysqld。运行过程中会生成Binlog.000001和binlog.index。前一个文件是mysqld对所有数据更新操作的记录，后一个文件。
