mysql - 关于mysql percona审计插件的用户过滤

Question

我有一个 mysql 服务器。我使用 Percona 并使用审核日志插件。我对此没有任何问题。

但是使用mysql的子结构中有太多的活跃用户。我只想保留真实用户的审核日志。我想为此使用包含和排除属性。

当我应用下面的percona文章时，它失败了，因为它找不到mysql变量。不添加新变量。

对此有什么建议吗？

https://www.percona.com/doc/percona-server/LATEST/management/audit_log_plugin.html#filtering-by-user

我已将以下行添加到 my.cnf 文件中，但同样没有任何更改。

audit_log_include_accounts = '测试@localhost'

[root@mysql-server ~]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)


[root@mysql-server ~]# yum list installed |grep "percona"
Percona-Server-client-57.x86_64 5.7.22-22.1.el7                @percona
Percona-Server-server-57.x86_64 5.7.11-4.1.el7                 @percona
Percona-Server-shared-57.x86_64 5.7.22-22.1.el7                @percona
Percona-Server-shared-compat-57.x86_64 5.7.22-22.1.el7         @percona
percona-toolkit.x86_64          3.0.10-1.el7                   @percona

[root@mysql-server ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.22-22, for Linux (x86_64) using  6.2

mysql> SHOW VARIABLES LIKE 'audit%';
+---------------------------+--------------------------+
| Variable_name             | Value                    |
+---------------------------+--------------------------+
| audit_log_buffer_size     | 1048576                  |
| audit_log_file            | /var/log/mysql/audit.log |
| audit_log_flush           | OFF                      |
| audit_log_format          | JSON                     |
| audit_log_handler         | FILE                     |
| audit_log_policy          | ALL                      |
| audit_log_rotate_on_size  | 10485760                 |
| audit_log_rotations       | 10                       |
| audit_log_strategy        | ASYNCHRONOUS             |
| audit_log_syslog_facility | LOG_USER                 |
| audit_log_syslog_ident    | percona-audit            |
| audit_log_syslog_priority | LOG_INFO                 |
+---------------------------+--------------------------+
12 rows in set (0.01 sec)


mysql> SET GLOBAL audit_log_include_accounts = 'test@localhost';
ERROR 1193 (HY000): Unknown system variable 'audit_log_include_accounts'

mysql>
mysql> SET GLOBAL audit_log_include_accounts = NULL;
ERROR 1193 (HY000): Unknown system variable 'audit_log_include_accounts'

[root@mysql-server ~]# cat/etc/my.cnf

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[isamchk]
key_buffer_size = 16M

[mysqld]
audit_log_file = /var/log/mysql/audit.log
audit_log_format = JSON
audit_log_policy = ALL
audit_log_rotate_on_size = 10M
audit_log_rotations = 10

basedir = /usr
bind-address = 0.0.0.0
binlog-format = ROW
datadir = /var/lib/mysql
default-storage-engine = innodb
expire_logs_days = 7
explicit_defaults_for_timestamp = 1

innodb-buffer-pool-size = 1G
innodb-file-per-table = 1
innodb-flush-log-at-trx-commit = 2
innodb-flush-method = O_DIRECT
innodb-log-file-size = 512M
innodb-log-files-in-group = 2

key_buffer_size = 16M
log-error = /var/log/mysql/mysqld.log
log-queries-not-using-indexes = 0

max-connect-errors = 1000000
max-connections = 1000
max-heap-table-size = 512M
max_allowed_packet = 16M
max_binlog_size = 100M

open-files-limit = 65535
pid-file = /var/run/mysqld/mysqld.pid
port = 3306

query-cache-type = 1
query_cache_limit = 12M
query_cache_size = 16M

server_id = 1
skip-external-locking
skip-name-resolve
slave-skip-errors = 1062,1053
slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
socket = /var/lib/mysql/mysql.sock
ssl = false
ssl-ca = /etc/mysql/cacert.pem
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem
sync-binlog = 0
sysdate-is-now = 1
table-definition-cache = 4096
table-open-cache = 4096
thread_cache_size = 8
thread_stack = 256K
tmp_table_size = 16M
tmpdir = /tmp
user = mysql

[mysqld-5.0]
myisam-recover = BACKUP

[mysqld-5.1]
myisam-recover = BACKUP

[mysqld-5.5]
myisam-recover = BACKUP

[mysqld-5.6]
myisam-recover-options = BACKUP

[mysqld-5.7]
myisam-recover-options = BACKUP

[mysqld_safe]
log-error = /var/log/mysql/mysqld_safe.log
nice = 0
socket = /var/lib/mysql/mysql.sock

[mysqldump]
max_allowed_packet = 16M
quick
quote-names

!includedir /etc/my.cnf.d

Answer 1

我找到了问题的原因。版本需要升级。当我升级版本时，变量会自动出现，我可以进行更改。

但是，文档中支持的版本是5.7。但不完全是。

我当前的版本:“5.7.11-4.1.el7”我升级到的版本:'5.7.24-27.1.el7'