MySQL5.7.37 审计日志插件安装mcafee日志插件

MySQL5.7.34之前版本也可以使用 MariaDB的server_audit插件

下面利用第三方开源审计插件 libaudit_plugin.so 在 MySQL 5.7.37上完成审计工作。

审计插件包下载地址 Releases · mcafee-enterprise/mysql-audit · GitHub

解压插件包

unzip audit-plugin-percona-5.7-1.1.12-999-linux-x86_64

执行命令获取MySQL的plugin目录

mysql> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| plugin_dir    | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
1 row in set (0.00 sec)

将解压好的插件复制到 MySQL 的插件目录下

cp /root/audit-plugin-mysql-5.7-1.1.4-725/lib/libaudit_plugin.so /usr/local/mysql/lib/plugin/
chmod a+x libaudit_plugin.so

安装插件

mysql> install plugin audit soname 'libaudit_plugin.so';

报错：ERROR 1123 (HY000): Can't initialize function 'audit'; Plugin initialization function faile。处理办法在末尾。

查看插件功能是否开启

mysql> show variables like '%audit_json_file%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| audit_json_file         | OFF   |
| audit_json_file_bufsize | 1     |
| audit_json_file_flush   | OFF   |
| audit_json_file_retry   | 60    |
| audit_json_file_sync    | 0     |
+-------------------------+-------+
5 rows in set (0.01 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     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| ARCHIVE                    | 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)

最后修改my.cnf 配置文件，将下面的配置添加到 [mysqld] 中，所以在配置文件中my.cnf加入参数：

plugin-load=AUDIT=libaudit_plugin.so
audit_offsets = 7832, 7880, 3640, 4800, 456, 360, 0, 32, 64, 160, 544, 7996, 4368, 3648, 3656, 3660, 6080, 2072, 8, 7064, 7104, 7088, 13480, 148, 672, 0
audit_json_file=on    #保证mysql重启后自动启动插件
audit_record_cmds='insert,delete,update,create,drop,alter,grant,truncate'   #要记录哪些命令语句,因为默认记录所有操作；

保存重启即可看到效果。

现在在 MySQL 目录下会多出一个审计日志 mysql-audit.json 文件

ERROR 1123 (HY000): Can't initialize function 'audit'; Plugin initialization function failed.

错误处理办法：

[root@edu-mysql-02 utils]# pwd/opt/audit-plugin-mysql-5.7-1.1.4-725/utils[root@edu-mysql-02 utils]# chmod +x offset-extract.sh[root@edu-mysql-02 opt]# which mysqld/usr/sbin/mysqld[root@edu-mysql-02 utils]# ./offset-extract.sh /usr/sbin/mysqld
//offsets for: /usr/sbin/mysqld (5.7.37)
{"5.7.37","784cde6ddb58c862b2dd1007e7812961", 7832, 7880, 3640, 4800, 456, 360, 0, 32, 64, 160, 544, 7996, 4368, 3648, 3656, 3660, 6080, 2072, 8, 7064, 7104, 7088, 13480, 148, 672, 0},

编辑my.cnf文件

plugin-load=AUDIT=libaudit_plugin.soaudit_offsets = 7832, 7880, 3640, 4800, 456, 360, 0, 32, 64, 160, 544, 7996, 4368, 3648, 3656, 3660, 6080, 2072, 8, 7064, 7104, 7088, 13480, 148, 672, 0

重启mysql,再次执行安装插件。

参考原文链接：https://blog.csdn.net/chj_1224365967/article/details/112879957