Not the answer you need?
Register and ask your own question!

Proxysql-admin for PXC HA configuration failing

prince85prince85 ContributorCurrent User Role Novice
Hi,

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

Error:

[[email protected] 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 [email protected]% |
+
+
| GRANT USAGE ON *.* TO 'monitor'@'%' |
+
+
1 row in set (0.00 sec)

mysql> show grants for 'admin'@'%';
+
+
| Grants for [email protected]% |
+
+
| GRANT USAGE ON *.* TO 'admin'@'%' |
+
+
1 row in set (0.00 sec)

Below are the proxysql-admin.cnf

[[email protected] 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"

Comments

  • przemekprzemek Percona Support Engineer Percona Staff Role
    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:
    https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/proxysql.html
  • prince85prince85 Contributor Current User Role Novice
    What is the difference in the variable that You have mentioned and the one I have mentioned?
  • przemekprzemek Percona Support Engineer Percona Staff Role
    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'
    
  • prince85prince85 Contributor Current User Role Novice
    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 [email protected]% | +
    + | GRANT USAGE ON *.* TO 'admin'@'%' | +
    + 1 row in set (0.00 sec)
  • przemekprzemek Percona Support Engineer Percona Staff Role
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.