MySQL completes audit function based on Init-connect+binlog

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.