netdata监控mysql 监控mysql binlog

背景

之前在做某个需求的时候用到了mysql binlog监听做相应业务处理的功能，比较简单所以采用了java的mysql-binlog-connector-java。
网上也有很多关于该插件的使用方法。但是到自己使用，上生产的时候又碰到几个问题。

问题

1.首先第一个，上线后发现binlog日志无法定位到最新的位置的问题。因为公司测试环境以磁盘空间有限，所以未开启binlog。所以我的测试只能在本地window完成，window安装的mysql serverId默认为1。在本地的测试都是正常，但是上生产以后就发现binlog无法定位到最新的文件，后面查了才发现需要设置好serverId，所以使用命令 ：show variables like ‘server_id’，查阅线上数据库，并且在client初始化时设置就可以解决这个问题。

2.后面又发现根本无法监听各种mysql插入更新等操作，本地mysql使用的binlog_format=row,而线上使用的binlog_format=mixed,通过对比两种不同格式的binlog日志，可以发现row格式的binlog会记录所有的更新，插入事件为不同的事件类型，但是mixed格式的binlog只会记录Query类型事件,而所有的create，update，drop等操作都是该类型的事件，因此这里使用binlog=row格式去监听binlog，导致无法监听到插入更新的操作。考虑到线上binlog日志数量巨大无法轻易变更binlog格式，所以只能在代码层面对binlog_format=mixed进行相应的兼容。

需要用到的查询sql如下:
show master status;显示当前binlog文件和位置
show BINARY logs;显示所有的binlog文件，监听binlog需要开通权限
show binlog events in ‘binlog.002969’ 显示该binlog所有记录的event
show VARIABLES like ‘binlog_format’; 显示binlog格式
show variables like 'server_id’显示serverId

mixed格式：

row格式：

3.mixed格式的binlog_format只能去监听QUERY事件，想要监听某张表的话也只能对这个事件中的sql做相应的解析再做处理。

public class BinlogListener implements CommandLineRunner {

    private static final Logger logger = LoggerFactory.getLogger(BinlogListener.class);

    @Resource
    private BinlogConfig config;

    private ThreadPoolExecutor threadPool = new ThreadPoolExecutor(1, 1, 0, TimeUnit.MINUTES,
            new ArrayBlockingQueue<>(1), new ThreadFactoryImpl("BinlogListenerThread_"), new ThreadPoolExecutor.CallerRunsPolicy());

    @Override
    public void run(String... args) throws Exception {
        // 起一个新的线程与主线程隔离,防止run失败导致项目无法启动
        threadPool.submit(()->{
            List<String> tables = Arrays.asList(config.getTable().split(","));
            BinaryLogClient client = new BinaryLogClient(config.getHost(),config.getPort(),config.getUsername(),config.getPassword());
            EventDeserializer eventDeserializer = new EventDeserializer();
            client.setEventDeserializer(eventDeserializer);
            client.setServerId(config.getServerId());
            client.registerEventListener(event -> {
                EventHeader header = event.getHeader();
                EventType eventType = header.getEventType();
                // 监听数据表变动,这里由于线上binlog_format=mixed所有的数据更新事件都是QUERY
                if(EventType.QUERY == eventType){
                    QueryEventData data = event.getData();
                    String sql = data.getSql();
                    String tableName = getTableName(sql);
                    if(tables.contains(tableName)){
                        // 业务处理
                    }
                }
            });
            try {
                client.connect();
            } catch (IOException e) {
            }
        });
    }

    private String getTableName(String sql){
        if(sql.startsWith("insert") || sql.startsWith("INSERT")){
            String result = ReUtil.getGroup0("(?<=INSERT INTO |insert into )(.*?)(?= \\(|\\()",sql);
            if(StringUtils.isEmpty(result)){
                return result;
            }
            result = result.replace("'","").replace("`",""); 
            if(result.contains(".")){
                return result.split("\\.")[1];
            }else {
                return result;
            }
        }
        if(sql.startsWith("update") || sql.startsWith("UPDATE")){
            String result = ReUtil.getGroup0("(?<=UPDATE |update )(.*?)(?= |\\n)",sql);
            if(StringUtils.isEmpty(result)){
                return result;
            }
            result = result.replace("'","").replace("`","");
            if(result.contains(".")){
                return result.split("\\.")[1];
            }else {
                return result;
            }
        }
        if(sql.startsWith("delete") || sql.startsWith("DELETE")){
            String result = ReUtil.getGroup0("(?<=DELETE from |delete from )(.*?)(?= |\\n)",sql);
            if(StringUtils.isEmpty(result)){
                return result;
            }
            result = result.replace("'","").replace("`","");
            if(result.contains(".")){
                return result.split("\\.")[1];
            }else {
                return result;
            }
        }
        return null;
    }
}