mysql audit log_关于MySQL AUDIT(审计)那点事

2017年06月02日MySQL社区版本最新版为MySQL_5.7.18,但是该版本不带AUDIT功能(MySQL Enterprise Edition自带AUDIT功能)，

因此需要加载plugin(第三方插件)，当前插件有以下几种：

1、MySQL Enterprise Audit Plugin – This plugin is not open source and is only available with MySQL Enterprise, which has a significant cost attached to it. It is the most stable and robust.

备注：MySQL企业版本才能使用这个audit插件哦，MySQL社区版慢慢等着吧

2、Percona Audit Log Plugin – Percona provides an open source auditing solution that installs with Percona Server 5.5.37+ and 5.6.17+. This plugin has quite a few output features as it outputs XML, JSON and to syslog. Percona’s implementation is the first to be a drop-in replacement for MySQL Enterprise Audit Plugin. As it has some internal hooks to the server to be feature-compatible with Oracle’s plugin, it is not available as a standalone for other versions of MySQL. This plugin is actively maintained by Percona.

备注：人家说了，我这个插件只能给Percona_sever使用，我Percona来维护

3、McAfee MySQL Audit Plugin – Around the longest and has been used widely. It is open source and robust, while not using the official auditing API. It isn’t updated as often as one may like. There hasn’t been any new features in some time. It was recently updated to support MySQL 5.7

下载地址：http://dl.bintray.com/mcafee/mysql-audit-plugin/

部署可参考：http://blog.csdn.net/bzfys/article/details/53695855

个人发现该插件貌似不支持审计日志自动切割，而且日志格式为JSON格式，个人感觉不易查看，以及时间格式需要转换

eg："msg-type":"header","date":"1494935783266","audit-version":"1.1.4-707","audit-protocol-version":"1.0","hostname":"salt-master","mysql-version":"5.7.18-log","mysql-program":"/usr/sbin/mysqld","mysql-socket":"/data/mysql/mysql.sock","mysql-port":"3306","server_pid":"43480"}

4、MariaDB Audit Plugin – The only plugin that claims to support MySQL, Percona Server and MariaDB. It is open source and constantly upgraded with new versions of MariaDB. Versions starting at 1.2 are most stable, and it may be risky to use versions below that in your production environment. Versions below 1.2 may be unstable and I have seen it crash production servers. Older versions also log clear text passwords.

下载地址：https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/ (可以直接下载MariaDB对应的版本后，解压后在plugin目录下有server_audit.so插件)

MariaDB_5.5.37版本和MariaDB_10.0.10以后版本的audit插件支持MariaDB, MySQL、Percona Server使用

备注：MariaDB_5.x.x和MariaDB_10.x.x区别

MariaDB_5.x.x：兼容MySQL5.x.x的，接口几乎一致，只限于社区版

MariaDB_10.x.x：10.x.x使用新技术，接口会与mysql逐渐区别开来。目标就是以后想MariaDB新接口过渡

因此综合以上，我个人选择了MariaDB Audit Plugin按安装到我的MySQL_5.7.18上，以下为具体部署操作：

1、下载mariadb-5.5.56-linux-x86_64.tar.gz解压获取server_audit.so插件

2、登录MySQL，执行命令获取MySQL的plugin目录

mysql> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';

+---------------+--------------------------+

| Variable_name | Value |

+---------------+--------------------------+

| plugin_dir | /usr/lib64/mysql/plugin/ |

+---------------+--------------------------+

1 row in set (0.02 sec)

3、将server_audit.so上传到 /usr/lib64/mysql/plugin/下

4、在命令下安装server_audit.so

mysql> INSTALL PLUGIN server_audit SONAME 'server_audit.so';

5、查看变量开启设置情况，默认貌似都是关闭的

mysql> show variables like '%audit%';

6、编辑my.cnf,添加配置

server_audit_events='CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML,QUERY_DCL'

备注：指定哪些操作被记录到日志文件中

server_audit_logging=on

server_audit_file_path =/data/mysql/auditlogs/

备注：审计日志存放路径，该路径下会生成一个server_audit.log文件，就会记录相关操作记录了

server_audit_file_rotate_size=200000000

server_audit_file_rotations=200

server_audit_file_rotate_now=ON

7、重启服务，service mysqld restart

登录MySQL后发现，在MySQL环境下执行的任何命令都被记录到/data/mysql/auditlogs/server_audit.log，如果日志文件达到指定的大小，会自动切割

mysql> show variables like '%audit%';

+-------------------------------+---------------------------------------------------+

| Variable_name | Value |

+-------------------------------+---------------------------------------------------+

| server_audit_events | CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML,QUERY_DCL |

| server_audit_excl_users | |

| server_audit_file_path | /data/mysql/auditlogs/ |

| server_audit_file_rotate_now | ON |

| server_audit_file_rotate_size | 200000000 |

| server_audit_file_rotations | 200 |

| server_audit_incl_users | |

| server_audit_loc_info | |

| server_audit_logging | ON |

| 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 |

+-------------------------------+---------------------------------------------------+

日志为：

20170516 23:21:23,salt-master,audit_log_user,localhost,4,19,QUERY,,'show variables like \'%audit%\'',0

8、参数说明：

详细请参考：https://mariadb.com/kb/en/mariadb/server_audit-system-variables/

server_audit_output_type：指定日志输出类型，可为SYSLOG或FILE

server_audit_logging：启动或关闭审计

server_audit_events：指定记录事件的类型，可以用逗号分隔的多个值(connect,query,table)，如果开启了查询缓存(query cache)，查询直接从查询缓存返回数据，将没有table记录

server_audit_file_path：如server_audit_output_type为FILE，使用该变量设置存储日志的文件，可以指定目录，默认存放在数据目录的server_audit.log文件中

server_audit_file_rotate_size：限制日志文件的大小

server_audit_file_rotations：指定日志文件的数量，如果为0日志将从不轮转

server_audit_file_rotate_now：强制日志文件轮转

server_audit_incl_users：指定哪些用户的活动将记录，connect将不受此变量影响，该变量比server_audit_excl_users优先级高

server_audit_syslog_facility：默认为LOG_USER，指定facility

server_audit_syslog_ident：设置ident，作为每个syslog记录的一部分

server_audit_syslog_info：指定的info字符串将添加到syslog记录

server_audit_syslog_priority：定义记录日志的syslogd priority

server_audit_excl_users：该列表的用户行为将不记录，connect将不受该设置影响

server_audit_mode：标识版本，用于开发测试

9、卸载server_audit

mysql> UNINSTALL PLUGIN server_audit;

mysql> show variables like '%audit%';

Empty set (0.00 sec)

防止server_audit 插件被卸载，需要在配置文件中添加:

[mysqld]

server_audit=FORCE_PLUS_PERMANENT

重启MySQL生效

值得注意的是，应该在server_audit插件被安装好，并且已经运行之后添加这些配置，否则过早在配置文件添加这个选项，会导致MySQL发生启动错误！

mysql> UNINSTALL PLUGIN server_audit;

ERROR 1702 (HY000): Plugin 'server_audit' is force_plus_permanent and can not be unloaded

。