mysql启用审计功能

【前言】其实mysql数据库本身并没有审计功能的，在实际的工作中往往会需要用到这方面的功能，公司最近就出现了这么一个让人很烦人的场景：pos系统有六台应用连接数据库，且数据库本身跟其他系统也有关联，最近业务反馈偶尔会出现系统日结翻倍的情况，开发找到了导致日结出现问题的SQL语句，但是不能确认是从哪台应用发起产生的，这时候感觉很无力，所以找到了数据库管理员；

 

【方法】业务需要找到哪台应用发出的SQL语句，通过查找发现可以通过init-connect和binlog的方法进行mysql的审计；

思路如下：

【1】mysql binlog记录了所有对数据库实际执行的sql语句，及其执行时间和connection_id；

【2】init-connect记录connection_id对应的详细用户信息。

【3】找到binlog的语句后，根据connection_id便可找到对应的用户连接信息

 

【操作步骤】

【1】修改参数文件，添加设置：init-connect='insert into accesslog.accesslog values(connection_id(),now(),user(),current_user());'

【2】创建存放信息的数据库

create database logDB;

【3】创建存放信息的表

CREATE TABLE logDB.log (`id` int(11) primary key auto_increment, `time` timestamp, `localname` varchar(30), `matchname` varchar(30))

【4】所有需要审计的用户进行检查，不能具有Super用户权限

【5】重启数据库

 

【结合binlog进行排查的步骤】

【1】查找时间范围内修改过zdyjb_day表的SQL语句：

mysqlbinlog --start-datetime="2015-8-25 09:00:00" --stop-datetime="2015-9-1 09:00:00" mysql-bin.002320| grep 'zdyjb_day' -B 5  > ab.log

 

【2】查找这个时间范围内执行UPDATE的语句

BEGIN /*!*/; # at 33871395 #150901  8:37:54 server id 1  end_log_pos 33871572     Query    thread_id=99127    exec_time=0    error_code=0 SET TIMESTAMP=1441067874/*!*/; UPDATE zdyjb_day SET zbje='6000',xgrq='1441067874' --

 

【3】根据thread_id查找用户的信息

mysql> select * from logDB.log where id=436 ; +-----+---------------------+---------------------+-----------+ | id  | time                | localname           | matchname | +-----+---------------------+---------------------+-----------+ | 436 | 2015-08-31 22:44:14 | ipos@192.168.0.15 | ipos@%    | +-----+---------------------+---------------------+-----------+ 1 row in set (0.00 sec)

通过日志可以看到执行的用户为ipos@%，从192.168.0.15这台登录的，一下子整个问题排查的范围少了很多，很快就能定位到问题的根源了；

 

【问题总结】

【1】启用init-connect会不会降低服务器的性能？

从原理上每个用户的连接成功并往数据库做变更的时候才会记录，所以整个插入其实并不会消耗很大的资源，占用的资源主要为IO和存储空间，而且也只是在出现问题期间启用；

 

【2】启用init-connect的数据记录全面吗

不会记录Super权限的用户，所以该记录是部分的；

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12679300/viewspace-1788525/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12679300/viewspace-1788525/