[转]mysql 用init-connect+binlog兑现用户操作追踪 做access 的ip的log 记录

[转]mysql 用init-connect+binlog实现用户操作追踪 做access 的ip的log 记录

from：http://blog.chinaunix.net/uid-24086995-id-168445.htmlhttp://www.mysqlsystems.com/2009/11/mysql-audit-access-log.html

在MYSQL中，每个连接都会先执行init-connect，进行连接的初始化。我们可以在这里获取用户的登录名称和thread的ID值。然后配合binlog，就可以追踪到每个操作语句的操作时间，操作人等。实现审计。实验过程：
1：创建登录日志库，登录日志表

1. CREATE?DATABASE?`accesslog`;
   
2. USE?`accesslog`;
3. CREATE?TABLE?`accesslog`?
4. (
5. ??`id`?int(11)?NOT?NULL?AUTO_INCREMENT,
6. ??`thread_id`?int(11)?DEFAULT?NULL,?#线程ID，这个值很重要
7. ??`log_time`?timestamp?NOT?NULL?DEF AULT CURRENT_TIMESTAMP ON?UPDATE?CURRENT_TIMESTAMP,?#登录时间
8. ??`localname`?varchar(30)?DEFAULT?NULL,?#登录名称带IP
   
9. ??`matchname`?varchar(30)?DEFAULT?NULL,?#登录用户，user的全称
10. ??PRIMARY KEY?(`id`)
11. )?ENGINE=InnoDB?AUTO_INCREMENT=1?DEFAULT?CHARSET=utf8;

2：在配置文件中配置init-connect参数。登录时插入日志表。如果这个参数是个错误的SQL语句，登录就会失败。
Linux 下的配置文件为 my.cnf,windows下位my.ini

1. init-connect='insert into accesslog.accesslog values(null,connection_id(),now(),user(),current_user());'
2. log-bin
   

重启service mysqld 以使其配置文件生效

3：创建普通用户，不能有super权限。init-connect对具有super权限的用户不起作用。同时此用户必须要有INSERT权限，如果没有，登录后的任何操作都会导致MYSQL登录失败。

1. grant?insert,select,update?on?*.*?to?'user1'@'localhost';?#带INSERT权限
   
2. grant?select,update?on?*.*?to?'user2'@'localhost';?#不带INSERT权限

4：SESSION1登录，并查看日志

1. D:\mysql6\bin>mysql -uuser1 -p
   
2. Enter?password:
3. Welcome?to?the MySQL monitor.?Commands?end?with?;?or?\g.
4. Your MySQL connection id?is?65
5. Server?version:?5.1.45-community-log?MySQL Community Server?(GPL)
6. Type?'help;'?or?'\h'?for?help.?Type?'\c'?to?clear the current input statement.
7. mysql>?select?*?FROM?accesslog.accesslog;
8. +----+-----------+---------------------+-----------------+-----------------+
9. |?id?|?thread_id?|?log_time?|?localname?|?matchname?|
10. +----+-----------+---------------------+-----------------+-----------------+
11. |?1?|?65?|?2011-03-11 19:18:25?|?user1@localhost?|?user1@localhost?|
12. +----+-----------+---------------------+-----------------+-----------------+
13. 1 row?in?set?(0.00 sec)
14. mysql>?show?processlist;# 当前运行的threadId
15. +----+-------+----------------+------+---------+------+-------+------------------+
16. |?Id?|?User?|?Host?|?db?|?Command?|?Time?|?State?|?Info?|
17. +----+-------+----------------+------+---------+------+-------+------------------+
18. |?65?|?user1?|?localhost:1339?|?NULL?|?Query?|?0?|?NULL?|?show?processlist?|
19. +----+-------+----------------+------+---------+------+-------+------------------+
20. 1 row?in?set?(0.00 sec)
21. mysql>

5：再用user2登录

1. D:\mysql6\bin>mysql -uuser2 -p
   
2. Enter?password:
3. Welcome?to?the MySQL monitor.?Commands?end?with?;?or?\g.
4. Your MySQL connection id?is?76
5. Server?version:?5.1.45-community-log
6. Type?'help;'?or?'\h'?for?help.?Type?'\c'?to?clear the current input statement.
7. mysql>?select?*?FROM?accesslog.accesslog;
8. ERROR 2006?(HY000):?MySQL server has gone away
9. No connection.?Trying?to?reconnect...
10. Connection id: 77
11. Current?database:?***?NONE?***
12. ERROR 2013?(HY000):?Lost connection?to?MySQL server during query
13. mysql>?select?*?FROM?accesslog.accesslog;
14. ERROR 2006?(HY000):?MySQL server has gone away
15. No connection.?Trying?to?reconnect...
16. Connection id: 78
17. Current?database:?***?NONE?***

看下错误日志
如果没有对log-bin指定log文件，默认在 /var/lib/mysql目录下以mysqld-bin.00000X等作为名称。而 mysqld-bin.index则记录了所有的log的文件名称
使用时则使用mysqlbinlog /var/lib/mysql|grep "*****"等来追踪database的操作。

1. 110311 19:23:47?[Warning]?Aborted connection 77?to?db:?'unconnected'?user:?'user2'?host:?'localhost'?(init_connect command failed)
   
2. 110311 19:23:47?[Warning]?INSERT?command denied?to?user?'user2'@'localhost'?for?table?'accesslog'
3. 110311 19:23:53?[Warning]?Aborted connection 78?to?db:?'unconnected'?user:?'user2'?host:?'localhost'?(init_connect command failed)
4. 110311 19:23:53?[Warning]?INSERT?command denied?to?user?'user2'@'localhost'?for?table?'accesslog'

6：下面以USER1登录，并做一个INSERT操作，查看日志文件。

1. mysql>?insert?into?t3?values(10,10,'2011-10-10 00:00:00');
   
2. Query OK,?1 row affected?(0.00 sec)
3. mysql>?show?processlist;
4. +----+-------+----------------+-----------+---------+------+-------+------------------+
5. |?Id?|?User?|?Host?|?db?|?Command?|?Time?|?State?|?Info?|
6. +----+-------+----------------+-----------+---------+------+-------+------------------+
7. |?69?|?user1?|?localhost:1439?|?accesslog?|?Query?|?0?|?NULL?|?show?processlist?|
8. +----+-------+----------------+-----------+---------+------+-------+------------------+
9. 1 row?in?set?(0.00 sec)
10. mysql>?select?*?from?accesslog.accesslog;
11. +----+-----------+---------------------+-----------------+-----------------+
12. |?id?|?thread_id?|?log_time?|?localname?|?matchname?|
13. +----+-----------+---------------------+-----------------+-----------------+
14. |?1?|?65?|?2011-03-11 19:18:25?|?user1@localhost?|?user1@localhost?|
15. |?2?|?91?|?2011-03-11 19:28:33?|?user1@localhost?|?user1@localhost?|
16. |?3?|?2?|?2011-03-11 19:31:49?|?user1@localhost?|?user1@localhost?|
17. |?4?|?2?|?2000-10-10 10:10:10?|?user1@localhost?|?user1@localhost?|
18. |?5?|?21?|?2000-10-10 11:11:11?|?root@localhost?|?root@%?|
19. |?6?|?69?|?2011-03-12 21:35:43?|?user1@localhost?|?user1@localhost?|
20. +----+-----------+---------------------+-----------------+-----------------+
21. 6 rows?in?set?(0.01 sec)

查看日志文件的内容

1. # at 340
   
2. #110312 21:36:01 server id 1 end_log_pos 453 Query thread_id=69 exec_time=0 error_code=0
3. use?text;
4. SET?TIMESTAMP=1299936961;
5. insert?into?t3?values(10,10,'2011-10-10 00:00:00')
6. ;
7. # at 453

thread_id=69
在日志表里记录的和日志文件里面记录的相同。可以通过这个thread_id来追踪到是谁，什么时间，做了什么操作。

查看更多 下一篇