追踪mysql操作记录实践2

from：http://blog.chinaunix.net/uid-24086995-id-168445.html http://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登录失败。

REATE USER 'username'@'host' IDENTIFIED BY 'password';

   例子: CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';

               CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';

               CREATE USER 'pig'@'%' IDENTIFIED BY '123456';

               CREATE USER 'pig'@'%' IDENTIFIED BY '';

               CREATE USER 'pig'@'%';

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来追踪到是谁，什么时间，做了什么操作。