MySQL记录用户操作日志

最新推荐文章于 2024-09-20 10:31:45 发布

草莓甜甜圈

最新推荐文章于 2024-09-20 10:31:45 发布

阅读量5.2k

点赞数 4

分类专栏： MySQL 文章标签： MySQL 二进制日志

本文链接：https://blog.csdn.net/qq_39626154/article/details/83505428

版权

MySQL 专栏收录该内容

19 篇文章 3 订阅

订阅专栏

有时，我们想追踪某个数据库操作记录，如想找出是谁操作了某个表（比如谁将字段名改了）。

二进制日志记录了操作记录，线程号等信息，但是却没有记录用户信息，因此需要结合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 | dba@192.168.10.130 | dba@%        |
+-----------+---------------------+--------------------+--------------+
1 row in set (0.00 sec)

此时即可锁定此用户ip为192.168.10.130。

注意： 对于所有的普通级别的用户，必须全部都要对日志表具有读写权限， 否则将导致，没有权限的用户无法使用数据库。
init_connect 不会记录有超级管理员权限的用户连接信息 (原因:当init_connect设置有误时,超级管理员可进行修改)
因此，对于一般的用户，不能赋予all privileges权限。
--如果想查看所有的增删改查记录，在general log（需要先开启）里查询即可。里面记录了连接的用户和IP信息。