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

Setnames UTF8Create DatabaseAuditlog;Create TableAuditlog.t_audit (IDint  not NULLauto_increment, thread_idint  not NULL, Login_timetimestamp, LocalNamevarchar( -)default NULL, MatchNamevarchar( -)default NULL,   Primary Key(ID)) ENGINE=InnoDBdefaultCharSet=UTF8 Comment'Audit User Login information';

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

Select concat ("Grantinserton to'", User, "' @'", Host,"' from MySQL. User ;  #拼结授权语句
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 *  fromAuditlog.t_audit;+----+-----------+---------------------+---------------------------+-------------------------+|Id|thread_id|Login_time|LocalName|MatchName|+----+-----------+---------------------+---------------------------+-------------------------+| 1 | 5 |  .- ,-Ten  One: on: - |User_app@192.168.1. - |User_app@192.168.1.% || 2 | 6 |  .- ,-Ten  One: Geneva: Geneva |User_app@192.168.1.236 |User_app@192.168.1.% || 3 | 7 |  .- ,-Ten  One: +: Wu |User_yunwei@192.168.1. - |User_yunwei@192.168.1.% |+----+-----------+---------------------+---------------------------+-------------------------+3Rowsinch Set(0.00Sec

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 audit function based on Init-connect+binlog