Shulou(Shulou.com)05/31 Report--

This article is about how mysql turns on the audit function. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Mysql audit- access logging

Background:

Suppose you are the mysql-DBA of a company, and suddenly everything in the company's database is artificially deleted.

Although there are data backups, there are tens of millions of losses caused by service outages, and now the company needs to find out who did the deletion.

But there are many people who have the right to operate the database, how to investigate, and where is the evidence?

Do you feel powerless?

Mysql itself does not have the function of operational audit, does that mean that you can only admit your own bad luck in this situation?

This article will discuss a simple and easy idea for mysql access audit.

Overview:

In fact, mysql itself already provides a detailed sql execution record-general log, but turning it on has the following disadvantages

No matter whether the sql has syntax errors or not, it will be recorded as long as it is executed, resulting in recording a large amount of useless information, which is difficult to filter in the later stage.

When the concurrency of sql is very large, the record of log will make a certain impression on io, which is that the efficiency of the database is reduced.

Log files can easily expand quickly, and improper handling will have an impact on disk space.

Point of view of this article:

Use the method of init-connect + binlog to audit the operation of mysql.

Because mysql binlog records all the actual changes to the database sql statements, and their execution time, and connection_id but does not record the detailed user information corresponding to connection_id.

So this article will record the user and connection_id information of the connection through init-connect during the initialization phase of each connection.

During the behavior tracking in the later audit, the final conclusion is drawn according to the behavior recorded by binlog and the corresponding connection-id combined with the previous connection log records.

Text:

1. Set up init-connect

1.1 create databases and tables for connection logs

Create database accesslog

CREATE TABLE accesslog.accesslog (`id` int (11) primary key auto_increment, `time` timestamp, `localname` varchar (30), `matchname` varchar (30))

1.2 create user permissions

Ready-made root users can be used to read information

Grant insert on accesslog.* to testuser@ . -other users need to have insert permission to change the library, otherwise the init cannot be plugged in during initialization.

1.3Setting init-connect

Add the following settings under [mysqld]:

Init-connect='insert into accesslog.accesslog values (connection_id (), now (), current_user (), user ());'

Log-bin

1.4 restart of the database takes effect

Shell > service mysqld restart

two。 Record tracking

2.1 thread_id confirmation

Suppose you want to know who deleted the test.dummy table after 9: 00 a. M. on November 25, 2009. You can use the following statement to locate

Mysqlbinlog-start-datetime='2009-11-25 09 stop-datetime='2009-11-25 09 stop-datetime='2009-11-25 09 binlog.xxxx | grep 'dummy'-B 5

You will get the following result (thread_id is 5):

# at 300777

# 091124 16:54:00 server id 10 end_log_pos 301396 Query thread_id=5 exec_time=0 error_code=0

SET TIMESTAMP=1259052840

Drop table test.dummy

2.2 user confirmation

After thread_id confirms, finding the culprit is just a matter of a sql statement.

Select * from accesslog.accesslog where conn_id=5

We'll find out it was testuser2@localhost.

+-+

| | id | time | localname | matchname | |

+-+

| | 5 | 2009-11-25 10:57:39 | testuser2@localhost | testuser2@% |

+-+

3. Qrooma

Q: does using init-connect affect server performance?

A: in theory, only one record will be inserted into the database each time the user connects, which will not have much impact on the database. Unless the connection frequency is very high (of course, what you need to pay attention to at this time is how to reuse and control the connection, not the problem of not using this method).

How to maintain the Q:access-log table?

A: as it is a log system, it is recommended to use archive storage engine, which is conducive to data compression and storage. If the number of database connections is very large, it is recommended to do a data export at a certain time, and then clear the table.

Q: is there any other use for the watch?

A: yes! Of course, the access-log table is not only used for auditing, of course, it can also be used for data analysis of database connections, such as daily connection distribution map, and so on.

Q: is there any record of omission?

A: yes, init-connect is not executed when super users log in. So there is no record of database superusers in access-log, which is why we do not advocate multiple superusers and multiple users.

Thank you for reading! This is the end of the article on "how to turn on the audit function of mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.