在MySQL中使用init-connect与binlog来实现用户操作追踪记录
分类： MySQL 前言：
测试环境莫名其妙有几条重要数据被删除了，由于在binlog里面只看到是公用账号删除的，无法查询是那个谁在那个时间段登录的，就考虑怎么记录每一个MYSQL账号的登录信息，在MYSQL中，每个连接都会先执行init-connect，进行连接的初始化，我们可以在这里获取用户的登录名称和thread的ID值。然后配合binlog，就可以追踪到每个操作语句的操作时间，操作人以及客户端的连接进程信息等。实现审计。 ，在mysql服务器db中建立单独的记录访问信息的库
set names utf8;
create database access_log;
CREATE TABLE `access_log`
(
`id` int() NOT NULL AUTO_INCREMENT,
`thread_id` int() DEFAULT NULL, -- 线程ID，这个值很重要
`log_time` timestamp NOT NULL DEF AULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 登录时间
`localname` varchar() DEFAULT NULL, -- 登录名称
`matchname` varchar() DEFAULT NULL, -- 登录用户
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8 comment '录入用户登录信息'; ，在配置文件中配置init-connect参数。登录时插入日志表。如果这个参数是个错误的SQL语句，登录就会失败。
vim /usr/local/mysql/my.cnf
init-connect='INSERT INTO access_log.access_log VALUES(NULL,CONNECTION_ID(),NOW(),USER(),CURRENT_USER());'
然后重启数据库 ，创建普通用户，不能有super权限，而且用户必须有对access_log库的access_log表的insert权限，否则会登录失败。
给登录用户赋予insert权限，但是不赋予access_log的insert、select权限，
GRANT INSERT,DELETE,UPDATE,SELECT ON test.* TO audit_user@'%' IDENTIFIED BY 'cacti_user1603';
mysql> GRANT CREATE,DROP,ALTER,INSERT,DELETE,UPDATE,SELECT ON test.* TO audit_user@'%' IDENTIFIED BY 'cacti_user1603';
Query OK, rows affected (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 the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.6.-log Copyright (c) , , Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names 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 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:
Current database: *** NONE *** ERROR (08S01): Aborted connection to db: 'unconnected' user: 'audit_user' host: 'localhost' (init_connect command failed)
mysql>
看到报错信息 (init_connect command failed)，再去错误日志error log验证一下：
tail -fn /usr/local/mysql/mysqld.log
-- :: [Warning] Aborted connection to db: 'unconnected' user: 'audit_user' host: 'localhost' (init_connect command failed)
-- :: [Warning] INSERT command denied to user ''@'localhost' for table 'access_log'
-- :: [Warning] Aborted connection to db: 'unconnected' user: 'audit_user' host: 'localhost' (init_connect command failed)
-- :: [Warning] INSERT command denied to user ''@'localhost' for table 'access_log'
看到必须要有对access_log库的access_log表的insert权限才行。 ，赋予用户access_log的insert、select权限，然后重新赋予权限：
GRANT SELECT,INSERT ON access_log.* TO audit_user@'%';
mysql>
mysql> GRANT SELECT,INSERT ON access_log.* TO audit_user@'%';
Query OK, rows affected (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 (): Access denied for user 'audit_user'@'localhost' (using password: YES)
[root@db_server ~]# 去查看error日志：
-- :: [Warning] INSERT command denied to user ''@'localhost' for table 'access_log'
-- :: [Warning] Aborted connection to db: 'unconnected' user: 'audit_user' host: 'localhost' (init_connect command failed)
-- :: [Warning] INSERT command denied to user ''@'localhost' for table 'access_log'
-- :: [Warning] Aborted connection to db: 'unconnected' user: 'audit_user' host: 'localhost' (init_connect command failed)
-- :: [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 | :: | |
| | localhost | |
| | db_server | |
| cacti_user | % | *EB9E3195E443D577879101A35EF64A701B35F949 |
| cacti_user | | *D5FF9B53A78232DA13D3643965A5961449B387DB |
| cacti_user | | *D5FF9B53A78232DA13D3643965A5961449B387DB |
| test_user | .% | *8A447777509932F0ED07ADB033562027D95A0F17 |
| test_user | | *8A447777509932F0ED07ADB033562027D95A0F17 |
| weakpwd_user_1 | .% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| weakpwd_user_2 | .% | *B1461C9C68AFA1129A5F968C343636192A084ADB |
| weakpwd_user_3 | .% | *DCB7DF5FFC82C441503300FFF165257BC551A598 |
| audit_user | % | *AEAB1915B137FAFDE9B949D67A9A42DDB68DD8A2 |
+----------------+-----------+-------------------------------------------+
rows in set (0.00 sec) mysql> drop user ''@'localhost';
Query OK, rows affected (0.00 sec) mysql> drop user ''@'db_server';
Query OK, rows affected (0.00 sec) mysql> 再用已经分配了access_log表的Insert权限的audit_user登录
mysql> select * from access_log.access_log;
+----+-----------+---------------------+---------------------------+--------------+
| id | thread_id | log_time | localname | matchname |
+----+-----------+---------------------+---------------------------+--------------+
| | | -- :: | audit_user@localhost | audit_user@% |
| | | -- :: | audit_user@localhost | audit_user@% |
| | | -- :: | audit_user@localhost | audit_user@% |
+----+-----------+---------------------+---------------------------+--------------+
rows in set (0.00 sec) mysql> show full processlist;
+----+------------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------------+-----------+------+---------+------+-------+-----------------------+
| | audit_user | localhost | NULL | Query | | init | show full processlist |
+----+------------+-----------+------+---------+------+-------+-----------------------+
row in set (0.00 sec) mysql> ，再用另外一个用户登录建表，录入测试数据。
建表录入数据记录
mysql> use test;
Database changed
mysql> create table t1 select as a, 'wa' as b;
Query OK, row affected (0.01 sec)
Records: Duplicates: Warnings: 查看跟踪用户行为记录。
mysql> select * from access_log.access_log;
+----+-----------+---------------------+---------------------------+--------------+
| id | thread_id | log_time | localname | matchname |
+----+-----------+---------------------+---------------------------+--------------+
| | | -- :: | audit_user@localhost | audit_user@% |
| | | -- :: | audit_user@localhost | audit_user@% |
| | | -- :: | audit_user@localhost | audit_user@% |
| | | -- :: | audit_user@192.168.3.62 | audit_user@% |
| | | -- :: | audit_user@192.168.3.62 | audit_user@% |
+----+-----------+---------------------+---------------------------+--------------+
rows in set (0.00 sec) 去mysql db服务器上查看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> use test;
Database changed
mysql> insert into test.t1 select ,'t5';
Query OK, row affected (0.00 sec)
Records: Duplicates: Warnings: mysql> select * from access_log.access_log;
+----+-----------+---------------------+---------------------------+--------------+
| id | thread_id | log_time | localname | matchname |
+----+-----------+---------------------+---------------------------+--------------+
| | | -- :: | cacti_user@192.168.171.71 | cacti_user@% |
| | | -- :: | cacti_user@192.168.171.71 | cacti_user@% |
| | | -- :: | cacti_user@192.168.171.71 | cacti_user@% |
| | | -- :: | audit_user@localhost | audit_user@% |
| | | -- :: | audit_user@localhost | audit_user@% |
| | | -- :: | audit_user@localhost | audit_user@% |
| | | -- :: | audit_user@192.168.3.62 | audit_user@% |
| | | -- :: | audit_user@192.168.3.62 | audit_user@% |
| | | -- :: | audit_user@192.168.1.12 | audit_user@% |
| | | -- :: | audit_user@192.168.3.62 | audit_user@% |
+----+-----------+---------------------+---------------------------+--------------+
rows in set (0.00 sec)
看到thread_id为1 ，如何查看何跟踪用户行为记录。
去mysql数据库服务器上查看binlog，应该thread_id=1的binlog记录。
[root@db_server binlog]# /usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS mysql-bin. -v>.log
[root@db_server binlog]# vim .log
# at
# :: server id end_log_pos CRC32 0xa323c00e Query thread_id= exec_time= error_code=
SET TIMESTAMP=/*!*/;
BEGIN
/*!*/;
# at
# :: server id end_log_pos CRC32 0xbb8ca914 Table_map: `access_log`.`t1` mapped to number
# at
# :: server id end_log_pos CRC32 0x8eed1450 Write_rows: table id flags: STMT_END_F
### INSERT INTO `access_log`.`t1`
### SET
### @=
### @='w0'
# at
# :: server id end_log_pos CRC32 0x72b26336 Xid =
COMMIT/*!*/; 看到thread_id=，然后，就可以根据thread_id=1来判断执行这条insert命令的来源，还可以在mysql服务器上执行show full processlist;来得到MySQL客户端的请求端口，
mysql> show full processlist;
+----+------------+-------------------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------------+-------------------+------+---------+------+-------+-----------------------+
| | audit_user | 192.168.3.62: | test | Sleep | | | NULL |
| | root | localhost | NULL | Query | | init | show full processlist |
+----+------------+-------------------+------+---------+------+-------+-----------------------+
rows in set (0.00 sec) mysql>
看到Id为1的线程，端口是44657。 我们切换回mysql客户端，去查看端口是44657的是什么进程，如下所示：
[tim@db_client ~]$ netstat -antlp |grep
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 192.168.3.62: 192.168.1.12: ESTABLISHED /mysql
[tim@db_client ~]$ 获取到该进程的PID ，再通过ps -eaf得到该进程所执行的命令，如下所示：
[tim@db_client ~]$ ps -eaf|grep
tim : pts/ :: mysql -uaudit_user -p -h 192.168.1.12 -P3307
tim : pts/ :: grep
[tim@db_client ~]$ 最后查到是通过mysql客户端登陆连接的。加入这个6335是某个web工程的，那么，也可以根据ps -eaf命令查询得到web工程的进程信息。 参考文章地址：http://blog.chinaunix.net/uid-24086995-id-168445.html