Right Approach to Setup Database Cluster (Fail Over)

April 25, 2017 796 views
Storage DigitalOcean Networking Nginx Firewall High Availability Load Balancing Ubuntu 16.04

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.

1) Load Balancer
2) 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

2 comments
  • I prefer to only allow internal IP addresses to access mysql, if you need external connectivity then use phpmyadmin for example, to connect to your database and execute queries from it web UI.

  • I agree with you, internal IP will be alot more secured. But for my scenario i am planning to have 2 Database Server (Master-Master Replication). What is the right way to setup its access point? (Floating IP? Load Balancer?) While having them accessible internally only

2 Answers
jtittle1 April 25, 2017
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.

@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.

by Jesin A
HAProxy is an open source software which can load balance HTTP and TCP servers. In the previous article on HAProxy we configured load balancing for HTTP and in this one we'll do the same for MySQL. All your MySQL servers have to be configured to perform Master-Master replication as load balancing involves both reading and writing to all the backends.
Have another answer? Share your knowledge.