官网
https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/
一、环境说明
MySQL 5.6.25 社区版
Mariadb 10.0.25 社区版
mysql 企业版有审计功能需要收费，社区版被阉割的不行不行了，和古时候的太监
没啥区别了，比较重要的功能特性都没有，不过也能凑合着用。可能说的有点过，
话糙理不糙，建议用不起企业版的可以考虑percona 和 mariadb，个人建议，仅供参考。
二、安装
1.下载mariadb 10.0.25_x64 安装包，解压缩上传server_audit.so 文件到/tmp 目录下
2. 查看插件路径
|
1
2
3
4
5
6
7
|
(root@localhost) [(none)]> show variables like
'plugin_dir'
;
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| plugin_dir |
/usr/lib64/mysql/plugin/
|
+---------------+--------------------------+
1 row
in
set
(0.00 sec)
|
3.cp 插件，授予执行权限
|
1
2
3
|
# cp /tmp/server_audit.so /usr/lib64/mysql/plugin/
# chmod +x /usr/lib64/mysql/plugin/server_audit.so
#
|
4.安装插件
|
1
2
|
(root@localhost) [(none)]>
install
plugin server_audit SONAME
'server_audit.so'
;
Query OK, 0 rows affected (0.00 sec)
|
5.查看参数
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
(root@localhost) [(none)]> 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 rows
in
set
(0.00 sec)
|
6.修改参数
|
1
2
3
4
|
(root@localhost) [(none)]>
set
global server_audit_logging=on;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [(none)]>
set
global server_audit_file_rotate_now=ON;
Query OK, 0 rows affected (0.00 sec)
|
7.查看审计日志路径
|
1
2
|
# find / -name server_audit.log
/data/mysqldata/server_audit
.log --默认在数据路径下
|
|
1
2
3
4
5
|
# tail -f /data/mysqldata/server_audit.log
20161226 22:46:38,mysql91,root,localhost,1,3,QUERY,,
'set global server_audit_logging=on'
,0
20161226 22:46:58,mysql91,root,localhost,1,4,QUERY,,
'show variables like \'%audit%\''
,0
20161226 22:48:36,mysql91,root,localhost,1,5,QUERY,,
'show databases'
,0
20161226 22:49:27,mysql91,root,localhost,1,6,QUERY,,
'GRANT ALL PRIVILEGES ON *.* TO \'roidba\'@\'%\' IDENTIFIED BY PASSWORD *****'
,0
|
三、正式环境需要解决两个问题
1.日志路径要规范
|
1
2
|
(root@localhost) [(none)]>
set
global server_audit_file_path=
'/data/mysqllogs/'
;
Query OK, 0 rows affected (0.00 sec)
|
2.通常查询不需要审计(mysql 测试过滤不掉，mariadb 可以)
|
1
2
|
(root@localhost) [(none)]>
set
global server_audit_events=
'query_ddl,query_dml'
;
Query OK, 0 rows affected (0.00 sec)
|
3.固化参数
|
1
2
3
4
5
6
7
|
vi
/etc/my
.cnf
########mariadb 10.0.25 audit plugin#######
server_audit_logging=on
server_audit_events=
'query_dml,query_ddl'
server_audit_file_path =
/data/mysqllogs/
server_audit_file_rotate_size=200000000
server_audit_file_rotations=200
|
4.建议重启
|
1
|
service mysql restart
|
本文转自 roidba 51CTO博客，原文链接：http://blog.51cto.com/roidba/1886301，如需转载请自行联系原作者
