binlog的基本介绍和操作

一、介绍

binlog指二进制日志，它记录了数据库上的所有改变，并以二进制的形式保存在磁盘中，它可以用来查看数据库的变更历史、数据库增量备份和恢复、MySQL的复制（主从数据库的复制）。

binlog有三种格式：

• statement：基于SQL语句的复制（statement-based replication，SBR）
• row：基于行的复制（row-based replication，RBR）
• mixed：混合模式复制（mixed-based replication，MBR）

statement：

每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。但是注意statement相比于row能节约多少性能与日志量，取决于应用的SQL情况。正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。

缺点：由于记录的只是执行语句，为了这些语句在slave上正确运行，我们还必须记录每条语句在执行时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时相同的结果。另外，一些特定的函数功能如果要在slave和master上保持一致会有很多相关问题。

row：

5.1.5版本的MySQL才开始支持row level的复制，它不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点：binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row level的日志会非常清楚的记下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。

缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。但是新版本的MySQL对row level模式进行了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

mixed：

从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。

在Mixed模式下，一般的语句修改使用statment格式保存binlog，如果一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

二、代码操作

2.1 获取binlog文件列表：

show binary logs;

2.2 生成新的日志文件的条件：

• 每当我们停止或重启服务器时，服务器会把日志文件记入下一个日志文件，MySQL会在重启时生成一个新的日志文件，文件序号递增。
• 如果日志文件超过max_binlog_size（默认值1G）系统变量配置的上限时，也会生成新的日志文件（在这里需要注意的是，如果你正使用大的事务，二进制日志还会超过max_binlog_size，不会生成新的日志文件，事务全写入一个二进制日志中,这种情况主要是为了保证事务的完整性）
• 日志被刷新时，新生成一个日志文件flush logs;：
  

2.3 查看指定的binlog文件中的内容：

假设我们执行flush logs;，刷新一个新的日志文件，此时执行show binary logs，输出如下：

然后我们执行下列的语句：

create database if not exists test default charset utf8 collate utf8_general_ci;

use test;

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `Websites`;
CREATE TABLE `Websites` (
  `id` int(11) NOT NULL,
  `name` char(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `access_log`;
CREATE TABLE `access_log` (
  `aid` int(11) NOT NULL,
  `site_id` int(11) NOT NULL,
  `count` int(11) NOT NULL
);

INSERT INTO `Websites` VALUES ('1', 'Google');
INSERT INTO `Websites` VALUES('2', 'TaoBao');
INSERT INTO `Websites` VALUES('3', 'CaiNiao');

INSERT INTO `access_log` VALUES ('1', '1', '45');
INSERT INTO `access_log` VALUES('2', '3', '100');
INSERT INTO `access_log` VALUES('3', '1', '230');
INSERT INTO `access_log` VALUES('4', '2', '10');

再执行show binary logs，可以看到输出如下，说明我们刚才执行的操作已经写入了mysql-bin.000005中

我们用如下的命令来查看mysql-bin.000005中的内容：

show binlog events in 'mysql-bin.000005';

输出如下：

我们可以使用如下的命令来查看当前正在写入的binlog文件：

show master status\G

输出如下：

使用show binlog events;等价于使用show binlog events in 'mysql-bin.000001';，作用是只查看第一个binlog文件的内容：

2.4 使用mysqlbinlog进行查看

还是以mysql-bin.000005日志为例，基本使用用法如下：

mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql-bin.000005"

这里如果不加双引号，cmd命令行会将空格视为断句，无法识别，因此路径要加双引号，输出如下：

可以看到使用mysqlbinlog可以输出更详细的信息。

参考-mysql查看binlog日志内容
参考-mysql查看binlog日志内容