mysql5.7审计功能开启_开启mysql审计功能

小程序710 更新时间：2025-06-08 20:01:47

2023年8月1日发(作者：)

mysql5.7审计功能开启_开启mysql审计功能下⾯⽅式是在线开启，重启后会失效：社区版没有审计插件，先获取server_⽂件，我是先在⼀台测试服务器上安装了⼀个mariadb数据库，然后搜索find / -name server_⽂件，scp复制出来的，也可以直接去mariadb官⽹下载⼀个⼆进制包找；2.获取到server_包后，把⽂件cp到mysql插件的路径下：cp /tmp/server_ /usr/local/mysql/lib/plugin/3.登录mysql安装插件mysql> INSTALL PLUGIN server_audit SONAME 'server_';Query OK, 0 rows affected (0.00 sec)4.检查审计功能是否开启，没有开启则开启mysql>show variables like '%audit%';+-------------------------------+-----------------------+| Variable_name | Value |+-------------------------------+-----------------------+| server_audit_events | || server_audit_excl_users | || server_audit_file_path | server_ || server_audit_file_rotate_now | OFF || server_audit_file_rotate_size | 1000000 || server_audit_file_rotations | 9 || server_audit_incl_users | || server_audit_loc_info | || server_audit_logging | OFF || server_audit_mode | 1 || 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 |+-------------------------------+-----------------------+16 rows in set (0.00 sec)5.开启审计功能mysql> set global server_audit_logging=on;Query OK, 0 rows affected (0.00 sec)6.⽤ show variables like '%audit%';查看是否已经开启，开启后ON代表开启，OFF代表没有开启mysql> show variables like '%audit%';+-------------------------------+-----------------------+| Variable_name | Value |+-------------------------------+-----------------------+| server_audit_events | || server_audit_excl_users | || server_audit_file_path | server_ || server_audit_file_rotate_now | OFF || server_audit_file_rotate_size | 1000000 || server_audit_file_rotations | 9 || server_audit_incl_users | || server_audit_loc_info | || server_audit_logging | ON || server_audit_mode | 1 || 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 |+-------------------------------+-----------------------+16 rows in set (0.00 sec)7.查看审计效果[root@smiletest mysql]#tail -f server_20190903 09:24:18,smiletest,root,localhost,2,0,FAILED_CONNECT,,,1 09:24:23,smiletest,root,localhost,3,0,CONNECT,,,020190903 09:24:23,smiletest,root,localhost,3,3,QUERY,,'select @@version_comment limit 1',020190903 09:24:25,smiletest,root,localhost,3,4,QUERY,,'SHOW VARIABLES LIKE '%audit%'',020190903 09:24:45,smiletest,root,localhost,3,5,QUERY,,'show databases',020190903 09:25:12,smiletest,root,localhost,3,0,DISCONNECT,,,08.参数说明，都可以通过 set global的⽅式修改server_audit_output_type：指定⽇志输出类型，可为SYSLOG或FILEserver_audit_logging：启动或关闭审计server_audit_events：指定记录事件的类型，可以⽤逗号分隔的多个值(connect,query,table)，如果开启了查询缓存(query cache)，查询直接从查询缓存返回数据，将没有table记录server_audit_file_path：如server_audit_output_type为FILE，使⽤该变量设置存储⽇志的⽂件，可以指定⽬录，默认存放在数据⽬录的server_⽂件中server_audit_file_rotate_size：限制⽇志⽂件的⼤⼩server_audit_incl_users：指定哪些⽤户的活动将记录，connect将不受此变量影响，该变量⽐server_audit_excl_users优先级⾼server_audit_excl_users：该列表的⽤户⾏为将不记录，connect将不受该设置影响案例只审计test账户其它账户不审计grant all on *.* to test@'172.16.1.%' identified by 'test@123';flushprivileges;INSTALL PLUGIN server_audit SONAME 'server_';set global server_audit_logging=on;set global server_audit_file_rotate_size=10000000;10Mset global server_audit_incl_users='test';show variables like '%audit%';下⾯⽅式是中断业务开启，再次重启后不会失效：社区版没有审计插件，先获取server_⽂件，我是先在⼀台测试服务器上安装了⼀个mariadb数据库，然后搜索find / -name server_⽂件，scp复制出来的，也可以直接去mariadb官⽹下载⼀个⼆进制包找；2.获取到server_包后，把⽂件cp到mysql插件的路径下：cp /tmp/server_ /usr/local/mysql/lib/plugin/3.编辑配置vim /etc/#开启审计功能server_audit_logging=on#指定审计⽇志⽂件存放路径，这⾥的路径要注意了，这个⽂件路径必须经是Mysql权限⾥server_audit_file_path =/data/mysql/server_ver_audit_file_rotate_size=10000000#防⽌server_audit 插件被卸载，需要在配置⽂件中添加server_audit=FORCE_PLUS_PERMANENT4.重启mysql服务/etc/init.d/mysqld restart5.查看审计功能是否开启mysql> show variables like '%audit%';

2023年8月1日发(作者：)

mysql5.7审计功能开启_开启mysql审计功能下⾯⽅式是在线开启，重启后会失效：社区版没有审计插件，先获取server_⽂件，我是先在⼀台测试服务器上安装了⼀个mariadb数据库，然后搜索find / -name server_⽂件，scp复制出来的，也可以直接去mariadb官⽹下载⼀个⼆进制包找；2.获取到server_包后，把⽂件cp到mysql插件的路径下：cp /tmp/server_ /usr/local/mysql/lib/plugin/3.登录mysql安装插件mysql> INSTALL PLUGIN server_audit SONAME 'server_';Query OK, 0 rows affected (0.00 sec)4.检查审计功能是否开启，没有开启则开启mysql>show variables like '%audit%';+-------------------------------+-----------------------+| Variable_name | Value |+-------------------------------+-----------------------+| server_audit_events | || server_audit_excl_users | || server_audit_file_path | server_ || server_audit_file_rotate_now | OFF || server_audit_file_rotate_size | 1000000 || server_audit_file_rotations | 9 || server_audit_incl_users | || server_audit_loc_info | || server_audit_logging | OFF || server_audit_mode | 1 || 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 |+-------------------------------+-----------------------+16 rows in set (0.00 sec)5.开启审计功能mysql> set global server_audit_logging=on;Query OK, 0 rows affected (0.00 sec)6.⽤ show variables like '%audit%';查看是否已经开启，开启后ON代表开启，OFF代表没有开启mysql> show variables like '%audit%';+-------------------------------+-----------------------+| Variable_name | Value |+-------------------------------+-----------------------+| server_audit_events | || server_audit_excl_users | || server_audit_file_path | server_ || server_audit_file_rotate_now | OFF || server_audit_file_rotate_size | 1000000 || server_audit_file_rotations | 9 || server_audit_incl_users | || server_audit_loc_info | || server_audit_logging | ON || server_audit_mode | 1 || 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 |+-------------------------------+-----------------------+16 rows in set (0.00 sec)7.查看审计效果[root@smiletest mysql]#tail -f server_20190903 09:24:18,smiletest,root,localhost,2,0,FAILED_CONNECT,,,1 09:24:23,smiletest,root,localhost,3,0,CONNECT,,,020190903 09:24:23,smiletest,root,localhost,3,3,QUERY,,'select @@version_comment limit 1',020190903 09:24:25,smiletest,root,localhost,3,4,QUERY,,'SHOW VARIABLES LIKE '%audit%'',020190903 09:24:45,smiletest,root,localhost,3,5,QUERY,,'show databases',020190903 09:25:12,smiletest,root,localhost,3,0,DISCONNECT,,,08.参数说明，都可以通过 set global的⽅式修改server_audit_output_type：指定⽇志输出类型，可为SYSLOG或FILEserver_audit_logging：启动或关闭审计server_audit_events：指定记录事件的类型，可以⽤逗号分隔的多个值(connect,query,table)，如果开启了查询缓存(query cache)，查询直接从查询缓存返回数据，将没有table记录server_audit_file_path：如server_audit_output_type为FILE，使⽤该变量设置存储⽇志的⽂件，可以指定⽬录，默认存放在数据⽬录的server_⽂件中server_audit_file_rotate_size：限制⽇志⽂件的⼤⼩server_audit_incl_users：指定哪些⽤户的活动将记录，connect将不受此变量影响，该变量⽐server_audit_excl_users优先级⾼server_audit_excl_users：该列表的⽤户⾏为将不记录，connect将不受该设置影响案例只审计test账户其它账户不审计grant all on *.* to test@'172.16.1.%' identified by 'test@123';flushprivileges;INSTALL PLUGIN server_audit SONAME 'server_';set global server_audit_logging=on;set global server_audit_file_rotate_size=10000000;10Mset global server_audit_incl_users='test';show variables like '%audit%';下⾯⽅式是中断业务开启，再次重启后不会失效：社区版没有审计插件，先获取server_⽂件，我是先在⼀台测试服务器上安装了⼀个mariadb数据库，然后搜索find / -name server_⽂件，scp复制出来的，也可以直接去mariadb官⽹下载⼀个⼆进制包找；2.获取到server_包后，把⽂件cp到mysql插件的路径下：cp /tmp/server_ /usr/local/mysql/lib/plugin/3.编辑配置vim /etc/#开启审计功能server_audit_logging=on#指定审计⽇志⽂件存放路径，这⾥的路径要注意了，这个⽂件路径必须经是Mysql权限⾥server_audit_file_path =/data/mysql/server_ver_audit_file_rotate_size=10000000#防⽌server_audit 插件被卸载，需要在配置⽂件中添加server_audit=FORCE_PLUS_PERMANENT4.重启mysql服务/etc/init.d/mysqld restart5.查看审计功能是否开启mysql> show variables like '%audit%';

本文发布于:2023-08-01，感谢您对本站的认可！

本文链接:http://torson.com.cn/chengxu/1690851841a446671.html