Mysql Load Balancing with Nginx (Ubuntu)

Environment setup:

Mysql master server : 192.168.1.10
Mysql slave server : 192.168.1.12

Nginx server : 192.168.1.14

1. Add Nginx configurations

Add below configuration to nginx main configuration file (/etc/nginx/nginx.conf) just after the http block     

stream {

    upstream mysql_cluster {

        server 192.168.1.10:3306; # node1

        server 192.168.1.12:3306 backup; # node2

    }

    server {

        listen 3306; 

        proxy_pass mysql_cluster;

    }

}

2. Open 3306 port in Nginx server

Since we are using 3306 port, we need to use SELinux and open port privileges

Try below command in Nginx server

sudo semanage port -l | grep http_port_t

check port 3306 is there, if not we can add it by using below command

sudo semanage port -a -t http_port_t  -p tcp 3306 

3. Enable firewall

 If you are using CentOS, you may use below command to enable firewall rules in Nginx server

firewall-cmd --zone=public --add-port=3306/tcp --permanent

firewall-cmd --reload