MySQL记录用户操作日志
有时,我们想追踪某个数据库操作记录,如想找出是谁操作了某个表(比如谁将字段名改了)。
二进制日志记录了操作记录,线程号等信息,但是却没有记录用户信息,因此需要结合init-connect来实现追踪。
init-connect,在每次连接的初始化阶段,记录下这个连接的用户,和connection_id信息。
1.创建监控连接信息的数据库及表
创建数据库:
create database dba;
创建表:
create table accesslog(`thread_id` int primary key auto_increment, `time` timestamp, `localname` varchar(40), `machine_name` varchar(40));
thread_id : 记录mysql 线程ID
time:记录操作时间
localname:记录操作远程IP
machine_name:记录用户
2. 设置变量init_connect
查看init_connect
mysql> show variables like 'init%';
+---------------+-------+
| Variable_name | Value | |
+---------------+-------+
| init_connect | |
| init_file | |
| init_slave | |
+---------------+-------+
3 rows in set (0.00 sec)
配置变量
set global init_connect='insert into dba.accesslog(thread_id,time,localname,machine_name) values(connection_id(),now(),user(),current_user());';
再次查看init_connect
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| init_connect | insert into dba.accesslog(thread_id,time,localname,machine_name) values(connection_id(),now(),user(),current_user()); |
| init_file | |
| init_slave | |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
3.为用户赋记录日志权限
mysql> grant select,insert,update on dba.accesslog to dba@'%';
4.创建原始数据
mysql> create database log_test;
mysql> create table test_table(uid INT,text VARCHAR(10));
5.赋予dba账户操作log_test库的操作权限
mysql> grant select,update,insert,delete on log_test.* to dba@'%' identified by 'dbapasswd' ;
如果想立即看到结果使用
flush privileges ;
6.用dba账户登陆MySQL客户端进行操作
[root@centos6 ~]# mysql -u dba -p -h 192.168.10.145
mysql> use log_test;
mysql> show tables ;
+--------------------+
| Tables_in_log_test |
+--------------------+
| test_table |
| tutorials_tbl |
+--------------------+
2 rows in set (0.00 sec)
mysql> insert into test_table (uid,text) VALUES (1,11);
mysql> insert into test_table (uid,text) VALUES (2,11111);
7.查看操作日志
查看日志记录所在的二进制文件
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 3309 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
查看所操作的语句的线程号为10
[root@centos6 mysql]# mysqlbinlog mysql-bin.000001
......
# at 3078
#181029 23:12:29 server id 1 end_log_pos 3192 Query thread_id=10 exec_time=0 error_code=0
use `log_test`/*!*/;
SET TIMESTAMP=1540825949/*!*/;
insert into test_table (uid,text) VALUES (1,11)
/*!*/;
# at 3192
#181029 23:12:40 server id 1 end_log_pos 3309 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1540825960/*!*/;
insert into test_table (uid,text) VALUES (2,11111)
/*!*/;
......
查看当时时间点(181029 23:12:29)线程号为10 所登录的账户和ip地址
mysql> select * from dba.accesslog where thread_id=10;
+-----------+---------------------+--------------------+--------------+
| thread_id | time | localname | machine_name |
+-----------+---------------------+--------------------+--------------+
| 10 | 2018-10-29 23:08:36 | [email protected] | dba@% |
+-----------+---------------------+--------------------+--------------+
1 row in set (0.00 sec)
此时即可锁定此用户ip为192.168.10.130。
注意: 对于所有的普通级别的用户,必须全部都要对日志表具有读写权限, 否则将导致,没有权限的用户无法使用数据库。
init_connect 不会记录有超级管理员权限的用户连接信息 (原因:当init_connect设置有误时,超级管理员可进行修改)
因此,对于一般的用户,不能赋予all privileges权限。
--如果想查看所有的增删改查记录,在general log(需要先开启)里查询即可。里面记录了连接的用户和IP信息。