Proxysql-admin for PXC HA configuration failing

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi,

I’m getting below error while configuring PXC HA using Proxysql-admin.

Error:

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

This script will assist with configuring ProxySQL for use with
Percona XtraDB Cluster (currently only PXC in combination
with ProxySQL is supported)

ProxySQL read/write configuration mode is singlewrite
ERROR 2005 (HY000): Unknown MySQL server host ‘pxc-cluster’ (2)
ERROR (line:612) : PXC connection check failed.
– Could not connect to the PXC cluster at pxc-cluster:3306
– Please check the PXC connection parameters and status.

Below are the PXC details.

mysql> show variables like ‘wsrep_cluster%’;
±----------------------±--------------------------------------------------+
| Variable_name | Value |
±----------------------±--------------------------------------------------+
| wsrep_cluster_address | gcomm://192.168.50.83,192.168.50.84,192.168.50.85 |
| wsrep_cluster_name | pxc-cluster |
±----------------------±--------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for ‘monitor’@‘%’;
±------------------------------------+
| Grants for monitor@% |
±------------------------------------+
| GRANT USAGE ON . TO ‘monitor’@‘%’ |
±------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for ‘admin’@‘%’;
±----------------------------------+
| Grants for admin@% |
±----------------------------------+
| GRANT USAGE ON . TO ‘admin’@‘%’ |
±----------------------------------+
1 row in set (0.00 sec)

Below are the proxysql-admin.cnf

[root@qtsxtradb01 tmp]# cat /etc/proxysql-admin.cnf

proxysql admin interface credentials.

export PROXYSQL_DATADIR=‘/var/lib/proxysql’
export PROXYSQL_USERNAME=‘admin’
export PROXYSQL_PASSWORD=‘admin’
export PROXYSQL_HOSTNAME=‘localhost’
export PROXYSQL_PORT=‘6032’

PXC admin credentials for connecting to pxc-cluster-node.

export CLUSTER_USERNAME=‘admin’
export CLUSTER_PASSWORD=‘admin’
export CLUSTER_HOSTNAME=‘pxc-cluster’
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=‘monit0r’

Application user to connect to pxc-node through proxysql

export CLUSTER_APP_USERNAME=‘proxysql_user’
export CLUSTER_APP_PASSWORD=‘passw0rd’

ProxySQL read/write hostgroup

export WRITE_HOSTGROUP_ID=‘10’
export READ_HOSTGROUP_ID=‘11’

ProxySQL read/write configuration mode.

export MODE=“singlewrite”

Writer-is-reader configuration

export WRITER_IS_READER=“ondemand”

2

This variable is wrong:

export CLUSTER_HOSTNAME='pxc-cluster'

It basically should be a real cluster host name or IP address where the admin tool can connect to and get the rest of the cluster details.
Check more details here:
[url]Redirecting

3

What is the difference in the variable that You have mentioned and the one I have mentioned?

4

I only quoted it from your configuration file. The ‘pxc-cluster’ does not look like a valid hostname to me. Try a real IP number instead, like:

export CLUSTER_HOSTNAME='192.168.50.83'
5

The one you have mentioned below this is the IP of one of the node in the cluster. Do I have to mention the one of the node IP in the cluster as cluster host name? Please confirm.
export CLUSTER_HOSTNAME=‘192.168.50.83’ also, the below login is having only usage permission is it enough of need more permission. export CLUSTER_USERNAME=‘admin’ export CLUSTER_PASSWORD=‘admin’ mysql> show grants for ‘admin’@‘%’; ±----------------------------------+ | Grants for admin@% | ±----------------------------------+ | GRANT USAGE ON . TO ‘admin’@‘%’ | ±----------------------------------+ 1 row in set (0.00 sec)

6

Yes, it is supposed to be a hostname or IP of one of the cluster node, doesn’t matter which one. The proxysql-admin will connect to this node and will read the IPs of the other nodes automatically.
Before the tool can discover the cluster addresses and do it’s job, you need to indeed add a user that has enough privileges. And “usage” is not enough. I’d suggest rather:

GRANT RELOAD, PROCESS, SELECT, CREATE USER ON *.* TO 'admin'@'192.168.%' WITH GRANT OPTION;

Later, you will need application users also added to ProxySQL. You may do it manually, or use the proxysql-admin tool with “–adduser” or “–syncusers” options.