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信息。