Use init-connect and binlog in MySQL to track user operation records

The test environment is inexplicably deleted from several important data records. Since only the public account is deleted in the binlog, you cannot query who logged on to the database during that time period.

The test environment is inexplicably deleted from several important data records. Since only the public account is deleted in the binlog, you cannot query who logged on to the database during that time period.

Preface:
In the test environment, several important data records are deleted. Because only the public account is deleted in the binlog, you cannot query who logged on to the database during that time period, consider how to record the login information of each MYSQL account. In MYSQL, each connection will first execute init-connect to initialize the connection, here we can get the user's login name and thread ID value. Then, with binlog, you can track the operation time and operator of each operation statement. Implement Audit.

MySQL Data Recovery-binlog

Clear binlog logs in MySQL

How to safely delete binlog logs under MySQL

MySQL -- binlog log data recovery

How does MySQL Delete binlog and restore Data?

Introduction and Analysis of Three MySQL binlog formats

MySQL uses binlog Incremental backup + restore instance

MySQL deletes binlog logs and restores data.

1. Create a separate database for recording access information in the mysql server database
Set names utf8;
Create databaseaccess_log;
Create table 'Access _ Log'
(
'Id' int (11) not null AUTO_INCREMENT,
'Thread _ id' int (11) default null, -- thread id. This value is very important.
'Log _ time' timestamp not null def ault CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- Logon time
'Localname' varchar (30) default null, -- Logon Name
'Matchname' varchar (30) default null, -- logon user
PRIMARYKEY ('id ')
) ENGINE = InnoDBAUTO_INCREMENT = 1 default charset = utf8 comment 'enter User Logon information ';



2. Configure the init-connect parameter in the configuration file. Insert a log table at login. If this parameter is an incorrect SQL statement, logon fails.
Vim/usr/local/mysql/my. cnf
Init-connect = 'insertinto access_log.access_logVALUES (NULL, CONNECTION_ID (), NOW (), USER (), CURRENT_USER ());'
Then restart the database.

3. Create a common user. the user cannot have the super permission and must have the insert permission on the access_log table of the access_log database. Otherwise, the logon will fail.
Grant the insert permission to the login user, but do not grant the insert and select permissions of access_log,
GRANTINSERT, DELETE, UPDATE, select on test. * TO audit_user @ '%' identified by 'cacti _ user1603 ';
Mysql> GRANTCREATE, DROP, ALTER, INSERT, DELETE, UPDATE, select on test. * TO audit_user @ '%' identified by 'cacti _ user1603 ';
Query OK, 0 rowsaffected (0.00 sec)
Mysql> exit
Then log on with the new audit_user.
[Root @ db_server ~] #/Usr/local/mysql/bin/mysql-uaudit_user-p-S/usr/local/mysql. sock
Enter password:
Welcome to theMySQL monitor. Commands end with; or \ g.
Your MySQL connectionid is 25
Server version: 5.6.12-log

Copyright (c) 2000,201 3, Oracle and/or its affiliates. All rights reserved.

Oracle is aregistered trademark of Oracle Corporation and/or its
Affiliates. Othernames may be trademarks of their respective
Owners.

Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement.

Mysql> lect * from access_log.access_log;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 26
Current database: *** NONE ***


ERROR 1184 (08S01): Aborted connection 26 to db: 'unconnected' user: 'audit _ user' host: 'localhost' (init_connect command failed)
Mysql>
Check the error message (init_connect command failed) and verify the error log again:
Tail-fn 5/usr/local/mysql/mysqld. log
16: 03: 3123743 [Warning] Aborted connection 25 to db: 'unconnected' user: 'audit _ user' host: 'localhost' (init_connect command failed)
2014-07-2816: 03: 31 23743 [Warning] INSERT command denied to user ''@ 'localhost' for table 'Access _ Log'
2014-07-2816: 04: 04 23743 [Warning] Aborted connection 26 to db: 'unconnected' user: 'audit _ user' host: 'localhost' (init_connect command failed)
2014-07-2816: 04: 04 23743 [Warning] INSERT command denied to user ''@ 'localhost' for table 'Access _ Log'
You must have the insert permission on the access_log table of the access_log database.


4. Grant the insert and select permissions of access_log to the user, and then re-grant the permissions:
GRANTSELECT, insert on access_log. * TO audit_user @ '% ';
Mysql>
Mysql> GRANTSELECT, insert on access_log. * TO audit_user @ '% ';
Query OK, 0 rowsaffected (0.00 sec)

Mysql> exit
Bye

Log on again and report the following error:
[Root @ db_server ~] #/Usr/local/mysql/bin/mysql-uaudit_user-p-S/usr/local/mysql. sock
Enter password:
ERROR 1045 (28000): Access denied for user 'audit _ user' @ 'localhost' (using password: YES)
[Root @ db_server ~] #

View the error log:
2014-07-2816: 15: 29 23743 [Warning] INSERT command denied to user ''@ 'localhost' for table 'Access _ Log'
2014-07-2816: 15: 41 23743 [Warning] Aborted connection 37 to db: 'unconnected' user: 'audit _ user' host: 'localhost' (init_connect command failed)
2014-07-2816: 15: 41 23743 [Warning] INSERT command denied to user ''@ 'localhost' for table 'Access _ Log'
2014-07-2816: 15: 50 23743 [Warning] Aborted connection 38 to db: 'unconnected' user: 'audit _ user' host: 'localhost' (init_connect command failed)
2014-07-2816: 15: 50 23743 [Warning] INSERT command denied to user ''@ 'localhost' for table 'Access _ Log'

For more details, please continue to read the highlights on the next page: