本文讨论【lùn】 MySQL 的备份和恢复机制🦒，以及如何维护数据表【shù jù biǎo】🔱，包括最主要的【zhǔ yào de】两种表🐳类型🌎：MyISAM 和 Innodb，文中设【wén zhōng shè】计的【jì de】🐺 MySQL 版本为 5.0.22。

目前【mù qián】 MySQL 支持的免费备【miǎn fèi bèi】份【fèn】💗工具有：mysqldump、mysqlhotcopy，还可以用 SQL 语法进行备份【fèn】💗：BACKUP TABLE 或者 SELECT INTO OUTFILE，又或者🕰备份【fèn】💗二进制【èr jìn zhì】日🚝志【zhì】👗（binlog），还可以是直接🧕拷贝数【kǎo bèi shù】🍎据文件【wén jiàn】🙆和相关【hé xiàng guān】的配置文件【wén jiàn】🈂。MyISAM 表是保【biǎo shì bǎo】存成文件【wén jiàn】🈂的形式【de xíng shì】📲，因此相对比较容易备份【fèn】💗，上面提🌠到的几种方法⏺都可以使用😙。Innodb 所有的【suǒ yǒu de】表都保存在同一个数🖌据文件【wén jiàn】🙆 ibdata1 中【zhōng】（也可能【yě kě néng】🔨是多个【shì duō gè】👟文件【wén jiàn】🈂，或者是独立的表空间✅文件【wén jiàn】🈂），相对来【xiàng duì lái】说比较【shuō bǐ jiào】不好备【bú hǎo bèi】份【fèn】💗，免费的方案可【fāng àn kě】以是拷😦贝数据🤭文件【wén jiàn】🈂、备份【fèn】💗 binlog，或者用 mysqldump。

1、mysqldump

1.1 备份

mysqldump 是采用SQL级别的【de】🔷备份机制👴，它将数【tā jiāng shù】据表导⬛成【chéng】🐜 SQL 脚本文【jiǎo běn wén】件【jiàn】，在不同的【de】🔷 MySQL 版本之间升级【jiān shēng jí】💓时相对比较合适🤴，这也是🔖最常用【zuì cháng yòng】🈶的【de】🔷备份方法。现在来讲一下 mysqldump 的【de】🔷一些主要参数：

--compatible=name 它告诉🥪 mysqldump，导出的数据将和哪种数据库或哪个【huò nǎ gè】旧版本【jiù bǎn běn】🗄的 MySQL 服务器相兼容🛁。值可以【zhí kě yǐ】为 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等【děng】🌷，要使用几个值，用逗号将它们【jiāng tā men】隔开。当然了🉑，它并不【tā bìng bú】🥐保证能完全兼【wán quán jiān】📇容，而是尽量兼容【liàng jiān róng】🌬。

--complete-insert，-c 导出的【dǎo chū de】🎞数据采用包含字段名的完整💽 INSERT 方式【fāng shì】🐑，也就是把所有🏄的值都写在一行【háng】。这么做【zhè me zuò】🚃能提高插入效【chā rù xiào】率，但是可能会受🗾到🍪 max_allowed_packet 参数的影响而【yǐng xiǎng ér】导致插【dǎo zhì chā】📆入失败。因此【yīn cǐ】，需要谨慎使用该参数【gāi cān shù】，至少我🍿不推荐。

--default-character-set=charset 指定导【zhǐ dìng dǎo】出数据【chū shù jù】时采用👿何种字👅符集，如果数【rú guǒ shù】🏓据表不🐛是采用默认的 latin1 字符集的话，那么导👵出时必须指定【xū zhǐ dìng】该选项，否则再次导入🎒数据后【shù jù hòu】🚠将产生乱码问【luàn mǎ wèn】题【tí】。

--disable-keys 告诉 mysqldump 在【zài】 INSERT 语句的【de】开头和😙结尾增加⏫ /*！40000 ALTER TABLE table DISABLE KEYS */; 和 /*！40000 ALTER TABLE table ENABLE KEYS */; 语句，这能大大提高🏆插入语句的速【jù de sù】度，因为它是在【zài】插入完所【rù wán suǒ】🎯有数据【yǒu shù jù】🐏后才重建索引【jiàn suǒ yǐn】的【de】。该选项🎲只适合【zhī shì hé】🚣 MyISAM 表。

--extended-insert = true　false 默认情况下🈵，mysqldump 开启【kāi qǐ】 --complete-insert 模式【mó shì】💮，因此不想用它【xiǎng yòng tā】🍻的的话，就使用本选项，设定它【shè dìng tā】📐的值为 false 即可。

--hex-blob 使用十六进制【liù jìn zhì】格式导【gé shì dǎo】出二进【chū èr jìn】🏺制字符🎩串字段🤞。如果有二进制数据就必须使【bì xū shǐ】用本选项💁。影响到的字段类型有【lèi xíng yǒu】🦇 BINARY、VARBINARY、BLOB。

--lock-all-tables，-x 在开始导出之前【qián】🍷，提交请【tí jiāo qǐng】🗣求锁定所有数据库中的所有表，以保证🤢数据的【shù jù de】🍱一致性。这是一【zhè shì yī】个全局🧦读锁，并且自【bìng qiě zì】🌿动关闭 --single-transaction 和【hé】 --lock-tables 选项。

--lock-tables 它和 --lock-all-tables 类似，不过是锁定当前导出🍝的数据表【biǎo】🐺，而不是【ér bú shì】一下子锁定全部库下的表【biǎo】🐺。本选项【běn xuǎn xiàng】💧只适用【yòng】于👢 MyISAM 表【biǎo】🐺，如果是😕 Innodb 表可以【biǎo kě yǐ】🧀用【yòng】 --single-transaction 选项💧。

--no-create-info，-t 只导出🙃数据【shù jù】，而不添Ⓜ加 CREATE TABLE 语句【yǔ jù】。

--no-data，-d 不导出任何数😏据，只导出【zhī dǎo chū】数据库表结构【biǎo jié gòu】♈。

--opt 这只是🤐一个快捷选项【xuǎn xiàng】🦑，等同于同时添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset 选项【xuǎn xiàng】🦑。本选项【xuǎn xiàng】🦑能让【néng ràng】🏅 mysqldump 很快的💙导出数据，并且导出的数【chū de shù】据能很【jù néng hěn】快导回。该选项【xuǎn xiàng】🦑默认开【mò rèn kāi】启【qǐ】，但可以🔵用😖 --skip-opt 禁用😖。注意🛏，如果运行【háng】 mysqldump 没有指定 --quick 或【huò】🍘 --opt 选项【xuǎn xiàng】🦑，则会将整个结果集放【guǒ jí fàng】在内存【zài nèi cún】中🍤。如果导出大数🎬据库的🖼话可能【huà kě néng】会出现🥢问题【wèn tí】。

--quick，-q 该选项在导出【zài dǎo chū】🏉大表时很有用【hěn yǒu yòng】，它强制 mysqldump 从服务器查询取得记👫录直接😉输出而不是取【bú shì qǔ】得所有【dé suǒ yǒu】🏈记录后🆖将它们【jiāng tā men】缓存到内存中。

--routines，-R 导出存【dǎo chū cún】😋储过程🦂以及自【yǐ jí zì】定义函数。

--single-transaction 该选项【xuǎn xiàng】🗑在导出【zài dǎo chū】数据之前提交一个【yī gè】♍ BEGIN SQL语句📰，BEGIN 不会阻塞任何【sāi rèn hé】应用程🛎序且能保证导出时数🏓据库的【de】一致性状态。它只适用于事务表，例如🔕 InnoDB 和【hé】 BDB。本选项【xuǎn xiàng】🗑和【hé】 --lock-tables 选项【xuǎn xiàng】🗑是互斥【shì hù chì】的【de】🚆，因为🔲 LOCK TABLES 会使任何挂起的事务【de shì wù】隐含提【yǐn hán tí】交。要想导【yào xiǎng dǎo】🔔出大表的话【de huà】🎌，应结合使用【shǐ yòng】⏺ --quick 选项【xuǎn xiàng】🗑。

--triggers 同时导【tóng shí dǎo】🔸出触发器。该选项🎓默认启【mò rèn qǐ】🕺用【yòng】，用【yòng】 --skip-triggers 禁用【yòng】它。

其他参【qí tā cān】数详情【shù xiáng qíng】请参考手册【shǒu cè】，我通常😶使用以🦖下 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 来备份【lái bèi fèn】 Innodb 表【biǎo】🐠：

/usr/local/mysql/bin/mysqldump -uyejr -pyejr --default

-character-set=utf8 --opt --extended-insert=

false \--triggers -R --hex-blob --single-transaction db_name

》 db_name.sql

1.2 还原

用🔣 mysqldump备份出【bèi fèn chū】来的文件是一个可以【gè kě yǐ】👖直接倒入的 SQL 脚本【jiǎo běn】，有两种🌙方法可以将数【yǐ jiāng shù】🍀据导入。

直接用 mysql 客户端 例如：

/usr/local/mysql/bin/mysql -uyejr -pyejr db_name 《 db_name.sql

用〽 SOURCE 语法【yǔ fǎ】🥞 其实这不是标准的🈵 SQL 语法【yǔ fǎ】🥞，而是【ér shì】 mysql 客户端【kè hù duān】提供的【tí gòng de】功能，例如🎉： SOURCE /tmp/db_name.sql;

这里需要指定【yào zhǐ dìng】文件的🌻绝对路径🛢，并且必🎲须是 mysqld 运行用户（例如【lì rú】🔰 nobody）有权限【yǒu quán xiàn】读取的【dú qǔ de】文件。

2、 mysqlhotcopy

2.1 备份

mysqlhotcopy 是一个💋 PERL 程序【chéng xù】👎，最初由【zuì chū yóu】Tim Bunce编写【biān xiě】🎎。它使用📱 LOCK TABLES、FLUSH TABLES 和 cp 或🎐 scp 来快速备份数据【fèn shù jù】库。它是备🆚份数据【fèn shù jù】库或【kù huò】🎐单个表的最快的✊途径【tú jìng】，但它只🛄能运行🐃在😃数据库文件🐥（包括数【bāo kuò shù】据表定义文件【yì wén jiàn】🐥、数据文【shù jù wén】件🐥、索引文【suǒ yǐn wén】📎件🐥）所在😃的机器上【shàng】。mysqlhotcopy 只能用于备份 MyISAM，并且只【bìng qiě zhī】能运行🐃在😃 类Unix 和 NetWare 系统上【shàng】。

mysqlhotcopy 支持一【zhī chí yī】次性拷❌贝多个数据库，同时还支持正🐞则表达🌖。以下是几个例【jǐ gè lì】子【zǐ】：

root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr db_name /tmp

（把数据【bǎ shù jù】库目录【kù mù lù】🎽 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

更详细的使用【de shǐ yòng】🚏方法请查看手册，或者调用下面的命令【de mìng lìng】📲来查看【lái chá kàn】🍦 mysqlhotcopy 的帮助：

perldoc /usr/local/mysql/bin/mysqlhotcopy

注意【zhù yì】，想要使🥎用 mysqlhotcopy，

必须要有⭕ SELECT、RELOAD（要执行【yào zhí háng】🆖 FLUSH TABLES） 权限【quán xiàn】🍵，并且还【bìng qiě hái】必须要能够有⭕读取 datadir/db_name 目录的权限【quán xiàn】🍵。

2.2 还原

mysqlhotcopy备份出来的是整个数😗据库目【jù kù mù】录，使用时【shǐ yòng shí】✴可以直接拷贝🔫到😫 mysqld 指定的【zhǐ dìng de】 datadir （在这里【zài zhè lǐ】是 /usr/local/mysql/data/）目录下即可【jí kě】🚬，同时要😕注意权【zhù yì quán】限的问题，如下例：

root#cp -rf db_name /usr/local/mysql/data/root#chown -R nobody:nobody /usr/local/mysql/data/

（将【jiāng】👑 db_name 目录的属主改成【chéng】 mysqld 运行用📲户）

3、 SQL 语法备份

3.1 备份

BACKUP TABLE 语法其实和 mysqlhotcopy 的工作😁原理差不多【bú duō】，都是锁表🧔，然后拷【rán hòu kǎo】👑贝数据🐴文件【wén jiàn】。它能实现在线备份【bèi fèn】，但是效【dàn shì xiào】果不理👇想【xiǎng】，因此不【yīn cǐ bú】推荐使【tuī jiàn shǐ】用🧥。它只拷贝表结😊构文件【wén jiàn】🏗和数据文件【wén jiàn】，不同时🐕拷贝索🛃引文件【wén jiàn】，因此恢复时比【fù shí bǐ】较慢。例子【lì zǐ】：

BACK TABLE tbl_name TO ‘/tmp/db_name/’;

注意🉐，必须要【bì xū yào】有⚫ FILE 权限才能执行本SQL，并且目录 /tmp/db_name/ 必须能😭被🔷 mysqld 用户可【yòng hù kě】写【xiě】，导出的文件不【wén jiàn bú】😤能覆盖【néng fù gài】😎已经存在的文件【jiàn】🎽，以避免安全问【ān quán wèn】题。

SELECT INTO OUTFILE 则是把【zé shì bǎ】数据导出来成为普通的文本文件【wén jiàn】⚽，可以自【kě yǐ zì】定义字🏍段间隔的方式❎，方便处【fāng biàn chù】👧理这些数据。

例子：

SELECT INTO OUTFILE ‘/tmp/db_name/tbl_name.txt’ FROM tbl_name;

注意【zhù yì】，必须要有 FILE 权限才🤖能执行🚀本🌞SQL，并且文【bìng qiě wén】件【jiàn】 /tmp/db_name/tbl_name.txt 必须能💷被【bèi】🤫 mysqld 用户可【yòng hù kě】写⏫，导出的文件不【wén jiàn bú】能覆盖已经存在的文【zài de wén】🔻件【jiàn】，以避免安全问题。

3.2 恢复

用📠 BACKUP TABLE 方法备🦅份出来的文件，可以运行【háng】 RESTORE TABLE 语句来恢复数据表【jù biǎo】。例子： RESTORE TABLE FROM ‘/tmp/db_name/’;权限要【quán xiàn yào】🛑求类似【qiú lèi sì】😼上面所【shàng miàn suǒ】🐟述。

用【yòng】 SELECT INTO OUTFILE 方法备【fāng fǎ bèi】份出来的文件，可以运【kě yǐ yùn】🥡行🦓 LOAD DATA INFILE 语句来恢复数据表🏍。例子：

LOAD DATA INFILE ‘/tmp/db_name/tbl_name.txt’ INTO TABLE tbl_name;

权限要【quán xiàn yào】🈵求类似上面所述【shù】。倒入数【dǎo rù shù】⛴据之前，数据表【shù jù biǎo】要已经存在才🛷行。如果担⛩心数据💲会发生重复【chóng fù】，可以增🚡加 REPLACE 关键字🕺来替换【lái tì huàn】已有记录或者【lù huò zhě】用 IGNORE 关键字🕺来忽略【lái hū luè】💝他们。

补充：

shell》 mysqldump --quick db_name 　 gzip 》 db_name.contents.gz

（该例子中创建的文件是压缩格式）。

恢复/转移到另一台的命令如下：

shell》 gunzip 《 db_name.contents.gz 　 mysql db_name

以上命令🍸，适用于【shì yòng yú】*nix 操作系统的机【tǒng de jī】器🦎

4、 启用二进制日志（binlog）

采用 binlog 的方法🆕相对来【xiàng duì lái】说更灵活🎐，省心省🏳力【lì】，而且还【ér qiě hái】可以支持增量【chí zēng liàng】🛀备份。

启用 binlog 时必须【shí bì xū】要重启【yào chóng qǐ】📳 mysqld。首先【shǒu xiān】，关闭 mysqld，打开🌽 my.cnf，加入以下几行😠：

server-id = 1log-bin = binloglog-bin-index = binlog.index

然后启动 mysqld 就可以【jiù kě yǐ】🛸了🍫。运行过程中会💹产生 binlog.000001 以及🌖 binlog.index，前面的文件是 mysqld 记录所有对数【yǒu duì shù】据的更新操作【xīn cāo zuò】🍀，后面的【hòu miàn de】文件