使用Canal + ClickHouse实时分析MySQL事务信息

使用Canal + ClickHouse实时分析MySQL事务信息

作为DBA, 有时候我们会希望能够了解线上核心库更具体的"样貌", 如:

• 这个库主要的DML类型是什么?
• 这个库的事务大小, 执行时间, 影响行数大概是什么样的?

以上信息也许没什么价值, 但大事务对复制的影响不用多说, 并且当我们希望升级当前主从架构到MGR/PXC等高可用方案的场景时以上信息就比较重要了(毕竟用数据说话更有力度).

大事务对MGR和PXC都是不友好的, 尤其是MGR(起码在5.7版本)严重时会导致整个集群hang死

在Galera 4.0中新特性Streaming Replication对大事务有了更好的支持

当然, 有人可能会说, 通过分析binlog就可以完成这样的工作, 最简单的方法写个shell脚本就可以, 比如这篇文章中介绍的方法Identifying Useful Info from MySQL Row-Based Binary Logs(这篇文章介绍的方法比较简单, 分析速度也较慢, 可以试试analysis_binlog). 当然还有很多其他工具, 比如infobin.

但个人认为上面的方法从某种角度来看还是比较麻烦, 而且现在ClickHouse越来越流行, 使用ClickHouse去完成这个工作也能帮助我们更好的学习ClickHouse

先看一下最终的成果

实现方法

1. canal + kafka

   部署canal, 订阅线上库的binlog, 写入到kafka 我这里没有使用flatMessage(canal.mq.flatMessage = false), 写入到kafka的消息是二进制的protobuf格式的, 当然也可以开启flatMessage, 那么写到kafka的消息就是json格式的.

2. 消费binlog, 持久化到clickhouse

   具体代码详见 https://github.com/Fanduzi/Use_clickhouse_2_analyze_mysql_binlog

3. clickhouse表

   基础表

   canal消费后直接写入

   -- 本地表
   CREATE TABLE mysql_monitor.broker_binlog_local
   (
       `schema` String COMMENT '数据库名',
       `table` String COMMENT '表名',
       `event_type` String COMMENT '语句类型',
       `is_ddl` UInt8 COMMENT 'DDL 1 else 0',
       `binlog_file` String COMMENT 'binlog文件名',
       `binlog_pos` String COMMENT 'binlog pos',
       `characterset` String COMMENT '字符集',
       `execute_time` DateTime COMMENT '执行的时间',
       `gtid` String COMMENT 'gtid',
       `single_statement_affected_rows` UInt32 COMMENT '此语句影响行数',
       `single_statement_size` String DEFAULT '0' COMMENT '此语句size,单位bytes',
       `ctime` DateTime DEFAULT now() COMMENT '写入clickhouse时间'
   )
   ENGINE = ReplicatedMergeTree('/clickhouse/mysql_monitor/tables/{layer}-{shard}/broker_binlog', '{replica}')
   PARTITION BY toDate(execute_time)
   ORDER BY (execute_time, gtid, table, schema)
   TTL execute_time + toIntervalMonth(30)
   SETTINGS index_granularity = 8192
   
   
   -- 分布式表
   CREATE TABLE mysql_monitor.broker_binlog
   (
       `schema` String COMMENT '数据库名',
       `table` String COMMENT '表名',
       `event_type` String COMMENT '语句类型',
       `is_ddl` UInt8 COMMENT 'DDL 1 else 0',
       `binlog_file` String COMMENT 'binlog文件名',
       `binlog_pos` String COMMENT 'binlog pos',
       `characterset` String COMMENT '字符集',
       `execute_time` DateTime COMMENT '执行的时间',
       `gtid` String COMMENT 'gtid',
       `single_statement_affected_rows` UInt32 COMMENT '此语句影响行数',
       `single_statement_size` String DEFAULT '0' COMMENT '此语句size,单位bytes',
       `ctime` DateTime DEFAULT now() COMMENT '写入clickhouse时间'
   )
   ENGINE = Distributed('ch_cluster_all', 'mysql_monitor', 'broker_binlog_local', rand())
   
   

   统计用表

   SummingMergeTree

   ClickHouse会将所有具有相同主键（或更准确地说, 具有相同sorting key）的行替换为包含具有数字数据类型的列的汇总值的一行

   每天binlog 各个event_type数量

   用于统计每日整体binlog event类型占比

-- 物化视图基表
CREATE TABLE mysql_monitor.broker_daily_binlog_event_count_local ON CLUSTER ch_cluster_all
(
    `day` Date,
    `event_type` String,
    `event_count` UInt64
)
ENGINE = ReplicatedSummingMergeTree('/clickhouse/mysql_monitor/tables/{layer}-{shard}/broker_daily_binlog_event_count', '{replica}')
PARTITION BY day
ORDER BY (day, event_type)
TTL day + toIntervalMonth(