Mysql记录用户操作日志

我们想追踪某个数据库操作记录，如想找出是谁操作了某个表（比如谁将字段名改了）。
二进制日志记录了操作记录，线程号等信息，但是却没有记录用户信息，因此需要结合init-connect来实现追踪。
init-connect，在每次连接的初始化阶段，记录下这个连接的用户，和connection_id信息。
操作如下：
  1 1:建监控连接信息的表  
  2 use dba;  
  3 create table accesslog(`thread_id` int primary key auto_increment, `time` timestamp, `localname` varchar(40), `machine_name` varchar(40));  
  4   
  5   
  6 2：设置变量init_connect  
  7    
  8 mysql> show variables like 'init%';  
  9 +---------------+-------+  
 10 | Variable_name | Value |  
 11 +---------------+-------+  
 12 | init_connect  |       |  
 13 | init_file     |       |  
 14 | init_slave    |       |  
 15 +---------------+-------+  
 16 3 rows in set (0.00 sec)  
 17    
 18 mysql> set global init_connect='insert into dba.accesslog(thread_id,time,localname,machine_name) values(connection_id(),now(),user(),current_user());';  
 19 Query OK, 0 rows affected (0.00 sec)  
 20    
 21 mysql> show variables like 'init%';  
 22 +---------------+-----------------------------------------------------------------------------------------------------------------------+  
 23 | Variable_name | Value                                                                                                                 |  
 24 +---------------+-----------------------------------------------------------------------------------------------------------------------+  
 25 | init_connect  | insert into dba.accesslog(thread_id,time,localname,machine_name) values(connection_id(),now(),user(),current_user()); |  
 26 | init_file     |                                                                                                                       |  
 27 | init_slave    |                                                                                                                       |  
 28 +---------------+-----------------------------------------------------------------------------------------------------------------------+  
 29 3 rows in set (0.00 sec)  
 30    
 31    
 32 3：分配用户权限  
 33 mysql> grant select,insert,update on dba.accesslog to baidandan@'192.168.9.45' identified by 'baidandan';  
 34 Query OK, 0 rows affected (0.00 sec)  
 35    
 36  --为了做实验，给baidandan赋予操作dba.t表的权限  
 37 mysql> grant select,delete on dba.t to baidandan@'192.168.9.45';  
 38 Query OK, 0 rows affected (0.00 sec)  
 39   
 40 4：测试  
 41 --客户端连接进行测试  
 42 C:Usersdandan>mysql -u baidandan -p -h 192.168.6.51  
 43 Enter password: *********  
 44 Welcome to the MySQL monitor.  Commands end with ; or g.  
 45 Your MySQL connection id is 371  
 46 Server version: 5.6.20-r5436-log Source distribution  
 47   
 48   
 49 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.  
 50   
 51   
 52 Oracle is a registered trademark of Oracle Corporation and/or its  
 53 affiliates. Other names may be trademarks of their respective  
 54 owners.  
 55   
 56   
 57 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.  
 58   
 59   
 60 mysql> use dba;  
 61 Database changed  
 62 mysql> delete from t;  
 63 Query OK, 1 row affected (0.10 sec)  
 64   
 65   
 66 mysql> commit;  
 67 Query OK, 0 rows affected (0.00 sec)  
 68   
 69   
 70 mysql> select * from t;  
 71 Empty set (0.00 sec)  
 72   
 73   
 74 假如我现在想看是谁把DBA.t表里的数据给删掉了。  
 75 查看日志：  
 76 mysql> show master status;  
 77 +------------------+----------+--------------+------------------+-------------------+  
 78 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |  
 79 +------------------+----------+--------------+------------------+-------------------+  
 80 | mysql-bin.000007 |     1640 |              |                  |                   |  
 81 +------------------+----------+--------------+------------------+-------------------+  
 82 1 row in set (0.00 sec)  
 83   
 84 --假如我知道这个数据是在6月18号9点后被删除的：  
 85 [root@ser6-51 data]# mysqlbinlog mysql-bin.000007 --start-datetime='2015-06-18 09:00:00'  
 86   
 87 --查到删除的语句：  
 88 #150618 16:55:30 server id 1  end_log_pos 1609 CRC32 0xa2296c53 Query thread_id=371 exec_time=0 error_code=0  
 89 use `dba`/*!*/;  
 90 SET TIMESTAMP=1434617730/*!*/;  
 91 delete from t  
 92 /*!*/;  
 93   
 94 --查询accesslog表  
 95 mysql> select * from dba.accesslog where thread_id=371;  
 96 +-----------+---------------------+------------------------+------------------------+  
 97 | thread_id | time                | localname              | machine_name           |  
 98 +-----------+---------------------+------------------------+------------------------+  
 99 |       371 | 2015-06-18 16:55:19 | baidandan@192.168.9.45 | baidandan@192.168.9.45 |  
100 +-----------+---------------------+------------------------+------------------------+  
101 1 row in set (0.00 sec)  
102 注意： 对于所有的普通级别的用户，必须全部都要对日志表具有读写权限， 否则将导致，没有权限的用户无法使用数据库。
103 
104  init_connect 不会记录有超级管理员权限的用户连接信息 (原因:当init_connect设置有误时,超级管理员可进行修改)
105 
106 因此，对于一般的用户，不能赋予all privileges权限。
107 
108  --如果想查看所有的增删改查记录，在general log（需要先开启）里查询即可。里面记录了连接的用户和IP信息。如：
109 
110 2016-10-08T12:09:58.476859Z   57 Connect
111 baidandan@10.0.9.121 on dba using TCP/IP