We currently have replication working through haproxy so that if a node goes down replication continues with one of the other nodes. We're trying to completely replace haproxy with proxysql. Due to this I'm trying to replicate this functionality in proxysql.
Test: 3 on-prem nodes setup as a Percona Galera cluster. 1 on-prem ProxySQL node pointing to the Percona cluster 1 aws EC2 node with Percona installed.
I setup replication between the EC2 node, and one of the 3 on-prem nodes without issue. I can also connect to the DB through proxysql from the aws node.
When ever I stop the slave, and run the following:
CHANGE MASTER TO MASTER_HOST="XX.XXX.XXX.193", MASTER_USER="[username]", MASTER_PASSWORD="[password]", MASTER_AUTO_POSITION = 1, MASTER_PORT = 6033;
I get the following error when I run show slave status:
Slave_IO_State: Waiting to reconnect after a failed registration on master Slave_IO_Running: Connecting Slave_SQL_Running: Yes Last_IO_Errno: 1597 Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Lost connection to MySQL server during query (Errno: 2013)
I can then swap back to directly connecting between DB nodes and replication works fine. Is the mysql backend of proxysql preventing this from working in the same way it does with haproxy?