Mysql uses init-connect initialization to connect to the instance

This article mainly introduces mysql to connect to an example using init-connect initialization, hoping to add and update some knowledge to you. If you have any other questions you need to know, you can continue to follow my updated article in the industry information.

Connections to mysql are first initialized through init-connect, and then connected to the instance.

We take advantage of this to realize the access audit function of db by recording the user's thread_id, user name and user address during init-connect.

Implementation steps

1. Create a database table for audit.

In order not to conflict with the business library, create your own library separately:

# Database creation table code create database db_monitor; use db_monitor; CREATE TABLE accesslog (thread_id int (11) DEFAULT NULL, # process id log_time datetime default null, # login time localname varchar (50) DEFAULT NULL, # login name, with detailed ip matchname varchar (50) DEFAULT NULL, # login user key idx_log_time (log_time)) ENGINE=InnoDB DEFAULT CHARSET=utf8

This parameter can be adjusted dynamically, and it should be added to the configuration file my.cnf, otherwise it will become invalid after the next restart. 2. Configure the init-connect parameter

Mysql > show variables like 'init_connect%'; +-+ | Variable_name | Value | +-+-+ | init_connect | | +-+-+ 1 row in set (0.00 sec)

Mysql > set global init_connect='insert into db_monitor.accesslog (thread_id, log_time,localname,matchname) values (connection_id (), now (), user (), current_user ());

Add in my.conf

Init_connect='insert into db_monitor.accesslog (thread_id, log_time,localname,matchname) values (connection_id (), now (), user (), current_user ());'

3. Grant ordinary users insert permissions on the accesslog table

This point is important.

This parameter only works for ordinary users, and those with super permission will not have an effect.

If you are an ordinary user, after adding this feature, you must be authorized:

Grant insert on db_monitor.accesslog to user@'%'

As a result of non-authorization, the connection to the database will fail:

Users with no insert privileges in the accesslog table:

4. Verify the audit function

A user deletes a table to the test library to see if we can track which user with the binlog log:

View binlog:

You can see which user did the operation to complete the audit.

Home of original script

Read the above about mysql using init-connect initialization to connect to the example, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.