mysql查看连接用户 mysql查看用户连接日志

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