Right Approach to Setup Database Cluster (Fail Over)

Question

Hi Guys,

I am trying to find out what is the right way to setup a Database Cluster with redundancy measures.

From my research I have summarised that there are 2 ways i can go about implementing this.

Load Balancer
Floating IP

But I am confuse with the go about to restrict external access to the Load Balancer IP address or Floating IP Address.

From what i gathered. It is best practise to only allow internal IP address within the network.

Appreciate your guidance. Thomas

Jonathan Tittle · Accepted Answer

@thomasleerab60d To expand on my previous answer, it is very much possible to use NGINX instead of HAProxy to load balance across multiple MySQL servers, or even a cluster that goes beyond Master-Master and other configurations such as Master-Slave. NGINX has a stream module which is what would be used. The configuration is a little different from the norm as you can’t include stream blocks in the http block like you would with server blocks. For example, if NGINX has the stream module built in, inside of the nginx.conf file, we can add our configuration right above the http block. We might have something that looks like: stream { upstream db { server 11.22.33.444:3306; } server { listen 3306; proxy_pass db; proxy_connect_timeout 1s; } } http { ... ... ... } Now what the above does is tells NGINX to listen on Port 3306. When a request comes through on that port, it’ll get proxied to 11.22.33.444:3306, which would be our database server. Of course, this is a very basic setup, doesn’t include health checks, etc. Additional servers can be added to upstream like so: stream { upstream db { server 11.22.33.444:3306; server 11.22.33.445:3306; server 11.22.33.446:3306; server 11.22.33.447:3306; } server { listen 3306; proxy_pass db; proxy_connect_timeout 1s; } } There’s a lot we can do here, so this is just the start and it’s one option as an alternative to HAProxy.

Jonathan Tittle · Answer

@thomasleerab60d Floating IP’s are just that, an IP that is capable of “floating” from one instance to the next, so long as that instance happens to be in the same data center. A Floating IP doesn’t automatically connect to one server or another unless you make the change manually or via the API. So in the case of a FIP, should one MySQL Master go down, you’d have to make the swap. It’s also important to note that with Floating IP’s, you don’t want to bind anything to the floating IP. You’d still bind to the Public IPv4 or Private IPv4 IP of the Droplet. Floating IP’s are simply means to increase uptime, though it takes a little more work since you have to do the swap. … Load Balancing would be a better option. There’s actually a guide for it here . In the above guide, you’d setup one Droplet as the HAProxy Load Balancer and two Droplets as your MySQL servers. The HAProxy server distributes the load across the two MySQL servers according to how it’s set up.

Become a contributor for community

DigitalOcean Documentation

Resources for startups and AI-native businesses

The developer cloud

Start building today

Report this

Right Approach to Setup Database Cluster (Fail Over)

Related questions

Has anybody deployed their own database cluster across data centers in a region?

How well are data centers within the same region isolated for high availability?

How much downtime to expect from single cluster DB

DO Cloud Firewall with VRRP(Keepalived)

High availability infrastructure with Load Balancer, HAProxy and Managed Databases

Max number of concurrent TCP connections to droplet?

devOps for payment gateway

How does browser know about the nearest Load Balancer

How to know managed postgres backups location ?

How does the digital ocean floating IP work? Could it essentially replace the need for a 'failover' Load Balancer?

Deployment of a scalable wordpress site

stopping pacemaker doesn't move resources to other node

Report this

Right Approach to Setup Database Cluster (Fail Over)

Related questions

Has anybody deployed their own database cluster across data centers in a region?

How well are data centers within the same region isolated for high availability?

How much downtime to expect from single cluster DB

DO Cloud Firewall with VRRP(Keepalived)

High availability infrastructure with Load Balancer, HAProxy and Managed Databases

Max number of concurrent TCP connections to droplet?

devOps for payment gateway

How does browser know about the nearest Load Balancer

How to know managed postgres backups location ?

How does the digital ocean floating IP work? Could it essentially replace the need for a 'failover' Load Balancer?

Deployment of a scalable wordpress site

stopping pacemaker doesn't move resources to other node