漫谈binlog | MySQL

vvccc
532 阅读7分钟

简介

文章分为以下三个模块：

  1. 基础：了解binlog是个什么东西。
  2. 使用：通过java代码监听并解析binlog。
  3. 项目：介绍在项目中运用binlog。

基础

我们先来了解一下什么是binlog。

Mysql Binlog是二进制格式的日志文件，但是不能把binlog文件等同于OS系统某目录下的具体文件，这是狭隘的。Binlog是用来记录Mysql内部对数据库的改动（只记录对数据的修改操作），主要用于数据库的主从复制以及增量恢复。

[Mysql Binlog_百度百科 (baidu.com)](baike.baidu.com/item/Mysql Binlog)

MySQL 日志：undo log、redo log、binlog 有什么用？ | 小林coding (xiaolincoding.com)

MySQL Binlog日志 - mysql架构 - SegmentFault 思否

阅读上面两篇文章，应该至少了解以下要点：

  • 一个事务没有执行完，MySQL崩溃了，但是有undo log，它里面存了事务开始前的数据，所以可以恢复到事务开始前的装填，保证了事务的原子性。

  • Innodb 设计了一个Buffer Pool，修改完的数据不直接写回磁盘，而是写入缓冲池，提高数据库的读写性能。

  • 缓冲池基于内存，如果这里面的脏数据（被修改过的数据。缓存中与磁盘中不一致的数据）还没写入磁盘MySQL服务器断电了，我们可以使用redo log解决这个问题，其中记录了缓冲池中每个数据页做了什么修改。

  • redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。

    在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。

    当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

    MySQL 日志：undo log、redo log、binlog 有什么用？ | 小林coding (xiaolincoding.com)

  • undo log和redo log都属于innodb门下，binlog属于MySQL server门下。

  • binlog两大作用：主从复制、数据恢复。程序员删库跑路了用binlog恢复，其他不行。

  • undo log与redo log区别

  • redo log与binlog区别

  • binlog记录模式

    • 被修改的每行都记录
    • 只记sql语句
    • 混合。自动从前两种中选一种。

  • binlog写入机制（理解log event）

    1. 修改数据的sql触发生成log event。

    2. 事务执行过程中log event写入缓冲区。

    3. 每个事务一个缓冲区（有两种缓冲区）。

    4. 事务提交，log event持久化到磁盘。（不同事务串行将log event写入binlog文件）。

使用

使用shyiko/mysql-binlog-connector-java: MySQL Binary Log connector (github.com)实现对binlog文件的监听。

下面我们通过一个demo演示如何使用。

public static void main(String[] args) {
    BinaryLogClient client = new BinaryLogClient(
            "127.0.0.1",
            3306,
            "root",
            "123456"
    );
    client.registerEventListener(event -> {//注意这个Event event
        EventData data = event.getData();

        EventHeader header = event.getHeader();

        System.out.println("EventType:" + header.getEventType());

        if (data instanceof UpdateRowsEventData) {
            System.out.println(">>>UPDATE<<<");
            System.out.println(data);
        } else if (data instanceof WriteRowsEventData) {
            System.out.println(">>>WRITE<<<");
            System.out.println(data);
        } else if (data instanceof DeleteRowsEventData) {
            System.out.println(">>>DELETE<<<");
            System.out.println(data);
        }

        System.out.println("");
    });

    try {
        client.connect();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

上面的代码是一个大体框架，通过client.registerEventListener()我们拿到了Event event，这就是对上面提到的log event的对象化。

Event类的结构

com.github.shyiko.mysql.binlog.event.Event结构如下：

  • Event
    • EventHeader
      • Timestamp时间开始执行的时间
      • EventType该事件的类型
      • ServerId服务器的serverId
      • HeaderLength时间头长度
      • DataLength数据长度
    • EventData
      • EventData有多种类型，与header中的EventType对应。比如UpdateRowsEventData类型的EventData，header中的EventType为EXT_UPDATE_ROWS。

EventData的类型

EventData是一个接口，它的实现类有许多类型，对应了log event有许多类型。理解下这句话：binlog日志文件的内容就是各种不同类型的log event的集合。

修改数据库中的数据时就会产生一个UpdateRowsEventData类型的EventData。

在上面的代码中我们已经拿到了Event event，又通过它拿到了EventData data，接下来我们通过sql语句修改数据库中的值，将EventData data打印出来看看会发生什么。

update t_room_type set type='test1' where type_id=11;

结果如下：

EventType:EXT_UPDATE_ROWS
>>>UPDATE<<<
UpdateRowsEventData{tableId=115, includedColumnsBeforeUpdate={0, 1}, includedColumns={0, 1}, rows=[	{before=[11, test2], after=[11, test1]}
]}

从第三行到最后就是EventData data的内容，可以看到其中包含了表id、修改前的数据、修改后的数据。

删除和添加操作同理会产生对应类型的EventData。

常用EventData的类型如下：

  • TABLE_MAP_EVENT：用于描述表的内部ID和结构定义

  • ROW_EVENT：每个ROW_EVENT之前都有一个TABLE_MAP_EVENT

    • EXT_UPDATE_ROWS：在DB中修改数据的操作信息

    • EXT_WRITE_ROWS：向DB中写入数据的操作信息

    • EXT_DELETE_ROWS：从DB中删除数据的操作信息

TABLE_MAP_EVENT后面要用到，这里先有个印象。

发现问题

我们发现上面的结果中只有includedColumns={0, 1}这样的列索引，而不是像type_id、type这样的列名，这样我们使用数据时不能直观的对应列名与数据。

下面我们会通过一条sql语句查询information_schema中的columns表，里面包含了列名和列索引之间的对应关系。

select table_schema, table_name, column_name, ordinal_position 
from information_schema.columns 
where table_schema = 'hotel' and table_name = 't_room_type';
  • table_schema表所处的数据库名
  • table_name表名
  • column_name列名
  • ordinal_position列索引

结果如下：

+--------------+-------------+-------------+------------------+
| TABLE_SCHEMA | TABLE_NAME  | COLUMN_NAME | ORDINAL_POSITION |
+--------------+-------------+-------------+------------------+
| hotel        | t_room_type | type        |                2 |
| hotel        | t_room_type | type_id     |                1 |
+--------------+-------------+-------------+------------------+
2 rows in set (0.00 sec)

注意：使用mysql-binlog-connector-java获得的列索引从0开始，而从information_schema.columns表中查到的列索引从1开始，使用时需要注意转换。

处理问题

我们的目标：获取被修改数据，数据的字段名与数据的值对应。

准备

先看一下我们的sql，where部分需要数据库名和表名，而刚才我们将这两个部分写死了，如何动态的获得我们正在操作的数据库名和表名呢？这两个信息隐藏在类型为TABLE_MAP的Event中（上面提到过）。通过如下方式获得：

EventHeader header = event.getHeader();
EventType eventType = header.getEventType();

if (eventType == EventType.TABLE_MAP) {
    TableMapEventData data = event.getData();
    DBName = data.getDatabase();
    tableName = data.getTable();
}

然后我们再通过JDBC执行sql在information_schema.columns表中查这个表中列名和列索引之间的对应关系，将结果存入Map<Integer, String> indexNameMap，方便我们后面的使用，代码如下：

final String SQL_SCHEMA = "select table_schema, table_name, column_name, ordinal_position " +
        "from information_schema.columns " +
        "where table_schema = ? and table_name = ?";

Object[] paramValues = new Object[]{DBName, tableName};//在TableMapEventData中获取
jdbcTemplate.query(
        SQL_SCHEMA,
        paramValues,
        (rs, i) -> {
            int pos = rs.getInt("ORDINAL_POSITION");
            String colName = rs.getString("COLUMN_NAME");
            indexNameMap.put(pos - 1, colName);//都存到这个map中
            return null;
        }
);

到现在我们干了什么？我们动态的获得了当前修改数据所在的数据库名和表名，进而又获得了该表列名和列索引之间的对应关系。

解析binlog

重点！

前面我们知道了有3种ROW_EVENT，分对应修改、删除、添加，binlog会记录被删除的数据，会记录被添加的数据是什么，被修改的数据有些特殊，binlog会记录被修改前是什么数据，修改后是什么数据，所以修改与删除和添加的数据格式有些不一样。而在修改操作中，我们一般只关心修改后的数据是什么，所有接下来我们会有一个函数统一这三种数据，我称之为三合一函数，代码如下：

private List<Serializable[]> getAfterValues(EventData eventData) {

    if (eventData instanceof WriteRowsEventData) {
        return ((WriteRowsEventData) eventData).getRows();
    }

    if (eventData instanceof UpdateRowsEventData) {
        return ((UpdateRowsEventData) eventData).getRows().stream()
                .map(Map.Entry::getValue)
                .collect(Collectors.toList());
    }

    if (eventData instanceof DeleteRowsEventData) {
        return ((DeleteRowsEventData) eventData).getRows();
    }

    return Collections.emptyList();
}

简单说下代码。删除和添加没什么好说的，添加的rows里是一个map的list，map的key是修改前的数据，value是修改后的数据，我们只需要修改后的数据，即只需要list中map的每个value。

这样就统一了三种EventData，方便我们后续的处理。

过滤我们不需要的binlog信息，代码如下：

//除了TABLE_MAP和这三条EXT_~_ROWS其他的都不需要关心
if (eventType != EventType.EXT_UPDATE_ROWS
        && eventType != EventType.EXT_WRITE_ROWS
        && eventType != EventType.EXT_DELETE_ROWS) {
    return;
}

这段代码在TABLE_MAP的下面，不用担心把TABLE_MAP给弄没了。

回顾下我们准备阶段干了什么。通过TABLE_MAP填充了Map<Integer, String> indexNameMap，其中key是列索引号，value是列名。

接下来我们准备一个List<Map<String, String>> afterMapList，我们的最终目的就是向list中填充数据。

为什么是个list？因为一次被修改的数据可能有很多个。

map表示什么？一个map表示一行记录。key表示列名，value表示存储的值。一行记录可能有多个列，所有map中可能有多组键值对。

了解了上面的东西，我们再写一行代码：

List<Serializable[]> afterValues = getAfterValues(event.getData());

用三合一函数getAfterValues()把修改后的值拿出来。afterValues看做一个二维数组，由被改变的记录构成，这些记录都只有值，没有对应的列名。

遍历afterValues。

for (Serializable[] afterValue : afterValues) {
    Map<String, String> afterMap = new HashMap<>();

    for (int i = 0; i < afterValue.length; i++) {
        String colName = indexNameMap.get(i);
        String colValue = afterValue[i].toString();
        afterMap.put(colName,colValue);
    }

    afterMapList.add(afterMap);
}

再回顾下indexNameMap，key是列索引，value是列名，我们根据列索引拿出列名作为afterMap的key。

一个afterMap相当于是一条记录。

到此我们完成了对binlog的解析。执行一条修改的sql语句，再打印下afterMapList看看。

update t_room_type set type='test1' where type_id=11;

结果：

[{type_id=11, type=test1}]

解析：一个只有一个元素的list，因为我们的sql语句只修改了一条记录。list中的元素是一个map，map中有两个键值对，因为这这张表有两列。第一个键值对表示：第一例的列名为type_id，值为11。第二个键值对表示：第一例的列名为type，值为test1。

我们可以一次删除多条数据执行sqldelete from t_room_type where type_id > 5;，结果如下：

Query OK, 2 rows affected (0.02 sec)

此时afterMapList内容为[{type_id=10, type=三人间}, {type_id=11, type=test1}]。

一个小问题

三合一函数getAfterValues的返回值为List<Serializable[]>，能不能解释Serializable在此处的用意？

以DeleteRowsEventData这个删除事件为例，源码中有一个private List<Serializable[]> rows;的成员变量，list很好理解，一次可能删除多个数据。可以推知list中的一个元素Serializable[]就是一条表中的一条记录，且是刚被删除的一条记录。一条记录有多个列，所以一个Serializable就是某个列的值。

为什么要这么设计呢？因为MySQL中的值的类型有许多，需要将这些类型对应到java中的类型，部分对应关系如下：

Current ColumnType to java type mapping is following:
   ColumnType.TINY: Integer
   ColumnType.SHORT: Integer
   ColumnType.ENUM: Integer
   ColumnType.SET: Long
   ColumnType.LONGLONG: Long
   ColumnType.FLOAT: Float
   ...

这些对应到java中的类型都是Serializable接口的实现类，所以我们就用一个Serializable[]表示了一条记录所有数据的类型。

需注意：UpdateRowsEventData中的rows为List<Map.Entry<Serializable[], Serializable[]>> rows，map中的key为修改前数据，value为修改后的数据。

其他

binlog的记录模式有三种，我们用的哪一种？执行sqlshow variables like '%binlog_format%';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set, 1 warning (0.02 sec)

Q. EventData for inserted/updated/deleted rows has no information about table (except for some weird id). How do I make sense out of it?

A. Each WriteRowsEventData/UpdateRowsEventData/DeleteRowsEventData event is preceded by TableMapEventData which contains schema & table name. If for some reason you need to know column names (types, etc). - the easiest way is to

select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, 
DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, 
CHARACTER_SET_NAME, COLLATION_NAME from INFORMATION_SCHEMA.COLUMNS;
# see https://dev.mysql.com/doc/refman/5.6/en/columns-table.html for more information

(yes, binary log DOES NOT include that piece of information).

You can find JDBC snippet here.

shyiko/mysql-binlog-connector-java: MySQL Binary Log connector (github.com)

关于mysql-binlog日志解析框架 - 知乎 (zhihu.com)

通过mysql-binlog-connector-java监控 MySQL 的 binlog_不负好时光⁡的博客-CSDN博客_mysql-binlog-connector

项目

项目中使用binlog解耦两个模块：投放模块实现数据增删改查，这个过程中产生binlog数据；检索模块获得binlog数据构建索引，实现检索功能。

下面我们一起看看binlog数据的旅行（纪念我2023.1.9-2023.1.14的成都之行）。

监听binlog.jpg

其他

Springboot的CommandLineRunner与PostConstruct执行顺序 - 周旗山 (zhouqishan.cn)

springboot中@PostConstruct、CommandLineRunner、BeanPostProcessor执行顺序 - 赛博朋克水管工 - 博客园 (cnblogs.com)

avatar
文章
阅读
粉丝