MYSQL 的审计日志插件

MYSQL 的审计日志插件，可惜目前只是LINUX用：
来自McAfee的MySQL插件，为MySQL提供审计功能，重点是安全性和审计要求。该插件可以用作独立的审核解决方案，也可以配置为将数据提供给外部监视工具。

插件下载地址：

https://bintray.com/mcafee/mysql-audit-plugin/release/1.1.4-725#files

首先查看mysql的插件保存目录：

mysql> show global variables like 'plugin_dir';
+---------------+---------------------------------------------+
| Variable_name | Value |
+---------------+---------------------------------------------+
| plugin_dir | /usr/local/mysql-5.7.17/lib64/mysql/plugin/ |
+---------------+---------------------------------------------+
1 row in set (0.00 sec)


把上面下载的插件复制到上面的目录下：

unzip audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip
cd audit-plugin-mysql-5.7-1.1.4-725
cp lib/libaudit_plugin.so /usr/local/mysql-5.7.17/lib64/mysql/plugin/


进入mysql命令窗口，安装插件：

mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
Query OK, 0 rows affected (0.06 sec)


查看mysql当前已经加载了哪些插件：

mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| AUDIT | ACTIVE | AUDIT | libaudit_plugin.so | GPL |
+----------------------------+----------+--------------------+--------------------+---------+
45 rows in set (0.00 sec)
查看安装后的插件版本：

mysql> show global status like 'AUDIT_version';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Audit_version | 1.1.4-725 |
+---------------+-----------+
1 row in set (0.00 sec)


开启audit功能：

mysql> SET GLOBAL audit_json_file=ON;
Query OK, 0 rows affected (0.00 sec)


可以查看插件有哪些可配置的参数：

mysql> SHOW GLOBAL VARIABLES LIKE '%audi%';
详细的参数说明，可以直接访问官方说明：

https://github.com/mcafee/mysql-audit/wiki/Configuration



最后为了保证重启数据库，配置不丢失，修改my.cnf 配置文件，将下面的配置添加到[mysqld]中，所以在配置文件中my.cnf加入参数：

audit_json_file=on #保证mysql重启后自动启动插件

plugin-load=AUDIT=libaudit_plugin.so #防止删除了插件，重启后又会加载

audit_record_cmds='insert,delete,update,create,drop,alter,grant,truncate' #要记录哪些命令语句,因为默认记录所有操作；

保存重启即可看到效果。


其他关于这个插件的推荐好文：
https://www.percona.com/blog/2015/06/09/auditing-mysql-with-mcafee-and-mongodb/

https://www.omgdba.com/mysql-audit-plugin-now-available-in-percona-server-5-5-and-5-6.html