Using NGINX as a TCP Load Balancer for your MYSQL or any other Database!

As your application grows, you will find yourself at a point where you need to scale your infrastructure. In this blog post, we will discuss using NGINX as a TCP load balancer while horizontally scaling MYSQL or any database that you are using, it doesn't really matter that much as our load balancer will be a layer 4 / TCP load balancer.
Using a TCP load balancer will have its pros like:

• Round Robin the databases, and avoid heavy load on just one
• All the databases can be accessed via the same IP or hostname from your application
• If one of the nodes goes down, you can easily avoid it and keep your service running
• Horizontal scaling will be easier and so on
  Of course it has its cons as well, as it is layer 4, you cannot parse the queries and send all the insert / update / delete queries to one DB and read queries to others, (there's kinda a way to tackle this) and, if your load balancer goes down, your entire application goes down as it won't be able to communicate with your database. You'll need to be careful with your infrastructure design.

Say you have 4 database servers, 1 Master, and 3 Slaves, now all your write queries should go to your master while your read queries may be load balanced to all 4 of them.
To achieve this, what I thought we could do was, make nginx listen on 2 different ports, 3306 and 33060. And then configure our application to make database connections in such a way that if they need to read data, connect to one port, and if they need to write data, connect to another port. While this might be slightly difficult with applications of monolithic architecture, it should be easier with microservcies.
You wouldn't want to write data to your slave instance as it could cause some serious issues.
Now, instead of 4 databases with 1 master and 3 slaves, if you have 2 databases with Master - Master replication, there might be other issues but you will not need to worry about this 2 ports thingy.

Let's spin up a few docker container and create our infrastructure.

version: '3'

networks: 
  nginx_mariadb:

services:
  load_balancer:
    image: nginx:stable-alpine
    container_name: nginx-load-balancer
    ports: 
      - "3306:3306"
      - "33060:33060"
    volumes: 
      - ./nginx/nginx_mariadb.conf:/etc/nginx/nginx.conf
    depends_on:
      - mariadb-master
      - mariadb-slave
    networks:
      - nginx_mariadb

  mariadb-master:
    image: 'bitnami/mariadb:latest'
    hostname: "master.db.abhizer.local"
    container_name: mariadb_master
    volumes:
      - ./mariadb:/var/lib/mysql
    restart: unless-stopped
    tty: true
    environment: 
      MARIADB_REPLICATION_MODE: master
      MARIADB_DATABASE: whateverdb
      MARIADB_USER: whateveruser
      MARIADB_PASSWORD: whateverpassword
      MARIADB_ROOT_PASSWORD: rootpassword
      MARIADB_REPLICATION_USER: replicationuser
      MARIADB_REPLICATION_PASSWORD: replicationpassword
    networks:
      - nginx_mariadb

  mariadb-slave:
    image: 'bitnami/mariadb:latest'
    hostname: "slave.db.abhizer.local"
    container_name: mariadb_slave
    restart: unless-stopped
    tty: true
    environment:
      MARIADB_REPLICATION_MODE: slave
      MARIADB_REPLICATION_USER: replicationuser
      MARIADB_REPLICATION_PASSWORD: replicationpassword
      MARIADB_MASTER_HOST: "master.db.abhizer.local"
      MARIADB_MASTER_PORT_NUMBER: 3306
      MARIADB_MASTER_ROOT_PASSWORD: rootpassword
    networks:
      - nginx_mariadb
    depends_on:
      - mariadb-master

Here, I'm using the bitnami images because it is easier to set up the Master - Slave replication.
Here, our master is "master.db.abhizer.local" and our slave is "slave.db.abhizer.local". And, note that all three of them are in the same internal network and only the ports 3306 and 33060 of the nginx load balancer are exposed.
The docker compose is pretty self explanatiory once you look at it.
Our nginx config file, nginx_mariadb.conf which is mounted to /etc/nginx/nginx.conf inside the docker contains:

worker_processes 1;
events {
    worker_connections 1024;
}
stream {
    log_format log_stream '$remote_addr - [$time_local] $protocol $status $bytes_sent $bytes_received $session_time "$upstream_addr"';
    access_log /var/log/nginx/mysql.log log_stream;

    upstream mariadb_read {
        server master.db.abhizer.local:3306; 
        server slave.db.abhizer.local:3306;
    }

    server {
        listen 3306;
        proxy_pass mariadb_read; 
        proxy_connect_timeout 1s;
        error_log /var/log/nginx/mysql_error.log;
    }

    upstream mariadb_write {
        server master.db.abhizer.local:3306;
    }

    server {
        listen 33060;
        proxy_pass mariadb_write;
        proxy_connect_timeout 1s;
        error_log /var/log/nginx/mysql_error.log;
    }
}

Here, I've set up two different upstreams, mariadb_read and mariadb_write. The read points to both the master and slave while the write only points to the master. Here, by default, nginx uses round robin, but you can change that to other algorithms as per your liking.

• You can also give a weight to one of the servers so that the weight is taken into consideration. To do this all you'd need to do is:
  server master.db.abhizer.local:3306 weight=5;
• You can also, setup one to be the backup and only be accessed when the other is down:
  server slave.db.abhizer.local:3306 backup;
  Now here, we're also storing the TCP logs:
  log_format log_stream '$remote_addr - [$time_local] $protocol $status $bytes_sent $bytes_received $session_time "$upstream_addr"';
  This defines our log format log_stream (that's the name of the log_format, you can change this to whatever you like).
  As you can see in the conf file, nginx listens on both 3306 and 33060 and proxies all the packets sent to its 3306 to the servers in the mariadb_read upstream and proxies all the packets sent to its 33060 to mariadb_write.
  This way, you can use the same hostname (of the nginx server) and in all your applications and just set the port as per your need.
  Now, let's add the hostname of our nginx to our /etc/hosts file.
  sudo echo "127.0.0.1 db.abhizer.local" > /etc/hosts
  Let's try connecting to port 3306:

mysql -h db.abhizer.local -u root -p 

	select @@hostname;

Now, let's try connecting to port 33060:

mysql -h db.abhizer.local -P 33060 -u root -p 

	select @@hostname;

Let's try exitting and reconnecting to port 33060.

As you can see, nginx alternates you between the master and slave when you connect to 33060 and only connects you to the master when you connect to 3306!
Now all you need to do is set this up with your application!

That's all for today, if you have any queries please feel free to email me at abhizer@abhizer.com