一、系统环境和要求
在MySQL中，开启日志审计可以记录数据库的操作日志，包括修改、删除、插入等操作。这对于追踪和分析数据库的使用情况以及排查潜在的安全问题非常有帮助。本文将详细介绍如何开启MySQL的日志审计功能。
操作系统：Ubuntu 20.04.5 LTS
数据库版本：8.0.35-commercial MySQL Enterprise Server - Commercial
TDE插件类型：keyring_encrypted_file
二、安装审计插件
-- 确认审计插件已安装并启用
SHOW PLUGINS;
-- 如果未启用，启用审计插件
INSTALL PLUGIN audit_log SONAME 'audit_log.so';下面是审计插件安装成功
mysql> SHOW PLUGINS;
+----------------------------------+----------+--------------------+---------------------------+-------------+
| Name | Status | Type | Library | License |
+----------------------------------+----------+--------------------+---------------------------+-------------+
| keyring_encrypted_file | ACTIVE | KEYRING | keyring_encrypted_file.so | PROPRIETARY |
| binlog | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | PROPRIETARY |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | PROPRIETARY |
| caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | PROPRIETARY |
| sha2_cache_cleaner | ACTIVE | AUDIT | NULL | PROPRIETARY |
| daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | PROPRIETARY |
| CSV | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| TempTable | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | PROPRIETARY |
| ndbcluster | DISABLED | STORAGE ENGINE | NULL | PROPRIETARY |
| ndbinfo | DISABLED | STORAGE ENGINE | NULL | PROPRIETARY |
| ndb_transid_mysql_connection_map | DISABLED | INFORMATION SCHEMA | NULL | PROPRIETARY |
| ngram | ACTIVE | FTPARSER | NULL | PROPRIETARY |
| mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | PROPRIETARY |
| mysqlx | ACTIVE | DAEMON | NULL | PROPRIETARY |
| audit_log | ACTIVE | AUDIT | audit_log.so | PROPRIETARY |
| keyring_file | ACTIVE | KEYRING | keyring_file.so | PROPRIETARY |
| keyring_udf | ACTIVE | DAEMON | keyring_udf.so | PROPRIETARY |
+----------------------------------+----------+--------------------+---------------------------+-------------+
52 rows in set (0.00 sec)
mysql> 二、修改配置文件
编辑MySQL的配置文件my.cnf，在文件中添加以下内容
# Audit function
audit_log = ON
audit_log_format = JSON
audit_log_policy = ALL
audit_log_file = /var/log/mysql/audit.log这些配置项的含义如下：
audit_log：设置为ON表示开启审计日志记录。
audit_log_format：指定审计日志的格式，这里我们选择了JSON格式。
audit_log_policy：指定审计策略，这里我们选择了ALL，表示记录所有操作。
audit_log_file：指定日志文件的路径和文件名，这里我们将日志写入到 audit.log 文件中。
修改完成后，保存并关闭文件。
查看audit相关参数
show global variables like 'audit%'; 
四、审计日志分析
开启审计功能后，所有的数据库访问和操作都会被记录在审计日志中。审计日志以JSON格式存储，我们可以通过分析JSON数据来了解数据库的使用情况。从上面的示例中，我们可以获取到以下信息：
tail -n 1 /var/log/mysql/audit.log如果操作被成功审计，你将会看到类似以下的日志记录：
{
"timestamp": "2024-03-26 08:57:33",
"id": 8,
"class": "general",
"event": "status",
"connection_id": 11,
"account": {
"user": "root",
"host": ""
},
"login": {
"user": "root",
"os": "",
"ip": "10.115.20.71",
"proxy": ""
},
"general_data": {
"command": "Query",
"sql_command": "show_keys",
"query": "SHOW INDEX FROM `sys`.`audit_log`",
"status": 0
}
}
