深入理解MySQL数据库的binlog

什么是binlog

binlog即binary log，根据mysql文档的说明，binlog包含一系列描述数据库状态变更的”events”（如table create或insert/update等操作），也包含可能对数据库造成潜在更新的”events”（如未命中任何行的delete操作）。此外，binlog还包含引起数据库状态变化的操作的执行时间

binlog有什么用

• Replication
  对于主从部署的mysql server，master将所有可能更改数据库状态的操作写入binlog并将其同步给slave，后者重放binlog中的操作序列以便使其数据库状态与master达到一致，这样就实现了master-slave的数据同步。
• Data Recover
  数据库恢复时可以回放binlog重建宕机时的数据状态。假设数据库在某个备份点后又运行一段时间后宕机，则server重启后，可以在上次备份点的基础上，通过回放binlog来重做数据以便恢复上次备份点到server宕机这段时间内的数据状态。

注意：binlog只包含可能改变数据库状态的操作，因此，select或show等不会修改数据的操作不会记录在binlog中。

如何查看是否启用binlog

默认情况下，mysql server不启用binlog。

• 验证方法1
  执行”show variables”，若输出中log_bin变量的值为OFF，则表明未启用binlog；

mysql> show variables like 'log_bin'; +---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | OFF   |+---------------+-------+1 row in set

• 验证方法2
  以拥有SUPER权限的用户名登录后执行”show binary logs”，会得到输”ERROR 1381 (HY000): You are not using binary logging”的提示）。

mysql> show binary logs;1381 - You are not using binary logging

如何启用binlog

若需要启用binlog，则在启动mysqld时需指定–log-bin[=base_name]参数。
如，修改/etc/my.cnf (Windows下为my.ini）, 加入：

log_bin =bin-log

则生成的binlog文件名称类似bin-log.000001，bin-log.000002。

此外，与binlog有关的mysqld启动参数还有–log-bin-index、–log-bin-trust-function-creators、–log-bin-trust-routine-creators、–binlog-do-db、–binlog-ignore-db等。其中，–binlog-do-db=db_name表明当前选中（通过use命令）的库名为db_name时，后续的所有可能更改数据库状态的操作都会被写入binlog；–binlog-ignore-db同理，只不过它显式指定不需要写binlog的db。
关于–binlog-do-db和–binlog-ignore-db，有个需要注意的地方：例如，当mysqld以–binlog-do-db=sales参数启动，则下面的操作中，针对prices库的update操作也被写入binlog（即使我们未指定prices的binlog行为）：

USE sales;UPDATE prices.discounts SET percentage = percentage + 10;

这是因为当执行update时，sales为当前被选中的db，同理，–binlog-ignore-db也存在类似的问题。关于这点，可参考mysql文档的说明。

如何查看binlog

• show binary logs

mysql> show binary logs;+-----------------+-----------+| Log_name        | File_size |+-----------------+-----------+| bin-log.000011  |    148329 | +-----------------+-----------+

• show master logs

mysql> show master logs;+-----------------+-----------+| Log_name        | File_size |+-----------------+-----------+| bin-log.000011  |    148329 | +-----------------+-----------+

如何清除binary logs

清除所有binary logs

• 清除master上的binlog

mysql> reset master;

• 清除slave上的binlog

mysql> reset slave;

清除指定部分logs

• 清除指定log之前的log
  将log-bin.000012之前的binary logs清掉

mysql>purge binary logs to 'log-bin.000012';

• 清除指定时间之前的log
  将2011-05-28 12:05:38之前的binary logs清掉

mysql>purge binary logs before '2011-05-28 12:05:38';

注意：整个过程应先stop slave，之后再start slave

查看当前binary log的情况

mysql>show master status;

查看binary logs的内容

mysql>show binlog events;

命令行下：

#mysqlbinlog /var/log/mysql/log-bin.000140;

或者

#mysqlbinlog --start-datetime='2011-07-01 00:00:00' --stop-datetime='2010-07-15 00:00:00' /var/log/mysql/log-bin.000020 > ./tmp.log

在my.cnf/my.ini中设定binary logs回滚天数

expire_logs_days = 7

与binlog有关的几个变量或配置项

• log_bin
  表明mysql server是否启用binlog，若启用，其值为ON，否则为OFF。不可动态配置
• log_slave_updates
  在replication部署方式下，slave是否将同步自master的updates写入自己的binlog，只在slave启用binlog时有意义。不可动态配置
• max_binlog_cache_size
  同写文件类似，binlog也是先写内核buffer然后在某个时机下才刷盘的。该配置项指明binlog内存cache的最大size，默认最小值为4096Bytes。可以动态配置，一旦修改，则立即对当前所有活跃会话(active sessions)生效。
• max_binlog_size
  指定单个binlog文件的最大size，超过该值则关闭当前binlog文件并创建新的binlog以便后续写入。最小值为4096Bytes，最大值为1GB，默认也为1GB。可以动态配置
• sync_binlog
  指定binlog刷盘的触发时机。若值大于0，则每sync_binlong次写binlog的操作会触发一次刷盘。若配为1，则每次写binlog操作都会刷盘，最大程度地保证了数据安全，但效率最低；实际生产环境中，需根据业务需求配置合理的阈值。可以动态配置

binlog写入时机及查看方法

根据mysql文档，写binlog的时机是：sql语句或transaction执行完，但任何相关的locks还未释放或事务还未最终commit前。这样保证了binlog记录的操作时序与数据库实际的数据变更顺序一致。
可以通过mysqlbinlog工具查看binlog内容，在mysql安装目录下输入./bin/mysqlbinlog -?或–help可以看到其用法，这里不再赘述。
当mysql数据需要恢复时，mysqlbinlog工具可以根据binlog的记录方便地实现命令重做：
shell> mysqlbinlog log_file | mysql -h server_name

参考链接
参考链接