折腾了2个mysql优秀分支的审计插件,个人觉得mariadb的不错,比较直观,而percona的是类似于json格式的,可能对那些擅长文本处理的人比较合适,以下是2个插件的大概配置,其中没有对各个参数做出详细解释。如需更加详细内容,请参考官方文档。
Percona audit plugin:
mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';
mysql> show variables like '%audit%';
+---------------------------+---------------+
|Variable_name | Value |
+---------------------------+---------------+
|audit_log_buffer_size | 1048576 |
|audit_log_file |audit.log |
|audit_log_flush | OFF |
|audit_log_format | OLD |
|audit_log_handler | FILE |
|audit_log_policy | LOGINS |
|audit_log_rotate_on_size | 0 |
|audit_log_rotations | 0 |
|audit_log_strategy |ASYNCHRONOUS |
|audit_log_syslog_facility | LOG_USER |
|audit_log_syslog_ident | percona-audit|
|audit_log_syslog_priority | LOG_INFO |
+---------------------------+---------------+
12 rowsin set (0.00 sec)
值 | 含义 |
ASYNCHRONOUS | 异步日志,等待输出缓冲区空间 |
PERFORMANCE | 异步日志,如果在输出缓冲区空间不足则降低请求 |
SEMISYBCHRONOUS | 同步日志,使用操作系统缓存 |
SYNCHRONOUS | 同步日志,每个请求过后都会调用sync()方法 |
Mariadb audit plugin:
mysql> install plugin server_audit soname 'server_audit.so';
mysql> show variables like '%audit%';
+-------------------------------+-----------------------+
|Variable_name |Value |
+-------------------------------+-----------------------+
|server_audit_events | |
|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_loc_info | |
|server_audit_logging | OFF |
|server_audit_mode | 1 |
|server_audit_output_type | file |
|server_audit_query_log_limit | 1024 |
|server_audit_syslog_facility |LOG_USER |
|server_audit_syslog_ident |mysql-server_auditing |
|server_audit_syslog_info | |
|server_audit_syslog_priority |LOG_INFO |
+-------------------------------+-----------------------+
16 rowsin set (0.00 sec)
mysql> set global server_audit_events='connect,query_ddl';
也可写在配置文件中:
[mysqld]
server_audit_events=connect,query
Type | Description | Introduced |
CONNECT | Logs connects, disconnects and failed connects (including the error code). |
|
QUERY | Queries issued and their results (in plain text), including failed queries due to syntax or permission errors. |
|
TABLE | Which tables were affected by query execution. |
|
QUERY_DDL | Works as the 'QUERY' value, but filters only DDL-type queries (CREATE, ALTER, etc). | |
QUERY_DML | Works as the 'QUERY' value, but filters only DML-type queries (INSERT, UPDATE, etc). | |
QUERY_DCL | Works as the 'QUERY' value, but filters only DCL-type queries (GRANT, REVOKE, etc.) |
mysql> set global server_audit_logging=1;
timestamp | serverhost | username | host | connectionid | queryid | operation | database | object | retcode |
20160516 15:31:18 | centos-demo.ledo.com | thunder | 10.237.13.148 | 21 |
| QUERY |
| SHOW GLOBAL VARIABLES | 0 |
2016051615:31:18,centos-demo.ledo.com,thunder,10.237.13.148,21,193,QUERY,,'SHOW GLOBALVARIABLES',0
2016051616:03:27,centos-demo.ledo.com,root,localhost,15,840,QUERY,thunder,'showtables',0
https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/#installation