MySQL completes audit function based on Init-connect+binlog

The current community version of MySQL's audit function is still relatively weak, plug-based audit currently exists in the Enterprise version of MySQL, Percona and mariadb, but the MySQL community version has a init-connect option, based on which we can use it to complete the audit function.

Init-connect parameter Description:

Http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_init_connect

Step1: Creating a User database table

Set names utf8create database Auditlog;create table auditlog.t_audit (  ID int not NULL auto_increment,  thread_id i NT NOT NULL,  login_time timestamp,  localname varchar () default NULL,  matchname varchar (default null,<) C5/>primary key (ID)) Engine=innodb default Charset=utf8 comment ' Audit user login information ';

Step2: Authorizing all users to have insert permissions on the audit table

Select Concat ("Grant insert on Auditlog.t_audit to '", User, "' @ '", host, "';") from Mysql.user;  #拼结授权语句
... flush privileges;

Note that each additional user must be authorized to insert permissions on this table at a later time, and not connect.

Step3: Setting the Init_connect parameter

Set global init_connect= ' INSERT INTO Auditlog.t_audit (id,thread_id,login_time,localname,matchname) VALUES (NULL, CONNECTION_ID (), now (), User (), current_user ());

and add the following statement to the configuration file: init-connect= ' insert into Auditlog.t_audit (id,thread_id,login_time,localname,matchname) VALUES (null , connection_id (), now (), User (), current_user ()); ' To take effect the next time you restart

Verify:

After we log in and delete a record to view binlog, we can see that the thread_id of this operation is 7:

Then we'll look at this table T_audit table:

[Zejin] 3301>select * from auditlog.t_audit;+----+-----------+---------------------+--------------------------- +-------------------------+| ID | thread_id | Login_time | LocalName | MatchName |+----+-----------+---------------------+---------------------------+-------------------------+| 1 | 5 | 2016-08-10 11:01:07 | [Email protected] | [Email protected]% | | 2 | 6 | 2016-08-10 11:02:02 | [Email protected] | [Email protected]% | | 3 | 7 | 2016-08-10 11:19:54 | [Email protected] | [Email protected]% |+----+-----------+---------------------+---------------------------+------------------------ -+3 rows in Set (0.00 sec)

You can see that thread_id is 7 users for User_yunwei, on the 192.168.1.240 machine operation deleted, completed a simple audit of the data. Extension Description: 1.init-connect is only executed at connection time and does not have a large performance impact on the database 2.init-connect is an action command executed at the time of connection, so it can be used to accomplish other functions, such as: init_connect= ' SET Autocommit=0 ' 3.init-connect does not log user records with super privileges, in order to prevent init_connect statements from being logged by all users due to grammatical errors or permissions problems, ensuring at least a super user can log in and modify this value

MySQL completes the audit function based on Init-connect+binlog