新年好~
本文主要介绍MySQL审计相关功能，审计功能虽然会造成额外的性能损耗，但在一些特定场景下，还是有应用需求的，且比开启general log要好用不少。MySQL除了企业版提供了审计功能，社区版或其他分支版本均需使用第三方审计插件。
当前所有第三方mysql审计插件：
①Percona Audit Log Plugin
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 Audit Log是Percona server的内置审计插件 此插件兼容性不高，仅适用于Percona server使用，安装到MySQL社区版上，筛选过滤语句类型时会出现问题
②McAfee MySQL Audit Plugin
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
MySQL Audit是McAfee公司基于percona开发的MySQL审计插件
McAfee的MySQL Audit插件只能以JSON格式保存，日志信息比较大，对性能影响较大，
不支持日志自动切割，且更新较慢，近期才支持5.7版本。③MariaDB Audit Plugin
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.
MariaDB Audit是MariaDB的内置审计插件
MariaDB_5.5.37版本和MariaDB_10.0.10以后版本的audit插件支持MariaDB, MySQL、
Percona Server使用本篇主要介绍①Percona Audit Log Plugin
安装插件：
install plugin audit_log soname 'audit_log.so'; in my.cnf [mysqld] plugin-load="audit_log=audit_log.so"
查看审计参数：
mysql> show global variables like '%audit%'; 5.7.18，5.7.19: +-----------------------------+---------------+ | Variable_name | Value | +-----------------------------+---------------+ | audit_log_buffer_size | 1048576 | | audit_log_exclude_accounts | | | audit_log_exclude_commands | | | audit_log_exclude_databases | | | audit_log_file | audit.log | | audit_log_flush | OFF | | audit_log_format | OLD | | audit_log_handler | FILE | | audit_log_include_accounts | | | audit_log_include_commands | | | audit_log_include_databases | | | audit_log_policy | ALL | | audit_log_rotate_on_size | 0 | | audit_log_rotations | 0 | | audit_log_strategy | ASYNCHRONOUS | | audit_log_syslog_facility | LOG_USER | | audit_log_syslog_ident | percona-audit | | audit_log_syslog_priority | LOG_INFO | +-----------------------------+---------------+ 5.6.27 +---------------------------+---------------+ | Variable_name | Value | +---------------------------+---------------+ | audit_log_buffer_size | 1048576 | | audit_log_file | audit.log | | audit_log_flush | OFF | | audit_log_format | OLD | | audit_log_handler | FILE | | audit_log_policy | ALL | | audit_log_rotate_on_size | 0 | | audit_log_rotations | 0 | | audit_log_strategy | ASYNCHRONOUS | | audit_log_syslog_facility | LOG_USER | | audit_log_syslog_ident | percona-audit | | audit_log_syslog_priority | LOG_INFO | +---------------------------+---------------+
参数解析：
audit_log_buffer_size：审计日志内存缓冲区大小，默认为1M，若要调整建议设置为4096的倍数。 仅对audit_log_strategy设置为ASYNCHRONOUS或PERFORMANCE策略才会有意义。 以下两个参数只生效一个： audit_log_exclude_accounts：控制哪些账户可以不进入审计，多个账户用逗号隔开。 audit_log_include_accounts：控制哪些账户进入审计，多个账户用逗号隔开。 以下两个参数只生效一个： audit_log_exlude_commands：控制那些命令不需要审计 audit_log_include_commands：控制那些命令需要审计 audit_log_file：审计日志路径。该值可以为相对于datadir的路径或绝对路径。 audit_log_flush：手动归档审计日志，一般不用 audit_log_format：审计日志格式，可配置： OLD/NWE:基于XML文件格式 JSON CSV audit_log_handler：审计日志以何种形式写入系统，可支持file,syslog audit_log_policy参数值:ALL,LOGINS,QUERY,NONE ALL:记录所有(包括登录登出信息，操作明细等) LOGINS：仅记录登录登出信息（客户端ip，登录用户，登录登出时间） QUERY:仅记录查询事件 NONE:不记录任何时间 选择ALL的话，会记录所有操作，哪怕敲错了命令也会记录。会生成大量审计日志，若无必要，尽量不要设置为ALL。 audit_log_rotate_on_size:单个审计日志文件增加到指定大小，会自动切分，轮换日志文件。 audit_log_rotations：与参数audit_log_rotate_on_size同时使用，限制要保留的日志文件数。 audit_log_strategy：控制日志文件以何种方式刷新： ASYNCHRONOUS：使用内存缓冲区，若缓冲区已满，也不会丢弃事件 PERFORMANCE：使用内存缓冲区，若缓冲区已满，则丢弃事件 SEMISYNCHRONOUS：不使用内存缓冲区，直接记录到文件，且不会fsync事件 SYNCHRONOUS：不适用内存缓冲区，直接记录到文件，且fsync每个事件 以下三个参数不需要修改： audit_log_syslog_facility audit_log_syslog_ident audit_log_syslog_priority
配置持久化：
防止数据库重启后审计参数失效，建议将修改的配置参数持久化到my.cnf中：
## Audit Logging ## audit_log_policy=LOGINS audit_log_format=OLD audit_log_file=/mysql/data/audit.log audit_log_rotate_on_size=1024M audit_log_rotations=10
审计日志明细:
由参数解析中讲到的，审计日志可以支持多种形式，一般在datadir目录下的audit.log中，下面展示XML和JSON两种格式：
XML:
JSON:
{"audit_record":{"name":"Connect","record":"29_2019-01-29T07:24:06","timestamp":"2019-01-29T07:24:14 UTC","connection_id":"13","status":0,"user":"sndsadmin","priv_user":"sndsadmin","os_login":"","proxy_user":"","host":"","ip":"10.37.123.181","db":""}}
