全量SQL的采集和利用

原创

保持热爱奔赴山海

发布于 2024-04-26 20:20:14

790

发布于 2024-04-26 20:20:14

文章被收录于专栏：饮水机管理员饮水机管理员

最近看到Dmall冯光普老师的关于TopSQL的分享，于是参考他的方案在生产做了个低配版的实现（冯老师的方案中需要较强的前端编码能力，我这里改用grafana代替）。

总体功能分2部分介绍：

part1、通过抓包采集mysql的会话明细

part2、通过伪装从库拉取binlog获取变更的事件

本文只介绍part1部分，part2之前有写过类似的方案稍作改动即可。

抓包这里使用的工具是sniffer-agent，抓包工具还可以选择packetbeat。二者的大体功能类似，都是pcap方式抓包。需要注意限制下采集器的资源占用情况（可以使用cgroup限制）

sniffer-agent的使用

https://github.com/zr-hebo/sniffer-agent

下载最新版解压即可，写笔记的时候最新版是1.7

默认是直接采集输出到控制台

$ ./sniffer-agent -interface ens33

然后启动navicat客户端，执行一些查询或者修改操作

sinffer-agent控制台看到的日志效果如下

{"sip":"192.168.31.181","sport":3306,"cpr":1,"bt":1711339402115,"cip":"192.168.31.1","cport":2303,"user":"dts","db":"db1234","sql":"SHOW STATUS","cms":2}
{"sip":"192.168.31.181","sport":3306,"cpr":1,"bt":1711339402125,"cip":"192.168.31.1","cport":2303,"user":"dts","db":"db1234","sql":"SHOW STATUS","cms":2}
{"sip":"192.168.31.181","sport":3306,"cpr":1,"bt":1711339402135,"cip":"192.168.31.1","cport":2303,"user":"dts","db":"db1234","sql":"SELECT * FROM `sbtest3` limit 100","cms":0}
{"sip":"192.168.31.181","sport":3306,"cpr":1,"bt":1711339402137,"cip":"192.168.31.1","cport":2306,"user":"dts","db":"db1234","sql":"SELECT * FROM `db1234`.`sbtest3` LIMIT 0","cms":0}
{"sip":"192.168.31.181","sport":3306,"cpr":1,"bt":1711339402138,"cip":"192.168.31.1","cport":2306,"user":"dts","db":"db1234","sql":"SHOW COLUMNS FROM `db1234`.`sbtest3`","cms":1}
{"sip":"192.168.31.181","sport":3306,"cpr":1,"bt":1711339402140,"cip":"192.168.31.1","cport":2303,"user":"dts","db":"db1234","sql":"SHOW STATUS","cms":4}
{"sip":"192.168.31.181","sport":3306,"cpr":1,"bt":1711339402149,"cip":"192.168.31.1","cport":2303,"user":"dts","db":"db1234","sql":"SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID","cms":1}
{"sip":"192.168.31.181","sport":3306,"cpr":1,"bt":1711339402151,"cip":"192.168.31.1","cport":2303,"user":"dts","db":"db1234","sql":"SELECT STATE AS `Status`, ROUND(SUM(DURATION),7) AS `Duration`, CONCAT(ROUND(SUM(DURATION)/0.001501*100,3), '') AS `Percentage` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=3 GROUP BY SEQ, STATE ORDER BY SEQ","cms":0}

其中cip代表客户端ip，cport代表客户端port(客户端ip：port组成session标识)，sip代表server ip，sport代表server port，user代表查询用户，db代表当前连接的库名，sql代表查询语句，cpr代表抓包率，bt代表查询开始时间戳，cms代表查询消耗的时间，单位是毫秒

注意，如果在启动sniffer-agent的时候，已经有MySQL会话在连接，则抓不到相关的user和db信息，如下：

{"sip":"192.168.31.181","sport":3306,"cpr":1,"bt":1711343261336,"cip":"192.168.31.1","cport":2796,"user":null,"db":null,"sql":"SHOW STATUSNG = 1","cms":0}
{"sip":"192.168.31.181","sport":3306,"cpr":1,"bt":1711343261336,"cip":"192.168.31.1","cport":2796,"user":null,"db":null,"sql":"SHOW STATUS","cms":2}

如果要解决这个问题，可以在启动的时候加上如下的几个参数（账号密码是为了去数据库连接里面找到会话的用户名）

./sniffer-agent -interface ens33 --log_level=info --strict_mode=true --admin_user=dts --admin_passwd=dts

但是！！！实际测试加了这个参数后，会导致在sniffer-agent启动之前就连接的会话的内容抓取不到。 ?因此是否需要使用这种方式各位自己评估（微服务场景下，根据实例名和库名通常也能定位到是哪个service或者业务线了）。

注意：

1 mysql8.0默认连接加密连接，这种情况目前还抓不到。如果设置useSSL=false的话，正常的明文 SQL应该能抓到。
2 高版本的 mysql client连接默认添加了 SSL模式，可以尝试 mysql client使用 --ssl-mode=DISABLED连接，然后看能不能抓到SQL。
3 对于prepare只保留语句，没有保留变量，这个涉及到协议的解析，作者表示难实，因此现暂不支持

官方文档上，采集到kafka的写法：

./sniffer-agent -interface ens192 -port 3306 --export_type=kafka -kafka-server 192.168.3.11:9092 -kafka-group-id dba-test-fullsql --kafka-async-topic=non_ddl_sql_collector --kafka-sync-topic=ddl_sql_collector

kafka中的消息类似：

ddl_sql_collector内容如下：
{"sip":"192.168.3.11","sport":3306,"cpr":1,"bt":1714023432202,"cip":"192.168.201.1","cport":6315,"user":"root","db":"dbtest","sql":"create database test","cms":0}
{"sip":"192.168.3.11","sport":3306,"cpr":1,"bt":1714023444360,"cip":"192.168.201.1","cport":6315,"user":"root","db":"dbtest","sql":"create table test.t1(a int ,b int)","cms":3}

non_ddl_sql_collector  内容如下：
{"sip":"192.168.3.11","sport":3306,"cpr":1,"bt":1714023485909,"cip":"192.168.201.1","cport":6315,"user":"root","db":"dbtest","sql":"SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID","cms":1}
{"sip":"192.168.3.11","sport":3306,"cpr":1,"bt":1714023485916,"cip":"192.168.201.1","cport":6315,"user":"root","db":"dbtest","sql":"SELECT STATE AS `Status`, ROUND(SUM(DURATION),7) AS `Duration`, CONCAT(ROUND(SUM(DURATION)/0.083307*100,3), '') AS `Percentage` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=18 GROUP BY SEQ, STATE ORDER BY SEQ","cms":0}

sql归一化/指纹提取

可以使用的pt-fingerprint或者小米开源的soar来实现目的，
个人测试下来还是pt-fingerprint的性能更好一些。

Python数据清洗

数据清洗的代码很简单，就是消费kafka里面的数据，然后简单处理下：
1、把bt时间戳的转为日期时间类型
2、过滤掉不需要的记录（我在生产测试发现sniffer抓到了本机对外部MySQL发起的请求报文，因此做了次过滤）
3、其它，可以增加些元数据标识（例如加个字段，标注所属的集群名等）

clickhouse_sinker消费数据写入到ClickHouse

这块很简单，参考官方文档即可。

Grafana看板

看板需要根据自己的情况绘制即可，至少得有：趋势图，明细图，TopN慢SQL明细表，日均汇总统计图等。

总体而言，画图难度不高，是个体力活。

其它

其实ELK套件在几年前也具备这块能力（packetbeat可以采集多种类型的报文，包括MySQL、MongoDB、PG等），并且是开箱即用的。

如果觉得Elasticsearch太烧钱的话，也可以使用packetbeat-->kafka ，最后处理好的数据存到ClickHouse中。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

#packetbeat

登录后参与评论

0 条评论

热度