用ELK分析每天4亿多条腾讯云MySQL审计日志(1)--解决过程
前言:
该文章将会介绍以下:
1,快速分析SQL日志的几种方法
2,使用mysql的全文索引快速分析少量SQL审计
3,准确快速分析4亿多条审计SQL日志(过程和最终解决方案)
公司核心库拆库拆表,对表和账号分析,大量系统连接,1主13个从库,几十个账号,后来都开通了全SQL审计,14个实例的审计日志条数每天超过4.5亿条+
要求
1,怎么分析出这几百个表是哪些账号在用?
2,这些表那些账号在读,哪些在写?
面临的问题:
1,如何能准确分析出表,账号和实例的关系
2,如何在14个实例中,能快速分析出结果
这件事情如何做,谁来做。弄来弄去,最后领导把这事硬丢给我,都不想接,但没办法硬着头皮做。
想想这些年都是做别人都不愿意做的事快速成长起来的,有时收获很多,现在想想的确是。
正如中国军事战略家金一南将军说的:做难事,必有所得!
这几个月做下来(从2020年10月到2021年2月),收获总结成以下文档:
方法:
1,方法一
手工下载csv文件,使用正则表达式,分析腾讯云导出这些审计日志csv文件 (分析很慢,而且不能把表对应具体的账号) (此方法不行)
2,使用JSqlParser这样对SQL语义分析,解析出SQL的表名
手工下载csv文件,测试了一下,的确可以,有少量SQL无法解析,但是有4.5亿条,即使一条花费0.01秒,也要4百万秒分析完(超过1千多小时), 即使100个线程并发,也需要10个多小时,而且只是一天的量,效率太低太慢,而且如果中间出错,重新分析时间更长,而且还有部分SQL无法解析 (此方法不行)
是否有其他方法,能快速分析出SQL语句的表和账号关系, 突然想起了,可以把csv导入到MySQL表中,在SQL字段建立全文索引,通过python来分析全文索引,获取表和账号的关系,测试了MySQL的表全文索引,的确很好用
如查询表: ol_order ,如: lt_ol_order 表这个查询就不会查询处理,他查的是词,很好用。
如查询表,insert的用户和表的分析,取一条sql样例:
SELECT count(*) num,user,'insert','ol_order',`sql`,date(`Timestamp`) dt FROM cc_0128 WHERE MATCH(`sql`) AGAINST('+ol_order +insert' IN BOOLEAN MODE) group by user
对于delete,select,update和replace都可以这样分析出来。写5条SQL,一起union all,就可以
分析完后,分析有些异常数据:比如这样的语句: insert into 表 select 1,2 这样,如果再上面的语句加上 : `sql` like 'insert%' ,几百个表,5条SQL,查询比以前变慢很多!
怎么办?分析效率和准确率如何平衡
后来看了看,异常数据有些,但不多,可以针对异常数据的分析,以前的分析就不用改。修复的语句:
SELECT count(*) num,user,'insert','ol_order',`sql`,date(`Timestamp`) FROM cc_0128 WHERE MATCH(`sql`) AGAINST('+ol_order +insert' IN BOOLEAN MODE) and `sql` like 'insert%' group by user
这样就可以分析出表,账号还用调用次数的关系。
还能分析出表的字段被那些账号update更新。但几百个表。有1万多个字段,如果Python遍历,太低效,有没有更好的办法?
根据分析的update和表的关系,其实几百个表有更新的只有2百多个,只需遍历这200多个表的分析就可以了,这样既解决效率问题。
其实这个方法也可用,但是每次要下载csv文件,导表,建全文索引,再分析,太耗时间,后面把程序自动下载csv,通过filebeat导到ELK,在ELK直接查询,效率更高。
虽然用MySQL的表全文索引的方法未最终使用,但在分析少量数据(百万条),的确还是比较方便,如下:
额外收获: 如某一时刻,MySQL的QPS高,如何快速查出QPS高的表,导出范围日志csv到表,简单查一下(一般百万级别) select tab,count(*) from (select id,substring_index(substring_index(LOWER(`sql`), 'where', 1),'from'
,-1) tab,`sql` from ol_sk1 ) t GROUP BY tab order by 2 desc 查询具体表的调用SQL: select `User`,count(*), `sql` from ol_sk1 where MATCH(`sql`) AGAINST ('ol_admin_config') GROUP BY `User`
, `sql` order by 2 desc
旧分析方法: