2. 首页 > MySQL教程 > 正文

MySQL教程FG294-MySQL数据库安全审计

教程整理：风哥教程  |  更新时间：2026-01-18  |  教程分类：MySQL教程  |  文档学习：36

内容简介：本文主要介绍MySQL存储过程参数与返回值的相关知识，包括参数类型、使用方法和最佳实践等内容。风哥教程参考MySQL官方文档MySQL Security、MySQL Server Administration。 01 更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 安全审计的重要性

安全审计是数据库安全管理的重要组成部分，通过记录和分析数据库操作行为，发现潜在的安全威胁和违规操作。 02 学习交流加群风哥微信: itpux-com

1.2 审计目标

数据库安全审计的主要目标： 03 学习交流加群风哥QQ113257174

# 安全审计目标

1. 合规性要求
– 满足监管要求（GDPR、SOX等）
– 通过安全认证审计
– 满足行业规范

2. 安全监控
– 检测异常访问行为
– 发现潜在安全威胁
– 追踪安全事件

3. 责任追溯
– 记录用户操作
– 追踪数据变更
– 取证分析

4. 风险评估
– 识别高风险操作
– 评估安全策略有效性
– 发现安全漏洞

Part02-生产环境规划与建议

2.1 通用日志配置

配置MySQL通用日志： 04 风哥提示：

# 通用日志配置

1. 查看当前日志配置
mysql> SHOW VARIABLES LIKE ‘%general_log%’;

输出示例：
+——————————————+——————————–+
| Variable_name | Value |
+——————————————+——————————–+
| general_log | OFF |
| general_log_file | /var/lib/mysql/mysql.log |
| log_output | FILE |
+——————————————+——————————–+

2. 开启通用日志
mysql> SET GLOBAL general_log = ‘ON’;
mysql> SET GLOBAL log_output = ‘FILE,TABLE’;

3. 设置日志文件路径
mysql> SET GLOBAL general_log_file = ‘/var/log/mysql/general.log’;

4. 永久配置my.cnf
vim /etc/my.cnf

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/general.log
log_output = FILE,TABLE

5. 查看通用日志内容
mysql> SELECT * FROM mysql.general_log
-> WHERE event_time > DATE_SUB(NOW(), INTERVAL 1 HOUR)
-> ORDER BY event_time DESC
-> LIMIT 10;

输出示例：
+————+————————+———–+———–+———–+————–+—————————————–+
| event_time | user_host | thread_id | server_id | command_type | argument |
+————+————————+———–+———–+———–+————–+—————————————–+
| 2026-04-04 12:00:00 | root[root] @ localhost [] | 10 | 1 | Query | SELECT * FROM users WHERE id = 1 |
| 2026-04-04 12:00:01 | root[root] @ localhost [] | 10 | 1 | Query | UPDATE users SET name = ‘test’ WHERE id = 1 |
| 2026-04-04 12:00:02 | root[root] @ localhost [] | 10 | 1 | Query | DELETE FROM logs WHERE created_at < '2026-01-01' |
+————+————————+———–+———–+———–+————–+—————————————–+

2.2 MySQL Enterprise Audit

配置MySQL Enterprise Audit审计：

# MySQL Enterprise Audit配置

1. 安装Audit插件
mysql> INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;

输出示例：
Query OK, 0 rows affected (0.00 sec)

2. 查看Audit配置
mysql> SHOW VARIABLES LIKE ‘audit%’;

输出示例：
+——————————————+——————————————+
| Variable_name | Value |
+——————————————+——————————————+
| audit_log_buffer_size | 1048576 |
| audit_log_capacity | 100000000 |
| audit_logEncryption | NONE |
| audit_log_file | /var/lib/mysql/audit.log |
| audit_log_flush_interval | 0 |
| audit_log_format | NEW |
| audit_log_include_accounts | NULL |
| audit_log_policy | ALL |
| audit_log_read_buffer_size | 1048576 |
| audit_log_rotate_on_size | 0 |
| audit_log_statement_policy | ALL |
| audit_log_strategy | ASYNCHRONOUS |
+——————————————+——————————————+

3. 配置审计策略
mysql> SET GLOBAL audit_log_policy = ‘ALL’;
mysql> SET GLOBAL audit_log_statement_policy = ‘ALL’;

4. 审计特定用户
mysql> SET GLOBAL audit_log_include_accounts = ‘app_user@localhost,dba_user@localhost’;

5. 排除特定用户
mysql> SET GLOBAL audit_log_exclude_accounts = ‘repl_user@localhost’;

6. 查看审计日志
mysql> SELECT * FROM mysql.audit_log_filter;

输出示例：
Empty set (0.00 sec)

7. 创建审计过滤器
mysql> CREATE AUDIT TABLE IF NOT EXISTS mysql.audit_log_filter (
-> NAME VARCHAR(64) BINARY,
-> FILTER JSON
-> );

8. 查看审计日志内容
cat /var/lib/mysql/audit.log | head -50

输出示例：
{
“timestamp”: “2026-04-04T12:00:00Z”,
“msg_version”: 2,
“name”: “Query”,
“server_id”: 1,
“thread_id”: 10,
“user”: “root”,
“host”: “localhost”,
“ip”: “”,
“cmd”: “execute”,
“sqltext”: “SELECT * FROM users WHERE id = 1”
}
{
“timestamp”: “2026-04-04T12:00:01Z”,
“msg_version”: 2,
“name”: “Query”,
“server_id”: 1,
“thread_id”: 10,
“user”: “root”,
“host”: “localhost”,
“ip”: “”,
“cmd”: “execute”,
“sqltext”: “UPDATE users SET name = ‘test’ WHERE id = 1”
}

Part03-生产环境项目实施方案

3.1 Performance Schema配置

使用Performance Schema进行审计： 05更多学习教程公众号风哥教程itpux_com

# Performance Schema审计配置

1. 启用历史事件
mysql> UPDATE performance_schema.setup_consumers
-> SET ENABLED = ‘YES’
-> WHERE NAME IN (‘events_statements_history’,
-> ‘events_statements_history_long’,
-> ‘events_transactions_history’,
-> ‘events_errors_history’);

输出示例：
Query OK, 2 rows affected (0.00 sec)
Rows matched: 4 Changed: 2 Warnings: 0

2. 配置语句监控
mysql> UPDATE performance_schema.setup_instruments
-> SET ENABLED = ‘YES’
-> WHERE NAME LIKE ‘statement/%’;

输出示例：
Query OK, 100 rows affected (0.00 sec)
Rows matched: 100 Changed: 100 Warnings: 0

3. 配置消费者
mysql> UPDATE performance_schema.setup_consumers
-> SET ENABLED = ‘YES’
-> WHERE NAME LIKE ‘statements%’;

输出示例：
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0

4. 查看最近执行的语句
mysql> SELECT
-> THREAD_ID,
-> EVENT_ID,
-> SQL_TEXT,
-> DIGEST_TEXT,
-> WORKLOAD_TIME_US
-> FROM performance_schema.events_statements_history
-> ORDER BY CREATED DESC
-> LIMIT 10;

输出示例：
+———–+———-+——————————————+——————————–+——————+
| THREAD_ID | EVENT_ID | SQL_TEXT | DIGEST_TEXT | WORKLOAD_TIME_US |
+———–+———-+——————————————+——————————–+——————+
| 10 | 100 | SELECT * FROM users WHERE id = 1 | SELECT * FROM users WHERE … | 1234 |
| 10 | 99 | UPDATE users SET name = ‘test’ WHERE id = 1 | UPDATE users SET name = ? … | 2345 |
| 10 | 98 | DELETE FROM logs WHERE created_at < '2026-01-01' | DELETE FROM logs WHERE ... | 45678 |
+———–+———-+——————————————+——————————–+——————+

5. 查看错误历史
mysql> SELECT
-> THREAD_ID,
-> EVENT_ID,
-> SQL_TEXT,
-> MESSAGE_TEXT,
-> ERROR_CODE
-> FROM performance_schema.events_errors_history
-> ORDER BY CREATED DESC
-> LIMIT 10;

输出示例：
+———–+———-+——————————————+—————————+————+
| THREAD_ID | EVENT_ID | SQL_TEXT | MESSAGE_TEXT | ERROR_CODE |
+———–+———-+——————————————+—————————+————+
| 15 | 50 | SELECT * FROM non_existent_table | Table ‘test.non_existent_table’ doesn’t exist | 1146 |
| 16 | 45 | INSERT INTO users (id) VALUES (NULL) | Column ‘id’ cannot be null | 1048 |
+———–+———-+——————————————+—————————+————+

3.2 Performance Schema查询

使用Performance Schema进行查询分析： 06 from mysql视频:www.itpux.com

# Performance Schema查询

1. 按用户统计查询量
mysql> SELECT
-> USER,
-> COUNT(*) AS query_count,
-> SUM(DIGEST_TEXT IS NOT NULL) AS prepared_count,
-> AVG(WORKLOAD_TIME_US) AS avg_time_us
-> FROM performance_schema.events_statements_history
-> GROUP BY USER
-> ORDER BY query_count DESC;

输出示例：
+—————+————-+—————-+————–+
| USER | query_count | prepared_count | avg_time_us |
+—————+————-+—————-+————–+
| app_user | 1234 | 1000 | 1234.56 |
| dba_user | 567 | 500 | 567.89 |
| root | 123 | 100 | 2345.67 |
+—————+————-+—————-+————–+

2. 按数据库统计查询量
mysql> SELECT
-> OBJECT_SCHEMA AS database_name,
-> COUNT(*) AS query_count,
-> SUM(DIGEST_TEXT IS NOT NULL) AS prepared_count,
-> AVG(WORKLOAD_TIME_US) AS avg_time_us
-> FROM performance_schema.events_statements_history
-> WHERE OBJECT_SCHEMA IS NOT NULL
-> GROUP BY OBJECT_SCHEMA
-> ORDER BY query_count DESC;

输出示例：
+——————+————-+—————-+————–+
| database_name | query_count | prepared_count | avg_time_us |
+——————+————-+—————-+————–+
| production_db | 5000 | 4500 | 1234.56 |
| user_db | 3000 | 2800 | 567.89 |
| order_db | 2000 | 1800 | 890.12 |
+——————+————-+—————-+————–+

3. 查看慢查询
mysql> SELECT
-> THREAD_ID,
-> SQL_TEXT,
-> WORKLOAD_TIME_US / 1000000 AS execution_time_sec,
-> LOCK_TIME_US / 1000000 AS lock_time_sec
-> FROM performance_schema.events_statements_history
-> WHERE WORKLOAD_TIME_US > 1000000 — 超过1秒
-> ORDER BY WORKLOAD_TIME_US DESC
-> LIMIT 10;

输出示例：
+———–+———————————————————-+———————+—————–+
| THREAD_ID | SQL_TEXT | execution_time_sec | lock_time_sec |
+———–+———————————————————-+———————+—————–+
| 10 | SELECT COUNT(*) FROM large_table WHERE created_at > ‘2026-01-01’ | 12.34 | 0.01 |
| 11 | UPDATE orders SET status = ‘completed’ WHERE status = ‘pending’ | 8.56 | 0.02 |
| 12 | SELECT * FROM users WHERE email LIKE ‘%@fgedu.net.cn’ | 5.67 | 0.01 |
+———–+———————————————————-+———————+—————–+

4. 查看锁等待
mysql> SELECT
-> THREAD_ID,
-> SQL_TEXT,
-> OBJECT_NAME,
-> LOCK_STATUS,
-> LOCK_MODE,
-> LOCK_DURATION
-> FROM performance_schema.events_statements_history
-> WHERE EXISTS (
-> SELECT 1 FROM performance_schema.events_transactions_history
-> WHERE events_statements_history.THREAD_ID = events_transactions_history.THREAD_ID
-> AND events_transactions_history.LOCK_STATUS = ‘LOCK WAIT’
-> )
-> LIMIT 10;

输出示例：
+———–+———————————————————-+————-+————-+—————-+—————+
| THREAD_ID | SQL_TEXT | OBJECT_NAME | LOCK_STATUS | LOCK_MODE | LOCK_DURATION |
+———–+———————————————————-+————-+————-+—————-+—————+
| 20 | UPDATE accounts SET balance = balance – 100 WHERE id = 1 | accounts | LOCK WAIT | RECORD LOCK | TRANSACTION |
| 21 | UPDATE accounts SET balance = balance + 100 WHERE id = 1 | accounts | LOCK WAIT | RECORD LOCK | TRANSACTION |
+———–+———————————————————-+————-+————-+—————-+—————+

Part04-生产案例与实战讲解

4.1 MariaDB Audit Plugin

使用MariaDB Audit Plugin进行审计：

# MariaDB Audit Plugin配置

1. 安装Audit插件
mysql> INSTALL SONAME ‘server_audit’;

输出示例：
Query OK, 0 rows affected (0.00 sec)

2. 查看Audit配置
mysql> SHOW VARIABLES LIKE ‘server_audit%’;

输出示例：
+——————————-+—————————————–+
| Variable_name | Value |
+——————————-+—————————————–+
| server_audit_events | |
| server_audit_excl_users | |
| server_audit_file_path | /var/lib/mysql/server_audit.json |
| server_audit_file_rotate_size| 100000000 |
| server_audit_file_rotations | 9 |
| server_audit_incl_users | |
| server_audit_logging | ON |
| server_audit_mode | 1 |
| server_audit_output | FILE |
| server_audit_policy | ALL |
| server_audit_pushover | OFF |
| server_audit_syslog_facility | LOG_USER |
| server_audit_syslog_ident | mysql-audit |
| server_audit_syslog_priority | LOG_INFO |
+——————————-+—————————————–+

3. 配置审计策略
mysql> SET GLOBAL server_audit_events = ‘CONNECT,QUERY,TABLE’;
mysql> SET GLOBAL server_audit_policy = ‘ALL’;

4. 审计特定用户
mysql> SET GLOBAL server_audit_incl_users = ‘app_user,dba_user’;

5. 排除特定用户
mysql> SET GLOBAL server_audit_excl_users = ‘repl_user’;

6. 查看审计日志
cat /var/lib/mysql/server_audit.json | head -20

输出示例：
20260404 12:00:00,localhost,root,Query,0,,
SELECT * FROM users WHERE id = 1
20260404 12:00:01,localhost,root,Query,0,,
UPDATE users SET name = ‘test’ WHERE id = 1
20260404 12:00:02,localhost,app_user,Connect,0,,
20260404 12:00:03,localhost,app_user,Query,0,production_db,SELECT * FROM orders WHERE status = ‘pending’
20260404 12:00:04,localhost,app_user,Query,0,production_db,UPDATE orders SET status = ‘completed’ WHERE id = 123
20260404 12:00:05,localhost,app_user,Disconnect,0,,

Part05-风哥经验总结与分享

5.1 审计报告生成

生成审计报告：

# 审计报告生成

1. 创建审计分析表
mysql> CREATE TABLE audit_reports (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> report_date DATE NOT NULL,
-> report_type VARCHAR(50),
-> user_name VARCHAR(64),
-> query_count INT,
-> error_count INT,
-> avg_execution_time DECIMAL(10,2),
-> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-> );

2. 生成每日审计报告
mysql> INSERT INTO audit_reports (report_date, report_type, user_name, query_count, error_count, avg_execution_time)
-> SELECT
-> CURDATE(),
-> ‘DAILY_USER_STATS’,
-> USER,
-> COUNT(*),
-> SUM(STATUS > 0),
-> AVG(WORKLOAD_TIME_US)
-> FROM performance_schema.events_statements_history
-> WHERE CREATED >= DATE_SUB(NOW(), INTERVAL 1 DAY)
-> GROUP BY USER;

输出示例：
Query OK, 3 rows affected (0.12 sec)
Records: 3 Duplicates: 0 Warnings: 0

3. 查看审计报告
mysql> SELECT * FROM audit_reports ORDER BY report_date DESC, query_count DESC;

输出示例：
+—-+————-+——————+————-+————-+————–+——————-+
| id | report_date | report_type | user_name | query_count | error_count | avg_execution_time |
+—-+————-+——————+————-+————-+————–+——————-+
| 1 | 2026-04-04 | DAILY_USER_STATS | app_user | 1234 | 5 | 1234.56 |
| 2 | 2026-04-04 | DAILY_USER_STATS | dba_user | 567 | 2 | 567.89 |
| 3 | 2026-04-04 | DAILY_USER_STATS | root | 123 | 0 | 2345.67 |
+—-+————-+——————+————-+————-+————–+——————-+

4. 导出审计报告到CSV
mysql> SELECT
-> report_date,
-> report_type,
-> user_name,
-> query_count,
-> error_count,
-> avg_execution_time
-> FROM audit_reports
-> WHERE report_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
-> INTO OUTFILE ‘/tmp/audit_report.csv’
-> FIELDS TERMINATED BY ‘,’
-> ENCLOSED BY ‘”‘
-> LINES TERMINATED BY ‘\n’;

输出示例：
Query OK, 21 rows affected (0.05 sec)

5. 查看导出文件
cat /tmp/audit_report.csv

输出示例：
“report_date”,”report_type”,”user_name”,”query_count”,”error_count”,”avg_execution_time”
“2026-04-04″,”DAILY_USER_STATS”,”app_user”,”1234″,”5″,”1234.56″
“2026-04-04″,”DAILY_USER_STATS”,”dba_user”,”567″,”2″,”567.89″
“2026-04-04″,”DAILY_USER_STATS”,”root”,”123″,”0″,”2345.67″

6. 最佳实践

6.1 审计最佳实践

数据库审计的最佳实践：

# 审计最佳实践

1. 审计范围
– 记录所有DDL操作（CREATE、ALTER、DROP）
– 记录所有DML操作（INSERT、UPDATE、DELETE）
– 记录所有连接和断开事件
– 记录所有错误和警告

2. 审计策略
– 生产环境：记录所有操作
– 测试环境：记录DDL和高风险操作
– 开发环境：按需记录

3. 日志管理
– 日志保留时间：根据合规要求
– 日志存储：独立于数据库服务器
– 日志压缩：节省存储空间
– 日志轮转：防止日志过大

4. 告警机制
– 异常登录告警
– 大量数据删除告警
– 权限变更告警
– 敏感表访问告警

5. 合规性
– 满足监管要求
– 保护敏感数据
– 定期审计报告

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息