MariaDB配置日志审计

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