[MariaDB] 数据库统计插件启用和配置

news/2024/7/27 16:32:22/文章来源:https://blog.csdn.net/onlyellow/article/details/135555357

数据库统计信息插件

最近因为需要监控数据库（MySQL/MariaDB）的登陆动作、数据修改等，需要调研一个监控数据库方案。经查询有canal等开源方案，调查过后基本也符合需求；最后查询GPT推荐MariaDB官方插件server audit：初步阅读文档感觉很符合需求，故进行部署验证和测试

部署MariaDB

基于CentOS7部署最新版本的MariaDB，官方推荐用下列脚本配置repo仓库：

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

执行完成后在/etc/yum.repos.d下面生成mariadb.repo文件，可以通过yum repolist查看新增的对应repo源。

但是由于网络原因，无法下载mariadb安装包，这里[mariadb-main]内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

然后再进行安装：

yum install mariadb-server  mariadb
systemctl start mariadb

安装server audit插件

默认server audit插件是不启用的，需要通过修改配置文件（重启生效）或者修改配置项（立即生效，但重启后丢失）来进行启用。
这里介绍下通过配置项启动的方法：

[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 ServerCopyright (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状态：


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

可以看到现在server_audit_logging对应的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)

此时我们已经开启了server audit插件。现在我们进行数据插入测试

测试

新建一个表，插入一条数据

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

查看server audit对应日志文件内容：


[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
....