前言：
测试环境莫名其妙有几条重要数据被删除了，由于在binlog里面只看到是公用账号删除的，无法查询是那个谁在那个时间段登录的，就考虑怎么记录每一个mysql账号的登录信息，在mysql中，每个连接都会先执行init-connect，进行连接的初始化，我们可以在这里获取用户的登录名称和thread的id值。然后配合binlog，就可以追踪到每个操作语句的操作时间，操作人等。实现审计。
1，在mysql服务器db中建立单独的记录访问信息的库set names utf8;create databaseaccess_log;create table`access_log`(`id`int(11) not null auto_increment,`thread_id` int(11) default null, -- 线程id，这个值很重要`log_time`timestamp not null def ault current_timestamp on update current_timestamp, -- 登录时间`localname` varchar(30) default null, -- 登录名称`matchname` varchar(30) default null, -- 登录用户primarykey (`id`)) engine=innodbauto_increment=1 default charset=utf8 comment '录入用户登录信息';2，在配置文件中配置init-connect参数。登录时插入日志表。如果这个参数是个错误的sql语句，登录就会失败。
vim/usr/local/mysql/my.cnf init-connect='insertinto access_log.access_logvalues(null,connection_id(),now(),user(),current_user());'然后重启数据库3，创建普通用户，不能有super权限，而且用户必须有对access_log库的access_log表的insert权限，否则会登录失败。
给登录用户赋予insert权限，但是不赋予access_log的insert、select权限，grantinsert,delete,update,select on test.* to audit_user@'%' identified by'cacti_user1603';mysql> grantcreate,drop,alter,insert,delete,update,select on test.* to audit_user@'%'identified by 'cacti_user1603';query ok, 0 rowsaffected (0.00 sec) mysql> exit然后去用新的audit_user登录操作[root@db_server~]# /usr/local/mysql/bin/mysql -uaudit_user -p -s/usr/local/mysql/mysql.sock enter password:welcome to themysql monitor. commands end with ; or \g.
your mysql connectionid is 25 server version:5.6.12-log copyright (c)2000, 2013, oracle and/or its affiliates. all rights reserved.
oracle is aregistered trademark of oracle corporation and/or its affiliates. othernames may be trademarks of their respective owners.
type 'help;' or'\h' for help. type '\c' to clear the current input statement.
mysql> lect *from access_log.access_log;error 2006(hy000): mysql server has gone away no connection.trying to reconnect...
connection id: 26 current database:*** none ***error 1184(08s01): aborted connection 26 to db: 'unconnected' user: 'audit_user' host:'localhost' (init_connect command failed) mysql>看到报错信息 (init_connect command failed)，再去错误日志error log验证一下：tail -fn 5/usr/local/mysql/mysqld.log 2014-07-28 16:03:3123743 [warning] aborted connection 25 to db: 'unconnected' user: 'audit_user'host: 'localhost' (init_connect command failed) 2014-07-2816:03:31 23743 [warning] insert command denied to user ''@'localhost' for table'access_log'2014-07-2816:04:04 23743 [warning] aborted connection 26 to db: 'unconnected' user:'audit_user' host: 'localhost' (init_connect command failed) 2014-07-2816:04:04 23743 [warning] insert command denied to user ''@'localhost' for table'access_log'看到必须要有对access_log库的access_log表的insert权限才行。
4，赋予用户access_log的insert、select权限，然后重新赋予权限：grantselect,insert on access_log.* to audit_user@'%';mysql>mysql> grantselect,insert on access_log.* to audit_user@'%';query ok, 0 rowsaffected (0.00 sec) mysql> exit bye
再登录，报错如下：
[root@db_server~]# /usr/local/mysql/bin/mysql -uaudit_user -p -s/usr/local/mysql/mysql.sock enter password:error 1045(28000): access denied for user 'audit_user'@'localhost' (using password: yes)[root@db_server~]#
去查看error日志：
2014-07-2816:15:29 23743 [warning] insert command denied to user ''@'localhost' for table'access_log'2014-07-2816:15:41 23743 [warning] aborted connection 37 to db: 'unconnected' user:'audit_user' host: 'localhost' (init_connect command failed) 2014-07-2816:15:41 23743 [warning] insert command denied to user ''@'localhost' for table'access_log'2014-07-2816:15:50 23743 [warning] aborted connection 38 to db: 'unconnected' user:'audit_user' host: 'localhost' (init_connect command failed) 2014-07-2816:15:50 23743 [warning] insert command denied to user ''@'localhost' for table'access_log'需要用root用户登录进去，清空掉用户为''的用户记录。
mysql>select user,host,password from mysql.user;+----------------+-----------+-------------------------------------------+| user | host | password |+----------------+-----------+-------------------------------------------+| root | localhost | || root | db_server | || root | 127.0.0.1 | || root | ::1 | || | localhost | || | db_server | || cacti_user | % |*eb9e3195e443d577879101a35ef64a701b35f949 || cacti_user | 1 |*d5ff9b53a78232da13d3643965a5961449b387db || cacti_user | 2 | *d5ff9b53a78232da13d3643965a5961449b387db|| test_user | 192.% |*8a447777509932f0ed07adb033562027d95a0f17 || test_user | 1 |*8a447777509932f0ed07adb033562027d95a0f17 || weakpwd_user_1| 10.% | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 || weakpwd_user_2| 10.% | *b1461c9c68afa1129a5f968c343636192a084adb || weakpwd_user_3| 10.% | *dcb7df5ffc82c441503300fff165257bc551a598 || audit_user | % |*aeab1915b137fafde9b949d67a9a42ddb68dd8a2 |+----------------+-----------+-------------------------------------------+15 rows in set(0.00 sec) mysql> dropuser ''@'localhost';query ok, 0 rowsaffected (0.00 sec) mysql> dropuser ''@'db_server';query ok, 0 rowsaffected (0.00 sec) mysql>再用已经分配了access_log表的insert权限的audit_user登录mysql> select* from access_log.access_log;+----+-----------+---------------------+---------------------------+--------------+| id | thread_id| log_time | localname | matchname |+----+-----------+---------------------+---------------------------+--------------+| 4 | 41 | 2014-07-28 16:19:37 | audit_user@localhost | audit_user@% || 5 | 42 | 2014-07-28 16:20:32 | audit_user@localhost | audit_user@% || 6 | 45 | 2014-07-28 16:21:11 | audit_user@localhost | audit_user@% |+----+-----------+---------------------+---------------------------+--------------+6 rows in set(0.00 sec) mysql> showfull processlist;+----+------------+-----------+------+---------+------+-------+-----------------------+| id | user | host | db | command | time |state | info |+----+------------+-----------+------+---------+------+-------+-----------------------+| 45 | audit_user| localhost | null | query | 0 | init | show fullprocesslist |+----+------------+-----------+------+---------+------+-------+-----------------------+1 row in set(0.00 sec) mysql>5，再用另外一个用户登录建表，录入测试数据。
建表录入数据记录mysql> usetest;database changed mysql> createtable t1 select 1 as a, 'wa' as b;query ok, 1 rowaffected (0.01 sec) records: 1 duplicates: 0 warnings: 0查看跟踪用户行为记录。
mysql> select* from access_log.access_log;+----+-----------+---------------------+---------------------------+--------------+| id | thread_id| log_time | localname | matchname |+----+-----------+---------------------+---------------------------+--------------+| 4 | 41 | 2014-07-28 16:19:37 | audit_user@localhost | audit_user@% || 5 | 42 | 2014-07-28 16:20:32 | audit_user@localhost | audit_user@% || 6 | 45 | 2014-07-28 16:21:11 | audit_user@localhost | audit_user@% || 7 | 48 | 2014-07-28 16:30:42 | audit_user@192.168.3.62 | audit_user@% || 8 | 50 | 2014-07-28 16:46:11 | audit_user@192.168.3.62 | audit_user@% |+----+-----------+---------------------+---------------------------+--------------+8 rows in set(0.00 sec)去mysqldb服务器上查看binlog 内容，解析完后，没有insert语句，怎么回事，去看my.cnf#binlog-ignore-db=mysql # no sync databases#binlog-ignore-db=test # no sync databases#binlog-ignore-db=information_schema # no sync databases#binlog-ignore-db=performance_schema原来是对test库有binlog过滤设置，全部注释掉。重启mysql库，重新来一遍，可以在看到binlog在mysql客户端上重新执行。
mysql> usetest;database changed mysql> insertinto test.t1 select 5,'t5';query ok, 1 rowaffected (0.00 sec) records: 1 duplicates: 0 warnings: 0 mysql> select* from access_log.access_log;+----+-----------+---------------------+---------------------------+--------------+| id | thread_id| log_time | localname | matchname |+----+-----------+---------------------+---------------------------+--------------+| 1 | 17 | 2014-07-28 15:41:04 | cacti_user@192.168.171.71| cacti_user@% || 2 | 18 | 2014-07-28 15:41:05 | cacti_user@192.168.171.71| cacti_user@% || 3 | 19 | 2014-07-28 15:41:05 | cacti_user@192.168.171.71| cacti_user@% || 4 | 41 | 2014-07-28 16:19:37 | audit_user@localhost | audit_user@% || 5 | 42 | 2014-07-28 16:20:32 | audit_user@localhost | audit_user@% || 6 | 45 | 2014-07-28 16:21:11 | audit_user@localhost | audit_user@% || 7 | 48 | 2014-07-28 16:30:42 | audit_user@192.168.3.62 | audit_user@% || 8 | 50 | 2014-07-28 16:46:11 | audit_user@192.168.3.62 | audit_user@% || 9 | 56 | 2014-07-28 19:32:12 | audit_user@192.168.1.12 | audit_user@% || 10 | 1 | 2014-07-28 20:02:56 |audit_user@192.168.3.62 | audit_user@% |+----+-----------+---------------------+---------------------------+--------------+10 rows in set(0.00 sec)看到thread_id为1 6，如何查看何跟踪用户行为记录。
去mysql数据库服务器上查看binlog，应该thread_id=1的binlog记录。
[root@db_serverbinlog]# /usr/local/mysql/bin/mysqlbinlog --base64-output=decode-rows mysql-bin.000018 -v>3.log[root@db_serverbinlog]# vim 3.log# at 1103#140728 20:12:48server id 72 end_log_pos 1175 crc32 0xa323c00e query thread_id=1 exec_time=0 error_code=0 settimestamp=1406549568/*!*/;begin/*!*/;# at 1175#140728 20:12:48server id 72 end_log_pos 1229 crc32 0xbb8ca914 table_map: `test`.`t1` mapped to number 72# at 1229#140728 20:12:48server id 72 end_log_pos 1272 crc32 0x8eed1450 write_rows: table id 72 flags: stmt_end_f### insert into `test`.`t1`### set### @1=5### @2='t5'# at 1272#140728 20:12:48server id 72 end_log_pos 1303 crc32 0x72b26336 xid = 14 commit/*!*/;看到thread_id=1，然后，就可以根据thread_id=1来判断执行这条insert命令的来源，还可以在mysql服务器上执行show full processlist;来得到mysql客户端的请求端口，mysql> showfull processlist;+----+------------+-------------------+------+---------+------+-------+-----------------------+| id | user | host |db | command | time | state | info |+----+------------+-------------------+------+---------+------+-------+-----------------------+| 1 |audit_user | 192.168.3.62:44657 | test | sleep | 162 | | null || 3 | root | localhost | null | query | 0 | init | show full processlist |+----+------------+-------------------+------+---------+------+-------+-----------------------+2 rows in set(0.00 sec) mysql>看到id为1的线程，端口是44657。
我们切换回mysql客户端，去查看端口是44657的是什么进程，如下所示：[tim@db_client~]$ netstat -antlp |grep 44657 (not allprocesses could be identified, non-owned process info will not beshown, you would have to be root to see it all.) tcp 0 0 192.168.3.62:44657 192.168.1.12:3307 established 6335/mysql[tim@db_client~]$获取到该进程的pid，再通过ps -eaf得到该进程所执行的命令，如下所示：[tim@db_client~]$ ps -eaf|grep 6335 tim 633525497 0 19:59 pts/1 00:00:00 mysql -uaudit_user -p -h192.168.1.12 -p3307 tim 6993 6906 0 20:16 pts/2 00:00:00 grep 6335[tim@db_client ~]$最后查到是通过mysql客户端登陆连接的。加入这个6335是某个web工程的，那么，也可以根据ps-eaf命令查询得到web工程的进程信息。
来自：http://blog.itpub.net/26230597/viewspace-1241240386/参考文章地址：http://blog.chinaunix.net/uid-24086995-id-168445.html