MariaDB配置日志審計

1.確認日志審計插件

首先確認插件路徑，執行下列SQL確認：

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| plugin_dir    | /usr/lib/mysql/plugin/ |
+---------------+------------------------+
1 row in set (0.001 sec)

確認插件路徑下是否有日志審計插件server_audit.so：

ls /usr/lib/mysql/plugin/server_audit.so

2.安裝日志審計插件

修改配置文件：

vim /etc/kubernetes/components/mysql/default/config.yml

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-config-default
  namespace: component
data:
  mariadb.cnf: |
    [mariadb]#增加這行
    plugin_load_add = server_audit#增加這行
    [client]
    default-character-set = utf8
    ......

(可選)SQL方式安裝日志審計插件：

INSTALL SONAME 'server_audit';

3.日志審計設置

修改配置文件：

vim /etc/kubernetes/components/mysql/default/config.yml

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-config-default
  namespace: component
data:
  mariadb.cnf: |
    [mysqld] 
    ... 
    server_audit_events=connect,query,table#增加這行,記錄連接、查詢、表事件
    server_audit_file_rotate_now=ON#增加這行,開啟日志輪換
    server_audit_file_rotate_size=1000000#增加這行,設置日志文件的文件大小限制
    server_audit_file_rotations=5#增加這行,限制創建的日志文件的數量
    ...

(可選)配置審計事件變量，記錄連接、查詢和表事件相關的日志：

SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE';

4.啟動日志審計插件

查看與日志審計設置有關的變量：

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'server_audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           |                       |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_logging          | OFF                   |#默認為OFF,表示關閉
| server_audit_mode             | 0                     |
| server_audit_output_type      | file                  |
| server_audit_query_log_limit  | 1024                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+
15 rows in set (0.001 sec)

修改配置文件：

vim /etc/kubernetes/components/mysql/default/config.yml

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-config-default
  namespace: component
data:
  mariadb.cnf: |
    [server]#增加這行
    server_audit_logging=ON#增加這行,啟用日志審計插件
    [mariadb]
    plugin_load_add = server_audit
    ......

(可選)SQL方式啟用日志審計：

SET GLOBAL server_audit_logging=ON;

確認上述配置無誤后，重啟服務：

kubectl -n component rollout restart sts mysql-default

鏈接：https://www.cnblogs.com/zgjj/p/16644213.html