二进制日志也叫作变更日志(update log)，主要用于记录数据库的变化情况。通过二进制日志可以查询MySQL数据库中进行了哪些改变。本节将为读者介绍二进制日志的内容。

17.2.1启动和设置二进制日志

默认情况下，二进制日志功能是关闭的。通过my.cnf或者my.ini文件的log-bin选项 可以开启二进制日志。将log-bin选项加入到my.cnf或者my.ini文件的[mysqlld]组中，形式如下：

#my.cnf(Linux操作系统下)或者my.ini(Windows操作系统下)
[mysqld]
log-bin[=DIR\[filename]]

其中，DIR参数指定二进制文件的存储路径；filename参数指定二进制文件的文件名， 其形式为filename.number, number的形式为000001、000002等。每次重启MySQL服务后，都会生成一个新的二进制日志文件，这些日志文件的“number”会不断递增。除了生 成上述文件外，还会生成一个名为filename.index的文件。这个文件中存储所有二进制日志文件的清单。

技巧：二进制日志与数据库的数据文件最好不要放在同一块硬盘上。即使数据文件所在的硬盘被破坏，也可以使用另一块硬盘上的二进制日志来恢复数据库文件。两块硬盘同时坏了的可能性要小得多。这样可以保证数据库中数据的安全。

如果没有DIR参数和filename参数，二进制日志将默认存储在数据库的数据目录下。 默认的文件名为hostname-bin.number,其中hostname表示主机名。

【示例17-1】下面在my.ini文件的如[mysqld]组中添加下面的语句:log-bin

重启MySQL服务器后，可以在MySQL数据库的数据目录下看到hjh-bin.000001这个文件,同时还生成了hjh-bin.index文件。此处，MySQL服务器的主机名为hjh。然后，在 my.ini文件的[mysqld]组中进行如下修改。语句如下：

log-bin=C:\log\mylog

重启MySQL服务后，可以在C:\log文件夹下看到mylog.000001文件和mylog.index文件。

17.2.2查看二进制日志

使用二进制格式可以存储更多的信息，并且可以使写入二进制日志的效率更高。但是，不能直接打开并査看二进制日志。如果需要查看二进制日志，必须使用mysqlbinlog命令。 mysqlbinlog 命令的语法形式如下：

mysqlbinlog filename.number

mysqlbinlog命令将在当前文件夹下查找指定的二进制日志。因此需要在二进制日志filename.number所在的目录下运行该命令，否则将会找不到指定的二进制日志文件。

【示例17-2】下面使用mysqlbinlog命令，来查看c:\log目录下的mylog.000001文件。

代码执行如下：

C:\log>mysqlbinlog mylog.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
######省略部分内容#######
#at 288
#091122 16:31:41 server id 1 end_log_pos 380 Query thread_id=1	exec_time=0
error_code=0
SET TIMESTAMP=1258878701/*!*/;
DELETE FROM score WHERE id=10
/*i*/;
#at 380
#091122 16:32:41 server id 1 end_log_jx)s 408 lntvar SETINSERTJD=11/*!*/;
#at 408
#091122 16:32:41 server id 1 end_log_jx)s 515 Query thread_id=1	exec_time=0
error_code=0
SETTIMESTAMP=1258878761/*!*/;
INSERT INTO score VALUES(NULL,905,'英语',84)
/*!*/;
DELIMITER ;
End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

上面是mylog.000001中的部分内容。上述内容中记录了从score表中删除id为10的记录和插入一条新记录的信息。使用mysqlbinlog命令时，可以指定二进制文件的存储路径。这样可以确保mysqlbinlog命令可以找到二进制日志文件。上面例子中的命令可以变为如下形式:

mysqlbinlog C:\log\mylog.000001

这样，mysqlbinlog命令就会到c:\log目录下去查找mylog.000001文件。如果不指定路径，mysqlbinlog命令将在当前目录下查找mylog.000001文件。

17.2.3删除二进制日志

二进制日志会记录大量的信息。如果很长时间不清理二进制日志，将会浪费很多的磁盘空间。删除二进制日志的方法很多。本小节将为读者详细介绍如何删除二进制日志。

1.删除所有二进制日志

使用RESERT MASTER语句可以删除所有二进制日志。该语句的形式如下：

RESET MASTER;

登录MySQL数据库后，可以执行该语句来删除所有二进制日志。删除所有二进制曰日志后，MySQL将会重新创建新的二进制日志。新二进制日志的编号从000001开始，如 mylog.000001。

2.根据编号来删除二进制日志

每个二进制日志文件后面有一个6位数的编号，如000001。使用PURGE MASTER LOGS TO语句,可以删除指定二进制日志的编号之前的日志。该语句的基本语法形式如下：

PURGE MASTER LOGS TO 'filename.number';

该语句将删除编号小于这个二进制日志的所有二进制日志。

【示例17-3】下面删除mylog.000004之前的二进制日志，代码如下：

PURGE MASTER LOGS TO ’'mylog.000004';

代码执行完后，编号为000001、000002和000003的二进制日志将被删除。

3. 根据创建时间来删除二进制日志

使用PURGE MASTER LOGS TO语句，可以删除指定时间之前创建的二进制日志。该语句的基本语法形式如下：

PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss';

其中，“hh”表示24表示制的小时。该语句将删除在指定时间之前创建的所有二进制曰志。

【示例17-4】下面删除2009-12-20 15:00:00之前创建的二进制日志，代码如下：

PURGE MASTER LOGS TO '2009-12-20 15:00:00';

代码执行完后，2009-12-20 15:00:00之前创建的所有二进制日志将被删除。

17.2.4 使用二进制日志还原数据库

二进制日志记录了用户对数据库中数据的改变。如INSERT语句、UPDATE语句、CREATE语句等都会记录到二进制日志中。一旦数据库遭到破坏，可以使用二进制日志来还原数据库。本小节将为读者详细介绍使用二进制日志还原数据库的方法。

如果数据库遭到意外损坏，首先应该使用最近的备份文件来还原数据库。备份之后， 数据库可能进行了一些更新。这可以使用二进制日志来还原。因为二进制日志中存储了更新数据库的语句，如update语句、insert语句等。二进制日志还原数据库的命令如下：

mysqlbinlog filename.number | mysql -u root -p

这个命令可以这样理解：使用mysqlbinlog命令来读取filename.number中的内容，然后，使用mysql命令将这些内容还原到数据库中。

技巧：二进制日志虽然可以用来还原MySQL数据库，但是其占用的磁盘空间也是非常大的。因此，在备份MySQL数据库之后，应该删除备份之前的二进制日志。如果备份之后发生异常，造成数据库的数据丢失，可以通过备份之后的二进制曰志进行还原。

使用mysqlbinlog命令进行还原操作时，必须是编号（number)小的先还原。例如，mylog.000001 必须在 mylog.000002 之前还原。

【示例17-5】下面使用二进制日志来还原数据库。代码如下：

mysqlbinlog mylog.000001 | mysql -u root -p
mysqlbinlog mylog.000002 | mysql -u root -p
mysqlbinlog mylog.000003 | mysql ni root -p
mysqlbinlog mylog.000004 | mysql -u root -p

17.2.5暂时停止二进制日志功能

在配置文件中设置了log-bin选项以后，MySQL服务器将会一直开启二进制日志功能。删除该选项后就可以停止二进制日志功能。如果需要再次启动这个功能，又需要重新添加log-bin选项。MySQL中提供了暂时停止二进制日志功能的语句。本小节将为读者介绍暂 时停止二进制日志功能的方法。

如果用户不希望自己执行的某些SQL语句记录在二进制日志中，那么需要在执行这些 SQL语句之前暂停二进制日志功能。用户可以使用SET语句来暂停二进制日志功能。SET语句的代码如下：

SET SQL_LOG_BIN=O ;

执行该语句后，MySQL服务器会暂停二进制日志功能。但是，只有拥有super权限的用户才可以执行该语句。如果用户希望重新开启二进制日志功能，可以使用下面的SET语句。

SET SQL_LOG_BIN=1;