1.在my.cnf [mysqld]组下加入
init-connect='insert intoaduit.accesslog(id,time,localname,matchname)values(connection_id(),now(),user(),current_user());’
#create database accesslog;
CREATE TABLE aduit.accesslog (`id` int(11) primary keyauto_increment, `time` timestamp, `localname` varchar(30), `matchname`varchar(30))engine=innodb;
2.被审计的用户需要有insert aduit.accesslog 权限
grant insert on aduit.accesslog to hhl@'172.17.62.%' identified by'xxx';
3.只能审计普通用户没有super权限的用户的DDL，DML，还用开启binlog 分析。
[root@wy ~]# mysql -upxy -p -P3306 -h 10.45.247.81
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> insert into test.t10 values(10);
Query OK, 1 row affected (0.00 sec)
#上面client 连接Processid = 11
mysql> select * from aduit.accesslog;
+----+---------------------+---------------------+-----------+
| id | time |localname | matchname |
+----+---------------------+---------------------+-----------+
| 3 | 2014-09-24 17:16:06 |admin@10.45.247.160 | admin@% |
| 10 | 2014-09-24 17:26:18 | pxy@10.45.247.160 | pxy@% |
| 11 | 2014-09-24 17:28:13 |pxy@10.45.247.160 | pxy@% |
+----+---------------------+---------------------+-----------+
Processid = 11 ，
mysql> show processlist;
+----+-----------------+---------------------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+---------------------+------+---------+------+------------------------+------------------+
| 2 | root | localhost | NULL | Query | 0| NULL | showprocesslist |
| 11 | pxy | 10.45.247.160:53086 | NULL |Sleep | 26 | | NULL |
+----+-----------------+---------------------+------+---------+------+------------------------+------------------+
看到了id=11的吧，就是aduit.accesslog 表的id列。
通过解析 binlog
[root@localhost binlog]# mysqlbinlog --base64-output=decode-rows -v-v mysql-bin.000030 |grep -nthread_id=11
22:#140924 17:28:13 server id 114 end_log_pos 282 Query thread_id=11 exec_time=0 error_code=0
24:SET @@session.pseudo_thread_id=11/*!*/;
42:#140924 17:32:21 server id 114 end_log_pos 491 Query thread_id=11 exec_time=0 error_code=0
定位到模糊的行位置，再找到binlog中Processid = 11 的操作。
--------------------------------------分割线 --------------------------------------
--------------------------------------分割线 --------------------------------------