需要用root用户登录进去，清空掉用户为''的用户记录。
MySQL>select user,host,password from mysql.user;
+----------------+-----------+-------------------------------------------+
| user | host | password |
+----------------+-----------+-------------------------------------------+
| root | localhost | |
| root | db_server | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | db_server | |
| cacti_user | % |*EB9E3195E443D577879101A35EF64A701B35F949 |
| cacti_user | 1 |*D5FF9B53A78232DA13D3643965A5961449B387DB |
| cacti_user | 2 | *D5FF9B53A78232DA13D3643965A5961449B387DB|
| test_user | 192.% |*8A447777509932F0ED07ADB033562027D95A0F17 |
| test_user | 1 |*8A447777509932F0ED07ADB033562027D95A0F17 |
| weakpwd_user_1| 10.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| weakpwd_user_2| 10.% | *B1461C9C68AFA1129A5F968C343636192A084ADB |
| weakpwd_user_3| 10.% | *DCB7DF5FFC82C441503300FFF165257BC551A598 |
| audit_user | % |*AEAB1915B137FAFDE9B949D67A9A42DDB68DD8A2 |
+----------------+-----------+-------------------------------------------+
15 rows in set(0.00 sec)
mysql> dropuser ''@'localhost';
Query OK, 0 rowsaffected (0.00 sec)
mysql> dropuser ''@'db_server';
Query OK, 0 rowsaffected (0.00 sec)
mysql>
再用已经分配了access_log表的Insert权限的audit_user登录
mysql> select* from access_log.access_log;
+----+-----------+---------------------+---------------------------+--------------+
| id | thread_id| log_time | localname | matchname |
+----+-----------+---------------------+---------------------------+--------------+
| 4 | 41 | 2014-07-28 16:19:37 | audit_user@localhost | audit_user@% |
| 5 | 42 | 2014-07-28 16:20:32 | audit_user@localhost | audit_user@% |
| 6 | 45 | 2014-07-28 16:21:11 | audit_user@localhost | audit_user@% |
+----+-----------+---------------------+---------------------------+--------------+
6 rows in set(0.00 sec)
mysql> showfull processlist;
+----+------------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time |State | Info |
+----+------------+-----------+------+---------+------+-------+-----------------------+
| 45 | audit_user| localhost | NULL | Query | 0 | init | show fullprocesslist |
+----+------------+-----------+------+---------+------+-------+-----------------------+
1 row in set(0.00 sec)
mysql>
5，再用另外一个用户登录建表，录入测试数据。
建表录入数据记录
mysql> usetest;
Database changed
mysql> createtable t1 select 1 as a, 'wa' as b;
Query OK, 1 rowaffected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
查看跟踪用户行为记录。
mysql> select* from access_log.access_log;
+----+-----------+---------------------+---------------------------+--------------+
| id | thread_id| log_time | localname | matchname |
+----+-----------+---------------------+---------------------------+--------------+
| 4 | 41 | 2014-07-28 16:19:37 | audit_user@localhost | audit_user@% |
| 5 | 42 | 2014-07-28 16:20:32 | audit_user@localhost | audit_user@% |
| 6 | 45 | 2014-07-28 16:21:11 | audit_user@localhost | audit_user@% |
| 7 | 48 | 2014-07-28 16:30:42 | audit_user@192.168.3.62 | audit_user@% |
| 8 | 50 | 2014-07-28 16:46:11 | audit_user@192.168.3.62 | audit_user@% |
+----+-----------+---------------------+---------------------------+--------------+
8 rows in set(0.00 sec)
去mysqldb服务器上查看binlog 内容，解析完后，没有insert语句，怎么回事，去看my.cnf
#binlog-ignore-db=mysql # No sync databases
#binlog-ignore-db=test # No sync databases
#binlog-ignore-db=information_schema # No sync databases
#binlog-ignore-db=performance_schema
原来是对test库有binlog过滤设置，全部注释掉。重启mysql库，重新来一遍，可以在看到binlog
在MySQL客户端上重新执行。
mysql> usetest;
Database changed
mysql> insertinto test.t1 select 5,'t5';
Query OK, 1 rowaffected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select* from access_log.access_log;
+----+-----------+---------------------+---------------------------+--------------+
| id | thread_id| log_time | localname | matchname |
+----+-----------+---------------------+---------------------------+--------------+
| 1 | 17 | 2014-07-28 15:41:04 | cacti_user@192.168.171.71| cacti_user@% |
| 2 | 18 | 2014-07-28 15:41:05 | cacti_user@192.168.171.71| cacti_user@% |
| 3 | 19 | 2014-07-28 15:41:05 | cacti_user@192.168.171.71| cacti_user@% |
| 4 | 41 | 2014-07-28 16:19:37 | audit_user@localhost | audit_user@% |
| 5 | 42 | 2014-07-28 16:20:32 | audit_user@localhost | audit_user@% |
| 6 | 45 | 2014-07-28 16:21:11 | audit_user@localhost | audit_user@% |
| 7 | 48 | 2014-07-28 16:30:42 | audit_user@192.168.3.62 | audit_user@% |
| 8 | 50 | 2014-07-28 16:46:11 | audit_user@192.168.3.62 | audit_user@% |
| 9 | 56 | 2014-07-28 19:32:12 | audit_user@192.168.1.12 | audit_user@% |
| 10 | 1 | 2014-07-28 20:02:56 |audit_user@192.168.3.62 | audit_user@% |
+----+-----------+---------------------+---------------------------+--------------+
10 rows in set(0.00 sec)
看到thread_id为1