概述
等保要求，数据库启用日志审计。Mysql8上面使用开源插件audit-plugin-for-mysql（MariaDB的审计插件不用折腾了，无论直接使用还是编译使用，在Mysql8上都不行）
插件下载
- 日志审计插件下载地址：
https://codeload.github.com/Vettabase/audit-plugin-for-mysql/zip/refs/heads/mysql-8.0
插件安装
-
- 解压下载文件
unzip audit-plugin-for-mysql-mysql-8.0.zip
-
- 找到mysql插件存储目录
mysql> SHOW VARIABLES LIKE 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| plugin_dir | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.01 sec)mysql>
-
- 拷贝插件并赋权
cp audit-plugin-for-mysql-mysql-8.0/build/server_audit.so /usr/lib64/mysql/plugin/
chown mysql:mysql /usr/lib64/mysql/plugin/server_audit.so
chmod 755 /usr/lib64/mysql/plugin/server_audit.so
-
- 编辑Mysql配置文件
vim /etc/my.cnf# 配置server_audit 参数，根据实际情况调整
plugin_load_add = server_audit
#plugin_load_add = server_audit.so # 确保插件开机加载
server_audit = FORCE_PLUS_PERMANENT # 防止插件被卸载:cite[5]:cite[9]
server_audit_logging = ON
server_audit_events = 'CONNECT,QUERY_DDL,QUERY_DCL'
server_audit_file_path = /var/log/mysql/audit.log
server_audit_file_rotate_size = 0 # 禁用
server_audit_file_rotations = 0 # 禁用
#server_audit_excl_users = root # 排除root用户
-
- 安装插件
mysql> INSTALL PLUGIN server_audit SONAME 'server_audit.so';
Query OK, 0 rows affected (0.09 sec)mysql>
-
- 检查审计日志
[root@centos7-05145 mysql]# ll /var/log/mysql
总用量 72
-rw-r----- 1 mysql mysql 4760 6月 24 10:00 audit.log
-rw-r----- 1 mysql mysql 19094 6月 24 09:12 mysql_3306-error.log
-rw-r----- 1 mysql mysql 43378 6月 24 09:53 mysql_3306-slow.log
配置日志保存
- 创建 logrotate配置文件
vim /etc/logrotate.d/mysql_audit
- 添加以下内存并保存（本机配置185天是示例，保存6个月日志请考虑合理备份方案）
- 如果要求日志保存严谨，请不要使用copytruncate方式，请参考以下postrotate示例
/var/log/mysql/audit.log {# 每天轮转一次daily# 保留185天的日志rotate 185# 压缩旧日志compress# 延迟1天压缩delaycompress# 如果日志不存在也不报错missingok# 空日志不轮转notifempty# 复制后截断原文件（避免重启）copytruncate# 使用mysql账号操作su mysql mysql# 设置文件日期后缀dateextdateformat -%Y%m%d
}
#/var/log/mysqld.log {
# create 640 mysql mysql
# notifempty
# daily
# rotate 5
# missingok
# compress
# postrotate
# # just if mysqld is really running
# if test -x /usr/bin/mysqladmin && \
# /usr/bin/mysqladmin ping &>/dev/null
# then
# /usr/bin/mysqladmin flush-logs
# fi
# endscript
#}
- 检查cron状态（确保开机运行）
systemctl status crond● crond.service - Command SchedulerLoaded: loaded (/usr/lib/systemd/system/crond.service; enabled; vendor preset: enabled)Active: active (running) since 五 2025-05-23 10:52:13 CST; 1 months 1 days agoMain PID: 1331 (crond)CGroup: /system.slice/crond.service└─1331 /usr/sbin/crond -n5月 23 10:52:13 centos7-172-028-002-001 systemd[1]: Started Command Scheduler.
5月 23 10:52:13 centos7-172-028-002-001 crond[1331]: (CRON) INFO (Syslog will be used instead of sendmail.)
5月 23 10:52:13 centos7-172-028-002-001 crond[1331]: (CRON) INFO (RANDOM_DELAY will be scaled with factor 79% if used.)
5月 23 10:52:13 centos7-172-028-002-001 crond[1331]: (CRON) INFO (running with inotify support)
)
