Java implementation of MySQL binlog log log listening

Posted by fri3ndly on Wed, 26 Jan 2022 06:12:15 +0100

Use case

Introducing maven dependency

<dependency>
    <groupId>com.github.shyiko</groupId>
    <artifactId>mysql-binlog-connector-java</artifactId>
    <version>0.21.0</version>
</dependency>

Implementation of monitoring binlog

public class App {
    public static void main(String[] args) throws Exception {
        BinaryLogClient logClient = new BinaryLogClient( 
                "127.0.0.1", 3306, "db", "root", "7654321"
        );
        logClient.registerEventListener(event -> {
            EventData data = event.getData();
            // If the log is an update record
            if(data instanceof UpdateRowsEventData){
                long tableId = ((UpdateRowsEventData) data).getTableId();
                System.out.println("update: " + tableId);
            }else if(data instanceof WriteRowsEventData){
                long tableId = ((WriteRowsEventData) data).getTableId();
                // If the log is a write operation
                System.out.println("write: " + tableId);
            }else if(data instanceof DeleteRowsEventData){
                long tableId = ((DeleteRowsEventData) data).getTableId();
                // If the log is a delete operation
                System.out.println("delete: " + tableId);
            }
        });

        // Start listening
        logClient.connect();
    }
}

Table in binlog_ id

table_id is not fixed. It is temporarily allocated when the table is loaded into memory (table_definition_cache). It is a growing variable.
When there is a new table change and it is not in the cache, the load table def operation will be triggered. At this time, it will be in the original last table_id + 1 as the id of the new table def.
flush tables, and then the update operation of the table will also trigger the table_id growth.
If the table def cache is too small, frequent swapping in and out will occur, resulting in the loss of the table_id growth is relatively fast.
Restart the main library to make the table_id returns to 0.
Table of the same table_id is not always the same. Remember, this is not a table definition, but an operation table.

table_ The meaning of ID

Everything has its meaning, table_id is the same. It is the link between frequent DML operations and table structure Table contained in binlog_ map_ Event (used to describe the internal ID and structure definition of the table)
If an insert updates 10000 pieces of data, does the corresponding table structure information need to be recorded 10000 times? In fact, it is an operation on the same table, so binlog only records a Table_map is used to record information related to the table structure, and the following Write_rows records the row information of the updated data. Between them is through table_id to contact.
table_ ID is used as hash key through set_ The method of table (table_id) hashes the information of a table into the cache; You can also get_table() method_ ID to obtain the corresponding table information.

flush table

Explanation: close all open table objects and empty the results in the query cache. That is, Flush tables will wait for all running SQL requests to end.

flush tables;

flush table table1,table2...

binlog related commands

-- query binglog Log list
show binary logs;

-- Query first(Earliest)of binlog journal
show binlog events; 
 
-- Specify query mysql-bin.000077 journal
show binlog events in 'mysql-bin.000077';
 
-- Specify query mysql-bin.000077 Log, and from pos=1024 Start checking
show binlog events in 'mysql-bin.000077' from 1024;
 
-- Specify query mysql-bin.000077 Log, and from pos=1024 Start checking and query 10 items
show binlog events in 'mysql-bin.000077' from 1024 limit 10;
 
-- Specify query mysql-bin.000077 Log, and from pos=1024 Start query, offset 2 lines and query 10 items
show binlog events in 'mysql-bin.000077' from 1024 limit 2,10;

reference

Topics: Java MySQL

Programmer Think