NGINX as a load balancer for MySQL or MariaDB Galera Cluster
This article is a translation of the original article from the Severalnines website.
Nginx is well known to everyone for its advanced features and performance as a proxy and / or low memory memory balancer. As a rule, nginx is used on the first “line of defense” of web applications in order 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 thing that was previously implemented using HAProxy. One of the biggest drawbacks is that nginx does not support advanced server health checks. This feature is required if you are using MySQL Galera Cluster. We will talk about this in more detail in the next chapter. It should be noted that in the paid version (called NGINX Plus) there is no such 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 a “fresh” host, as shown in the diagram:
With the advent of synchronously replicated clusters like Galera or NDB, the use of TCP proxy 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, and you can also write to all of them. The separation between master and slave is no longer required, as was the case with the classic MySQL replication. Nginx is not designed for databases, so you have to resort to additional configurations in order to force the cluster to return a “clear” answer to the health check.
If you use HAProxy, the health check script on each MySQL server must be able to return HTTP status. For example, if the MySQL server “feels good”, the script returns HTTP status 200 OK. Otherwise, the script will return 503 Service unavailable. With such settings, HAProxy can update the routing list and exclude “problematic” servers. Unfortunately, for testing, HAProxy uses xinetd as a daemon listening on port 9200. These configurations are not yet available in nginx.
This flowchart illustrates the process of determining the health of a Galera cluster with setting up a multi-master:
At the time of this writing, NGINX Plus (paid) also supported server health checks, but without custom port settings.
To get around the limitations, we created a health check script called clustercheck-iptables . This is a background script that checks the performance of the Galera node and adds a redirecting 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 checks, such as nginx (> 1.9), IPVS, keepalived, piranha, distributor, balance or pen.
How does he work? The script checks the performance on each node of the Galera cluster, once a second. If the node is working in normal mode (wsrep_cluster_state_comment = Synced and read_only = OFF) or (wsrep_cluster_state_comment = Donor and wsrep_sst_method = xtrabackup / xtrabackup-v2), the redirection port will be lifted using iptables (3308).
Otherwise, this rule will be removed from the list of iptables rules. On the balancer, you must set port 3308 instead of the default port 3306. If the node is unhealthy, port 3308 will be unavailable, the balancer in such a case 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 need to make sure that the user exists and has the necessary rights to test the functionality. Execute these commands on one of the nodes of the cluster (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 is an example.
3. The script needs iptables to work. In this example, we use CentOS 7, where firewalld is installed by default. We need to install iptables-services:
Then, set the basic rules for the MySQL Galera cluster, so that iptables does not affect communication with the database:
4. After installing 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 start of the server:
In some distributions it’s worth checking that rc.local has the necessary permissions to run the script:
On the application side, check 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’ve finished with performance 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 configuration file (
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 MySQL is “listening”:
Fine! We installed nginx as a load balancer for the MySQL Galera Cluster. We proceed to testing.
We performed the following tests to check the nginx balancing for the Galera cluster:
1. Install read-only = ON and read_only = OFF on g1.local.
2. Killed the mysql process on g1.local and forced SST on startup.
3. 2 other DB nodes are killed 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:
Verifying the performance of the nodes of the Galera cluster was limited to 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 nodes of the Galera cluster.
Nginx is well known to everyone for its advanced features and performance as a proxy and / or low memory memory balancer. As a rule, nginx is used on the first “line of defense” of web applications in order 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 thing that was previously implemented using HAProxy. One of the biggest drawbacks is that nginx does not support advanced server health checks. This feature is required if you are using MySQL Galera Cluster. We will talk about this in more detail in the next chapter. It should be noted that in the paid version (called NGINX Plus) there is no such 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 a “fresh” host, as shown in the diagram:
Health check
With the advent of synchronously replicated clusters like Galera or NDB, the use of TCP proxy 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, and you can also write to all of them. The separation between master and slave is no longer required, as was the case with the classic MySQL replication. Nginx is not designed for databases, so you have to resort to additional configurations in order to force the cluster to return a “clear” answer to the health check.
If you use HAProxy, the health check script on each MySQL server must be able to return HTTP status. For example, if the MySQL server “feels good”, the script returns HTTP status 200 OK. Otherwise, the script will return 503 Service unavailable. With such settings, HAProxy can update the routing list and exclude “problematic” servers. Unfortunately, for testing, HAProxy uses xinetd as a daemon listening on port 9200. These configurations are not yet available in nginx.
This flowchart illustrates the process of determining the health of a Galera cluster with setting up a multi-master:
At the time of this writing, NGINX Plus (paid) also supported server health checks, but without custom port settings.
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 performance of the Galera node and adds a redirecting 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 checks, such as nginx (> 1.9), IPVS, keepalived, piranha, distributor, balance or pen.
How does he work? The script checks the performance on each node of the Galera cluster, once a second. If the node is working in normal mode (wsrep_cluster_state_comment = Synced and read_only = OFF) or (wsrep_cluster_state_comment = Donor and wsrep_sst_method = xtrabackup / xtrabackup-v2), the redirection port will be lifted using iptables (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 list of iptables rules. On the balancer, you must set port 3308 instead of the default port 3306. If the node is unhealthy, port 3308 will be unavailable, the balancer in such a case 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 need to make sure that the user exists and has the necessary rights to test the functionality. Execute these commands on one of the nodes of the cluster (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 is an example.
3. The script needs iptables to work. In this example, we use 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, set 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 installing 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 start of the server:
$ echo '/usr/local/sbin/mysqlchk_iptables -d' >> /etc/rc.local In some distributions it’s worth checking that rc.local has the necessary permissions to run the script:
$ chmod +x /etc/rc.local On the application side, check 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’ve finished with performance 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 configuration file (
/usr/local/nginx/conf/nginx.conf ): 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 MySQL is “listening”:
$ netstat -tulpn | grep 3307 tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 5348/nginx: master Fine! We installed nginx as a load balancer for the MySQL Galera Cluster. We proceed to testing.
Testing
We performed the following tests to check the nginx balancing for the Galera cluster:
1. Install read-only = ON and read_only = OFF on g1.local.
2. Killed the mysql process on g1.local and forced SST on startup.
3. 2 other DB nodes are killed 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 (left below): read_only and wsrep_sst_method output to g1.local.
- Terminal 6 (bottom right): working panel.
Conclusion
Verifying the performance of the nodes of the Galera cluster was limited to 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 nodes of the Galera cluster.
Source: https://habr.com/ru/post/271467/
All Articles