我的学习分享

https://ask.hellobi.com/blog/cimen/9133

Maven 配置

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

Java文件

import com.github.shyiko.mysql.binlog.BinaryLogFileReader;
import com.github.shyiko.mysql.binlog.event.Event;
import com.github.shyiko.mysql.binlog.event.deserialization.ChecksumType;
import com.github.shyiko.mysql.binlog.event.deserialization.EventDeserializer;

import java.io.File;
import java.io.IOException;
class Sample {
    public static void main(String[] args) throws IOException {
    	String filePath="D:\\DATA\\mysql-bin.000987";
    	File binlogFile = new File(filePath);
    	EventDeserializer eventDeserializer = new EventDeserializer();
    	eventDeserializer.setChecksumType(ChecksumType.CRC32);
    	BinaryLogFileReader reader = new BinaryLogFileReader(binlogFile, eventDeserializer);
		try {
		    for (Event event; (event = reader.readEvent()) != null; ) {
		        System.out.println(event.toString());
		    }
		} finally {
		    reader.close();
		}
		
		
    }
}

执行日志

Event{header=EventHeaderV4{timestamp=1501376071000, eventType=TABLE_MAP, serverId=3366725048, headerLength=19, dataLength=43, nextPosition=494953, flags=0}, data=TableMapEventData{tableId=72, database='mysql', table='ha_health_check', columnTypes=8, -2, columnMetadata=0, 65027, columnNullability={0}}}

Event{header=EventHeaderV4{timestamp=1501376071000, eventType=UPDATE_ROWS, serverId=3366725048, headerLength=19, dataLength=37, nextPosition=495009, flags=0}, data=UpdateRowsEventData{tableId=72, includedColumnsBeforeUpdate={0, 1}, includedColumns={0, 1}, rows=[
    {before=[1501376059857, m], after=[1501376071917, m]}
]}}

mysql-binlog-connector-java

Frequently Asked Questions

Q. How does a typical transaction look like?

A. GTID event (if gtid_mode=ON) -> QUERY event with "BEGIN" as sql -> ... -> XID event | QUERY event with "COMMIT" or "ROLLBACK" as sql.

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.

Connection connection = ...
DatabaseMetaData metaData = connection.getMetaData();
ResultSet tableResultSet = metaData.getTables(null, "public", null, new String[]{"TABLE"});
try {
while (tableResultSet.next()) {
String tableName = tableResultSet.getString("TABLE_NAME");
ResultSet columnResultSet = metaData.getColumns(null, "public", tableName, null);
try {
while (columnResultSet.next()) {
String columnName = columnResultSet.getString("COLUMN_NAME");
...
}
} finally {
columnResultSet.close();
}
}
} finally {
tableResultSet.close();
}