MySQL 如何查看某用户对数据库做了增删改操作

binlog日志会记录增删改操作：create，drop，insert，delete，update等的操作。但是不会记录是什么用户操作的。

记录用户的上述操作：

使用init-connect + binlog的方法进行mysql的操作审计。

由于mysql binlog记录了所有对数据库长生实际修改的sql语句，及其执行时间，和connection_id但是却没有记录connection_id对应的详细用户信息。

因此本文将通过init-connect，在每次连接的初始化阶段，记录下这个连接的用户，和connection_id信息。

在后期审计进行行为追踪时，根据binlog记录的行为及对应的connection-id 结合 之前连接日志记录 进行分析，得出最后的结论。

 

 实现步骤：

1，创建审计的库和表

#建库表代码

create database db_monitor ;
use db_monitor ;
CREATE TABLE accesslog
( thread_id int(11) DEFAULT NULL,   #进程id
  log_time datetime default null,   #登录时间
  localname varchar(50) DEFAULT NULL, #登录名称，带详细ip
  matchname varchar(50) DEFAULT NULL,  #登录用户 
  key idx_log_time(log_time)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

 

2，配置init-connect参数

mysql> show variables like 'init_connect%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| init_connect  |       |
+---------------+-------+
1 row in set (0.00 sec

mysql> set global init_connect='insert into db_monitor.accesslog(thread_id,log_time,localname,matchname) values(connection_id(),now(),user(),current_user());';

 

 这个参数是可以动态调整的，也注意要加到配置文件my.cnf中，否则下次重启后就失效了；

在[mysqld]下添加以下设置：

init-connect='insert into db_monitor.accesslog(thread_id,log_time,localname,matchname) values(connection_id(),now(),user(),current_user());'

 

3、授予普通用户对accesslog表的insert权限

该参数只对普通用户生效，有super权限的都不会有作用。

如果是普通用户，增加了该功能后，一定需要授权：

grant insert on db_monitor.accesslog to user@'xx.xx.xx.%';

不授权的后果是，连接数据库会失败：

accesslog表没有insert权限的用户：

报错信息：

mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    7
Current database: *** NONE ***

ERROR 1184 (08S01): Aborted connection 7 to db: 'unconnected' user: 'user2' host: 'localhost' (init_connect command failed)

4、验证审计功能

假如某个用户已经对某个数据库进行了修改操作，或者删除了某条数据。

mysql> show master status; 先查看当前binlog的日志文件位置信息。

--假如我知道这个数据是在6月19号9点后被删除的：

 mysqlbinlog mysql-bin.000027 --start-datetime='2017-06-18 09:00:00' >check_user_operate.sql

 

# at 137585048
#170619 16:01:10 server id 6  end_log_pos 137585137 CRC32 0x0451e519    Query   thread_id=118181    exec_time=0 error_code=0
SET TIMESTAMP=1497859270/*!*/;
drop database abc
/*!*/;

 

找到   thread_id=118181

然后到数据库里面去查：

mysql> select * from monitor.accesslog where thread_id=118181;
+-----------+---------------------+-------------------+--------------+
| thread_id | time                | localname         | machine_name |
+-----------+---------------------+-------------------+--------------+
|    118181 | 2017-06-19 16:01:01 | monitor@localhost | monitor@%    |
+-----------+---------------------+-------------------+--------------+
1 row in set (0.03 sec)

 

可以看到 用户monitor 在  2017-06-19 16:01:01 操作的drop命令。

参考：http://blog.csdn.net/wlzjsj/article/details/52415378