1 简介

二进制日志，记录对数据发生或潜在发生更改的 SQL 语句，并以二进制形式保存在磁盘。

2 Binlog 的作用

主要作用：复制、恢复和审计。归档，也可以用来做主备同步。

3 开启 Binlog

3.1 查询当前 MySQL 是否支持 binlog

• 如下 OFF 代表不支持

• 

3.2 配置 binlog 格式

修改 my.cnf 文件

查看 my.cnf 路径

• mysql --help --verbose | grep my.cnf

• 
• 

在对应的 my.cnf 文件中添加如下内容：

• 注意添加 mysqld 组

• 

• 重启 MySQL

• 

• 再次查看是否支持 binlog

• 

命令行修改

set binlog_format=ROW;

3 binlog 管理命令

show master logs

• 查看所有 Binlog 的日志列表。

• 

show master status

• 查看 binlog 日志状态。查看最后一个 Binlog 日志的编号名称，及最后一个事件结束的位置( pos )

• 

flush logs

刷新 binlog 日志文件，刷新之后会创建一个新的 Binlog 日志文件

reset master

清空所有的 binlog 日志文件

查看 binlog 日志文件

mysqlbinlog mysql-bin.000002

4 Binlog 相关变量

log_bin

Binlog 的开关。

查看该变量：

show variables like 'log_bin';

binlog_format

Binlog 日志的格式。

查看变量：

show variables like 'binlog_format';

5 Binlog 日志的格式

ROW

仅保存记录被修改细节，不记录 SQL 语句上下文相关信息。

优点

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

缺点

所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条 update 语句，修改多条记录，则 binlog 中每一条修改都会有记录，这样造成 binlog 日志量会很大，特别是当执行 alter table 之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

STATEMENT

每一条会修改数据的 SQL 都会记录在 Binlog 中。

优点

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

缺点

由于记录的只是执行语句，为了这些语句能在 slave 上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在 slave 得到和在 master 端执行时候相同 的结果。另外 mysql 的复制,像一些特定函数功能，slave 可与 master 上要保持一致会有很多相关问题(如 sleep()函数， last_insert_id()，以及 user-defined functions(udf)会出现问题).

MIXED

以上两种 level 的混合使用。一般的语句修改使用 statment 格式保存 binlog，如一些函数，statement 无法完成主从复制的操作，则采用 row 格式保存 binlog,MySQL 会根据执行的每一条具体的 sql 语句来区分对待记录的日志形式，也就是在 Statement 和 Row 之间选择一种.新版本的 MySQL 中队 row level 模式也被做了优化，并不是所有的修改都会以 row level 来记录，像遇到表结构变更的时候就会以 statement 模式来记录。至于 update 或者 delete 等修改数据的语句，还是会记录所有行的变更。

Binlog 日志格式选择

Mysql 默认是使用 Statement 日志格式，推荐使用 MIXED.

由于一些特殊使用，可以考虑使用 ROWED，如自己通过 binlog 日志来同步数据的修改，这样会节省很多相关操作。对于 binlog 数据处理会变得非常轻松,相对 mixed，解析也会很轻松(当然前提是增加的日志量所带来的 IO 开销在容忍的范围内即可)。

mysqlbinlog 格式选择

mysql 对于日志格式的选定原则:如果是采用 INSERT，UPDATE，DELETE 等直接操作表的情况，则日志格式根据 binlog_format 的设定而记录,如果是采用 GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么无论如何 都采用 SBR 模式记录

6 查看 Binlog 相关的 SQL

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] 

查看第一个 Binlog 日志

show binlog events;

查看指定的 Binlog 日志

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

从指定的位置开始,查看指定的 Binlog 日志

show binlog events in 'mysql-bin.000001' from 666;

从指定的位置开始,查看指定的 Binlog 日志,限制查询的条数

show binlog events in 'mysql-bin.000001' from 666 limit 2;

从指定的位置开始，带有偏移，查看指定的 Binlog 日志,限制查询的条数

show binlog events in 'mysql-bin.000001' from 666 limit 1, 2; 

7 Binlog 列说明

Event_type

• QUERY_ EVENT 与数据无关的操作，begin、drop table、truncate table 等

• TABLE MAP EVENT 记录下一个操作所对应的表信息，存储了数据库名和表名

• XID_ EVENT 标记事务提交

• WRITE_ ROWS_ EVENT 插入数据,即 insert 操作

• UPDATE_ ROWS_ EVENT 更新数据,即 update 操作

• DELETE ROWS EVENT 删除数据,即 delete 操作

8 代码实战监控 binlog

• 测试插入数据

• 

• 成功监控到：

• 

• update

• 

• 输出

• 

参考

• https://blog.csdn.net/vhomes/article/details/8082734