Shulou(Shulou.com)06/01 Report--

As a dba, sometimes, we will always encounter a database, a table, a field exception, or data has been inexplicably deleted, at this time we all infer whether it is bug, ah, is hacked. At this time, an audit function is highlighted.

The mariadb database supports the audit plug-in (server_audit.so) quite well. Today we will do a simple installation and test.

The installation method is also simple: INSTALL SONAME "server_audit.so"

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

These parameters and variables can be set dynamically. For more information, please see the official website: https://mariadb.com/kb/en/mariadb/server_audit-system-variables/

Generally speaking, we only need to set a few parameters.

Set global server_audit_events = QUERY / * this parameter has three values connect,table,query

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

Set global server_audit_file_rotations = 200

Set global server_audit_file_path = "/ data/mysql/auditlog/server_audit.log" / * the log file path must ensure that the / data/mysql/auditlog/ path exists! And chown mysql.mysql, otherwise the audit function will be turned on, which will cause mysql to report an error or even hang up the service!

Set global server_audit_logging = 1 / * enable the audit function is strongly recommended: do not enable it until you have set the following parameters

After the setting is 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 take a look at the actual effect. Let's compare the log comparison of three different values of the parameter server_audit_events, connet,table,query.

1. When the parameter is server_audit_events= query

Log content:

20161229 11VRV 35VOR 39recorder localhost. LocaldomainLocalhost42J0745QUERYREQUERYJI mysqlMAX show databases',0

20161229 111VRV 35VOR 55recorder localhost.localdomainLocalhost.rootMore localhost42pr 746MUERYJI mysqlMagol create database yhtest',0

20161229 11RV 36VOR 03 localhost.localdomainLocalhostLocalhostLocalhost42 747 QUERYMARY mysql DATABASE select ()', 0

20161229 11VRV 36VOR 03 localhost.localdomainLocalhost42 42QUERYQUERYREY YHTESTRIME show show databases',0

20161229 11VRV 36VOR 03 localhost.localdomainLocalhost42 42QUERYJY YHTESTRIME show show tables',0

20161229 11RV 36 int 42 localhost.localdomain localhost.localhost 42 751 QUERYQ yhtest.creating table yhtest (localhost, b int)', 1064

20161229 111536 int 56 localhost.localdomain localhost.localhost.localhost42 752 QUERYQ yhtest.creating table yhtest (localhost, b int)', 0

20161229 111REV 37JZ 35 localhost.localdomainLocalhost.localdomain42and753 QUERYYTESTRIMENTION insert into yhtest value (1), (2), (3)', 0

20161229 11VUR 37VOR 46, localhost. Localdomain.root.localdomainLocalhost42, 754, QUERYJY yhtest.select * from yhtest',0

20161229 111438 from yhtest where 07 localhost.localdomainlocalhost42localhost42QUERYQ yhtest.delete localhost.localdomainlocalhost.localdomain.localhost.localdomain.localhost.localhost

20161229 1114 1415 localhost. Localdomain.localdomain42localhost.756 table yhtest',0 QUERYJING yhtestrecording drop table yhtest',0

20161229 1515 Velocity 45 server% 07 localhost.localdomain localhost.localhost.localhost42 757 QUERYMagi yhtest.show show variables like\ 'localhost\', 0

two。 When the parameter is server_audit_events=connect, 20161229 16 displacement 09 Flux 50 localhost. Localdomain.localdomain.rootwrit.localhost.42 localhost.DISCONNECTDISCONNECTDISCONECTDISCONECT.yhtestLECHERONE 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 14 52 localhost. Localdomainretro localhost 47 7pr 857 pr yhtestret 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 query is enough, it should be noted that we need to pay attention to disk space when using this plug-in, if the database operations are frequent, a large number of logs may be generated!

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.