I have a mysql server. I use Percona and I use the audit log plugin. I have no problem with this.
But there are too many active users in the sub-structure using mysql. I just want to keep the audit logs of real users. I want to use the include and exclude properties for this.
When I apply the following percona article, it fails because it cannot find the mysql variable. Does not add new variable.
Any advice on this?
https://www.percona.com/doc/percona-server/LATEST/management/audit_log_plugin.html#filtering-by-user
I have added the following line to my.cnf file, but again nothing has changed.
audit_log_include_accounts = 'test @ 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
I found the cause of the problem. The version needs to be upgraded. When I upgraded the version, the variables came automatically and I can make changes.
However, the supported version is 5.7 in the documentation. But not exactly.
my current version: '5.7.11-4.1.el7' The version I upgraded to: '5.7.24-27.1.el7'