I have two MySQL servers with master/slave replication between them. The connection between them is ADSL. It is not reliable at all. I decided to add another ADSL connection from another ISP with its own IP address.
The problem is that when when one ADSL connection goes down, the other one is useless because the slave is using the first connection's IP address which is down now.
I want to know if is it possible to put a proxysql node between and give its IP to the slave.
Is it possible for proxysql to do fail-over connection handling for the same node? Proxysql must choose the second IP address when the first one is not available.
My Idea. You use dynamic dns to know always, which IP Address is valid, to reach the site with the spotty connection.
A fail over Router like the one in the comment, has the advantage, that it always knows which connection is the better one, and actualizes the dyndns serverip.
For the rest, i would see that both sites build a vpn. Most router can be VPN server as also client and with the dynamic Ip, establishing the connection is no problems, as long any connection actually work. For your replication it is the same network, with a fixed ip.
If you have already a ssl server,all is the same, only you need to bridge the port, so that packet will enter the network.
For all that, you should get some help, because the are many points that can be tweaked, to fit the situation on side.