mysql binlog 解析

开启、查看binlog

1、查看是否开启binlog
show variables like ‘log_bin%’;

2、查看binlog位置
show variables like ‘%datadir%’;

3、查看binlog
show binary logs;
show binlog events in ‘binlog.000031’;

4、使用mysqlbinlog 查看binlog内容
#mysqlbinlog --base64-output=decode-rows -v --database=test D:\dev\mysql-8.0.15-winx64\data\binlog.000033

5、配置server-id
6、配置需要监听的数据库
7、为用户授权从库同步binlog的权限

使用shyiko的mysql-binlog-connector-java解析binlog数据

本例使用shyiko的mysql-binlog-connector-java将数据监听到，并转化为可用的map，再将map转化为java model的完整实例，转化部分为可选

监听binlog

@Component
@Slf4j
public class BinLogEventListener implements CommandLineRunner {

    @Autowired
    private BinlogProperties binlogProperties;

    @Autowired
    private BinaryLogDataListener binaryLogDataListener;

    @Async
    @Override
    public void run(String... args) throws Exception {
        // 创建binlog监听客户端
        BinaryLogClient client = new BinaryLogClient(binlogProperties.getHost(),
                binlogProperties.getPort(), binlogProperties.getUser(), binlogProperties.getPassword());
        client.setServerId(binlogProperties.getServerId());
        client.registerEventListener(binaryLogDataListener);
        client.connect();
    }
}

Binlog监听器

@Component
@Slf4j
public class BinaryLogDataListener implements BinaryLogClient.EventListener {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private Converter baseConverter;

    @Autowired
    private DataProceser dataProceser;

    @Autowired
    private RedisUtil redisUtil;

    private Map<String, List<String>> columnNamesMap = new HashMap<>();
    private HashMap<Long, TableData> tableMap = new HashMap<>();

    private static final String METHOD_GET_TABLE_ID_NAME = "getTableId";

    @Value("${binlog.event.listener.database}")
    private List<String> database;

    @Override
    public void onEvent(Event event) {
        EventData data = event.getData();
        EventHeader header = event.getHeader();


        try {
            EventType eventType = header.getEventType();
            // 表信息
            TableData table = null;
            if (eventType == TABLE_MAP) {
                if (data instanceof TableMapEventData) {
                    TableMapEventData tableMapEventData = (TableMapEventData) data;
                    if (tableMapEventData != null) {
                        // 没有监听的库不处理
                        if (!database.contains(tableMapEventData.getDatabase())) {
                            return;
                        }
                        log.info(" 时间:" + Calendar.getInstance().getTime() + "tableId: " + tableMapEventData.getTableId() + ", tableName: " + tableMapEventData.getDatabase() + "." + tableMapEventData.getTable());
                        table = new TableData(tableMapEventData.getDatabase(), tableMapEventData.getTable());
                        tableMap.put(tableMapEventData.getTableId(), table);
                    }
                }
            }
            //-------------------------------
            // 每次数据变动都会先走TABLE_MAP 事件，需要处理的表tableMap里肯定有，如果没有，则不需要监听处理该表
            try {
                if (!tableMap.containsKey(data.getClass().getDeclaredMethod(METHOD_GET_TABLE_ID_NAME).invoke(data))) {
                    return;
                }
            } catch (Exception e) {
                log.debug("{}", e);
                return;
            }
            String method = null;
            boolean isProcess = false;
            try {
                // 最后的Map数据
                List<Map<String, Object>> datas = null;
                List list = null;
                List<String> columnNames = null;
                // 删除
                if (eventType == DELETE_ROWS || eventType == EXT_DELETE_ROWS) {
                    isProcess = true;
                    DeleteRowsEventData deleteRowsEventData = (DeleteRowsEventData) data;
                    method = "DELETE";
                    if (data != null) {
                        table = tableMap.get(deleteRowsEventData.getTableId());
                        log.info(" 时间:{}，method:{}，tableName:{}，sqlParameter:{}", Calendar.getInstance().getTime(), method, table, JSON.toJSONString(Arrays.asList(deleteRowsEventData.getRows())));
                        columnNames = getColumnNames(table);
                    }
                }
                // 新增
                if (eventType == WRITE_ROWS || eventType == EXT_WRITE_ROWS) {
                    isProcess = true;
                    WriteRowsEventData writeRowsEventData = (WriteRowsEventData) data;
                    method = "INSERT";
                    if (data != null) {
                        table = tableMap.get(writeRowsEventData.getTableId());
                        log.info(" 时间:{}，method:{}，tableName:{}，sqlParameter:{}", Calendar.getInstance().getTime(), method, table, JSON.toJSONString(Arrays.asList(writeRowsEventData.getRows())));
                        columnNames = getColumnNames(table);
                    }
                }
                // 更新
                if (eventType == UPDATE_ROWS || eventType == EXT_UPDATE_ROWS) {
                    isProcess = true;
                    method = "UPDATE";
                    if (data != null) {
                        UpdateRowsEventData updateRowsEventData = (UpdateRowsEventData) data;