0816 audit of MySQL init-connect+binlog implementation of user operation tracking

Transferred from: http://blog.sina.com.cn/s/blog_605f5b4f01013xkv.html

MySQL uses Init-connect+binlog to implement user operation tracking log record of Access IP
in MySQL, each connection executes the Init-connect first, and the connection is initialized. Here we can get the user's login name and the thread ID value. Then with Binlog, we can trace the operation time of each operation statement, operator, etc. Implement audits. Experimental process:
1: Create login log library, log in log table

1. CREATE DATABASE ' Accesslog ';
2. Use ' Accesslog ';
3. CREATE TABLE ' Accesslog '
4. (
5. ' id ' int (one) not NULL auto_increment,
6. ' thread_id ' int (one) default NULL, #线程ID, this value is important
7. ' Log_time ' timestamp not NULL DEF ault current_timestamp on UPDATE current_timestamp, #登录时间
8. ' localname ' varchar (+) DEFAULT NULL, #登录名称with IP
   
9. ' matchname ' varchar (+) DEFAULT NULL, #登录用户, user's full name
10. PRIMARY KEY (' id ')
11. ) Engine=innodb auto_increment=1 DEFAULT Charset=utf8;

2: Configure the Init-connect parameter in the configuration file. Insert Log table when logging in. If this parameter is an incorrect SQL statement, the login will fail.
the configuration file under Linux is my.cnf,windows inferior my.ini

1. init-connect= ' INSERT into Accesslog.accesslog values (null,connection_id (), now (), User (), current_user ()); '-- Note Modify the corresponding SQL

Restart Service mysqld for the configuration file to take effect

3: Create a normal user, cannot have super privilege. Init-connect does not work for users with super privileges. At the same time this user must have at least insert permission to the Accesslog.accesslog table, and if not, any operation after logon will cause MySQL login to fail.

1. Grant Insert,select,update on *. * to ' user1 ' @ ' localhost '; #带INSERT权限
2. Grant Select,update on *. * to ' user2 ' @ ' localhost '; #不带INSERT权限

4:session1 Log in and view the log

2. Mysql> select * from Accesslog.accesslog;
4. +----+-----------+---------------------+-----------------+-----------------+
6. | ID | thread_id | Log_time | LocalName | MatchName |
8. +----+-----------+---------------------+-----------------+-----------------+
10. | 1 | 65 | 2011-03-11 19:18:25 | [Email protected] | [Email protected] |
12. +----+-----------+---------------------+-----------------+-----------------+
14. 1 row in Set (0.00 sec)
16. Mysql> Show processlist;# currently running ThreadID
18. +----+-------+----------------+------+---------+------+-------+------------------+
20. | Id | User | Host | db | Command | Time | State | Info |
22. +----+-------+----------------+------+---------+------+-------+------------------+
24. | 65 | User1 | localhost:1339 | NULL | Query | 0 | NULL | Show Processlist |
26. +----+-------+----------------+------+---------+------+-------+------------------+
28. 1 row in Set (0.00 sec)
30. Mysql>

5: Re-login with User2

2. D:\mysql6\bin>mysql-uuser2-p
4. Enter Password:
6. Welcome to the MySQL Monitor. Commands End With; or \g.
8. Your MySQL Connection ID is 76
10. Server Version:5.1.45-community-log
12. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.
14. Mysql> select * from Accesslog.accesslog;
16. ERROR 2006 (HY000): MySQL server has gone away
18. No connection. Trying to reconnect ...
20. Connection id:77
22. Current database: * * * NONE * * *
24. ERROR (HY000): Lost connection to MySQL server during query
26. Mysql> select * from Accesslog.accesslog;
28. ERROR 2006 (HY000): MySQL server has gone away
30. No connection. Trying to reconnect ...
32. Connection id:78
34. Current database: * * * NONE * * *

Look at the error log
If you do not specify a log file for Log-bin, the default name is mysqld-bin.00000x, and so on in the/var/lib/mysql directory. and Mysqld-bin.index records all of the log's file names.
use mysqlbinlog/var/lib/mysql|grep-b "table name" and so on to track the operation of database.

1. 110311 19:23:47 [Warning] aborted connection 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 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: Log in with USER1 below and do an insert operation to view the log file.

2. mysql> INSERT into T3 values (10,10, ' 2011-10-10 00:00:00 ');
4. Query OK, 1 row Affected (0.00 sec)
6. Mysql> show Processlist;
8. +----+-------+----------------+-----------+---------+------+-------+------------------+
10. | Id | User | Host | db | Command | Time | State | Info |
12. +----+-------+----------------+-----------+---------+------+-------+------------------+
14. | 69 | User1 | localhost:1439 | Accesslog | Query | 0 | NULL | Show Processlist |
16. +----+-------+----------------+-----------+---------+------+-------+------------------+
18. 1 row in Set (0.00 sec)
20. Mysql> select * from Accesslog.accesslog;
22. +----+-----------+---------------------+-----------------+-----------------+
24. | ID | thread_id | Log_time | LocalName | MatchName |
26. +----+-----------+---------------------+-----------------+-----------------+
28. | 1 | 65 | 2011-03-11 19:18:25 | [Email protected] | [Email protected] |
30. | 2 | 91 | 2011-03-11 19:28:33 | [Email protected] | [Email protected] |
32. | 3 | 2 | 2011-03-11 19:31:49 | [Email protected] | [Email protected] |
34. | 4 | 2 | 2000-10-10 10:10:10 | [Email protected] | [Email protected] |
36. | 5 | 21 | 2000-10-10 11:11:11 | [Email protected] | [Email protected]% |
38. | 6 | 69 | 2011-03-12 21:35:43 | [Email protected] | [Email protected] |
40. +----+-----------+---------------------+-----------------+-----------------+
42. 6 rows in Set (0.01 sec)

View the contents of a log file

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
Recorded in the log table is the same as recorded in the log file. This thread_id can be used to track who, what time, and what to do.

3. Q&a

Q: Does using init-connect affect server performance?

A: Theoretically, only one record is inserted into the database each time the user connects, and the database does not have a significant impact. Unless the connection frequency is very high (of course, this time you need to be aware of how to use the connection multiplexing and control, rather than to use this method of the problem)

How is the Q:access-log table maintained?

A: Because it is a log system, it is recommended to use the archive storage engine, which facilitates data compression and storage. If the number of database connections is large, it is recommended to do a data export for a certain time, and then clear the table.

Q: Does the table have other uses?

A: there! The Access-log table is of course not only for auditing, but also for data analysis of database connections, such as daily connection maps, and so on, only to be unexpected.

Q: Will there be any missing records?

A: Yes, Init-connect is not executed when a super user logs on. So Access-log will not have a database Superuser record, which is why we do not advocate multiple super users, and the reason why many people use

--it's about permissions.

0816 audit of MySQL init-connect+binlog implementation of user operation tracking