Database built-in audit function

As a DBA, sometimes, always encounter database a library, a table, a field exception, or the data is inexplicably deleted, this time we all kinds of inference is not a bug ah, is not black ah ... This time an audit function is highlighted.

MARIADB database to audit plug-in (server_audit.so) support is good, today we do a simple installation and testing

The installation method is also simple: InstallSONAME "server_audit.so"

650) this.width=650; "title=" Untitled. png "src=" http://s1.51cto.com/wyfs02/M02/8C/2A/ Wkiol1hke-mb-kb3aablfurffja252.png-wh_500x0-wm_3-wmp_4-s_2453716365.png "alt=" Wkiol1hke-mb-kb3aablfurffja252.png-wh_50 "/>

So that the installation is complete, we can look at the corresponding parameter variables

650) this.width=650; "title=" Untitled. png "src=" http://s4.51cto.com/wyfs02/M02/8C/2A/ Wkiol1hkfvvsvu0taacjdy2oma0984.png-wh_500x0-wm_3-wmp_4-s_166257261.png "alt=" Wkiol1hkfvvsvu0taacjdy2oma0984.png-wh_50 "/>

These parameters and variables can be set dynamically, specific parameters, see official website: https://mariadb.com/kb/en/mariadb/server_audit-system-variables/

Generally, we can set a few parameters.

Set Global Ser ver_audit_events = QUERY/* This parameter has a value of three connect,table,query

Set global server_audit_file_rotate_size = 524288000/* Size of each log file

Sset global erver_audit_file_rotations = 200

Set global Server_audit_file_path = "/data/mysql/auditlog/server_audit.log"/*log file path must be guaranteed /data/mysql/a uditlog/this path is there! and Chown Mysql.mysql, otherwise will turn on the audit function, will lead to MySQL error, or even the service hung off!

Set Global server_audit_logging = 1/* Open auditing is highly recommended: After setting the following parameters, turn on

Once the settings are complete, add the corresponding parameters to the configuration file

server_audit_logging = 1

Server_audit_events = QUERY

Server_audit_file_rotate_size = 524288000

Server_audit_file_rotations = 200

Server_audit_file_path =/data/mysql/auditlog/server_audit.log

Let's look at the actual results and compare the log comparisons of the server_audit_events parameter connet,table,query three different values;

1. When the parameter is server_audit_events= Query

650) this.width=650; "title=" Untitled. png "src=" http://s3.51cto.com/wyfs02/M00/8C/2F/ Wkiom1hkhehc8a2paach6p8bagg046.png-wh_500x0-wm_3-wmp_4-s_2560620423.png "alt=" Wkiom1hkhehc8a2paach6p8bagg046.png-wh_50 "/>

Log content:
20161229 11:35:39,localhost.localdomain,root,localhost,42,745,query,mysql, ' show databases ', 0
20161229 11:35:55,localhost.localdomain,root,localhost,42,746,query,mysql, ' Create Database Yhtest ', 0
20161229 11:36:03,localhost.localdomain,root,localhost,42,747,query,mysql, ' SELECT DATABASE () ', 0
20161229 11:36:03,localhost.localdomain,root,localhost,42,749,query,yhtest, ' show databases ', 0
20161229 11:36:03,localhost.localdomain,root,localhost,42,750,query,yhtest, ' show Tables ', 0
20161229 11:36:42,localhost.localdomain,root,localhost,42,751,query,yhtest, ' CREATE TABLE yhtest (a int primary, B int) ', 1064
20161229 11:36:56,localhost.localdomain,root,localhost,42,752,query,yhtest, ' CREATE TABLE yhtest (a int primary key, b int) ', 0
20161229 11:37:35,localhost.localdomain,root,localhost,42,753,query,yhtest, ' insert into Yhtest value (2,2), ( 3,3) ', 0
20161229 11:37:46,localhost.localdomain,root,localhost,42,754,query,yhtest, ' select * from Yhtest ', 0
20161229 11:38:07,localhost.localdomain,root,localhost,42,755,query,yhtest, ' delete from yhtest where A=1 ', 0
20161229 11:38:15,localhost.localdomain,root,localhost,42,756,query,yhtest, ' drop table Yhtest ', 0
20161229 15:45:07,localhost.localdomain,root,localhost,42,757,query,yhtest, ' Show variables like ' server%\ ', 0

 2. Parameters are ser ver_audit_events =connect                                                                                                               20161229 16:09:50,localhost.localdomain,root,localhost,42,0,disconnect, yhtest,,0
20161229 16:09:54,localhost.localdomain,root,localhost,43,0,connect,,, 0
20161229 16:11:37, localhost.localdomain,root,localhost,43,0,disconnect,yhtest2,,0
20161229 16:11:39,localhost.localdomain,root , localhost,44,0,connect,,, 0
20161229 16:12:06,localhost.localdomain,root,localhost,44,0,disconnect,mysql,,0

3. When the parameter is server_audit_events=table 20161229 16:17:52,localhost.localdomain,root,localhost,47,857,create,yhtest,t2,
20161229 16:17:59,localhost.localdomain,root,localhost,47,858,write,mysql,table_stats,
20161229 16:17:59,localhost.localdomain,root,localhost,47,858,write,mysql,column_stats,
20161229 16:17:59,localhost.localdomain,root,localhost,47,858,write,mysql,index_stats,
20161229 16:17:59,localhost.localdomain,root,localhost,47,858,drop,yhtest,t2,
20161229 16:18:04,localhost.localdomain,root,localhost,47,859,create,yhtest,t3,
20161229 16:18:27,localhost.localdomain,root,localhost,47,860,write,yhtest,t3,
20161229 16:19:04,localhost.localdomain,root,localhost,47,861,write,yhtest,t3,
20161229 16:19:18,localhost.localdomain,root,localhost,47,862,write,mysql,table_stats,
20161229 16:19:18,localhost.localdomain,root,localhost,47,862,write,mysql,column_stats,
20161229 16:19:18,localhost.localdomain,root,localhost,47,862,write,mysql,index_stats,
20161229 16:19:18,localhost.localdomain,root,localhost,47,862,drop,yhtest,t3,

As can be seen from the above, we need to generally use the query is enough, we need to note that we need to pay attention to the use of the plug-in disk space, if the database operations frequently, may generate a large number of logs!

Database built-in audit function