Background:
Assuming such a situation, you are a company mysql-DBA, one day all of a sudden the company database was artificially deleted.
Despite the backup, but the service is stopped due to damage caused tens of millions, and now companies need to find out that people who do the delete operation.
However, permission to have database operations a lot of people, how to troubleshoot, Where is the evidence?
Is not that powerless?
mysql itself does not operate the audit function, it is not the means in which case nothing with it?
This article will discuss a simple, ideas for mysql access to the audit.
Keywords: init-connect, binlog, trigger
Overview:
In fact, in itself mysql sql to provide a detailed implementation of records-general log, but it has several drawbacks open
Sql syntax error regardless of whether, if carried out will be recorded, leading to record a lot of useless information, post-screening difficult.
sql concurrency is large, log io record will cause some the impression that the database efficiency.
Rapid expansion of the log file is easy, do not properly handle disk space will cause a certain extent.
This point of view:
Init-connect + binlog using the method of operation of the audit mysql.
As mysql binlog longevity record of all the actual changes to the database sql statement, its execution time, and connection_id But there is no corresponding record connection_id detailed user information.
This article will init-connect, in the initial stage of each connection, the connection of the user record, and connection_id information.
Conduct audits in the latter track, in accordance with the behavior recorded binlog connection-id and the corresponding log records connected with the analysis before, draw final conclusions.
Text:
1. Set init-connect
1.1 create table for sotre user connect log
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 for read log infomation
GRANT READ ON accesslog.* to root@localhost identified by ‘password’
1.3 set init-connect
open my.cnf and at [mysqld] add fllow line
log-bin
init-connect=’insert into accesslog.accesslog values(connection_id(),user(),current_user(),now());’
1.4 restart mysqld
shell>service mysqld restart
2. Record Tracking
2.1 thread_id confirmed
Suppose want to know November 25, 2009, more than 9 am when it test.dummy this table who deleted the. The following statement can be positioning
mysqlbinlog –start-datetime=’2009-11-25 09:00:00′ –stop-datetime=’2009-11-25 09:00:00′ binlog.xxxx | grep ‘dummy’ -B 5
Will get the following results (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 the user to confirm
thread_id recognized, find the culprit is just a question of a sql statement.
select * from accesslog.accesslog where conn_id=5 ; select * from accesslog.accesslog where conn_id = 5;
Can be found testuser2 @ localhost dry out.
+——+——————————-+———
| Id | time | localname | matchname |
+——+——————————-+———
| 5 | 2009-11-25 10:57:39 | testuser2@localhost | testuser2@% |
+——+——————————-+———
3. Q & A
Q: using init-connect can affect server performance?
A: In theory, the only connection to the database each time a user to insert a record, will not have a significant impact on the database. Unless the connection is very high frequency (of course, need to pay attention this time is how to reuse the connection and control, rather than the use of this method is not the problem)
Q: access-log table how to maintain?
A: Because it is a log system, the recommended archive storage engine will help Ecuador compressed data storage. If the large number of database connection, I suggest a certain time to do a data export, and then clear the table.
Q: What table has other uses?
A: Yes! access-log table, of course not only for the audit, of course, can also be used for the database connection for data analysis, such as distribution of the daily number of connections, etc., not only can not think of.
Q: there will be missing records?
A: Council, init-connect is not executed when the super user login. So there will not have access-log record of the database superuser, which is why we do not recommend more than super-user and multi-user reasons.
