一、准备
先确认下当前位点信息,最新位点信息是1601,
执行命令:show BINLOG EVENTS in "mysql-bin.000031";
而项目目前位点信息是761 ,和1601 差别太多,是因为中间多了,插入,更新,删除操作。如下
二、解析插入,更新,删除操作事件。
2.1 ANONYMOUS_GTID_LOG_EVENT=34 该事件是mysql5.7 以后自动开启的,表示事物id,每个事物开始之前都会存在一个该事件,注释:
event:
因为header.type=34 ,sink 目前直接返回null,
2.2、QUERY_EVENT=2 该event 以文本记录binlog的操作
根据type=2
代码如下:querystring=begin,然后构造transactionbegin对象,
private Entry parseQueryEvent(QueryLogEvent event, boolean isSeek) { String queryString = event.getQuery(); if (StringUtils.endsWithIgnoreCase(queryString, BEGIN)) { TransactionBegin transactionBegin = createTransactionBegin(event.getSessionId()); Header header = createHeader(binlogFileName, event.getHeader(), "", "", null); return createEntry(header, EntryType.TRANSACTIONBEGIN, transactionBegin.toByteString()); } else if (StringUtils.endsWithIgnoreCase(queryString, COMMIT)) { TransactionEnd transactionEnd = createTransactionEnd(0L); // MyISAM可能不会有xid事件 Header header = createHeader(binlogFileName, event.getHeader(), "", "", null); return createEntry(header, EntryType.TRANSACTIONEND, transactionEnd.toByteString()); } else { boolean notFilter = false; EventType type = EventType.QUERY; String tableName = null; String schemaName = null; if (useDruidDdlFilter) { List<DdlResult> results = DruidDdlParser.parse(queryString, event.getDbName()); for (DdlResult result : results) { if (!processFilter(queryString, result)) { // 只要有一个数据不进行过滤 notFilter = true; } } if (results.size() > 0) { // 如果针对多行的DDL,只能取第一条 type = results.get(0).getType(); schemaName = results.get(0).getSchemaName(); tableName = results.get(0).getTableName(); } } else { DdlResult result = SimpleDdlParser.parse(queryString, event.getDbName()); if (!processFilter(queryString, result)) { notFilter = true; } type = result.getType(); schemaName = result.getSchemaName(); tableName = result.getTableName(); } if (!notFilter) { // 如果是过滤的数据就不处理了 return null; } if (!isSeek) { // 使用新的表结构元数据管理方式 EntryPosition position = createPosition(event.getHeader()); tableMetaCache.apply(position, event.getDbName(), queryString, null); } Header header = createHeader(binlogFileName, event.getHeader(), schemaName, tableName, type); RowChange.Builder rowChangeBuider = RowChange.newBuilder(); if (type != EventType.QUERY) { rowChangeBuider.setIsDdl(true); } rowChangeBuider.setSql(queryString); if (StringUtils.isNotEmpty(event.getDbName())) { // 可能为空 rowChangeBuider.setDdlSchemaName(event.getDbName()); } rowChangeBuider.setEventType(type); return createEntry(header, EntryType.ROWDATA, rowChangeBuider.build().toByteString()); } }
构造header对象(使用probuf)
private Header createHeader(String binlogFile, LogHeader logHeader, String schemaName, String tableName, EventType eventType) { // header会做信息冗余,方便以后做检索或者过滤 Header.Builder headerBuilder = Header.newBuilder(); headerBuilder.setVersion(version); headerBuilder.setLogfileName(binlogFile); headerBuilder.setLogfileOffset(logHeader.getLogPos() - logHeader.getEventLen()); headerBuilder.setServerId(logHeader.getServerId()); headerBuilder.setServerenCode(UTF_8);// 经过java输出后所有的编码为unicode headerBuilder.setExecuteTime(logHeader.getWhen() * 1000L); headerBuilder.setSourceType(Type.MYSQL); if (eventType != null) { headerBuilder.setEventType(eventType); } if (schemaName != null) { headerBuilder.setSchemaName(schemaName); } if (tableName != null) { headerBuilder.setTableName(tableName); } headerBuilder.setEventLength(logHeader.getEventLen()); // enable gtid position if (gtidSet != null) { String gtid = gtidSet.toString(); headerBuilder.setGtid(gtid); } return headerBuilder.build(); }构造entry对象(使用probuf)
public static Entry createEntry(Header header, EntryType entryType, ByteString storeValue) { Entry.Builder entryBuilder = Entry.newBuilder(); entryBuilder.setHeader(header); entryBuilder.setEntryType(entryType); entryBuilder.setStoreValue(storeValue); return entryBuilder.build(); }
最终对象为:
BEGIN std test
header {
version: 1logfileName: "mysql-bin.000031"
logfileOffset: 460
serverId: 1
serverenCode: "UTF-8"
executeTime: 1529994671000
sourceType: MYSQL
schemaName: ""
tableName: ""
eventLength: 72
}
entryType: TRANSACTIONBEGIN
storeValue: " \200\001"
2.3、 TABLE_MAP_EVENT=19 数据库有5列,
sink过滤: 跳过
2.4、WRITE_ROWS_EVENT=30 插入的对象在rowsbuf(byte)里
插入,删除,更新,在sink模块里共用的是同一个 event方法,如下
代码
private Entry parseRowsEvent(RowsLogEvent event) { if (filterRows) { return null; } try { TableMapLogEvent table = event.getTable(); if (table == null) { // tableId对应的记录不存在 throw new TableIdNotFoundException("not found tableId:" + event.getTableId()); } boolean isHeartBeat = isAliSQLHeartBeat(table.getDbName(), table.getTableName()); boolean isRDSHeartBeat = tableMetaCache.isOnRDS() && isRDSHeartBeat(table.getDbName(), table.getTableName()); String fullname = table.getDbName() + "." + table.getTableName(); // check name filter if (nameFilter != null && !nameFilter.filter(fullname)) { return null; } if (nameBlackFilter != null && nameBlackFilter.filter(fullname)) { return null; } // if (isHeartBeat || isRDSHeartBeat) { // // 忽略rds模式的mysql.ha_health_check心跳数据 // return null; // } TableMeta tableMeta = null; if (isRDSHeartBeat) { // 处理rds模式的mysql.ha_health_check心跳数据 // 主要RDS的心跳表基本无权限,需要mock一个tableMeta FieldMeta idMeta = new FieldMeta("id", "bigint(20)", true, false, "0"); FieldMeta typeMeta = new FieldMeta("type", "char(1)", false, true, "0"); tableMeta = new TableMeta(table.getDbName(), table.getTableName(), Arrays.asList(idMeta, typeMeta)); } else if (isHeartBeat) { // 处理alisql模式的test.heartbeat心跳数据 // 心跳表基本无权限,需要mock一个tableMeta FieldMeta idMeta = new FieldMeta("id", "smallint(6)", false, true, null); FieldMeta typeMeta = new FieldMeta("type", "int(11)", true, false, null); tableMeta = new TableMeta(table.getDbName(), table.getTableName(), Arrays.asList(idMeta, typeMeta)); } EventType eventType = null; int type = event.getHeader().getType(); if (LogEvent.WRITE_ROWS_EVENT_V1 == type || LogEvent.WRITE_ROWS_EVENT == type) { eventType = EventType.INSERT; } else if (LogEvent.UPDATE_ROWS_EVENT_V1 == type || LogEvent.UPDATE_ROWS_EVENT == type) { eventType = EventType.UPDATE; } else if (LogEvent.DELETE_ROWS_EVENT_V1 == type || LogEvent.DELETE_ROWS_EVENT == type) { eventType = EventType.DELETE; } else { throw new CanalParseException("unsupport event type :" + event.getHeader().getType()); } Header header = createHeader(binlogFileName, event.getHeader(), table.getDbName(), table.getTableName(), eventType); EntryPosition position = createPosition(event.getHeader()); RowChange.Builder rowChangeBuider = RowChange.newBuilder(); rowChangeBuider.setTableId(event.getTableId()); rowChangeBuider.setIsDdl(false); rowChangeBuider.setEventType(eventType); RowsLogBuffer buffer = event.getRowsBuf(charset.name()); BitSet columns = event.getColumns(); BitSet changeColumns = event.getChangeColumns(); boolean tableError = false; if (tableMetaCache != null && tableMeta == null) { // 入错存在table meta // cache tableMeta = getTableMeta(table.getDbName(), table.getTableName(), true, position); if (tableMeta == null) { tableError = true; if (!filterTableError) { throw new CanalParseException("not found [" + fullname + "] in db , pls check!"); } } } while (buffer.nextOneRow(columns)) { // 处理row记录 RowData.Builder rowDataBuilder = RowData.newBuilder(); if (EventType.INSERT == eventType) { // insert的记录放在before字段中 tableError |= parseOneRow(rowDataBuilder, event, buffer, columns, true, tableMeta); } else if (EventType.DELETE == eventType) { // delete的记录放在before字段中 tableError |= parseOneRow(rowDataBuilder, event, buffer, columns, false, tableMeta); } else { // update需要处理before/after tableError |= parseOneRow(rowDataBuilder, event, buffer, columns, false, tableMeta); if (!buffer.nextOneRow(changeColumns)) { rowChangeBuider.addRowDatas(rowDataBuilder.build()); break; } tableError |= parseOneRow(rowDataBuilder, event, buffer, changeColumns, true, tableMeta); } rowChangeBuider.addRowDatas(rowDataBuilder.build()); } RowChange rowChange = rowChangeBuider.build(); if (tableError) { Entry entry = createEntry(header, EntryType.ROWDATA, ByteString.EMPTY); logger.warn("table parser error : {}storeValue: {}", entry.toString(), rowChange.toString()); return null; } else { Entry entry = createEntry(header, EntryType.ROWDATA, rowChangeBuider.build().toByteString()); return entry; } } catch (Exception e) { throw new CanalParseException("parse row data failed.", e); } }
解析思路:
1、获取table。
2、心跳,rds判断
3、获取库.表 test.student
4、对eventype归纳
5、组装header(如上代码)
( version: 1
logfileName: "mysql-bin.000031"logfileOffset: 894
serverId: 1
serverenCode: "UTF-8"
executeTime: 1529994880000
sourceType: MYSQL
schemaName: "test"
tableName: "student"
eventLength: 60
eventType: INSERT)
6、组装position
EntryPosition[included=false,journalName=mysql-bin.000031,position=954,serverId=1,gtid=<null>,timestamp=1529994880000]
7、从db2里获取tableMeta(表结构信息)
public TableMeta getTableMeta(String schema, String table, boolean useCache, EntryPosition position) { TableMeta tableMeta = null; if (tableMetaTSDB != null) { tableMeta = tableMetaTSDB.find(schema, table); if (tableMeta == null) { // 因为条件变化,可能第一次的tableMeta没取到,需要从db获取一次,并记录到snapshot中