ProxySQL user not connecting - XtraDB

Question

I'm trying to set up a xtraDB Cluster consisting of 3 nodes. Now i've got the cluster up and running and per the instructions i'm trying to set up ProxySQL for load balancing.

So i've installed proxySQL on all 3 nodes. And now i'm trying to configure it with the admin tool. But everytime that i run the command:

[root@node1 log]# proxysql-admin --config-file=/etc/proxysql-admin.cnf --enable

It comes back with:

This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)

ProxySQL read/write configuration mode is singlewrite
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'proxysql_admin'@'' (using password: YES)
Please check the ProxySQL connection parameters! Terminating.

Now i would say the error is pretty straight forward. However i have the user proxysql_admin properly defined in both the MySQL cluster and in the .cnf files.

Here's my proxysql-admin.cnf file:

# proxysql admin interface credentials.
export PROXYSQL_DATADIR='/var/lib/proxysql'
export PROXYSQL_USERNAME='proxysql_admin'
export PROXYSQL_PASSWORD='placeholder_pass'
export PROXYSQL_HOSTNAME='localhost'
export PROXYSQL_PORT='6032'

# PXC admin credentials for connecting to pxc-cluster-node.
export CLUSTER_USERNAME='proxysql_admin'
export CLUSTER_PASSWORD='placeholder_pass'
#export CLUSTER_HOSTNAME='localhost'
export CLUSTER_HOSTNAME='ccloud'
export CLUSTER_PORT='3306'

# proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.
export MONITOR_USERNAME='monitor'
export MONITOR_PASSWORD='placeholder_pass'

# Application user to connect to pxc-node through proxysql
export CLUSTER_APP_USERNAME='proxysql_user'
export CLUSTER_APP_PASSWORD='placeholder_pass'

# ProxySQL read/write hostgroup 
export WRITE_HOSTGROUP_ID='10'
export READ_HOSTGROUP_ID='11'

# ProxySQL read/write configuration mode.
export MODE="singlewrite"

# ProxySQL Cluster Node Priority File
export HOST_PRIORITY_FILE=$PROXYSQL_DATADIR/host_priority.conf

Here's the user on the mysql cluster:

mysql> select User,Host from mysql.user;
+----------------+-----------+
| User           | Host      |
+----------------+-----------+
| proxysql_admin |           |
| proxysql_admin | %         |
| mysql.session  | localhost |
| mysql.sys      | localhost |
| proxysql_admin | localhost |
| proxysql_user  | localhost |
| root           | localhost |
| sstuser        | localhost |
+----------------+-----------+

I've tried changing up the interfaces a few times but that doesn't seem to be the problem. Anyone have any idea why my user isn't connecting?

Just in case, here's the my.cnf of the cluster on the node i'm trying this on:

#
# The Percona XtraDB Cluster 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/my.cnf.d/
!includedir /etc/percona-xtradb-cluster.conf.d/

[mysqld]
server-id=1
datadir=/mysql-data
socket=/mysql-data/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid

wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

wsrep_cluster_name=ccloud
wsrep_cluster_address=gcomm://ip1,ip2,ip3

wsrep_node_name=pxc1
wsrep_node_address=ip1

wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:placeholder_pass

pxc_strict_mode=ENFORCING

binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

Answer 1

Turns out i had created the admin user in the mysql users table manually in ProxySQL and that was not allowed.

Removing the proxysql database file /var/lib/proxysql/proxysql.db and restarting the proxysql service (rolling back to the default database) fixed this issue for me.

Also documented in: https://github.com/sysown/proxysql/issues/709

Answer 2

Looks like your proxysql db doesn't have user proxysql_admin. Please use default proxysql user credentials (admin/admin).