November 23, 2015 at 11:57NGINX as load balancer for MySQL or MariaDB Galera Cluster
This article is a translation of the original article from Severalnines.
Nginx is well known to all for its advanced features and efficiency as a proxy and / or balancer of low-memory web applications. As a rule, nginx is used on the first “line of defense” of web applications to distribute the load on the backend servers, periodically checking their performance. This technology is quite popular for applications that require increased fault tolerance.
Recently, nginx 1.9 announced support for TCP balancing, about the same as before using HAProxy. One of the biggest drawbacks is that nginx does not support advanced server health checks. This function is necessary if you are using MySQL Galera Cluster. We will talk more about this in the next chapter. It is worth noting that the paid version (called NGINX Plus) does not have this limitation.
In this article, we use nginx as a proxy for MySQL Galera Cluster and try to get high performance. We raised the Galera cluster using ClusterControl on CentOS 7.1; install nginx on the “fresh” host, as indicated in the diagram:
With the advent of synchronously replicated clusters like Galera or NDB, using TCP proxies as a load balancer has become quite popular. All MySQL nodes are processed in the same way, since you can read from all of them, as well as write to all of them. Separation between master and slave is no longer required, as was the case with classic MySQL replication. Nginx is not designed for databases, so you will have to resort to additional configurations to force the cluster to return a “clear" response to a health check.
If you use HAProxy, the health check script on each MySQL server should be able to return HTTP status. For example, if the MySQL server “feels good” - the script will return an HTTP status of 200 OK. Otherwise, the script will return 503 Service unavailable. With these settings, HAProxy can update the routing list and exclude “problematic” servers. Unfortunately, to test the health, HAProxy uses xinetd as a daemon listening on port 9200. These configurations are not in nginx yet.
This flowchart illustrates the process of determining the health of a Galera cluster with setting up a multi-master:
At the time of writing, NGINX Plus (paid) also supported checking the health of servers, but without setting up a custom port.
To get around the limitations, we created a health check script called clustercheck-iptables . This is a background script that checks the operation of the Galera node and adds a redirect port using iptables if the node works as expected (instead of returning an HTTP response). This script can be used with other TCP balancers, with limited functionality checking capabilities, such as nginx (> 1.9), IPVS, keepalived, piranha, distributor, balance or pen.
How does he work? The script checks the operation on each node of the Galera cluster, once per second. If the node is operating normally (wsrep_cluster_state_comment = Synced and read_only = OFF) or (wsrep_cluster_state_comment = Donor and wsrep_sst_method = xtrabackup / xtrabackup-v2), the forwarding port will be raised using iptables (3308 by default, with redirection: 3308)
Otherwise, this rule will be removed from the iptables rule list. On the balancer, you must install port 3308 instead of the default port 3306. If the node is unhealthy, port 3308 will be unavailable, in this case, the balancer should remove this node from the balancing list.
Let's install the script and see how it works in practice:
1. On the database servers - run the following commands to install the script:
2. By default, the script will use the MySQL user “mysqlchk_user” with the password “mysqlchk_password”. We should make sure that the user exists and has the necessary rights to check the health. Run these commands on one of the cluster nodes (Galera will execute them on the other nodes).
** If you want to use another user or password, specify them in the -u and -p arguments. Here then is an example.
3. The script needs iptables to work. In this example, we are using CentOS 7, where firewalld is installed by default. We need to install iptables-services:
Then, we will establish the basic rules for the MySQL Galera cluster, so that iptables does not affect communication with the database:
4. After setting the rules, check them using the command:
5. Test mysqlchk_iptables:
6. Looks good. Now run the script as a daemon:
7. Our routing rules will look something like this:
8. Finally, add the script launch to /etc/rc.local so that it starts at the server start:
In some distributions, it is worth checking that rc.local has the necessary rights to run the script:
On the application side, verify that you can connect to the database on port 3308. Repeat the above steps (except 2) on all (remaining) nodes of the cluster. So, we are done with health checks, now we’ll do balancing.
1. On the balancer (server) - install the necessary packages:
2. Install nginx 1.9 (from the code) with the TCP proxy module:
3. Add the following lines to the nginx (
4. Run nginx:
5. Make sure that nginx is “listening” on port 3307, as we indicated in the configuration. MySQL connections should go through this port, after which they will be transferred to port 3308. Then iptables (on the node) will transfer the request to port 3306, where it “listens” to MySQL:
Excellent! We installed nginx as a load balancer on MySQL Galera Cluster. We pass to testing.
We performed the following tests to check the nginx balance for the Galera cluster:
1. Set read-only = ON and read_only = OFF to g1.local.
2. They killed the mysql process on g1.local and forced SST to start.
3. Kill 2 other database nodes so that g1.local becomes non-primary.
4. Run g1.local from non-primary status.
5. Attached the other 2 nodes.
The screencast below will show the output of several terminals:
The health check of the Galera cluster node was limited by HAProxy, since only HAProxy allowed the use of a custom port. With this script, you can use any TCP load balancer and / or proxy to correctly monitor the Galera cluster nodes.
Nginx is well known to all for its advanced features and efficiency as a proxy and / or balancer of low-memory web applications. As a rule, nginx is used on the first “line of defense” of web applications to distribute the load on the backend servers, periodically checking their performance. This technology is quite popular for applications that require increased fault tolerance.
Recently, nginx 1.9 announced support for TCP balancing, about the same as before using HAProxy. One of the biggest drawbacks is that nginx does not support advanced server health checks. This function is necessary if you are using MySQL Galera Cluster. We will talk more about this in the next chapter. It is worth noting that the paid version (called NGINX Plus) does not have this limitation.
In this article, we use nginx as a proxy for MySQL Galera Cluster and try to get high performance. We raised the Galera cluster using ClusterControl on CentOS 7.1; install nginx on the “fresh” host, as indicated in the diagram:
Health Check
With the advent of synchronously replicated clusters like Galera or NDB, using TCP proxies as a load balancer has become quite popular. All MySQL nodes are processed in the same way, since you can read from all of them, as well as write to all of them. Separation between master and slave is no longer required, as was the case with classic MySQL replication. Nginx is not designed for databases, so you will have to resort to additional configurations to force the cluster to return a “clear" response to a health check.
If you use HAProxy, the health check script on each MySQL server should be able to return HTTP status. For example, if the MySQL server “feels good” - the script will return an HTTP status of 200 OK. Otherwise, the script will return 503 Service unavailable. With these settings, HAProxy can update the routing list and exclude “problematic” servers. Unfortunately, to test the health, HAProxy uses xinetd as a daemon listening on port 9200. These configurations are not in nginx yet.
This flowchart illustrates the process of determining the health of a Galera cluster with setting up a multi-master:
At the time of writing, NGINX Plus (paid) also supported checking the health of servers, but without setting up a custom port.
Using clustercheck-iptables
To get around the limitations, we created a health check script called clustercheck-iptables . This is a background script that checks the operation of the Galera node and adds a redirect port using iptables if the node works as expected (instead of returning an HTTP response). This script can be used with other TCP balancers, with limited functionality checking capabilities, such as nginx (> 1.9), IPVS, keepalived, piranha, distributor, balance or pen.
How does he work? The script checks the operation on each node of the Galera cluster, once per second. If the node is operating normally (wsrep_cluster_state_comment = Synced and read_only = OFF) or (wsrep_cluster_state_comment = Donor and wsrep_sst_method = xtrabackup / xtrabackup-v2), the forwarding port will be raised using iptables (3308 by default, with redirection: 3308)
$ iptables -t nat -A PREROUTING -s $0.0.0.0/0 -p tcp --dport 3308 -j REDIRECT --to-ports 3306
Otherwise, this rule will be removed from the iptables rule list. On the balancer, you must install port 3308 instead of the default port 3306. If the node is unhealthy, port 3308 will be unavailable, in this case, the balancer should remove this node from the balancing list.
Let's install the script and see how it works in practice:
1. On the database servers - run the following commands to install the script:
$ git clone https://github.com/ashraf-s9s/clustercheck-iptables
$ cp clustercheck-iptables/mysqlchk_iptables /usr/local/sbin
2. By default, the script will use the MySQL user “mysqlchk_user” with the password “mysqlchk_password”. We should make sure that the user exists and has the necessary rights to check the health. Run these commands on one of the cluster nodes (Galera will execute them on the other nodes).
mysql> GRANT PROCESS ON *.* TO 'mysqlchk_user'@'localhost' IDENTIFIED BY 'mysqlchk_password';
mysql> FLUSH PRIVILEGES;
** If you want to use another user or password, specify them in the -u and -p arguments. Here then is an example.
3. The script needs iptables to work. In this example, we are using CentOS 7, where firewalld is installed by default. We need to install iptables-services:
$ yum install -y iptables-services
$ systemctl enable iptables
$ systemctl start iptables
Then, we will establish the basic rules for the MySQL Galera cluster, so that iptables does not affect communication with the database:
$ iptables -I INPUT -m tcp -p tcp --dport 3306 -j ACCEPT
$ iptables -I INPUT -m tcp -p tcp --dport 3308 -j ACCEPT
$ iptables -I INPUT -m tcp -p tcp --dport 4444 -j ACCEPT
$ iptables -I INPUT -m tcp -p tcp --dport 4567:4568 -j ACCEPT
$ service iptables save
$ service iptables restart
4. After setting the rules, check them using the command:
$ iptables -L -n
5. Test mysqlchk_iptables:
$ mysqlchk_iptables -t
Detected variables/status:
wsrep_local_state: 4
wsrep_sst_method: xtrabackup-v2
read_only: OFF
[11-11-15 08:33:49.257478192] [INFO] Galera Cluster Node is synced.
6. Looks good. Now run the script as a daemon:
$ mysqlchk_iptables -d
/usr/local/sbin/mysqlchk_iptables started with PID 66566.
7. Our routing rules will look something like this:
$ iptables -L -n -t nat
Chain PREROUTING (policy ACCEPT)
target prot opt source destination
REDIRECT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:3308 redir ports 3306
Chain INPUT (policy ACCEPT)
target prot opt source destination
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
Chain POSTROUTING (policy ACCEPT)
target prot opt source destination
8. Finally, add the script launch to /etc/rc.local so that it starts at the server start:
$ echo '/usr/local/sbin/mysqlchk_iptables -d' >> /etc/rc.local
In some distributions, it is worth checking that rc.local has the necessary rights to run the script:
$ chmod +x /etc/rc.local
On the application side, verify that you can connect to the database on port 3308. Repeat the above steps (except 2) on all (remaining) nodes of the cluster. So, we are done with health checks, now we’ll do balancing.
Installing nginx as a load balancer on a MySQL cluster
1. On the balancer (server) - install the necessary packages:
$ yum -y install pcre-devel zlib-devel
2. Install nginx 1.9 (from the code) with the TCP proxy module:
$ wget http://nginx.org/download/nginx-1.9.6.tar.gz
$ tar -xzf nginx-1.9.6.tar.gz
$ ./configure --with-stream
3. Add the following lines to the nginx (
/usr/local/nginx/conf/nginx.conf) configuration file :stream {
upstream stream_backend {
zone tcp_servers 64k;
server 192.168.55.201:3308;
server 192.168.55.202:3308;
server 192.168.55.203:3308;
}
server {
listen 3307;
proxy_pass stream_backend;
proxy_connect_timeout 1s;
}
}
4. Run nginx:
$ /usr/local/nginx/sbin/nginx
5. Make sure that nginx is “listening” on port 3307, as we indicated in the configuration. MySQL connections should go through this port, after which they will be transferred to port 3308. Then iptables (on the node) will transfer the request to port 3306, where it “listens” to MySQL:
$ netstat -tulpn | grep 3307
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 5348/nginx: master
Excellent! We installed nginx as a load balancer on MySQL Galera Cluster. We pass to testing.
Testing
We performed the following tests to check the nginx balance for the Galera cluster:
1. Set read-only = ON and read_only = OFF to g1.local.
2. They killed the mysql process on g1.local and forced SST to start.
3. Kill 2 other database nodes so that g1.local becomes non-primary.
4. Run g1.local from non-primary status.
5. Attached the other 2 nodes.
The screencast below will show the output of several terminals:
- Terminal 1 (upper left corner): iptables PREROUTING output.
- Terminal 2 (upper right corner): MySQL error.log on g1.local.
- Terminal 3 (center left): application output when connected to the nginx balancer. Shows date, hostname, wsrep_last_committed and wsrep_local_state_comment.
- Terminal 4 (center right): output / var / log / mysqlchk_iptables.
- Terminal 5 (bottom left): output of read_only and wsrep_sst_method to g1.local.
- Terminal 6 (bottom right): operation panel.
Conclusion
The health check of the Galera cluster node was limited by HAProxy, since only HAProxy allowed the use of a custom port. With this script, you can use any TCP load balancer and / or proxy to correctly monitor the Galera cluster nodes.