MySQL 8 社区版安装Percona的审计插件

2023-12-16 13:44 abce 阅读(1395) 评论(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.安装插件

1 2	`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

1 2	`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=""
/>