2.linux 9 mysql 5.7.44 开启审计功能

szsqs

已于 2024-12-24 19:32:24 修改

阅读量263

点赞数 2

文章标签： linux mysql android

于 2024-12-24 19:24:32 首次发布

本文链接：https://blog.csdn.net/szsqs/article/details/144701215

版权

# linux 9 mysql 5.7.44 开启审计功能

# 环境说明

提前安装好 mysql 并正常使用

# mysql 安装教程请查看另外一篇文章
https://blog.csdn.net/szsqs/article/details/144701199?spm=1001.2014.3001.5502

# 创建审计日志目录

[root@localhost ~]# mkdir -pv /usr/local/mysql/logs/audit
mkdir: created directory '/usr/local/mysql/logs/audit'
[root@localhost ~]#

# 修改权限

[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql
[root@localhost ~]#

# 登入 mysql 执行如下命令，可以查看 mysql 数据对应的插件文件存放位置

[root@localhost ~]# mysql -uroot -p'**********' -e "show global variables like 'plugin_dir';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| plugin_dir    | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
[root@localhost ~]#

# 安装审计插件

[root@localhost ~]# mysql -uroot -p'**********' -e "install plugin audit_log soname 'audit_log.so';"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]#

# 安装成功，查看审计参数配置

[root@localhost ~]# mysql -uroot -p'**********' -e "show variables like '%audit%';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------------------+---------------+
| Variable_name               | Value         |
+-----------------------------+---------------+
| audit_log_buffer_size       | 1048576       |
| audit_log_exclude_accounts  |               |
| audit_log_exclude_commands  |               |
| audit_log_exclude_databases |               |
| audit_log_file              | audit.log     |
| audit_log_flush             | OFF           |
| audit_log_format            | OLD           |
| audit_log_handler           | FILE          |
| audit_log_include_accounts  |               |
| audit_log_include_commands  |               |
| audit_log_include_databases |               |
| audit_log_policy            | ALL           |
| audit_log_rotate_on_size    | 0             |
| audit_log_rotations         | 0             |
| audit_log_strategy          | ASYNCHRONOUS  |
| audit_log_syslog_facility   | LOG_USER      |
| audit_log_syslog_ident      | percona-audit |
| audit_log_syslog_priority   | LOG_INFO      |
+-----------------------------+---------------+
[root@localhost ~]#

# 在 my.cnf 添加审计配置

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]#

#开启审计日志
# 设置审计日志策略，ASYNCHRONOUS(默认，使用内存buffer，如果buffer满，不删除消息)，PERFORMANCE(使用内存buffer，如果buffer满删除消息)，SEMISYNCHRONOUS(日志直接到文件，没有刷新和同步)，SYNCHRONOUS(日志直接到文件，每个事件都要刷新和同步)
audit_log_strategy = ASYNCHRONOUS
# 以文件的方式记录
audit_log_handler = FILE 
# 指定日志文件，可以指定绝对路径，没有指定路径，将存储在数据目录
audit_log_file = /usr/local/mysql/logs/audit/audit.log
# 设置为ON，能关闭和重新打开日志，用于手工进行日志轮转
audit_log_flush = ON
# 指定审计日志的内存buffer，当audit_log_strategy为ASYNCHRONOUS，PERFORMANCE时
audit_log_buffer_size = 1048576
# 指定审计日志格式，OLD记录日志为xml属性，NEW记录日志为XML标签
audit_log_format = JSON
# 指定记录哪些事件，ALL(所有事件)，LOGINS(仅仅用户登录事件)，QUERIES(仅仅查询语句)，NONE(不记录任何事件)
audit_log_policy = ALL
# 设置审计日志的大小，当日志到设置值，将自动轮转
audit_log_rotate_on_size = 1073741824
# 指定审计日志文件数量
audit_log_rotations = 0
# 
audit_log = FORCE_PLUS_PERMANENT

# 重新加载服务配置文件

[root@localhost ~]# systemctl daemon-reload
[root@localhost ~]#

# 启动 mysqld 服务

[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]#

# 查看 mysqld 服务

[root@localhost ~]# systemctl status mysqld
● mysqld.service - Mysql
     Loaded: loaded (/etc/systemd/system/mysqld.service; disabled; preset: disabled)
     Active: active (running) since Wed 2024-12-24 00:22:12 CST; 22s ago
    Process: 3887 ExecStart=/usr/local/mysql/support-files/mysql.server start (code=exited, status=0/SUCCESS)
   Main PID: 3901 (mysqld_safe)
      Tasks: 57 (limit: 10754)
     Memory: 376.3M
        CPU: 910ms
     CGroup: /system.slice/mysqld.service
             ├─3901 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/mysql.pid
             └─5027 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mys>

Dec 24 00:22:08 localhost.localdomain systemd[1]: Starting Mysql...
Dec 24 00:22:12 localhost.localdomain mysql.server[3887]: Starting MySQL.... SUCCESS!
Dec 24 00:22:12 localhost.localdomain systemd[1]: Started Mysql.
lines 1-15/15 (END)

# 查看 mysql 启动日志

[root@localhost ~]# tail /usr/local/mysql/logs/mysql-error/mysql_error.log
2024-12-24T00:22:11.668759+08:00 0 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2024-12-24T00:22:11.668790+08:00 0 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2024-12-24T00:22:11.668822+08:00 0 [Note] Server socket created on IP: '0.0.0.0'.
2024-12-24T00:22:11.671363+08:00 0 [Note] Failed to start slave threads for channel ''
2024-12-24T00:22:11.673697+08:00 0 [Note] Event Scheduler: Loaded 0 events
2024-12-24T00:22:11.673745+08:00 0 [Note] WSREP: Read nil XID from storage engines, skipping position init
2024-12-24T00:22:11.673749+08:00 0 [Note] WSREP: wsrep_load(): loading provider library 'none'
2024-12-24T00:22:11.673789+08:00 1 [Note] Event Scheduler: scheduler thread started with id 1
2024-12-24T00:22:11.673866+08:00 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.44-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
[root@localhost ~]#

# 添加开机启动 nysqld 服务

[root@localhost ~]# systemctl enable mysqld
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /etc/systemd/system/mysqld.service.
[root@localhost ~]#

# 查看3306端口状态

[root@localhost ~]# netstat -tunpl|grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      5027/mysqld         
[root@localhost ~]#

# 查看审计日志

[root@localhost ~]# tail /usr/local/mysql/logs/audit/audit.log
{"audit_record":{"name":"Audit","record":"1_2024-12-24T16:22:08","timestamp":"2024-12-24T16:22:08 UTC","mysql_version":"5.7.44-log","startup_optionsi":"--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/logs/mysql-error/mysql_error.log --pid-file=/usr/local/mysql/mysql.pid --socket=/usr/local/mysql/mysql.sock --port=3306 --wsrep_recover --log_error=/usr/local/mysql/data/wsrep_recovery.CHIsGD --pid-file=/usr/local/mysql/data/localhost.localdomain-recover.pid","os_version":"x86_64-Linux"}}
{"audit_record":{"name":"NoAudit","record":"2_2024-12-24T16:22:08","timestamp":"2024-12-24T16:22:08 UTC","mysql_version":"5.7.44-log","startup_optionsi":"--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/logs/mysql-error/mysql_error.log --pid-file=/usr/local/mysql/mysql.pid --socket=/usr/local/mysql/mysql.sock --port=3306 --wsrep_recover --log_error=/usr/local/mysql/data/wsrep_recovery.CHIsGD --pid-file=/usr/local/mysql/data/localhost.localdomain-recover.pid","os_version":"x86_64-Linux"}}
{"audit_record":{"name":"Audit","record":"1145_2024-12-24T16:22:09","timestamp":"2024-12-24T16:22:11 UTC","mysql_version":"5.7.44-log","startup_optionsi":"--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/logs/mysql-error/mysql_error.log --pid-file=/usr/local/mysql/mysql.pid --socket=/usr/local/mysql/mysql.sock --port=3306 --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1","os_version":"x86_64-Linux"}}
[root@localhost ~]#

# 到此审计插件安装结束