Mysql用户审计方法简介

Mysql用户审计方法主要是以下几种：

1）init-connec+binlog

此方法是比较常见的一种。由于MySQL binlog记录了所有对数据库长生实际修改的sql语句，及其执行时间，和connect_id。但是没有记录connection_id对应的详细用户信息。在后期审计进行行为追踪时，根据binlog记录的行为及对应的connection-id，结合之前连接日志记录进行分析，得出最后的结论。

该方法需要新建一个数据库来记录审计信息，只会在用户每次连接时往数据库里插入一条记录，不会对数据库产生很大的影响，除非连接频率非常高。不过这种方法无法记录到数据库超级权限用户的操作记录。在本次研究中不采用这种方法。

2）Mysql官方插件MySQL Audit Plugin

从5.5版本开始，Oracle开放了Audit Plugin API，可以让所有的MySQL用户编写他们自己的审计插件。Oracle还从5.5.28版本开始在企业版MySQL提供了官方审计插件——MySQL Enterprise Audit Plugin，易上手，可以基于策略进行审计，但是需要收费。

3）Mcafee推出的Mcafee MySQL Audit Plugin

基于THD的Offset，从内存对象中直接获取信息，因此日志可以获取更多的内核信息。只要得到offset，理论上就可以支持所有的MySQL版本（包括各个分支）。

4）MariaDB的Audit插件

由Monty Program AB和SkySQL Ab开发，能记录用户连接到mysql服务器，记录用户对数据库或表的操作行为，最重要的它能和syslog协作，把记录日志发送到本地的syslogd。

5）Percona Server自带的Audit Plugin

是Percona Server用于替代MySQL Enterprise Audit Log Plugin的审计插件，相比后者它是基于GPL开源的。但是支持的版本为5.5.37及以上。

在本次研究中，主要是对Mcafee MySQL Audit Plugin、MariaDB MySQL Audit Plugin、Percona Server Audit Plugin进行对比测试。

安装及应用

2.1 Mcafee的MySQL Audit Plugin

2.1.1 下载并安装

https://bintray.com/mcafee/mysql-audit-plugin/release

根据我们实验用mysql版本，选择audit-plugin-mysql-5.5-1.0.6-491-linux-x86_64.zip

# Unzip audit-plugin-mysql-5.5-1.0.6-491-linux-x86_64.zip

其中lib/libaudit-plugin.so是我们需要安装的二进制文件。

获取当前mysql插件目录路径，并将解压的lib/libaudit-plugin.so复制到该目录下

由于Mcafee Audit Plugin我们采用的Percona Server 5.5.27默认没有受到Mcafee Audit Plugin很好的支持，安装起来有点麻烦，不能直接在mysql里面简单安装。为了安装上，需要获得一个抓取我们mysql版本的offset以及Percona-Server-55-debuginfo-5.5.27-rel29.0.315.rhel6.x86_64.rpm

offset-extract.sh脚本下载地址：

https://raw.github.com/mcafee/mysql-audit/master/offset-extract/offset-extract.sh

执行./offset-extract.sh usr/local/mysql/bin/mysqld usr/lib/debug/usr/sbin/mysqld.debug

# ./offset-extract.sh usr/local/mysql/bin/mysqld usr/lib/debug/usr/sbin/mysqld.debug 
//offsets for: usr/local/mysql/bin/mysqld (5.5.27-log)
{"5.5.27","7e4bfe01ba083a67abcd343e5f8d05ef", 6464, 6512, 4072, 4512, 104, 2584, 8, 0, 16, 24},

抓取到的offset如下

{"5.5.27","7e4bfe01ba083a67abcd343e5f8d05ef", 6464, 6512, 4072, 4512, 104, 2584, 8, 0, 16, 24},

修改/etc/my.cnf的设置，在mysqld模块里面添加

#audit_config
plugin-load=AUDIT=libaudit_plugin.so
audit_offsets=6464, 6512, 4072, 4512, 104, 2584, 8, 0, 16, 24

重启mysql就可以安装上插件了。

查看是否安装上了插件

2.1.2 设置审计参数

• 查看有关审计的参数

我们需要关注的参数有：

#是否开启audit功能
audit_json_file

#记录文件的路径和名称信息 
audit_json_log_file

#记录的命令,默认为记录所有命令，可以设置为任意dml、dcl、ddl的组合
#如：audit_record_cmds=select,insert,delete,update#
还可以在线设置set global audit_record_cmds=NULL (表示记录所有命令)
audit_record_cmds

#记录操作的对象,默认为记录所有对象
#可以用SET GLOBAL audit_record_objs=NULL设置为默认
#也可以指定为audit_record_objs=,test.*,mysql.*,information_schema.*
audit_record_objs

#用户白名单
audit_whitelist_users

• 设置如下：

Set global audit_json_log_file=audit.log
Set audit_json_file=ON 开启审计日志
Set global audit_json_file_sync=30 每30秒同步日志到磁盘，默认为0Set audit_record_cmds=’select,create,delete,drop,insert,update’ 记录的命令
Set global audit_record_objs=’*.*’//记录操作对象数据库

2.2 MariaDB的MySQL Audit Plugin

2.2.1 下载并安装

https://mariadb.com/products/connectors-plugins

我们选择了server_audit-1.1.7.tar.gz

解压后得到插件server_audit.so，将其复制到MySQL的插件目录下

# tar –zxvf server_audit-1.1.7.tar.gz# 
cp server_audit-1.1.7/linux-64/server_audit.so usr/local/mysql/lib/mysql/plugin/

启用插件

Mysql> INSTALL PLUGIN server-audit SONAME ‘server_audit.so’;

2.2.2 设置插件参数

设置日志写到syslog

Mysql> SET GLOBAL server_audit_output_type=SYSLOG;
Mysql> SET GLOBAL server_audit_events='CONNECT,QUERY';
Mysql> SET GLOBAL server_audit_logging=on;

查看配置

查看插件运行状态

注意：为了将审计日志记录到syslog，需要确保rsyslog在运行，日志写到/var/log/messages是由/etc/rsyslog.conf配置的。如果想将日志写到不同的文件，可以这样配置：if $programname == 'mysql-server_auditing' then var/log/mariadbaudit1

2.3 安装Percona Server的审计插件

2.3.1 下载安装

由于Percona Server从5.5.37版本就自带有审计插件audit_log.so，所以不需要特别下载。

激活插件：Mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so'; 或者修改my.cnf

[mysqld]
plugin-load="audit_log=audit_log.so"

查看是否安装成功

mysql> SHOW PLUGINSG
...
*************************** 38. row ***************************
Name: audit_log
Status: ACTIVE
Type: AUDIT
Library: audit_log.so
License: GPL38 rows in set (0.00 sec)

2.3.2 参数配置

查看可配置变量

日志同步模式：

·       ASYNCHRONOUS 异步模式，日志使用内存缓存, 缓存占满的情况下不会丢掉事件

·       PERFORMANCE 性能模式，日志使用内存缓存，缓存爆满会丢掉事件

·       SEMISYNCHRONOUS 半同步模式，日志直接刷入文件，不一定同步到所有的事件

·       SYNCHRONOUS 同步模式，直写文件，同步所有的事件

备注：依据自身业务需求使用SET GLOBAL xxx=’’ 进行变更。

性能评估

3.1 测试目的

为了了解这几种mysql审计方法对我们mysql及服务器的性能影响，评估其优缺点，已确认其是否符合我们的线上应用要求。

3.2 测试环境

硬件配置(Vmware)：

CPU：Intel(R) Core(TM) i3-3120M CPU @ 2.50GHz

  内存：1G

  硬盘：20GB

测试系统/软件环境：

3.3 测试方法

测试不同的插件在高并发的情境下的性能，并重点针对Percona Server自带的插件的性能，探讨四种日志同步模式的性能表现。

3.3.1 高并发情景下的各个插件的性能评测

由于目前的Percona Server5.5.40版本尚无法准确获取它的THD offset，无法安装Mcafee Audit Plugin，而线上应用的定制系统所安装的Percona Server版本未达到5.5.37要求而无法安装Percona Audit Plugin。

所以分为两组测试：

第一组在环境N1（上表）测试，测试的插件为Mcafee Audit Plugin和MariaDB Audit Plugin；

第二组在环境N2（上表）测试，测试的插件为Maria Audit Plugin和Percona Server Audit Plugin。

测试语句：

# mysqlslap -a --concurrency=100,300,500 --debug-info -uroot -p`cat data/save/mysql_root`

• 第一组测试：

1）没开启审计插件的情况下

平均响应时间：0.494s、3.693s，8.503s

2）开启Mcafee Audit Plugin的情况下：

平均响应时间：1.676s，4.150s，11.963s

3）开启MariaDB Audit Plugin的情况下：

平均响应时间：0.464s、3.670s，9.182s

对比日志大小：

（1）Mcafee Audit Plugin测试前日志大小：

测试后日志大小：

12046条日志，3.6M

（2）MariaDB Audit Plugin测试前的日志大小：

测试后的日志大小：

12014条日志，2.0M。

• 第二组测试

1）没有开启审计插件的情况下：

平均响应时间：0.420s，2.980s，6.918s

2）开启MariaDB Audit Plugin的情况下

平均响应时间：1.410s，4.112s，8.778s

3）开启Percona Audit Plugin的情况下：

对比日志大小：

（1）MariaDB Audit Plugin测试前的日志大小：

测试后的日志大小：

日志12009条，大小增加了2M。

（2）Percona Audit Plugin测试前的日志大小：

测试后：

12017条日志，增加到3.9M。

3.3.2 Percona Audit Plugin的四种日志同步模式下的性能评测

由于Percona Audit Plugin的日志的内存到磁盘的同步模式具有四种模式（见2.3.2节），各种模式对mysql的性能影响不一，所以有必要对四种模式进行一次性能评测。

评测方法：

统一OLD格式记录，500并发数，评测四种模式的性能表现。

# mysqlslap -a --concurrency=500 --debug-info -uroot -p`cat data/save/mysql_root`

评测结果：

1）未开启插件的情况下：

IO情况（vmstat –n 1）：

2）ASYNCHRONOUS模式：

IO情况（vmstat –n 1）：

3）PERFORMANCE模式

IO情况（vmstat –n 1）：

4）SEMISYNCHRONOUS模式

IO情况（vmstat –n 1）：

5）SYNCHRONOUS模式

IO情况（vmstat –n 1）：

3.4 测试结果

1）  三种插件的对比

第一组测试发现，MariaDB的性能比Mcafee的性能表现要好很多。日志大小方面，MariaDB比Mcafee的审计日志要小，这跟两者选择的日志格式有关。

第二组测试发现，MariaDB Audit Plugin和Percona Audit Plugin的性能相近，日志大小方面，MariaDB略胜一筹。

2）  Percona Audit Plugin的四种日志同步模式对比

在测试中，四种模式，Performance的响应时间最短，ASYNCHRONOUS模式次之，SEMISYNCHRONOUS模式第三，SYNCHRONOUS模式表现最差。根据Percona Server官网的相关评测，Percona Server的自带审计插件的性能表现如图，与本次试验的结果基本一致。

IO方面，PERFORMANCE、ASYNCHRONOUS、SEMISYNCHRONOUS这三种模式的IO损耗基本接近，SYNCHRONOUS损耗最大。

图3-1 来自官方的四种模式评测结果

分析总结

可以看出，开启了审计后，mysql的性能会发生减退现象，在本次试验中大概有20%~25%左右的衰减幅度。

Mcafee的审计插件目前尚未支持Percona Server 5.5.40版本，无法抓取到相应的THD offset，未能与Percona Audit Plugin一较高下。但是Mcafee审计插件在性能上落后与MariaDB的审计插件，在线上应用中可以不考虑此插件。

MariaDB Audit Plugin本次评测中性能上表现略优于Percona Audit Plugin，但是有一个缺点，就是无法记录”USE DATABASE XXX;“究竟用了哪个数据库（如下图所示），尤其对于我们多个游戏库表结构相近的情况下，很难判断用户动了哪个库。

Percona Server的自带审计插件性能上没有MariaDB Audit Plugin表现那么优秀，日志格式内容比较丰富，由于自带标签方便我们针对性进行处理，但日志容量增长会相对较快。Percona Server的审计插件不适用于我们的5.5.27版本的数据库，插件加入后会导致mysql不断重启。

综上所述，Percona Audit Plugin和MariaDB Audit Plugin性能相近，但各有缺点，Mcafee的兼容性较差，性能不够优秀，如果线上应用的话，推荐Percona Audit Plugin。

• 参考文献

1《Percona Server官网关于mysql审计的一些博客文章》http://www.percona.com/blog/search/audit/

END

如果你喜欢我们的文章，请转发到朋友圈