How to implement the audit of MySQL database usage

How to implement the audit of MySQL database usageBest Answer

MySQL Audit function
 
The MySQL server itself does not provide auditing, but we can use the Init-connect + Binlog method to perform a MySQL operation audit. Because MySQL Binlog records all the actual changes to the database SQL statement, and its execution time, and connection_id but did not record connection_id corresponding detailed user information. In the post-audit behavior tracking, according to the behavior of Binlog records and the corresponding Connection-id combination before the connection log records analysis, draw the final conclusion.
 
1. Set Init-connect
1.1 Creating a database and table to hold the connection log
Create Database Accesslog;
CREATE TABLE accesslog.accesslog (' id ' int (one) primary key auto_increment, ' time ' timestamp, ' localname ' varchar (), ' Mat Chname ' varchar (30))
 
1.2 Creating User Rights
Ready-to-use root user for information reading
Grant Select on accesslog.* to root;
If there is a user with the to *. * permission, a restriction is required.
 
It is also important to note that users must have insert permissions on the Accesslog table
Grant Select on accesslog.* to [email protected] '% ';
 
1.3 Setting Init-connect
Under [Mysqld], add the following settings:
Init-connect= ' Insertinto accesslog.accesslog (ID, time, localname, MatchName)
VALUES (connection_id (), now (), User (), current_user ()); '
------Note the difference between user () and Current_User ()
Log-bin=xxx
Binlog must be turned on here.
 
1.4 Restart database is in effect
shell>/etc/init.d/mysql Restart
 
2. Record tracking
2.1 thread_id Confirmation
 
You can use the following statement to locate a statement executor.
 
tencent:~ # mysqlbinlog--start-datetime= ' 2011-01-26 16:00:00 '
--stop-datetime= ' 2011-01-26 17:00:00 '/var/lib/mysql/mysql-bin.000010
| Grep-b 5 ' WSJ '
 
commit/*!*/;
# at 767
#110126 16:16:43 Server ID 1 end_log_pos 872 Query thread_id=19 exec_time=0 error_code=0
Use test/*!*/;
SET timestamp=1296029803/*!*/;
CREATE TABLE WSJ (ID int unsigned NOT NULL)
--
BEGIN
/*!*/;
# at 940
#110126 16:16:57 Server ID 1 end_log_pos 1033 Query thread_id=19 exec_time=0 error_code=0
SET timestamp=1296029817/*!*/;
INSERT INTO WSJ (ID) VALUES (1)
--
BEGIN
/*!*/;
# at 1128
#110126 16:16:58 Server ID 1 end_log_pos 1221 Query thread_id=19 exec_time=0 error_code=0
SET timestamp=1296029818/*!*/;
INSERT INTO WSJ (ID) VALUES (2)

2.2 User Confirmation
 
THREAD_ID confirmation, finding the culprit is just a matter of a SQL statement.
 
Mysql> SELECT * from Accesslog where id=19;
+----+---------------------+---------------------+-----------+
| ID | Time | LocalName | MatchName |
+----+---------------------+---------------------+-----------+
| 19 | 2011-01-26 16:15:54 | [Email protected] | [Email protected]% |
+----+---------------------+---------------------+-----------+
1 row in Set (0.00 sec)

3. Q
Q: Does using init-connect affect server performance?
A: Theoretically, only one record is inserted into the database each time the user connects, and the database does not have a significant impact. Unless the connection frequency is very high (of course, this is the time to pay attention to how to use the connection multiplexing and control, rather than to use this method of the problem)---if the long connection and cache, can improve performance
 
How is the Q:access-log table maintained?
A: Because it is a log system, it is recommended to use the archive storage engine, which facilitates data compression and storage. If the number of database connections is large, it is recommended to do a data export for a certain time, and then clear the table.
Q: Does the table have other uses?
A: there! The Access-log table is of course not only for auditing, but also for data analysis of database connections, such as daily connection maps, and so on, only to be unexpected. ---can be used to test read-write separation, verify load balancing, etc.
 
Q: Will there be any missing records?
A: Yes, Init-connect is not executed when a super user logs on. So Access-log will not have database Superuser record, this is why we do not advocate multiple super users, and many people use the reason. -This audit does not record access to the database for accounts with super privileges such as root

How to implement the audit of MySQL database usage