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

How to open the user audit function in mysql, in response to this problem, this article details the corresponding analysis and solution, hoping to help more small partners who want to solve this problem find a simpler and easier way.

Background:

Suppose you are a company mysql-DBA, and one day suddenly everything in the company is deleted.

Despite backups, the outage cost tens of millions of dollars, and now the company needs to track down who did the deletion.

But there are many people who have database operation authority. How to investigate and where is the evidence?

Do you feel powerless?

There is no audit function in itself. Does that mean that you can only admit that you are unlucky when you encounter this kind of situation?

This article will discuss a simple and easy-to-use approach to mysql access auditing.

Overview:

In fact, mysql itself has provided a detailed sql execution record-general log (see the previous blog), but opening it has the following shortcomings

Regardless of whether sql has syntax errors, it will be recorded as long as it is executed, resulting in a large amount of useless information being recorded, and later filtering is difficult.

SQL concurrency is very large, log records will cause a certain impression on io, is the database efficiency is reduced.

Log files are prone to rapid bloat, and improper handling will have a certain impact on disk space.

View of this article:

Use init-connect + binlog to audit mysql operations.

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

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

When the behavior is traced in the later audit, the behavior recorded in the binlog and the corresponding connection-id are analyzed in combination with the previous connection log records, and the final conclusion is drawn.

Text:

1. Set 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 Rights

Ready-made root users are available for reading information

grant read on accesslog.* to root@localhost identified by ‘password’;

If there is a to *.* Permission of users needs to be restricted.

The currently logged in user needs at least insert permissions on the accesslog library

1.3 Set init-connect

Add the following settings under [mysqld]:

init-connect='insert into accesslog.accesslog values(connection_id(),now(),user(),current_user());'-Note that the syntax and quotation marks of insert sentences are correct. If it is wrong, after logging into mysql, the operation db will prompt you that you have lost your connection with the server.

Eg. Tips

No connection. Trying to reconnect...

Connection id: 220

Current database: *** NONE ***

ERROR 2013 (HY000): Lost connection to MySQL server during query

log-bin-------If the original configuration file already has logging enabled, omit here

1.4 Restart database effective

shell> service mysqld restart

2. record tracing

2.1 thread_id confirmation

Suppose you want to know who it is on November 25, 2009, at 9:00 a.m., test.dummy. You can locate with the following statement

mysqlbinlog –start-datetime=’2009-11-25 09:00:00′ –stop-datetime=’2009-11-25 09:00:00′ binlog.xxxx | grep ‘dummy’-B 5

The result is as follows (see thread_id = 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 is confirmed, finding the culprit is just a matter of sql statements.

select * from accesslog.accesslog where conn_id=5 ;

You can see that testuser2@localhost did it.

+--+-----------+-----------+---------–+

| id | time | localname | matchname |

+--+-----------+-----------+---------–+

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

+--+-----------+-----------+---------–+

3. Q&A

Q: Does using init-connect affect server performance?

A: Theoretically, only one record will be inserted into the database every time the user connects, which will not have a great impact on the database. Unless the connection frequency is very high (of course, what needs to be paid attention to at this time is how to multiplex and control the connection, not whether to use this method)

Q: How is the access-log table maintained?

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

Q: Is there any other use for the watch?

A: Yes! The access-log table is of course not only used for auditing, but also for data analysis of database connections, such as daily connection number distribution charts, etc., only unexpected can not be done.

Q: Are there any missing records?

A: Yes, init-connect is not executed when super users log in. Therefore, there will be no record of database superusers in access-log, which is why we do not advocate multiple superusers and multi-user reasons

About mysql how to open the user audit function questions to share here, I hope the above content can have some help for everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.

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.