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?
Best Answer
This sounds really bad. Replication and Galera clustering will (I think) get confused if a slave/node is suddenly talking to a different master/node.
Sure, use HAProxy or proxysql for connecting clients to slaves/nodes. And have rerouting happen. That's their purpose.