6

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.

Paulo Boaventura
  • 1,365
  • 1
  • 9
  • 29
Behdad
  • 184
  • 3
  • 12
  • fail-over thing can be done in your application level which is connecting to the database. If connection with slave one fails then it will connect to slave 2 – Aabir Hussain Jan 31 '20 at 12:12
  • I prefer hardware solutions with fail over like https://www.draytek.co.uk/information/our-technology/failover – nbk Jan 31 '20 at 23:06
  • @AabirHussain I think it is reinventing the wheel. – Behdad Feb 01 '20 at 20:15
  • @nbk We can use hardware solutions but it doesn't solve the problem: anyway it has 2 ips and this ip changes when one goes down – Behdad Feb 01 '20 at 20:18
  • no, the fail over will garantee that at least one connection is available. What you also need is a dynamic IP adress which gives always the right ip to a address. And the router always updates your current ip, doesn't matter which provider, to that address There are also check Router thtat can umts or LTE as another option. – nbk Feb 01 '20 at 20:28

2 Answers2

0

ADSL is generally not a fixed IP, which means that the server does not recognize it.

As I understand it, you want to balance the load.

Analyzing your doubt. I leave my case study.

1 - How each server is separate. Place a fixed IPv4 from there, create a fixed DNS and point to the corresponding fixed IP. Also place a domain and add the DNS configuration. If you have IPv6 (AAA), add it for even more answers. And prioritize cryptography.

If you need a secure proxy, use CloudFlaure to manage your settings. It helps the configuration become more practical.

2 - Pointing the DNS to fixed IP, create NS for both servers. example: ns1.server.com //// ns2.server.com I recommend creating 4 NS for each server.

3 - The same procedure is used on the slave. but do not use ns, switch to another silga, for example: (tl1.server.com). This facilitates orientation.

So, for each server. An IPv4 An Ipv6 Four NS Two DNS

Therefore, when the first server goes down, the other server takes over. Be on IPV4 or IPV6. And the DNS and the DNS are congested or are not recognized for some reason, promptly the other takes over. So, without loss.

If there is a problem with your hosting, create IP or domains. Due to fees.

There are some free ones and, that work 90%, cloudflare itself has this service.

If I was able to understand your demand, I recommend the services later.

It works for me.

Submit questions and follow the case.

Paulo Boaventura
  • 1,365
  • 1
  • 9
  • 29
  • thanks for your answer but it is the same as accepted answer – Behdad Feb 05 '20 at 19:40
  • I'm sorry to disagree with you. But the answer is not the same, as I am referring to the possibilities of configuring a load balance and not just a connection between two machines, as our friend's answer was. It's ok – Paulo Boaventura Feb 11 '20 at 19:34
-1

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.

nbk
  • 45,398
  • 8
  • 30
  • 47
  • actually I had done it with vpn server and client.But I'd like to hear more comments to find best solution – Behdad Feb 04 '20 at 08:06
  • I had gove an answer, that anybody with that probem can use, besides you didn't mention anything about a vpn. – nbk Feb 05 '20 at 18:13
  • I accepted your answer because it is an alternate answer with failover and dynamic dns.I've not tested it , but it can be another option. using proxysql can be the third way to do it. – Behdad Feb 05 '20 at 19:39
  • I personally don't think that is a solution to your problem, because the proxysql would hide the sql Server(cluster and the user would alny connect to one ip.but in your case you don't know at a giving time the ip that could reach the server. But let me know how it goes. And somone downvoted me again,great. – nbk Feb 05 '20 at 19:48
  • Our adsl connections have static ip address, then we know their ip address at first. The problem was that one server could have two ips, one at a time.you mentioned hardware fail-over solution .I think it can be used by software fail over(proxysql) solution simultaneously. – Behdad Feb 05 '20 at 19:57
  • That would cost a lot of ip packet rewriting, to make that work. – nbk Feb 05 '20 at 20:05