简短的需求说明
最近有个需求是做代码质量管理，没法跨库查询，通过接口获取数据做统计也不好处理，于是打算把禅道中需要统计的表同步过来，同时禅道数据修改了也需要能同步。
第一个想到的是用阿里开源的 canal， 但canal比较重，需要单独起服务。Maxwell也一样。我只是需要同步几张表，对高可用也没有要求。最终发现 mysql-binlog-connector-java，少量代码即可消费到binlog的变动。
前提
- MySQL需要开启 binlog
通过 SHOW VARIABLES LIKE 'log_bin'; 查看，ON为开启
- 通过 SHOW VARIABLES LIKE 'binlog_format'; 确认一下 binlog_format，MySQL的binlog_format有三种值，row、statement、mixed，具体区别可以自己搜索一下。下面的实验只针对row和mixed
实验流程
- 连接数据库，设置监听器，这一步是通用的。
<dependency>
<groupId>com.zendesk</groupId>
<artifactId>mysql-binlog-connector-java</artifactId>
<version>0.25.0</version>
</dependency>
BinaryLogClient logClient = new BinaryLogClient(dataSyncConfig.getHostname(), dataSyncConfig.getPort(),
dataSyncConfig.getUsername(), dataSyncConfig.getPassword());
syncTable = dataSyncConfig.getTables();
initPosition(logClient);
EventDeserializer eventDeserializer = new EventDeserializer();
eventDeserializer.setCompatibilityMode(
EventDeserializer.CompatibilityMode.DATE_AND_TIME_AS_LONG
//EventDeserializer.CompatibilityMode.CHAR_AND_BINARY_AS_BYTE_ARRAY
);
logClient.setEventDeserializer(eventDeserializer);
logClient.setServerId(1);
logClient.registerEventListener(new BinLogEvent());
logClient.connect();
-
mixed的事件处理，（binlog_format设置为mixed和row收到的事件类型不一样）。 mixed收到的需要处理的事件类型为QUERY，如下所示，拿到事件中的sql，重放到需要同步的库中即可。
Event{header=EventHeaderV4{timestamp=1668390976000, eventType=QUERY, serverId=1, headerLength=19, dataLength=252, nextPosition=100297550, flags=0}, data=QueryEventData{threadId=308383, executionTime=0, errorCode=0, database='conf', sql='update STATISTICS set use_num = null where type = 0 and update_time < STR_TO_DATE('2022-11-14','%Y-%m-%d')'}}
@Override public void onEvent(Event event) { EventHeaderV4 header = event.getHeader(); position = header.getNextPosition(); EventType eventType = event.getHeader().getEventType(); EventData eventData = event.getData(); if (eventType == EventType.ROTATE) { logger.info("rotate event trigger"); RotateEventData rotateEventData = (RotateEventData) eventData; savePosition(rotateEventData); } // 只处理我想要处理的事件 if (eventType != EventType.QUERY) { return; } if (eventData instanceof QueryEventData) { QueryEventData queryEventData = (QueryEventData) eventData; String sql = queryEventData.getSql(); if ("BEGIN".equals(sql) || "COMMIT".equals(sql)) { return; } if (StringUtils.isNotEmpty(sql)) { Statement statement = CCJSqlParserUtil.parse(sql); String tableName = null; String schemaName = null; if (statement instanceof Update) { Update update = (Update) statement; tableName = update.getTable().getName(); schemaName = update.getTable().getSchemaName(); } else if (statement instanceof Delete) { Delete delete = (Delete) statement; tableName = delete.getTable().getName(); schemaName = delete.getTable().getSchemaName(); } else if (statement instanceof Insert) { Insert insert = (Insert) statement; tableName = insert.getTable().getName(); schemaName = insert.getTable().getSchemaName(); } if (syncTable.contains(tableName)) { if (StringUtils.isNotEmpty(schemaName)) { sql = sql.replaceAll(schemaName+".", ""); } logger.info("excute sync sql:{}", sql); jdbcTemplate.execute(sql); } } } } } -
row事件处理
row收到的事件，以update为例：
update Event触发流程：
- Event{header=EventHeaderV4{timestamp=1668156904000, eventType=GTID, serverId=497377, headerLength=19, dataLength=46, nextPosition=59636830, flags=0}, data=GtidEventData{flags=0, gtid='3e56cbd4-3e0e-11ed-8c63-1c34da4fc691:12432'}}
- Event{header=EventHeaderV4{timestamp=1668156904000, eventType=QUERY, serverId=497377, headerLength=19, dataLength=64, nextPosition=59636913, flags=8}, data=QueryEventData{threadId=1751583, executionTime=0, errorCode=0, database='go_test', sql='BEGIN'}}
- Event{header=EventHeaderV4{timestamp=1668156904000, eventType=TABLE_MAP, serverId=497377, headerLength=19, dataLength=50, nextPosition=59636982, flags=0}, data=TableMapEventData{tableId=723, database='go_test', table='USER', columnTypes=15, 15, 15, 1, 18, columnMetadata=192, 192, 192, 0, 0, columnNullability={}, eventMetadata=null}}
- Event{header=EventHeaderV4{timestamp=1668156904000, eventType=EXT_UPDATE_ROWS, serverId=497377, headerLength=19, dataLength=27, nextPosition=59637028, flags=0}, data=UpdateRowsEventData{tableId=723, includedColumnsBeforeUpdate={0}, includedColumns={3}, rows=[ {before=[23], after=[1]}]}}
- Event{header=EventHeaderV4{timestamp=1668156904000, eventType=XID, serverId=497377, headerLength=19, dataLength=12, nextPosition=59637059, flags=0}, data=XidEventData{xid=19845592}}
主要处理 两个事件 TABLE_MAP 和 EXT_UPDATE_ROWS
TABLE_MAP 事件主要是获取 tableId和table之间的关联关系，后面的事件只有tableId，需要通过tableId获取到table。
EXT_UPDATE_ROWS 事件获取到修改前和修改后的原始数据,。
下面的代码只是把修改的数据封装成json的实例代码：
@Override
public void onEvent(Event event) {
EventType eventType = event.getHeader().getEventType();
// 只处理想要处理的事件
if (eventType != EventType.QUERY
&& eventType != EventType.TABLE_MAP
&& eventType != EventType.EXT_UPDATE_ROWS
&& eventType != EventType.EXT_DELETE_ROWS
&& eventType != EventType.EXT_WRITE_ROWS) {
return;
}
EventData eventData = event.getData();
if (null != eventData) {
if (eventType == EventType.TABLE_MAP) {
TableMapEventData tableData = (TableMapEventData)eventData;
String table = tableData.getTable();
//关联tableId和tableName
if (tableColumnMap.keySet().contains(table)) {
tableMap.put(tableData.getTableId(), table);
}
} else {
handlerDataChange(eventType, eventData);
}
}
}
private void handlerDataChange(EventType eventType, EventData data) {
if (eventType == EventType.EXT_UPDATE_ROWS) {
UpdateRowsEventData eventData = (UpdateRowsEventData) data;
long tableId = eventData.getTableId();
if (tableMap.keySet().contains(tableId)) {
/*String tableName = tableMap.get(tableId);
final BitSet includedColumns = eventData.getIncludedColumns();
final long[] columnIds = includedColumns.toLongArray();*/
updateEvent(eventData);
}
} else if (eventType == EventType.EXT_WRITE_ROWS) {
//获取表名，字段名称，字段类型 拼接SQL执行同步
WriteRowsEventData eventData = (WriteRowsEventData) data;
long tableId = eventData.getTableId();
if (tableMap.keySet().contains(tableId)) {
eventData.getIncludedColumns();
for(Object[] rows : eventData.getRows()) {
}
}
} else if (eventType == EventType.EXT_DELETE_ROWS) {
DeleteRowsEventData eventData = (DeleteRowsEventData) data;
long tableId = eventData.getTableId();
if (tableMap.keySet().contains(tableId)) {
}
}
}
private void updateEvent(UpdateRowsEventData updateRowsEventData) {
// rows 每一个 Entry 是条记录,其中 Key 为修改前的记录，Value 为修改后的新的记录
List<Map.Entry<Serializable[], Serializable[]>> rows = updateRowsEventData.getRows();
// 获取修改后的新的值
List<Serializable[]> newValues = rows.stream().map(entry -> entry.getValue()).collect(Collectors.toList());
// 获取修改前的值
List<Serializable[]> oldValues = rows.stream().map(entry -> entry.getKey()).collect(Collectors.toList());
this.rowsToEntity(newValues, updateRowsEventData.getTableId());
System.out.println("=======================");
this.rowsToEntity(oldValues, updateRowsEventData.getTableId());
}
private void rowsToEntity(List<Serializable[]> rows, Long tableId) {
String tableFullName = tableMap.get(tableId);
// 获得当前 row 的数据库中对应的字段名称
List<String> columnNames = tableColumnMap.get(tableFullName);
JSONObject beanJSON = new JSONObject();
for (Serializable[] row : rows) {
for (int i = 0; i < row.length; i++) {
System.out.println("===:"+row[i]);
beanJSON.put(columnNames.get(i), row[i]);
}
System.out.println(beanJSON.toString());
}
}
}
