mairadb Audit Plugin简单说明

Bertha 。 2022-06-03 06:43 187阅读 0赞

\#\#\#安装插件

[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],

\#\#\#压测性能损耗

*  压测到服务器瓶颈  
    ![这里写图片描述][format_png]
 *  audit开启  
    ![这里写图片描述][format_png 1]
 *  general\_log开启  
    ![这里写图片描述][format_png 2]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoZW4yMzA4_size_16_color_FFFFFF_t_70]

[format_png]: /images/20220603/c8e763efa13d4400a589c911ad3edd6a.png
[format_png 1]: /images/20220603/8466114a0cb24c7fab47e043ebb18019.png
[format_png 2]: /images/20220603/330e35d6992f4c97b9af6521b85597f7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoZW4yMzA4_size_16_color_FFFFFF_t_70]: /images/20220603/8df12eb0d77d47e8b57e4b5577eb3898.png