mairadb Audit Plugin简单说明

###安装插件

[mysqld]
...
plugin_load=server_audit=server_audit.so
...

###审计日志类型参数
server_audit_events：CONNECT,QUERY,TABLE
简单测试：

• CONNECT：只选择此值，会记录每次的连接创建和断开

20171219 11:19:08,hostname,root,localhost,24,0,DISCONNECT,,,0
20171219 11:19:09,hostname,root,localhost,25,0,CONNECT,,,0
20171219 11:19:31,hostname,root,localhost,25,0,DISCONNECT,,,0

• TABLE：只选择此值，不会记录连接记录

20171219 11:20:26,hostname,root,localhost,27,182,READ,username,t,
20171219 11:20:46,hostname,root,localhost,27,183,ALTER,username,t,
20171219 11:20:46,hostname,root,localhost,27,183,READ,username,t,

• QUERY：只选择此值，DML/DDL统一归纳为query，没有单独的操作状态提示

20171219 11:21:47,hostname,root,localhost,29,192,QUERY,username,'show tables',0
20171219 11:21:56,hostname,root,localhost,30,195,QUERY,,'select @@version_comment limit 1',0
20171219 11:21:58,hostname,root,localhost,30,196,QUERY,,'SELECT DATABASE()',0
20171219 11:21:58,hostname,root,localhost,30,198,QUERY,username,'show databases',0
20171219 11:21:58,hostname,root,localhost,30,199,QUERY,username,'show tables',0
20171219 11:22:17,hostname,root,localhost,30,201,QUERY,username,'alter table t drop index name',0

此参数默认值为空，即选择CONNECT,QUERY,TABLE

20171219 11:24:20,hostname,root,localhost,35,0,CONNECT,,,0
20171219 11:24:20,hostname,root,localhost,35,214,QUERY,,'select @@version_comment limit 1',0
20171219 11:24:24,hostname,root,localhost,35,215,QUERY,,'SELECT DATABASE()',0
20171219 11:24:24,hostname,root,localhost,35,217,QUERY,username,'show databases',0
20171219 11:24:24,hostname,root,localhost,35,218,QUERY,username,'show tables',0
20171219 11:24:29,hostname,root,localhost,35,220,READ,username,t,
20171219 11:24:29,hostname,root,localhost,35,220,QUERY,username,'select * from t',0
20171219 11:24:36,hostname,root,localhost,35,221,WRITE,username,t,
20171219 11:24:36,hostname,root,localhost,35,221,QUERY,username,'delete from t',0
20171219 11:24:59,hostname,root,localhost,35,222,ALTER,username,t,
20171219 11:24:59,hostname,root,localhost,35,222,READ,username,t,
20171219 11:24:59,hostname,root,localhost,35,222,QUERY,username,'alter table t add index (name)',0

完整的记录

记录开关参数

server_audit_logging：开启审计日志记录日志参数（ON/OFF）
###白名单参数
server_audit_incl_users：记录指定用户的操作
server_audit_excl_users：不去记录指定用户的操作
###日志相关参数
日志文件可以单独的存在一个文件中，或者与syslog放在同一个文件中

• 本地日志
  • server_audit_output_type：文件类型，file
  • server_audit_file_rotate_now：日志轮询写开关（默认off）（ps:测试发现参数可以动态调整不生效，但无论是on还是off好像只要指定了副本数后自动轮询。。测试版本10.1.22）
  • server_audit_file_path：日志文件名和位置
  • server_audit_file_rotate_size：日志文件大小，单位值byte
  • server_audit_file_rotations：轮询的文件个数
• 远程日志
  • server_audit_output_type：文件类型：syslog
  • server_audit_syslog_ident：如下面范例所示：mysql-server_auditing
  • server_audit_syslog_info：如下面范例所示：prod1

###其他参数
server_audit_query_log_limit：能够展现的最大sql长度，如果sql写的超过阀值，会被阶段。默认1024
server_audit_mode：没实际意义，内部参数

• syslog相关
  • server_audit_syslog_priority
  • server_audit_syslog_facility
  • server_audit_syslog_info

###本地输出审计日志格式

[timestamp],[serverhost],[username],[host],[connectionid],[queryid],CONNECT,[database],,0 
[timestamp],[serverhost],[username],[host],[connectionid],[queryid],DISCONNECT,,,0 
[timestamp],[serverhost],[username],[host],[connectionid],[queryid],FAILED_CONNECT,,,[retcode]
[timestamp],[serverhost],[username],[host],[connectionid],[queryid],CREATE,[database],[object], 
[timestamp],[serverhost],[username],[host],[connectionid],[queryid],READ,[database],[object], 
[timestamp],[serverhost],[username],[host],[connectionid],[queryid],WRITE,[database],[object], 
[timestamp],[serverhost],[username],[host],[connectionid],[queryid],ALTER,[database],[object], 
[timestamp],[serverhost],[username],[host],[connectionid],[queryid],RENAME,[database], 
[object_old]|[database_new].[object_new], 
[timestamp],[serverhost],[username],[host],[connectionid],[queryid],DROP,[database],[object],

###压测性能损耗

• 压测到服务器瓶颈
  

• audit开启
  

• general_log开启
  

注：测试线条比较粗，仅供参考