Mariadb (Mysql) enable audit log

Reference:

mariadb server audit plugin for mysql 

MariaDB Audit Plugin 

MySQL 基本操作語法

詳解MySQL四種Log機制 以日誌稽核解析追蹤事件 

MySQL Auditing With MariaDB Auditing Plugin 

mysql 几种日志

 

#login mysql
mysql -u root -p

#確認plugin目錄 (in mysql)
SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+

#確認是否有 /usr/lib64/mysql/plugin/server_audit.so
#用yum install madiadb會有這個檔案
ls -l /usr/lib64/mysql/plugin/server_audit.so

#Installing the Plugin (in mysql)
INSTALL SONAME 'server_audit';

#確認已安裝 (in mysql)
show global variables like '%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                   |
| 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              |
+-------------------------------+-----------------------+

#Log Settings (in mysql)
#audit_events type
SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE';

#啟用audit log
SET GLOBAL server_audit_logging=ON;

#測試 login, query 是否有 log
#server_audit.log 預設放在 datadir /var/lib/mysql/
show databases;
use mysql;
show tables;
select * from user;