MySQL uses Init-connect+binlog to implement user operation tracking log record of Access IP

in the 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 (one) default NULL, #线程ID, this value is important

' Log_time ' timestamp not NULL the DEFAULT current_timestamp on UPDATE current_timestamp, #登录时间

' LocalName ' varchar (+) DEFAULT NULL, #登录名称带IP

' MatchName ' varchar (+) DEFAULT NULL, #登录用户, user's full name

PRIMARY KEY (' id ')

) 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 for My.ini

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

Log-bin

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 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-p

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 65

Server 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;# currently running ThreadID

+----+-------+----------------+------+---------+------+-------+------------------+

| 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-p

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 76

Server Version:5.1.45-community-log

Type ' 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 away

No connection. Trying to reconnect ...

Connection id:77

Current database: * * * NONE * * *

ERROR (HY000): Lost connection to MySQL server during query

Mysql> select * from Accesslog.accesslog;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect ...

Connection id:78

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 "* * * *" to track the operation of database.

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 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=0

Use text;

SET timestamp=1299936961;

INSERT into 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 uses Init-connect+binlog to implement user operation tracking log record of Access IP