MySQL安装开源审计插件 mysql-audit
MySQL 5.7.38安装审计插件 mysql-audit
MySQL版本众多， 同样审计的软件众多，为什么使用 mysql-audit ，原因：老外的弄得，一直在维护，支持的MySQL版本多
安装MySQL
1.查看Linux服务器版本和glibc版本
[root@localhost ~]# cat /etc/centos-release
CentOS Linux release 7.9.2009 (Core)[root@localhost ~]# ldd --version
ldd (GNU libc)2.17
Copyright (C)2012 Free Software Foundation, Inc.
This is free software; see the sourcefor copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
Written by Roland McGrath and Ulrich Drepper.
2.根据自己的系统下载对应的MySQL版本，由于mysql-audit并不支持所有版本的MySQL，所以在确定MySQL版本之前请注意下插件支持的MySQL版本
mysql-audit ： https://github.com/trellix-enterprise/mysql-audit/releases
显示所有可用的插件 ， 比如我的系统是64为的，我要用5.7.38的MySQL，我就下载对应的插件
[root@localhost ~]# cd ~/Desktop/[root@localhost Desktop]# wget https://github.com/trellix-enterprise/mysql-audit/releases/download/v1.1.13/audit-plugin-mysql-5.7-1.1.13-1008-linux-x86_64.zip
--2024-05-15 14:08:08-- https://github.com/trellix-enterprise/mysql-audit/releases/download/v1.1.13/audit-plugin-mysql-5.7-1.1.13-1008-linux-x86_64.zip
Resolving github.com (github.com)... 20.205.243.166
Connecting to github.com (github.com)|20.205.243.166|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://objects.githubusercontent.com/github-production-release-asset-2e65be/3552521/18e50e6c-aa02-413b-a796-61cbcb3ee58a?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=releaseassetproduction%2F20240515%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20240515T060813Z&X-Amz-Expires=300&X-Amz-Signature=6d74005dbfa59d69565d309c5d7b1ef64d62811c3b715976d9506b5108c2ee83&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=3552521&response-content-disposition=attachment%3B%20filename%3Daudit-plugin-mysql-5.7-1.1.13-1008-linux-x86_64.zip&response-content-type=application%2Foctet-stream [following]
--2024-05-15 14:08:13-- https://objects.githubusercontent.com/github-production-release-asset-2e65be/3552521/18e50e6c-aa02-413b-a796-61cbcb3ee58a?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=releaseassetproduction%2F20240515%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20240515T060813Z&X-Amz-Expires=300&X-Amz-Signature=6d74005dbfa59d69565d309c5d7b1ef64d62811c3b715976d9506b5108c2ee83&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=3552521&response-content-disposition=attachment%3B%20filename%3Daudit-plugin-mysql-5.7-1.1.13-1008-linux-x86_64.zip&response-content-type=application%2Foctet-stream
Resolving objects.githubusercontent.com (objects.githubusercontent.com)... 185.199.109.133, 185.199.108.133, 185.199.110.133, ...
Connecting to objects.githubusercontent.com (objects.githubusercontent.com)|185.199.109.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 596787(583K)[application/octet-stream]
Saving to: ‘audit-plugin-mysql-5.7-1.1.13-1008-linux-x86_64.zip’
100%[======================================>]596,7871.80MB/s in0.3s
2024-05-15 14:08:14 (1.80 MB/s) - ‘audit-plugin-mysql-5.7-1.1.13-1008-linux-x86_64.zip’ saved [596787/596787][root@localhost Desktop]#[root@localhost Desktop]# ls -al
total 588
drwxr-xr-x. 2 root root 65 May 1514:08 .
dr-xr-x---. 14 root root 4096 May 1116:13 ..
-rw-r--r--. 1 root root 596787 Oct 122022 audit-plugin-mysql-5.7-1.1.13-1008-linux-x86_64.zip
3.下载MySQL
MySQL下载地址： https://downloads.mysql.com/archives/community/
我的服务器版本是centos7的64位操作系统， 根据自己情况选择自己的版本 ，下载选择 tar源码包
[root@localhost Desktop]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar
--2024-05-15 14:15:02-- https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar
Resolving downloads.mysql.com (downloads.mysql.com)... 23.15.136.176, 2600:140b:a00:6af::2e31, 2600:140b:a00:6b2::2e31
Connecting to downloads.mysql.com (downloads.mysql.com)|23.15.136.176|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar [following]
--2024-05-15 14:15:08-- https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar
Resolving cdn.mysql.com (cdn.mysql.com)... 23.77.214.217, 2600:140b:a00:6a1::1d68, 2600:140b:a00:6b7::1d68
Connecting to cdn.mysql.com (cdn.mysql.com)|23.77.214.217|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 709361152(676M)[application/x-tar]
Saving to: ‘mysql-5.7.38-linux-glibc2.12-x86_64.tar’
14% [========================>]105,386,474 10.9MB/s eta 55s
4.安装MySQL
1.安装之前看是否安装过MySQL数据库
[root@localhost Desktop]# yum remove mysql
Loaded plugins: fastestmirror, langpacks
No Match for argument: mysql
No Packages marked for removal
2.查看是否有MySQL依赖
[root@localhost Desktop]# rpm -qa | grep mysql[root@localhost Desktop]#
若有就卸载
//普通删除模式
rpm-e xxx(mysql_libs)
//强力删除模式,如果上述命令删除时，提示有依赖其他文件，则可以用该命令对其进行强力删除
rpm-e--nodeps xxx(mysql_libs)
4.检查是否有mariadb
[root@localhost Desktop]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@localhost Desktop]#
若有就卸载
[root@localhost Desktop]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@localhost Desktop]# rpm -e --nodeps mariadb-libs[root@localhost Desktop]# rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64
error: package mariadb-libs-5.5.68-1.el7.x86_64 is not installed
[root@localhost Desktop]#
5.安装mysql依赖包
[root@localhost Desktop]# yum install libaio
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.ustc.edu.cn
* extras: mirrors.ustc.edu.cn
* updates: mirrors.ustc.edu.cn
base |3.6 kB 00:00
extras |2.9 kB 00:00
updates |2.9 kB 00:00
(1/4): base/7/x86_64/group_gz |153 kB 00:05
(2/4): extras/7/x86_64/primary_db |253 kB 00:05
(3/4): updates/7/x86_64/primary_db |27 MB 00:08
(4/4): base/7/x86_64/primary_db |6.1 MB 00:11
Package libaio-0.3.109-13.el7.x86_64 already installed and latest version
Nothing to do[root@localhost Desktop]#
6.解压MySQL的tar文件 安装准备
clear[root@localhost Desktop]# tar -xvf mysql-5.7.38-linux-glibc2.12-x86_64.tar
mysql-test-5.7.38-linux-glibc2.12-x86_64.tar.gz
mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
[root@localhost Desktop]# tar -zxvf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
mysql-5.7.38-linux-glibc2.12-x86_64/bin/myisam_ftdump
mysql-5.7.38-linux-glibc2.12-x86_64/bin/myisamchk
mysql-5.7.38-linux-glibc2.12-x86_64/bin/myisamlog
mysql-5.7.38-linux-glibc2.12-x86_64/bin/myisampack
.......
mysql-5.7.38-linux-glibc2.12-x86_64/support-files/mysql.server
mysql-5.7.38-linux-glibc2.12-x86_64/docs/INFO_BIN
mysql-5.7.38-linux-glibc2.12-x86_64/docs/INFO_SRC
[root@localhost Desktop]#
修改解压后路径名称
[root@localhost Desktop]# ls
audit-plugin-mysql-5.7-1.1.13-1008-linux-x86_64.zip
mysql-5.7.38-linux-glibc2.12-x86_64
mysql-5.7.38-linux-glibc2.12-x86_64.tar
mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
mysql-test-5.7.38-linux-glibc2.12-x86_64.tar.gz
[root@localhost Desktop]# mv mysql-5.7.38-linux-glibc2.12-x86_64 mysql[root@localhost Desktop]#[root@localhost Desktop]#
安装习惯把MySQL 移动到 /usr/local目录
[root@localhost Desktop]# ls /usr/local/
bin etc games include lib lib64 libexec sbin share src
[root@localhost Desktop]#[root@localhost Desktop]# mv mysql /usr/local/[root@localhost Desktop]# ls /usr/local/
bin etc games include lib lib64 libexec mysql sbin share src
[root@localhost Desktop]# ls /usr/local/mysql/
bin docs include lib LICENSE man README share support-files
[root@localhost Desktop]#
切换到MySQL目录 ，创建相关用户名密码
[root@localhost Desktop]# cd /usr/local/mysql/[root@localhost mysql]# pwd
/usr/local/mysql
[root@localhost mysql]# ls
bin docs include lib LICENSE man README share support-files
[root@localhost mysql]# mkdir mysqld[root@localhost mysql]# ls
bin docs include lib LICENSE man mysqld README share support-files
[root@localhost mysql]#
mysql安装目录赋予权限
[root@localhost mysql]# ls
bin docs include lib LICENSE man mysqld README share support-files
[root@localhost mysql]# chmod -R 777 /usr/local/mysql/[root@localhost mysql]# ls -al
total 272
drwxrwxrwx. 10 root root 143 May 1618:25 .
drwxr-xr-x. 13 root root 144 May 1618:24 ..
drwxrwxrwx. 2 root root 4096 May 1618:19 bin
drwxrwxrwx. 2 root root 55 May 1618:19 docs
drwxrwxrwx. 3 root root 4096 May 1618:19 include
drwxrwxrwx. 5 root root 230 May 1618:19 lib
-rwxrwxrwx. 1716131415259251 Mar 222022 LICENSE
drwxrwxrwx. 4 root root 30 May 1618:19 man
drwxrwxrwx. 2 root root 6 May 1618:25 mysqld
-rwxrwxrwx. 1716131415566 Mar 222022 README
drwxrwxrwx. 28 root root 4096 May 1618:19 share
drwxrwxrwx. 2 root root 90 May 1618:19 support-files
[root@localhost mysql]# pwd
/usr/local/mysql
[root@localhost mysql]#
创建mysql组和用户
创建组
[root@localhost mysql]#[root@localhost mysql]# groupadd mysql[root@localhost mysql]#
创建用户(-s /bin/false参数指定mysql用户仅拥有所有权，而没有登录权限)
[root@localhost mysql]#[root@localhost mysql]# groupadd mysql[root@localhost mysql]# useradd -r -g mysql -s /bin/false mysql[root@localhost mysql]#[root@localhost mysql]#
将用户添加到组中
[root@localhost mysql]# clear[root@localhost mysql]#[root@localhost mysql]# groupadd mysql[root@localhost mysql]# useradd -r -g mysql -s /bin/false mysql[root@localhost mysql]#[root@localhost mysql]# chown -R mysql:mysql ./[root@localhost mysql]# ls -al
total 272
drwxrwxrwx. 10 mysql mysql 143 May 1618:25 .
drwxr-xr-x. 13 root root 144 May 1618:24 ..
drwxrwxrwx. 2 mysql mysql 4096 May 1618:19 bin
drwxrwxrwx. 2 mysql mysql 55 May 1618:19 docs
drwxrwxrwx. 3 mysql mysql 4096 May 1618:19 include
drwxrwxrwx. 5 mysql mysql 230 May 1618:19 lib
-rwxrwxrwx. 1 mysql mysql 259251 Mar 222022 LICENSE
drwxrwxrwx. 4 mysql mysql 30 May 1618:19 man
drwxrwxrwx. 2 mysql mysql 6 May 1618:25 mysqld
-rwxrwxrwx. 1 mysql mysql 566 Mar 222022 README
drwxrwxrwx. 28 mysql mysql 4096 May 1618:19 share
drwxrwxrwx. 2 mysql mysql 90 May 1618:19 support-files
[root@localhost mysql]#
修改 /etc/my.cnf 文件，如果没有文件就新建
[root@localhost mysql]# cd /etc/[root@localhost etc]# ls | grep my.cnf[root@localhost etc]#[root@localhost etc]# pwd
/etc
[root@localhost etc]# touch my.cnf[root@localhost etc]# ls -al | grep my.cnf
-rw-r--r--. 1 root root 0 May 1618:54 my.cnf
[root@localhost etc]#
编辑 my.cnf文件
配置如下
[mysqld]# 设置3306端口port=3306# 设置mysql的安装目录basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录datadir=/usr/local/mysql/mysqldb
# 允许最大连接数max_connections=10000# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统max_connect_errors=10# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证default_authentication_plugin=mysql_native_password
[mysql]# 设置mysql客户端默认字符集
default-character-set=utf8
[client]# 设置mysql客户端连接服务端时默认使用的端口port=3306
default-character-set=utf8
7.安装MySQL
进入MySQL的bin目录
[root@localhost etc]# cd /usr/local/mysql/bin/[root@localhost bin]# pwd
/usr/local/mysql/bin
安装mysql，并记住初始化随机密码
[root@localhost bin]# ./mysqld --initialize --console2024-05-16T10:57:55.645402Z 0[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation formore details).
2024-05-16T10:57:55.899299Z 0[Warning] InnoDB: New log files created, LSN=457902024-05-16T10:57:55.934999Z 0[Warning] InnoDB: Creating foreign key constraint system tables.
2024-05-16T10:57:56.008700Z 0[Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 2683643d-1373-11ef-b486-000c29193667.
2024-05-16T10:57:56.011251Z 0[Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-05-16T10:57:56.161299Z 0[Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-05-16T10:57:56.161325Z 0[Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-05-16T10:57:56.161696Z 0[Warning] CA certificate ca.pem is self signed.
2024-05-16T10:57:56.192763Z 1[Note] A temporary password is generated for root@localhost: ..g0Xl1wB8u.
[root@localhost bin]#
注意这一行
[Note] A temporary password is generated for root@localhost: ..g0Xl1wB8u.
说明我的默认密码是 ，注意是 localhost冒号后面的
…g0Xl1wB8u.
启动mysql服务，切换目录，给权限
[root@localhost bin]# cd /usr/local/mysql/support-files[root@localhost support-files]# chmod -R 777 /usr/local/mysql[root@localhost support-files]#[root@localhost support-files]#
启动MySQL
[root@localhost bin]# cd /usr/local/mysql/support-files[root@localhost support-files]# chmod -R 777 /usr/local/mysql[root@localhost support-files]#[root@localhost support-files]# ./mysql.server start
Starting MySQL.Logging to '/usr/local/mysql/mysqldb/localhost.localdomain.err'.
SUCCESS![root@localhost support-files]#
将mysql添加到系统进程中， 就可以使用服务进程操作mysql了
[root@localhost support-files]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld[root@localhost support-files]#
设置mysql自启动
[root@localhost support-files]# chmod +x /etc/init.d/mysqld[root@localhost support-files]# systemctl enable mysqld
mysqld.service is not a native service, redirecting to /sbin/chkconfig.
Executing /sbin/chkconfig mysqld on
[root@localhost support-files]#
修改root用户登录密码，密码就是上面的随机字符串 ，我的是 …g0Xl1wB8u. 每个人随机的都不一样
[root@localhost bin]# pwd
/usr/local/mysql/bin
[root@localhost bin]# ./mysql -u root -p[root@localhost bin]# ./mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38
Copyright (c)2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.
mysql>
修改密码为123456
mysql> alter user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
允许远程登录 ，并 刷新
mysql>
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>
mysql> FLUSH PRIVILEGES ;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> quit
Bye
[root@localhost bin]#[root@localhost bin]#
重启服务且测试
命令1：systemctl restart mysql
命令2： service mysql restart
两条命令都可以重启MySQL
[root@localhost bin]#[root@localhost bin]# service mysql restart
Redirecting to /bin/systemctl restart mysql.service
[root@localhost bin]#
查看MySQL启动状态
[root@localhost bin]#[root@localhost bin]# cd ~/Desktop/[root@localhost Desktop]# systemctl status mysql
● mysqld.service - LSB: start and stop MySQL
Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
Active: active (exited) since Thu 2024-05-16 19:11:41 CST; 1min 55s ago
Docs: man:systemd-sysv-generator(8)
Process: 4310ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
May 1619:11:41 localhost.localdomain systemd[1]: Starting LSB: start and stop MySQL...
May 1619:11:41 localhost.localdomain mysqld[4310]: Starting MySQL SUCCESS!
May 1619:11:41 localhost.localdomain systemd[1]: Started LSB: start and stop MySQL.
May 1619:11:42 localhost.localdomain mysqld[4310]: 2024-05-16T11:11:42.026045Z mysqld_safe A mysqld process already exists
[root@localhost Desktop]#
8.防火墙，端口开放
查看所有端口
[root@localhost Desktop]# firewall-cmd --list-all
public (active)
target: default
icmp-block-inversion: no
interfaces: ens33
sources:
services: dhcpv6-client ssh
ports:
protocols:
masquerade: no
forward-ports:
source-ports:
icmp-blocks:
rich rules:
[root@localhost Desktop]#
开放3306端口 , --permanent 永久生效
[root@localhost Desktop]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@localhost Desktop]#[root@localhost Desktop]# firewall-cmd --reload
success
看下自己的IP，连接测试
[root@localhost Desktop]# ifconfig | grep 192
inet 192.168.1.106 netmask 255.255.255.0 broadcast 192.168.1.255
inet 192.168.122.1 netmask 255.255.255.0 broadcast 192.168.122.255
4.安装审计插件mysql-audit
1.审计软件我已经下载了 ，上文有下载说明
[root@localhost Desktop]# ls -l | grep aud
-rw-r--r--. 1 root root 596787 Oct 122022 audit-plugin-mysql-5.7-1.1.13-1008-linux-x86_64.zip
[root@localhost Desktop]#[root@localhost Desktop]#[root@localhost Desktop]# pwd
/root/Desktop
2.解压软件
[root@localhost Desktop]# unzip audit-plugin-mysql-5.7-1.1.13-1008-linux-x86_64.zip
Archive: audit-plugin-mysql-5.7-1.1.13-1008-linux-x86_64.zip
creating: audit-plugin-mysql-5.7-1.1.13-1008/
creating: audit-plugin-mysql-5.7-1.1.13-1008/lib/
inflating: audit-plugin-mysql-5.7-1.1.13-1008/lib/libaudit_plugin.so
inflating: audit-plugin-mysql-5.7-1.1.13-1008/COPYING
inflating: audit-plugin-mysql-5.7-1.1.13-1008/THIRDPARTY.txt
inflating: audit-plugin-mysql-5.7-1.1.13-1008/README.txt
inflating: audit-plugin-mysql-5.7-1.1.13-1008/plugin-name.txt
creating: audit-plugin-mysql-5.7-1.1.13-1008/utils/
inflating: audit-plugin-mysql-5.7-1.1.13-1008/utils/offset-extract.sh
[root@localhost Desktop]#
3.登录进入MySQL，找到你的插件所在目录
[root@localhost Desktop]# pwd
/root/Desktop
[root@localhost Desktop]# cd /usr/local/mysql/bin/[root@localhost bin]# ./mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38 MySQL Community Server (GPL)
Copyright (c)2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.
mysql>
mysql> show global variables like 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| plugin_dir | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
1 row inset(0.01 sec)
mysql>
4.我们的MySQL插件目录是 /usr/local/mysql/lib/plugin/
把插件复制到MySQL的插件目录，并修改权限和所有者
mysql>
mysql> quit
Bye
[root@localhost bin]# cd ~/Desktop/audit-plugin-mysql-5.7-1.1.13-1008/lib/[root@localhost lib]#[root@localhost lib]# cp libaudit_plugin.so /usr/local/mysql/lib/plugin/[root@localhost lib]# chmod +x /usr/local/mysql/lib/plugin/libaudit_plugin.so[root@localhost lib]# chown mysql:mysql /usr/local/mysql/lib/plugin/libaudit_plugin.so[root@localhost lib]#[root@localhost lib]#
5.获取偏移量信息
找到自己的mysqld 文件，可使用 whereis mysqld
但是我们是源码包安装的 mysqld 位置 : /usr/local/mysql/bin/
[root@localhost Desktop]# pwd
/root/Desktop
[root@localhost Desktop]# ls /usr/local/mysql/bin/ | grep mysqld
mysqld
mysqld-debug
mysqld_multi
mysqld_safe
mysqldump
mysqldumpslow
[root@localhost Desktop]#
进入插件的解压目录 ,添加权限，然后获取数值
[root@localhost Desktop]# cd audit-plugin-mysql-5.7-1.1.13-1008/utils/[root@localhost utils]# ls -al
total 8
drwxr-xr-x. 2 root root 31 Sep 62022.
drwxr-xr-x. 4 root root 108 Sep 62022..
-rw-r--r--. 1 root root 4726 Sep 62022 offset-extract.sh
[root@localhost utils]# chmod +x offset-extract.sh[root@localhost utils]#[root@localhost utils]# ls -al
total 8
drwxr-xr-x. 2 root root 31 Sep 62022.
drwxr-xr-x. 4 root root 108 Sep 62022..
-rwxr-xr-x. 1 root root 4726 Sep 62022 offset-extract.sh
[root@localhost utils]# ./offset-extract.sh /usr/local/mysql/bin/mysqld
//offsets for: /usr/local/mysql/bin/mysqld (5.7.38){"5.7.38","adf0327064da666263ab1ba6b3cf55bb", 7832, 7880, 3640, 4800, 456, 360, 0, 32, 64, 160, 544, 7996, 4368, 3648, 3656, 3660, 6080, 2072, 8, 7064, 7104, 7088, 13480, 148, 672, 0},
[root@localhost utils]#
6.修改MySQL配置文件
[root@localhost utils]# cd /etc/[root@localhost etc]# vim my.cnf
这是我的my.cnf完整配置
[root@localhost etc]# cat my.cnf[mysqld]# 设置3306端口port=3306# 设置mysql的安装目录basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录datadir=/usr/local/mysql/mysqldb
# 允许最大连接数max_connections=10000# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统max_connect_errors=10# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证default_authentication_plugin=mysql_native_password
#审计offsets 不要乱配置audit_offsets=7832, 7880, 3640, 4800, 456, 360, 0, 32, 64, 160, 544, 7996, 4368, 3648, 3656, 3660, 6080, 2072, 8, 7064, 7104, 7088, 13480, 148, 672, 0#审计操作命令# 如果不指定audit_record_cmds，所有DDL，DML全记录audit_record_cmds='select,insert,delete,update,create,drop,alter,grant,truncate'#审计开关audit_json_file=on
#加载审计第三方库
plugin-load=AUDIT=libaudit_plugin.so
#审计日志路径audit_json_log_file=/var/log/mysql_audit.json
[mysql]# 设置mysql客户端默认字符集
default-character-set=utf8
[client]# 设置mysql客户端连接服务端时默认使用的端口port=3306
default-character-set=utf8
[root@localhost etc]#
7.安装mysql-audit插件
连接MySQL数据库 ，并查看安装的所有插件
[root@localhost log]# cd ~/Desktop/[root@localhost Desktop]# cd /usr/local/mysql/bin/[root@localhost bin]# ./mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38 MySQL Community Server (GPL)
Copyright (c)2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.
mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL || mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL || sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL || MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL || CSV | ACTIVE | STORAGE ENGINE | NULL | GPL || InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL || INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL || MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL || PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL || MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL || FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL || BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL || partition | ACTIVE | STORAGE ENGINE | NULL | GPL || ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL || ngram | ACTIVE | FTPARSER | NULL | GPL |
+----------------------------+----------+--------------------+---------+---------+
44 rows inset(0.00 sec)
mysql>
安装mysql-audit
mysql>install plugin audit soname 'libaudit_plugin.so';
Query OK, 0 rows affected (0.36 sec)
mysql>
mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL || mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL || sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL || MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL || CSV | ACTIVE | STORAGE ENGINE | NULL | GPL || InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL || INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL || MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL || PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL || MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL || FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL || BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL || partition | ACTIVE | STORAGE ENGINE | NULL | GPL || ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL || ngram | ACTIVE | FTPARSER | NULL | GPL || AUDIT | ACTIVE | AUDIT | libaudit_plugin.so | GPL |
+----------------------------+----------+--------------------+--------------------+---------+
45 rows inset(0.01 sec)
mysql>
我们发现插件已经成功安装
| AUDIT | ACTIVE | AUDIT | libaudit_plugin.so | GPL |
开启audit功能
mysql> SET GLOBAL audit_json_file=ON;
Query OK, 0 rows affected (0.00 sec)
mysql>
查看audit配置，包括mysql_audit.json存储路径
mysql> show variables like '%audit%'\G;
*************************** 1. row ***************************
Variable_name: audit_before_after
Value: after
*************************** 2. row ***************************
Variable_name: audit_checksum
Value:
*************************** 3. row ***************************
Variable_name: audit_client_capabilities
Value: OFF
*************************** 4. row ***************************
Variable_name: audit_delay_cmds
Value:
*************************** 5. row ***************************
Variable_name: audit_delay_ms
Value: 0
*************************** 6. row ***************************
Variable_name: audit_force_record_logins
Value: OFF
*************************** 7. row ***************************
Variable_name: audit_header_msg
Value: ON
*************************** 8. row ***************************
Variable_name: audit_json_file
Value: ON
*************************** 9. row ***************************
Variable_name: audit_json_file_bufsize
Value: 1
*************************** 10. row ***************************
Variable_name: audit_json_file_flush
Value: OFF
*************************** 11. row ***************************
Variable_name: audit_json_file_retry
Value: 60
*************************** 12. row ***************************
Variable_name: audit_json_file_sync
Value: 0
*************************** 13. row ***************************
Variable_name: audit_json_log_file
Value: /var/log/mysql_audit.json
*************************** 14. row ***************************
Variable_name: audit_json_socket
Value: OFF
*************************** 15. row ***************************
Variable_name: audit_json_socket_name
Value: /var/run/db-audit/mysql.audit__usr_local_mysql_mysqldb_3306
*************************** 16. row ***************************
Variable_name: audit_json_socket_retry
Value: 10
*************************** 17. row ***************************
Variable_name: audit_json_socket_write_timeout
Value: 1000
*************************** 18. row ***************************
Variable_name: audit_offsets
Value: 7832, 7880, 3640, 4800, 456, 360, 0, 32, 64, 160, 544, 7996, 4368, 3648, 3656, 3660, 6080, 2072, 8, 7064, 7104, 7088, 13480, 148, 672, 0
*************************** 19. row ***************************
Variable_name: audit_offsets_by_version
Value: ON
*************************** 20. row ***************************
Variable_name: audit_password_masking_cmds
Value: CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER,UPDATE
*************************** 21. row ***************************
Variable_name: audit_password_masking_regex
Value: identified(?:/\*.*?\*/|\s)*?by(?:/\*.*?\*/|\s)*?(?:password)?(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]|password(?:/\*.*?\*/|\s)*?\((?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"](?:/\*.*?\*/|\s)*?\)|password(?:/\*.*?\*/|\s)*?(?:for(?:/\*.*?\*/|\s)*?\S+?)?(?:/\*.*?\*/|\s)*?=(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]|password(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]
*************************** 22. row ***************************
Variable_name: audit_record_cmds
Value: select,insert,delete,update,create,drop,alter,grant,truncate
*************************** 23. row ***************************
Variable_name: audit_record_objs
Value:
*************************** 24. row ***************************
Variable_name: audit_sess_connect_attrs
Value: ON
*************************** 25. row ***************************
Variable_name: audit_socket_creds
Value: ON
*************************** 26. row ***************************
Variable_name: audit_uninstall_plugin
Value: OFF
*************************** 27. row ***************************
Variable_name: audit_validate_checksum
Value: ON
*************************** 28. row ***************************
Variable_name: audit_validate_offsets_extended
Value: ON
*************************** 29. row ***************************
Variable_name: audit_whitelist_cmds
Value: BEGIN,COMMIT,PING
*************************** 30. row ***************************
Variable_name: audit_whitelist_users
Value:
30 rows inset(0.01 sec)
ERROR:
No query specified
mysql>
安装完成后，重启MySQL服务器
ERROR:
No query specified
mysql> quit
Bye
[root@localhost bin]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS![root@localhost bin]#
8.连接数据库测试
我在这里添加了两条数据
[root@localhost Desktop]# cd /var/log/[root@localhost log]# ls | grep mysql_audit.json[root@localhost log]#[root@localhost log]# ls | grep mysql_audit.json[root@localhost log]#[root@localhost log]#[root@localhost log]#[root@localhost log]#
但是我到这个目录下，找不到 mysql_audit.json
我们只需要把这个文件创建出来，然后修改所有者和所属组就可以了
[root@localhost log]# ls | grep mysql_audit.json[root@localhost log]#[root@localhost log]#[root@localhost log]# touch mysql_audit.json[root@localhost log]#[root@localhost log]# chown mysql:mysql mysql_audit.json[root@localhost log]# tail -f mysql_audit.json{"msg-type":"header","date":"1715911942350","audit-version":"1.1.13-1008","audit-protocol-version":"1.0","hostname":"localhost.localdomain","mysql-version":"5.7.38","mysql-program":"/usr/local/mysql/bin/mysqld","mysql-socket":"/tmp/mysql.sock","mysql-port":"3306","server_pid":"5528"}{"msg-type":"activity","date":"1715911942350","thread-id":"3","query-id":"75","user":"root","priv_user":"root","ip":"192.168.1.104","host":"192.168.1.104","_os":"Windows","_client_name":"libmariadb","_pid":"14792","_thread":"15300","_platform":"AMD64","_client_version":"3.2.3","_server_host":"192.168.1.106","rows":"1","status":"0","cmd":"insert","objects":[{"db":"test","name":"tb_test_h1","obj_type":"TABLE"}],"query":"INSERT INTO `test`.`tb_test_h1` (`id`, `name`, `value`, `age`, `leven`) VALUES (3, 'teww', 'fwer', 23, 'fafaw')"}{"msg-type":"activity","date":"1715911942351","thread-id":"3","query-id":"76","user":"root","priv_user":"root","ip":"192.168.1.104","host":"192.168.1.104","_os":"Windows","_client_name":"libmariadb","_pid":"14792","_thread":"15300","_platform":"AMD64","_client_version":"3.2.3","_server_host":"192.168.1.106","rows":"1","status":"0","cmd":"select","objects":[{"db":"test","name":"tb_test_h1","obj_type":"TABLE"}],"query":"SELECT * FROM `test`.`tb_test_h1` WHERE `id` = 3"}
本文转载自: https://blog.csdn.net/chmod_R_755/article/details/138905953
版权归原作者 hebia0 所有， 如有侵权，请联系我们删除。
版权归原作者 hebia0 所有， 如有侵权，请联系我们删除。