用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

   旧分析方法:

       SQL审计日志手工下载导入--MySQL全文索引--Python分析全文索引(最快遍历940张+表分析6000多万条需3个多小时,不能跨天,手工导出导入耗费大量人力时间)
    新分析方法:
       SQL审计日志Python程序化下载--filebeat--Kafka--ELK(14个实例每天4.5亿+条SQL执行日志) 
    新系统和ELK带来巨大便利:
        1,能全实例(14个实例),跨天长时间复杂查询分析(便利性和准确性远超腾讯云提供的审计日志查询)
        2,  新实例审计日志加到ELK,只需表中配置一条记录
        3,能快速准确的分析出表在online全实例账号调用情况,请求数量等
        4,审计日志导ELK程序系统化和配置化,无需人工介入,节省大量人力时间.
        ......
    额外带来好处便利
       1,更高效便利的线上bug数据分析
       2,数据安全审查的快速跟踪和分析
       ......
  
    相关文档:
 
posted @ 2021-03-04 16:45  zping  阅读(570)  评论(0编辑  收藏  举报