ProxySQL causing 1000s of "Access denied for user " alerts in MySQL error log.

[Dawid Jordaan]

I have a 5 node Percona cluster. ProxySQL is installed on each node and keepalived is used to create a VIP. This also happens on my 3 node cluster (same setup).

My error log on each node is filled with this alerts. Why is ProxySQL trying to connect to MySQL as me and how do I find out what is happening so that I can turn it off.

There are tens of thousands of these in my /var/log/mysqld.log:

2018-09-13T14:12:28.900937Z 207968 [Note] Access denied for user 'user'@'localhost' (using password: NO)

2018-09-13T14:12:32.245798Z 207970 [Note] Access denied for user 'user'@'localhost' (using password: NO)

2018-09-13T14:12:35.567506Z 207971 [Note] Access denied for user 'user'@'localhost' (using password: NO)

2018-09-13T14:12:38.899147Z 207972 [Note] Access denied for user 'user'@'localhost' (using password: NO)

2018-09-13T14:12:42.225250Z 207974 [Note] Access denied for user 'user'@'localhost' (using password: NO)

2018-09-13T14:12:45.550799Z 207975 [Note] Access denied for user 'user'@'localhost' (using password: NO)

'user'@'localhost' is my personal user that I used to set up ProxySQL. This user has no access to ProxySQL or MySQL.

I set ProxySQL up manually using these steps:

sudo yum install proxysql

Edit proxysql.cnf to look like this:

######START OF CONFIG#####

datadir="/var/lib/proxysql"

admin_variables =

{

        admin_credentials="admin:admin;proxyadmin:admin"

        mysql_ifaces="0.0.0.0:6032"

        cluster_username="proxyadmin"

        cluster_password="admin"

        cluster_check_interval_ms=200

        cluster_check_status_frequency=100

        cluster_mysql_query_rules_save_to_disk=true

        cluster_mysql_servers_save_to_disk=true

        cluster_mysql_users_save_to_disk=true

        cluster_proxysql_servers_save_to_disk=true

        cluster_mysql_query_rules_diffs_before_sync=3

        cluster_mysql_servers_diffs_before_sync=3

        cluster_mysql_users_diffs_before_sync=3

        cluster_proxysql_servers_diffs_before_sync=3

}

mysql_variables =

{

        monitor_username="monitor"

        monitor_password="monit0r"

        monitor_history=600000

        monitor_connect_interval=60000

        monitor_ping_interval=10000

        monitor_read_only_interval=1500

        monitor_read_only_timeout=500

        ping_interval_server_msec=120000

        ping_timeout_server=500

        commands_stats=true

        sessions_sort=true

        connect_retries_on_failure=10

}

######END OF CONFIG#####

sudo service proxysql start

--Log into the main node and check if it is configured

mysql -uadmin -p --host=127.0.0.1 --port=6032

--Add the mysql nodes

INSERT INTO `mysql_servers` VALUES 

('10','10.20.30.100','3306','ONLINE','1000000','0','1000','0','0','0','WRITE'),

('11','10.20.30.101','3306','ONLINE','1000','0','1000','0','0','0','READ'),

('11','10.20.30.102','3306','ONLINE','100000','0','1000','0','0','0','READ'),

('11','10.20.30.103','3306','ONLINE','1000','0','1000','0','0','0','READ'),

('11','10.20.30.104','3306','ONLINE','1000','0','1000','0','0','0','READ');

LOAD MYSQL SERVERS TO RUNTIME;

--Add the mysql_replication_hostgroups

INSERT INTO mysql_replication_hostgroups VALUES (10,11,'prd-clus');

LOAD MYSQL SERVERS TO RUNTIME;

SAVE MYSQL SERVERS TO DISK;

--Add 3 user to ProxySQL

INSERT INTO `mysql_users` VALUES 

('proxysql_user','*PASSWORD','1','0','11','','0','1','0','1','1','10000'),

('usr_ro','*PASSWORD','1','0','11',NULL,'0','1','0','1','1','10000'),

('usr_rw','*PASSWORD','1','0','10',NULL,'0','1','0','1','1','10000');

LOAD MYSQL USERS TO RUNTIME;

SAVE MYSQL USERS TO DISK;

--Adding Galera Support 

INSERT INTO `scheduler` VALUES 

('1','1','3000','/bin/proxysql_galera_checker','--config-file=/etc/proxysql-admin.cnf --write-hg=10 --read-hg=11 --writer-count=1 --mode=singlewrite  --log=/var/lib/proxysql/vcs-prd-clus_proxysql_galera_check.log',NULL,NULL,NULL,NULL,'vcs-prd-clus');

LOAD SCHEDULER TO RUNTIME;

When I stop ProxySQL, the "2018-09-13T14:12:28.900937Z 207968 [Note] Access denied for user 'user'@'localhost' (using password: NO)" alerts stop and when I start it up, it immediately starts flooding the error log again.

Any assistance will be greatly appreciated.

[René Cannaò]

The error entry is every ~3 seconds.

Scheduler is configured to call proxysql_galera_checker every 3 seconds.

You can easily guess it is some misconfiguration in /etc/proxysql-admin.cnf, or a bug in Percona proxysql-admin .

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.