mariadb可以启用审计插件来对数据库的各种操作进行审计，以防运维背锅！
启用MariaDB的审计插件，并调整相关参数
|
1
2
|
MariaDB [(none)]> show variables like
'%audit%'
;
Empty
set
(0.02 sec)
|
安装MariaDB审计插件
|
1
2
|
MariaDB [(none)]> INSTALL PLUGIN server_audit SONAME
'server_audit'
;
Query OK, 0 rows affected (0.41 sec)
|
查看安装审计插件后的变量
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
MariaDB [(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 | 0 |
| 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 |
|
设置相关参数及说明：
1.设置记录的事件
|
1
2
|
MariaDB [(none)]>
set
global server_audit_events=
'connect,query,table'
;
Query OK, 0 rows affected (0.00 sec)
|
注：connect,query,table可满足我们所有审计需求
相关事件说明可参考：https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/
2.指定不审计某些用户的操作
|
1
2
|
MariaDB [(none)]>
set
global server_audit_excl_users=
'liuwei'
;
Query OK, 0 rows affected (0.01 sec)
|
3.审计日志存放的位置
|
1
|
server_audit_file_path | server_audit.log
|
4.设置日志轮转
|
1
2
|
MariaDB [(none)]>
set
global server_audit_file_rotate_now=on;
Query OK, 0 rows affected (0.00 sec)
|
5.设置审计日志的大小
|
1
2
|
MariaDB [(none)]>
set
global server_audit_file_rotate_size=1024*1024*1024;
Query OK, 0 rows affected (0.03 sec)
|
6.开启审计日志
|
1
2
|
MariaDB [(none)]>
set
global server_audit_logging=on;
Query OK, 0 rows affected (0.00 sec)
|
7.设置日志可以轮转的个数
|
1
|
server_audit_file_rotations | 9
|
查看设置之后的参数：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
MariaDB [(none)]> show variables like
'%audit%'
;
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| server_audit_events | QUERY,TABLE |
| server_audit_excl_users | liuwei |
| server_audit_file_path | server_audit.log |
| server_audit_file_rotate_now | OFF |
| server_audit_file_rotate_size | 1073741824 |
| server_audit_file_rotations | 9 |
| server_audit_incl_users | |
| server_audit_loc_info | |
| server_audit_logging | ON |
| server_audit_mode | 0 |
| 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 |
+-------------------------------+-----------------------+
|
查看审计日志是否进行记录：可以记录增删改查所有操作
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
[root@test2 mysql]
# cat server_audit.log
20160705 16:25:24,test2,root,localhost,9,2544,QUERY,,
'set global server_audit_logging=on'
,0
20160705 16:25:38,test2,root,localhost,9,2545,QUERY,,
'show variables like \'%audit%\''
,0
20160705 16:28:06,test2,root,localhost,9,2546,QUERY,,
'SELECT DATABASE()'
,0
20160705 16:28:10,test2,root,localhost,9,2548,QUERY,,
'show databases'
,0
20160705 16:28:44,test2,root,localhost,9,2549,QUERY,,
'create database v1'
,0
[root@test2 mysql]
# tailf server_audit.log
20160705 16:25:24,test2,root,localhost,9,2544,QUERY,,
'set global server_audit_logging=on'
,0
20160705 16:25:38,test2,root,localhost,9,2545,QUERY,,
'show variables like \'%audit%\''
,0
20160705 16:28:06,test2,root,localhost,9,2546,QUERY,,
'SELECT DATABASE()'
,0
20160705 16:28:10,test2,root,localhost,9,2548,QUERY,,
'show databases'
,0
20160705 16:28:44,test2,root,localhost,9,2549,QUERY,,
'create database v1'
,0
20160705 16:31:20,test2,root,localhost,9,2552,QUERY,,
'set global server_audit_events=\'connect,query,table\''
,0
20160705 16:31:30,test2,root,localhost,9,2553,QUERY,,
'show variables like \'%audit%\''
,0
20160705 16:31:35,test2,root,localhost,9,0,DISCONNECT,,,0
20160705 16:31:51,test2,root,localhost,24,0,CONNECT,,,0
20160705 16:31:51,test2,root,localhost,24,2555,QUERY,,
'select @@version_comment limit 1'
,0
20160705 16:33:44,test2,root,192.168.10.215,25,0,FAILED_CONNECT,,,1045
20160705 16:33:44,test2,root,192.168.10.215,25,0,DISCONNECT,,,0
20160705 16:33:53,test2,liuwei,192.168.10.215,26,0,CONNECT,,,0
20160705 16:35:21,test2,root,localhost,24,2561,QUERY,,
'show variables like \'%audit%\''
,0
20160705 16:40:22,test2,root,localhost,24,2563,WRITE,mysql,user,
20160705 16:40:22,test2,root,localhost,24,2563,WRITE,mysql,db,
20160705 16:40:22,test2,root,localhost,24,2563,QUERY,mysql,
'grant all on *.* to hb@\'%\' identified by *****'
,0
|
在配置文件中配置审计参数，因为global重启后就失效：
[mysqld]
server_audit_events=‘CONNECT,QUERY,TABLE‘
server_audit_logging=on
server_audit_file_rotate_size=200000
server_audit_file_rotations=10
server_audit_excl_users='liuwei'
重启生效
本文转自激情燃烧的岁月博客51CTO博客，原文链接http://blog.51cto.com/liuzhengwei521/1882767如需转载请自行联系原作者
weilovepan520
