mysql审计插件--MariaDB Audit Plugin

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.youkuaiyun.com/crj_9527/article/details/50789816

1.下载地址：https://mariadb.com/my_portal/download/audit_plugin

• 先要注册

2.安装插件

• 通过show variables like ‘plugin_dir’;查看你的插件目录，我的是：plugin_dir    | /usr/lib64/mysql/plugin/

• 把下载好的插件“server_audit.so”复制到/usr/lib64/mysql/plugin/

• 登录mysql执行插件安装命令：INSTALL PLUGIN server_audit SONAME ‘server_audit.so’;

• 插件安装成功后有这些全局变量：show variables like ‘%audit%’;

3.设置审计插件变量

参考官方文档： https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/

https://mariadb.com/kb/en/mariadb/server_audit-system-variables/#server_audit_output_type

mysql> show variables like ‘%audit%’;

±------------------------------±--------------------------------+

| Variable_name                 | Value                           |

±------------------------------±--------------------------------+

| server_audit_events           | QUERY_DDL                       |

| server_audit_excl_users       |                                 |

| server_audit_file_path        | /var/log/mysql/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             | 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                        |

±------------------------------±--------------------------------+

关键参数：

server_audit_file_path：如果server_audit_output_type=file，设置为日志路径

server_audit_logging：必须要开启才记录日志

server_audit_events：有如下选项，多个用逗号隔开

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.)

4.查看审计日志

[root@EC-Web3 mysql]# tail -f /var/log/mysql/server_audit.log

20160225 18:00:11,EC-Web3,lepus,localhost,2007953,235303,QUERY,lepus,‘truncate table mysql_variables’,0

20160225 18:00:26,EC-Web3,lepus,localhost,2009349,243633,QUERY,lepus,‘truncate table mysql_variables_mid’,0

20160225 18:01:26,EC-Web3,lepus,localhost,2009487,244389,QUERY,lepus,‘truncate table mysql_variables’,0

20160225 18:01:42,EC-Web3,lepus,localhost,2010804,252290,QUERY,lepus,‘truncate table mysql_variables_mid’,0

20160225 18:02:42,EC-Web3,lepus,localhost,2010942,253046,QUERY,lepus,‘truncate table mysql_variables’,0

20160225 18:02:57,EC-Web3,lepus,localhost,2012259,260947,QUERY,lepus,‘truncate table mysql_variables_mid’,0

20160225 18:03:57,EC-Web3,lepus,localhost,2012397,261702,QUERY,lepus,‘truncate table mysql_variables’,0

20160225 18:04:12,EC-Web3,lepus,localhost,2013714,269603,QUERY,lepus,‘truncate table mysql_variables_mid’,0

20160225 18:05:12,EC-Web3,lepus,localhost,2013852,270358,QUERY,lepus,‘truncate table mysql_variables’,0

20160225 18:05:27,EC-Web3,lepus,localhost,2015169,278259,QUERY,lepus,‘truncate table mysql_variables_mid’,0

5.问题

• server_audit_file_rotate_now设置为ON，循环写日志，值还是OFF，但是已经生效，是不BUG？

• 对于审计日志，应该对每个用户设置一个帐号，不然没有意义