Right Approach to Setup Database Cluster (Fail Over)

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