MySQL to user operation Plus audit function--Advanced edition

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

CREATE DATABASE ' Accesslog '; Use ' Accesslog '; CREATE TABLE ' Accesslog ' (  ' id ' int (one) not null auto_increment,  ' thread_id ' int ') default NULL, #线程ID, this value is important 
    ' log_time ' timestamp not NULL DEF ault current_timestamp on UPDATE current_timestamp, #登录时间  ' localname ' varchar (30 Default NULL, #登录名称  ' matchname ' varchar ($) default NULL, #登录用户  PRIMARY KEY (' id ')) engine=innodb auto_incremen T=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.

init-connect= ' INSERT into Accesslog.accesslog values (null,connection_id (), now (), User (), current_user ()); '

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 INSERT permission, if not, any operation after login will cause MySQL login failure.

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

4:session1 Log in and view the log

D:\mysql6\bin>mysql-uuser1-penter Password:welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 65Server version:5.1.45-community-log mysql community Server (GPL) Type ' help; ' or ' \h ' For help. Type ' \c ' to clear the current input statement.mysql> select * FROM accesslog.accesslog;+----+-----------+------------ ---------+-----------------+-----------------+| ID | thread_id | Log_time | LocalName | MatchName |+----+-----------+---------------------+-----------------+-----------------+| 1 | 65 | 2011-03-11 19:18:25 | [Email protected] | [email protected] |+----+-----------+---------------------+-----------------+-----------------+1 row in Set ( 0.00 sec) Mysql> Show processlist;+----+-------+----------------+------+---------+------+-------+-------------- ----+| Id | User | Host | db | Command | Time | State | Info |+----+-------+----------------+------+---------+------+-------+------------------+| 65 | User1 | localhost:1339 | NULL | Query| 0 | NULL | Show Processlist |+----+-------+----------------+------+---------+------+-------+------------------+1 row in Set ( 0.00 sec) Mysql>

5: Re-login with User2

D:\mysql6\bin>mysql-uuser2-penter Password:welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 76Server version:5.1.45-community-logtype ' help, ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> select * from Accesslog.accesslog; ERROR 2006 (HY000): MySQL server has gone awayno connection. Trying to reconnect ... Connection id:77current Database: * * * NONE ***error (HY000): Lost Connection to MySQL server during querymysql> s elect * from Accesslog.accesslog; ERROR 2006 (HY000): MySQL server has gone awayno connection. Trying to reconnect ... Connection id:78current Database: * * * NONE * * *

Look at the error log

110311 19:23:47 [Warning] aborted connection to DB: ' unconnected ' User: ' User2 ' host: ' localhost ' (init_connect command Failed) 110311 19:23:47 [Warning] INSERT command denied to user ' user2 ' @ ' localhost ' for table ' Accesslog ' 110311 19:23:53 [ Warning] aborted connection to DB: ' unconnected ' User: ' User2 ' host: ' localhost ' (init_connect command failed) 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.

mysql> INSERT into T3 values (10,10, ' 2011-10-10 00:00:00 '); Query OK, 1 row Affected (0.00 sec) mysql> Show processlist;+----+-------+----------------+-----------+---------+--- ---+-------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-------+----------------+-----------+---------+------+-------+------------------+| 69 | User1 | localhost:1439 | Accesslog | Query | 0 | NULL | Show Processlist |+----+-------+----------------+-----------+---------+------+-------+------------------+1 row in Set (0.00 sec) mysql> SELECT * from accesslog.accesslog;+----+-----------+---------------------+-----------------+ -----------------+| ID | thread_id | Log_time | LocalName | MatchName |+----+-----------+---------------------+-----------------+-----------------+| 1 | 65 | 2011-03-11 19:18:25 | [Email protected] | [Email protected] | | 2 | 91 | 2011-03-11 19:28:33 | [Email protected] | [Email protected] | | 3 | 2 | 2011-03-11 19:31:49 | [Email&nbsP;protected] | [Email protected] | | 4 | 2 | 2000-10-10 10:10:10 | [Email protected] | [Email protected] | | 5 | 21 | 2000-10-10 11:11:11 | [Email protected] | [email protected]% | | 6 | 69 | 2011-03-12 21:35:43 | [Email protected] | [email protected] |+----+-----------+---------------------+-----------------+-----------------+6 rows in Set ( 0.01 sec)

View the contents of a log file

# at 340#110312 21:36:01 server ID 1 end_log_pos 453 Query thread_id=69 exec_time=0 error_code=0use text/*!*/; SET Timestamp=1299936961/*!*/;insert to T3 values (10,10, ' 2011-10-10 00:00:00 ')/*!*/;# 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.

MySQL to user operation Plus audit function--Advanced edition