MySQL Audit implementation method

MySQL version: 5.6.24-72.2

First, the basic principle of MySQL audit function is realized through Init-connect + Binlog:

Because the key to auditing is DML statements, all DML statements can be logged by Binlog.

Unfortunately, the current MySQL binlog only records, resulting in this record of the connection ID (with the number of connections from the increase, recycling), which is not any help after the anti-check.

So consider using Init-connect to record the user, and connection_id information for this connection during the initialization phase of each connection.

In the post-audit behavior tracking, according to the behavior of Binlog records and corresponding Connection-id combination before the connection log records to analyze, to draw the final conclusion

1. Set Init-connect:

1.1 Creating a table to hold connection information

Create DATABASE Auditdb default CharSet Utf8;use auditdb; CREATE TABLE accesslog (' id ' int (one) primary key auto_increment, ' logintime ' timestamp, ' localname ' varchar (), ' Matchnam E ' varchar (30));

1.2 Ensure that all users have write access to this table

Use Mysql;insert into db (Host,db,user,insert_priv) values ('% ', ' auditdb ', ' ', ' Y '); flush privileges;

1.3 Setting Init-connect

Add the following configuration to the block of [mysqld] in my.cnf;

init-connect= ' INSERT into Auditdb.accesslog (id,logintime,localname,matchname) VALUES (connection_id (), now (), User () , Current_User ()); '

Binlog must be turned on here:
Log-bin=xxx

1.4 Restart database is in effect

Service MySQL Restart

2. Test:

2.1 Create user, authorize. Create a table

CREATE USER ' monitor ' @ ' localhost ' identified by ' 123456 ', grant all on maildb.* to [email protected] '% '; mysql-h localhost -umonitor-p123456-d maildbcreate table test1 (name varchar (+), id int (11));

　　

2.2 Querying records for each login:

Mysql-h Localhost-umonitor-p123456select * from Auditdb.accesslog;

Find out who created the Test1 table first in Binlog to find the time when the table was created thread_id

[[Email protected]_01 ~]# mysqlbinlog/usr/local/mysql/binlog/binlog.000021 |grep--color ' test1 '-b 5#150917 12:41:43 SE RVer ID end_log_pos 939 CRC32 0x5691b7f5 Xid = 20commit/*!*/;# at 939#150917 12:42:18 server ID one end_log_pos 1066 CRC 0xde7951a0 Query thread_id=4 (here) exec_time=0 error_code=0set timestamp=1442464938/*!*/;create table test1 (name varchar (+), id int (11))

　　

According to thread_id in the Auditdb.accesslog corresponding ID field: You can find this is [email protected] dry.

Mysql> SELECT * from Auditdb.accesslog where id=4; +----+---------------------+-------------------+-------------------+| ID | Logintime | LocalName | MatchName |+----+---------------------+-------------------+-------------------+| 4 | 2015-09-17 12:41:43 | [Email protected] | [Email protected] |+----+---------------------+-------------------+-------------------+

　　

3. Q&a

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 time you need to be aware of how to use the connection multiplexing and control, rather than to use this method of the problem)

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.

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.

Reference: http://www.cnblogs.com/cenalulu/archive/2012/05/09/2491736.html

Second, MacFee company based on the development of Percona MySQL audit plugin:

Wiki home: Https://github.com/mcafee/mysql-audit/wiki

Binary package Download: https://bintray.com/mcafee/mysql-audit-plugin/release contains 5.1,5.5,5.6 corresponding binary package

Download: https://bintray.com/artifact/download/mcafee/mysql-audit-plugin/1.0.8/ Audit-plugin-mysql-5.6-1.0.8-527-linux-x86_64.zip

1 Installing the configuration plug-in

1.1 Decompression:

Unzip Audit-plugin-mysql-5.6-1.0.8-527-linux-x86_64.zip

　　

1.2 View MySQL Plugin directory:

Mysql> SHOW GLOBAL VARIABLES like ' plugin_dir '; +---------------+------------------------------------+| variable_name | Value |+---------------+------------------------------------+| Plugin_dir | /usr/local/mysql/lib/mysql/plugin/|+---------------+------------------------------------+

　　

1.3 Copy the downloaded so file to Plugin_dir, create the log directory

CD AUDIT-PLUGIN-MYSQL-5.6-1.0.8-527CP lib/libaudit_plugin.so/usr/local/mysql/lib/mysql/plugin/mkdir/usr/local/ Mysql/audit_log/chown mysql.mysql/usr/local/mysql/audit_log/

　　

1.4 Download The offset script, calculated based on the version
Offsets specific reference https://github.com/mcafee/mysql-audit/wiki/Troubleshooting

wget https://raw.github.com/mcafee/mysql-audit/master/offset-extract/offset-extract.sh# chmod +x offset-extract.sh #./offset-extract.sh/usr/local/mysql/bin/mysqld//offsets for:/usr/local/mysql/bin/mysqld (5.6.24-72.2) {" 5.6.24-72.2 "," c518d31ce76de4d470fcf2712877712e ", 7680, 7728, 4384, 5024, 88, 2720, 96, 0, 32, 104, 152, 7848},

　　

1.5: Configure MY.CNF, add the following content in the MYSQLD block:

plugin-load=audit=libaudit_plugin.soaudit_offsets=7680, 7728, 4384, 5024, 0, 2720, 104, 7848audit_json_f Ile=onaudit_json_log_file=/usr/local/mysql/audit_log/mysql-audit.jsonaudit_record_cmds=insert,delete,update, Create,drop,revoke,alter,grant,set #针对这些语句来审计

　　

1.6 Restarting the MySQL database

Service MySQL Restart

　　

2.1 Validation is in effect:

View version:

Mysql> SHOW GLOBAL STATUS like ' audit_version '; +---------------+-----------+| variable_name | Value |+---------------+-----------+| audit_version | 1.0.8-527 |+---------------+-----------+

　　

To see if it is turned on:

Mysql> SHOW GLOBAL VARIABLES like ' audit_json_file '; +-----------------+-------+| variable_name | Value |+-----------------+-------+| Audit_json_file | On |+-----------------+-------+

　　

You can create a test1 table to see the records in the/usr/local/mysql/audit_log/mysql-audit.json file.

2.2 Important Parameter descriptions:

1. Audit_json_file #是否开启audit功能
2. Audit_json_log_file #记录文件的路径和名称信息
3. Audit_record_cmds #audit记录的命令, the default is to record all commands can be set to any combination of DML, DCL, DDL
such as: Audit_record_cmds=select,insert,delete,update
Set global Audit_record_cmds=null (for recording all commands) is also available online

Additional configuration Parameters Reference: Https://github.com/mcafee/mysql-audit/wiki/Configuration

MySQL Audit implementation method