MySQL 8 社区版安装Percona的审计插件
2023-12-16 13:44 abce 阅读(717) 评论(0) 编辑 收藏 举报1.下载插件
# tar -xvf Percona-Server-8.0.32-24-Linux.x86_64.glibc2.17-minimal.tar.gz # cd Percona-Server-8.0.32-24-Linux.x86_64.glibc2.17-minimal/lib/plugin # cp audit_log.so /usr/local/mysql/lib/plugin/
2.查看插件位置
root@localhost (none)>show variables like '%plugin_dir%'; +---------------+------------------------------+ | Variable_name | Value | +---------------+------------------------------+ | plugin_dir | /usr/local/mysql/lib/plugin/ | +---------------+------------------------------+ 1 row in set (0.01 sec)
3.查看是否已经安装过审计插件
root@localhost mysql>SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%'; Empty set (0.01 sec) root@localhost mysql>SHOW variables LIKE 'audit%'; Empty set (0.00 sec) root@localhost mysql>
4.安装插件
root@localhost (none)> INSTALL PLUGIN audit_log SONAME 'audit_log.so'; Query OK, 0 rows affected (0.00 sec)
5.查看是否安装成功
root@localhost (none)> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%'\G *************************** 1. row *************************** PLUGIN_NAME: audit_log PLUGIN_VERSION: 0.2 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: AUDIT PLUGIN_TYPE_VERSION: 4.1 PLUGIN_LIBRARY: audit_log.so PLUGIN_LIBRARY_VERSION: 1.11 PLUGIN_AUTHOR: Percona LLC and/or its affiliates. PLUGIN_DESCRIPTION: Audit log PLUGIN_LICENSE: GPL LOAD_OPTION: ON 1 row in set (0.00 sec) root@localhost (none)>SHOW variables LIKE 'audit%'; +-----------------------------+-----------------------------+ | Variable_name | Value | +-----------------------------+-----------------------------+ | audit_log_buffer_size | 1048576 | | audit_log_exclude_accounts | | | audit_log_exclude_commands | | | audit_log_exclude_databases | | | audit_log_file | /test/mysql_audit/audit.log | | audit_log_flush | OFF | | audit_log_format | CSV | | audit_log_handler | FILE | | audit_log_include_accounts | | | audit_log_include_commands | | | audit_log_include_databases | | | audit_log_policy | LOGINS | | audit_log_rotate_on_size | 0 | | audit_log_rotations | 0 | | audit_log_strategy | ASYNCHRONOUS | | audit_log_syslog_facility | LOG_USER | | audit_log_syslog_ident | percona-audit | | audit_log_syslog_priority | LOG_INFO | +-----------------------------+-----------------------------+ 18 rows in set (0.00 sec)
6.添加配置
在配置文件中添加审计配置
plugin-load = audit_log.so audit_log_file = /test/mysql_audit/audit.log audit_log_format = CSV audit_log_policy = LOGINS audit_log_handler = FILE audit_log_rotate_on_size = 1048576
其中 audit_log_policy 的取值有:
·ALL - all events will be logged ·LOGINS - only logins will be logged ·QUERIES - only queries will be logged ·NONE - no events will be logged
创建审计日志目录闭并重启mysql
mkdir -p /test/mysql_audit chown -R mysql:mysql /test/mysql_audit
7.重启后查看
安装后,Performance Schema会启用一些instruments
root@localhost mysql>SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%'; +-------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +-------------+---------------+ | audit_log | ACTIVE | +-------------+---------------+ 1 row in set (0.00 sec) root@localhost (none)>SELECT NAME FROM performance_schema.setup_instruments WHERE NAME LIKE '%audit%'; +-------------------------------------------------------------+ | NAME | +-------------------------------------------------------------+ | wait/synch/mutex/sql/LOCK_audit_mask | | wait/synch/mutex/audit_log/file_logger::lock | | wait/synch/mutex/audit_log/audit_log_buffer::mutex | | wait/synch/rwlock/audit_log/audit_log_filter::account_list | | wait/synch/rwlock/audit_log/audit_log_filter::database_list | | wait/synch/rwlock/audit_log/audit_log_filter::command_list | | wait/synch/cond/audit_log/audit_log_buffer::written_cond | | wait/synch/cond/audit_log/audit_log_buffer::flushed_cond | | memory/audit_log/audit_log_logger_handle | | memory/audit_log/audit_log_handler | | memory/audit_log/audit_log_buffer | | memory/audit_log/audit_log_accounts | | memory/audit_log/audit_log_databases | | memory/audit_log/audit_log_commands | +-------------------------------------------------------------+ 14 rows in set (0.00 sec)
8.审计事件分析
以下面的登录记录为例
<AUDIT_RECORD NAME="Connect" RECORD="2_2023-12-06T03:11:01" TIMESTAMP="2023-12-06T03:11:13Z" CONNECTION_ID="8" STATUS="0" --0表示登录成功;非0表示登录失败 USER="root" PRIV_USER="root" OS_LOGIN="" PROXY_USER="" HOST="localhost" IP="" DB="" />
9.日志格式
支持OLD, NEW, JSON, 和 CSV 格式。其中old和new是基于xml格式的。由变量 audit_log_format 控制。
更多使用方法可以参考:
https://planet.mysql.com/entry/?id=5992239
https://docs.percona.com/percona-server/5.7/management/audit_log_plugin.html
https://cybersecthreat.com/2021/12/09/mysql-community-edition-audit-logging/
https://blog.51cto.com/u_16213454/7738338
https://medium.com/@larrie.loi/mysql-8-0-x-audit-solution-ee0d16d2d332
https://www.percona.com/blog/how-to-store-mysql-audit-logs-in-mongodb-in-a-maintenance-free-setup/