MySQL Add Audit function

MySQL Community Edition does not have its own design features or plugins. The research found that MARIADB's audit plugin also works with MySQL, supporting finer-grained audits, such as just auditing DDL operations to meet our needs. Because a table structure of the recently tested environment is frequently changed and data is emptied, the MARIADB plug-in is used to audit the DDL

MariaDB Audit Plugin Official website

MariaDB Audit Plugin

View MySQL's plugin path

Mysql>Show global variables like '%plugin%';+---------------+------------------------------+|Variable_name|Value|+---------------+------------------------------+|Plugin_dir| /Usr/Local/Mysql/Lib/Plugin/ |+---------------+------------------------------+1Rowinch Set(0.00sec) MySQL> Selectversion ();+------------+|Version ()|+------------+| 5.6. --Log |+------------+1Rowinch Set(0.00Sec

I choose to download the plugin version file for server_audit-1.4.0.tar.gz

Copy the plugin file server_audit.so to the MySQL plug-in file directory after decompression

Installation

MySQL>'server_audit.so'0 rows affected (0.02 sec )
#在线安装加载插件重启后会失效, you can configure it in the configuration file

...
Plugin_load=server_audit=server_audit.so

Configuring Audit Entries

# After installation, the relevant configuration items have show GLOBAL VARIABLES like 'server_audit%';+-------------------------------+-----------------------+|Variable_name|Value|+-------------------------------+-----------------------+|Server_audit_events|Connect,query,TABLE   ||Server_audit_excl_users|                       ||Server_audit_file_path|Server_audit.Log      ||Server_audit_file_rotate_now| OFF                   ||Server_audit_file_rotate_size| 1000000               ||Server_audit_file_rotations| 9                     ||Server_audit_incl_users|                       ||Server_audit_logging|  on                    ||Server_audit_mode| 0                     ||Server_audit_output_type| file                  ||Server_audit_query_log_limit| 1024x768                  ||Server_audit_syslog_facility|Log_user||Server_audit_syslog_ident|Mysql-Server_auditing||Server_audit_syslog_info|                       ||Server_audit_syslog_priority|Log_info|+-------------------------------+-----------------------+

Set according to our needs

MySQL>set global server_audit_events='query_ddl,table' ;         0 rows affected (0.00  sec) MySQL>set global Server_audit_ Logging=on0 rows affected (0.00 sec)

After the setup is complete, the audit log for the DDL

20180416 : mysql-5.6dev.yz,root,localhost,34950852,21554, Query,test,'truncate table t1',0

About server_audit_events Optional parameters are connect: All connections are logged, including the log of failed and closed connections, as recorded in the log, but not for us

[[email protected] 3306_develop]# TAILF Server_audit.Log             20180416  One: A: theMysql-5.6. Dev.yz,root,10.211.253.104,34950731,0, Connect,test,,020180416  One: A: -Mysql-5.6. Dev.yz,admin,10.211.253.153,34950655,0, Disconnect,test,,020180416  One: A: -Mysql-5.6. Dev.yz,admin,10.211.253.153,34950732,0, Connect,test,,020180416  One: A: theMysql-5.6. Dev.yz,admin,10.211.253.101,34950664,0, Disconnect,test,,0

MySQL Add Audit function