【MySQL（二十一）】binlog 事件

原创

wx636dc453ed367 2022-11-11 14:03:14 博主文章分类：mysql ©著作权

文章标签 binlog mysql 数据主键 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者wx636dc453ed367的原创作品，请联系作者获取转载授权，否则将追究法律责任

binlog时机

事务提交时写入binlog，但是binlog持久化到磁盘与sync_binlog参数有关：

0：只fwrite写入操作系统cache，由操作系统决定什么时候持久化到磁盘，及fsync；

1：fsync直接写入磁盘；

n：提交n个事务后fsync；

1最安全，0性能最好；

binlog文件

有两种，一种是binlog的索引文件，也即xxx-bin.index，另一种是binlog文件。

索引文件：其实就是存储了当前所有binlog文件的文件名。

比如：

./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003

binlog文件：xxx-bin.0000N，N代表第几个文件，是二进制文件，存储了binlog的内容。

什么时候会新建一个binlog文件？

超过max_binlog_size或者使用flush logs命令。

binlog日志类型

statement：记录执行的语句；对于更新而言不需要记录大量的行数据，但是有些信息无法记录比如随机数或者当前时间，所以还需要一些上下文信息。

row：只记录修改的行，不用上下文，但是数据量可能较大。

mixed：对于不需要上下文的语句，使用statement，否则使用row，所以会同时存在两种格式；

如何查看binlog

如果在mysql客户端内，可以使用show binlog events;这条命令会显示第一个binlog文件内的事件；当然可以指定binlog文件，比如：show binlog events in 'xxx-bin.0000N'。另外也可以使用show binary logs命令查看当前所有binlog文件名。

如果不在mysql客户端，可以使用mysql提供的mysqlbinlog命令。比如：mysqlbinlog -v --base64-output=decode-rows --start-position="156" ~/mysql/master/data/mysql-bin.000004

简单记一下几个参数的含义：

-v：将row模式的反解为statement模式，方便查阅；

--base64-output：如果不加，默认输出是base64格式的，加上这个参数，输出是base64解密后的；

--start-position：指定开始位置，当然也可以指定结束位置；

另外还有一些其他参数也可以指定，比如库名以及表名等。

binlog事件类型

总共有3个版本：v1，v2和v4。mysql5以上使用过的v4。这里只记录这几个关键的事件类型：

QUERY_EVENT：在statement模式下，增删改的语句都会生成该事件；在row模式下，DDL的改动会生成该事件;

ROTATE_EVENT：新的binlog文件生成时，会记录该事件，内容就是下一个binlog文件的文件名；

FORMAT_DESCRIPTION_EVENT：每一个binlog文件的起始事件，描述文件属性；

TABLE_MAP_EVENT：在row模式下会有，每一个更新事件都会先有一个TABLE_MAP_EVENT事件，用于记录表的一些信息。

WRITE_ROWS_EVENT：在row模式下会有，insert；

UPDATE_ROWS_EVENT：在row模式下会有，update；

DELETE_ROWS_EVENT：在row模式下会有，delete；

binlog示例

statement格式

可以通过这个命令设置binlog格式：set global binlog_format='statement/row/mixed'; 登出同时输入如下命令查看binlog格式：show variables like '%binlog%';

执行如下两行sql语句：

insert into user (`name`) values ('ly1');

update user set `name` = 'ly2' where id = 3;

show binlog events:

【MySQL（二十一）】binlog 事件_mysql

两个更新语句分别对应了两个Query事件。其中的insert语句前有一个Intvar事件，这是一个Context事件，用于指定自增主键id。

mysqlbinlog -v --base64-output=decode-rows ~/mysql/master/data/mysql-bin.000006

【MySQL（二十一）】binlog 事件_mysql_02

row格式

flush logs;insert into user (`name`) values ('ly3');update user set `name` = 'ly4' where id = 4;

【MySQL（二十一）】binlog 事件_mysql_03

可以看到这里分别有一个write_rows事件和update_rows事件，两个事件之前都有一个table_map事件描述表名和列信息；这里没有显示修改的具体内容，可以使用myslqbinlog命令看下。

【MySQL（二十一）】binlog 事件_binlog_04

binlog冲突

理想情况下，从库的复制是重放主库的sql，不会有问题，但是实际操作时，会因为一些原因导致从库binlog执行冲突，这种情况下从库复制将会终止，通过show slave status\G;命令可以查看具体的出错原因。比如主键重复、对应的表或者库不存在等等。出现冲突从库停止复制是正确的设计，符合fail-fast原则，否则，将会导致主从数据不一致。我们可以手动处理冲突，然后再继续复制，当然确认对业务无影响，也可以选择跳过冲突。比如通过set global sql_slave_skip_counter=N;命令跳过特定数目的event。或者配置mysql配置文件，跳过特定的error类型。

这里演示一个例子：

在一个有自增id的表里，从库先插入一条数据，主库再插入一条数据，此时从库会因为主键冲突而停止复制。

现在user表的next主键是9，表里均没有数据，先在从库insert：

【MySQL（二十一）】binlog 事件_mysql_05