#Demo to show TCP Load balancing with health checks for a Mysql Galera cluster using NGINX Plus

This demo uses NGINX Plus as a TCP load balancer for a MySQL Galera cluster consisting of two mysqld servers. It does round-robin load balancing between the 2 mysqld servers and also does active health checks using an xinetd script running on port 9200 inside each mysqld container. This demo is based on docker and spins' up the following containers:

Galera cluster consisting of two mysqld containers built using this Dockerfile
and of course NGINX Plus

Prerequisites and Required Software

The following software needs to be installed on your laptop:

Docker Toolbox
docker-compose. I used Homebrew to install it: brew install docker-compose As the demo uses NGINX Plus a nginx-repo.crt and nginx-repo.key needs to be copied into the nginxplus/ directory

Setting up & Running the demo

Clone demo repo

$ git clone https://github.com/nginxinc/NGINX-Demos.git
Copy nginx-repo.key and nginx-repo.crt files for your account to ~/NGINX-Demos/mysql-galera-demo/nginxplus/
Move into the demo directory:
```
$ cd ~/NGINX-Demos/mysql-galera-demo
```
If you have run this demo previously or have any docker containers running, start with a clean slate by running
```
$ ./clean-containers.sh
```
NGINX Plus container will be listening on port 8080, 3306 & 9200 on the docker host, and you can get the IP address by running
```
$ docker-machine ip default
192.168.99.100
```
Export this IP into an environment variable HOST_IP export HOST_IP=192.168.99.100 (used by test.sh below)
Spin up the MySQL and NGINX Plus containers:
```
$ docker-compose up -d
```
The first time you issue this, it will build all the Docker images for you.
Now execute the following two docker exec commands in order to start xinetd inside the two mysqld containers
```
$ docker exec -ti mysqld1 nohup service xinetd start
$ docker exec -ti mysqld2 nohup service xinetd start
```
Now follow the steps under section 'Running the demo'

Running the demo

You should have a bunch of containers up and running now:

$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                                                    NAMES
c8850125278d        ktcplb_nginxplus    "nginx -g 'daemon off"   43 minutes ago      Up 42 minutes       0.0.0.0:3306->3306/tcp, 0.0.0.0:8080->8080/tcp, 0.0.0.0:9200->9200/tcp   nginxplus
3696bcca070e        ktcplb_mysqld2      "mysqld --wsrep-clust"   43 minutes ago      Up 43 minutes       0.0.0.0:3308->3306/tcp, 0.0.0.0:9202->9200/tcp                           mysqld2
7f5fd84b0042        ktcplb_mysqld1      "mysqld --wsrep-clust"   43 minutes ago      Up 43 minutes       0.0.0.0:3307->3306/tcp, 0.0.0.0:9201->9200/tcp                           mysqld1

Going to http://<HOST_IP>:8080/ in your favorite browser will bring up the NGINX Plus dashboard. Under the TCP Upstreams tab, you will see two upstream groups named tcp_backend & hc_backend created. The upstreams under the group tcp_backend marked in green indicates that the health checks with the two mysqld servers passed.
You could now execute the script test.sh and see that the total connections count increases for both the upstreams in a round robin fashion thereby implying that NGINX Plus is load balancing the TCP connections. But change the Grant privilege to enable remote access to both the mysqld containers using enable-grant.sh first
```
$ ./mysql_backend/enable-grant.sh
$ ./test.sh
```
The way health checks work here is using a mysqlchk script inside mysql_backend/ directory is litening on port 9200 inside both the mysql containers. It simply executes a show databases command and throws a string output MySQL is running or MySQL is *down* based on the result. And NGINX Plus hits this custom port 9200 on MySQL for health checks using the port parameter added to the health_check directive. The important section of nginx.conf file inside nginxplus/conf dir used here is this
```
upstream hc_backend {
    zone hc_backend 64k;
    server 172.17.42.1:9201;
    server 172.17.42.1:9202;
}
match tcp {
    expect ~ "MySQL is running";
}
server {
    listen 9200;
    status_zone hc_server;
    proxy_pass hc_backend;
}
server {
    listen 3306;
    status_zone tcp_server;
    health_check match=tcp port=9200;
    proxy_pass tcp_backend;
}
```