[转]MySQL数据库灾难恢复

MySQL数据库灾难恢复

当 MySQL Server 因为各种无法预期的原因而损坏(Crash)的时候，你就必须要进行灾难恢复。如果你有做好定期的数据库备份那么灾难还原的时候应该会轻松很多，只要将备份起来的数据还原回去即可，但光是这样子还是会造成部份数据的遗失，例如 "现在" 至 "最后一次备份" 之间的数据，这时我们可以通过 MySQL 提供的 Binary Log 机制将可能遗失的数据降至最低。

Binary Log 的运作原理很简单，它只是单纯的将所有会修改到数据库内容的操作记录在 Log 文件中，然后通过这个 Binary Log 你就可以重新执行所有会修改到数据库内容的操作。例如若你最后一次备份的时间是 1/1 AM 0:00 ，并且有启用 Binary Log 功能记录 1/1 AM 0:00 这个时间点以后所有会修改到数据库内容的操作，假设你的 MySQL Server 在 1/2 AM 10:00 故障，你就可以将 1/1 AM 0:00 备份的数据还原回去，然后利用 Binary Log 将 1/1 AM 0:00 ~ 1/2 AM 10:00 之间所有的操作重新执行一次，这样子一来你就可以将数据库还原到当机的那个时间点。

使用 Binary Log 进行灾难恢复的步骤：
启用 Binary Log
使用 mysqlbinlog 将 Binary Log 转换成可执行的 SQL 命令

在接下来的文章中会使用的范例与假设：
最后一次备份的时间点为 1/1 AM 0:00
MySQL Server 在 1/2 AM 10:00 故障

一、启用 Binary Log

修改 MySQL Server 的系统设置文件(eg. /etc/my.cnf)，在 [mysqld] 区块中加上 log-bin=mysql-bin 选项，然后重新启动 MySQL Server，例如：

[mysqld]
log-bin=mysql-bin

启用后你应该可以在 MySQL 的 Data Dir 里面发现如下的文件：

mysql-bin.index
mysql-bin.000001
mysql-bin.000002
...............
mysql-bin.00000X

MySQL 在以下几种情况会进行 lograrote：

• 执行 Flush Logs 命令
• MySQL Server 重新启动
• 设置文件中有进行额外的设置

注：
请注意，当你使用 mysqldump 进行数据库备份时请记得加上 --flush-logs 选项，例如：

mysqldump --flush-logs -u root -p 数据库名称 > example.sql

这么做的目的是在备份时让 MySQL Server 进行 logrotate，这样子日后要辨别 "最后一次备份时间点" 之后的 Binary Log 会比较方便，因为若你没有主动(或通过设置)去删除 Binary Log，则只要你的硬盘空间够大，MySQL 会无限期的保存 Binary Log，也就是说你的 Binary Log 里面所记载的数据有可能包含 "最后一次备份时间点" 之前的数据。

二、使用 mysqlbinlog 将 Binary Log 转换成可执行的 SQL 命令

Binary Log 是无法被 MySQL Server 直接执行、也无法直接以人眼去阅读的，必须要先使用 MySQL 所提供的 mysqlbinlog 程式，将 Binary Log 转换为 MySQL Server 可以执行的 SQL 命令。mysqlbinlog 的语法如下：

mysqlbinlog -H --set-charset="utf8"
               --start-datatime="2007-01-01 00:00:00"
               --stop-datatime="2007-01-02 10:00:00"
               mysql-bin.[0-9]* > example.sql

-H：Display a hex dump of the log in comments.
--set-charset：设置编码
--start-datatime：要转换的开始时间点
--stop-datatime：要转换的结束时间点

mysql-bin.[0-9]*：这里要注意的是，要一次处理所有的 Binary Log，因为储存在 Binary Log 中的数据有可能会 "跨文件"，例如从 mysql-bin.000001 的结尾接到 mysql-bin.000002 的开头。

example.sql：转换出来的文件名称，这个名称可以自已取。

需要加 -H 选项的原因如下：

mysqlbinlog didn't escape the string content of user variables, and did not
deal well when these variables were in non-ASCII character sets; this is
now fixed by always printing the string content of user variables in hexadecimal.
The character set and collation of the string is now also printed. (Bug #3875)

三、实际执行转换后的 Binary Log
很简单，只要一行简单的命令：

mysql &< example.sql

如果没有什么错误讯息发生，那么只要等它执行完就大功告成了。话又说回来，要是执行失败呢？这是有可能的。MySQL 在处理 Binary Log 时有一些 Bug 存在，它的 Bug Report 似乎是说在最新版本的 MySQL Server 中已修正此 Bug，我没有实际测试过所以不清楚，但若是你和我一样也遇到这个 Bug 的话，也不用太担心。这些问题其实不难解决，自己 Workaround 即可。 目前看到的情况有： Comment 没有正确标示 Comment 语法错误 不正确的使用 DELIMITER 奇怪的 STOP 命令(不太确定这是做什么用的) 自己用 sed 去修改转换过后的 example.sql 即可。

sed -f replace.rules example.sql > final.sql

replace.rules文件的内容：

s/(Query.*thread)/#1/g
s/(###.*###)//g
s/DELIMITER ;//g
s/Stop//g

上面几行的意义：

s/(Query.*thread)/#1/g

MySQL 的 Binary Log 在处理 Comment 的时候，有的时候会漏加 "#" 符号在 Comment Line 的最前面。
例如本来是：

Query thread_id=227528 exec_time=- error_code=0

要改成：

#Query thread_id=227528 exec_time=- error_code=0

s/(###.*###)//g

在某些 SQL statement(例如 REPLACE INTO search)的最后面会有一些 Comment 存在，但这些 Comment 的语法不正确反而会造成执行失败，故删除之。
类似以下的行都应该删除：

### Bitfield: user.options ###
### SAVE ORDERED IDS TO SEARCH CACHE ###

........等等

s/DELIMITER ;//g

删除不正确的 DELIMITER 命令，像以下这样就是不正确的：

DELIMITER ;

s/Stop//g

有的时候会在 Binary Log 中出现 Stop 这个命令而导致执行失败，故删除之。但我不太确定这个 Stop 命令实质上的用途是什么。