數(shù)據(jù)庫統(tǒng)計信息插件

最近因為需要監(jiān)控數(shù)據(jù)庫（MySQL/MariaDB）的登陸動作、數(shù)據(jù)修改等，需要調(diào)研一個監(jiān)控數(shù)據(jù)庫方案。經(jīng)查詢有canal等開源方案，調(diào)查過后基本也符合需求；最后查詢GPT推薦MariaDB官方插件server audit：初步閱讀文檔感覺很符合需求，故進(jìn)行部署驗證和測試

部署MariaDB

基于CentOS7部署最新版本的MariaDB，官方推薦用下列腳本配置repo倉庫：

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

執(zhí)行完成后在/etc/yum.repos.d下面生成mariadb.repo文件，可以通過yum repolist查看新增的對應(yīng)repo源。

但是由于網(wǎng)絡(luò)原因，無法下載mariadb安裝包，這里[mariadb-main]內(nèi)baseurl替換成清華源地址：

[mariadb-main]
name = MariaDB Server
baseurl = https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/11.2/rhel/7/x86_64/
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Server-GPG-KEY
gpgcheck = 1
enabled = 1

然后再進(jìn)行安裝：

yum install mariadb-server  mariadb
systemctl start mariadb

安裝server audit插件

默認(rèn)server audit插件是不啟用的，需要通過修改配置文件（重啟生效）或者修改配置項（立即生效，但重啟后丟失）來進(jìn)行啟用。
這里介紹下通過配置項啟動的方法：

[root@localhost] # mysql
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 11.2.2-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

SHOW GLOBAL VARIABLES LIKE 'server_audit%';INSTALL SONAME 'server_audit';
Query OK, 0 rows affected (0.000 sec)

啟動server audit插件

安裝完成后，可以通過以下命令查詢server audit狀態(tài)：

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                    |
| 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              |
+-------------------------------+-----------------------+

可以看到現(xiàn)在server_audit_logging對應(yīng)的value是OFF。通過下列命令修改該配置項，啟動server audit

MariaDB [(none)]> SET GLOBAL server_audit_logging=ON;
Query OK, 0 rows affected (0.000 sec)

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          | ON                    |
| 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)

此時我們已經(jīng)開啟了server audit插件。現(xiàn)在我們進(jìn)行數(shù)據(jù)插入測試

測試

新建一個表，插入一條數(shù)據(jù)

CREATE TABLE hello (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(255)  
);
INSERT INTO hello (name) VALUES ('onlyellow');

查看server audit對應(yīng)日志文件內(nèi)容：

[root@ip-10-99-1-49 yum.repos.d]# cat /var/lib/mysql/server_audit.log
....
1.compute.internal,root,localhost,3,15,CREATE,test,hello,
20240111 17:29:49,ip-10-99-1-49.cn-northwest-1.compute.internal,root,localhost,3,15,QUERY,test,'CREATE TABLE hello (  \n    id INT AUTO_INCREMENT PRIMARY KEY,  \n    name VARCHAR(255)  \n)',0
20240111 17:29:53,ip-10-99-1-49.cn-northwest-1.compute.internal,root,localhost,3,16,QUERY,test,'show tables',0
20240111 17:30:44,ip-10-99-1-49.cn-northwest-1.compute.internal,root,localhost,3,17,WRITE,test,hello,
20240111 17:30:44,ip-10-99-1-49.cn-northwest-1.compute.internal,root,localhost,3,17,READ,mysql,table_stats,
20240111 17:30:44,ip-10-99-1-49.cn-northwest-1.compute.internal,root,localhost,3,17,READ,mysql,column_stats,
20240111 17:30:44,ip-10-99-1-49.cn-northwest-1.compute.internal,root,localhost,3,17,READ,mysql,index_stats,
20240111 17:30:44,ip-10-99-1-49.cn-northwest-1.compute.internal,root,localhost,3,17,QUERY,test,'INSERT INTO hello (name) VALUES (\'onlyellow\')',0
....

以上日志詳細(xì)記錄了用戶登陸時間、IP、以及進(jìn)行的操作，方便后續(xù)進(jìn)行數(shù)據(jù)監(jiān)控和溯源。文章來源地址http://www.zghlxwxcb.cn/news/detail-785929.html

到了這里，關(guān)于[MariaDB] 數(shù)據(jù)庫統(tǒng)計插件啟用和配置的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！