With our MariaDB Galera cluster configured, each node can take traffic, and the writes are seamlessly replicated to other nodes in the cluster. We could use any of the MariaDB node addresses and place them in our configuration files, but if that node failed, we would not have a database to attach to and our OpenStack environment would fail. A possible solution to this is to front the MariaDB cluster using Load Balancing. Given that any of the nodes can take reads and writes, with data consistency, Load Balancing is a great solution. Generally, physical Load Balancers, such as those from F5 or Brocade, are recommended. In the absence of physical Load Balancers, High Availability (HA) Proxy can be used.
Install two servers, both running Ubuntu 14.04, which are configured on the same management network as our OpenStack environment and the MariaDB Galera cluster. In the following steps, the two nodes will be on the IP addresses 172.16.0.248 and 172.16.0.249. In the next recipe, we will configure these two nodes with a FloatingIP address (that will be set up using keepalived) of 172.16.0.251. This address will be used when we configure the database connections in our OpenStack configuration files.
As we are setting up identical servers to act in a pair, we will configure a single server first and then repeat the process for the second server. The first will utilize the IP address 172.16.0.248. We then repeat the steps utilizing the IP address 172.16.0.249.
To configure HA Proxy for MariaDB Galera Load Balancing, carry out step 1 to step 5 twice to create two HA Proxy instances—both configured to access our MariaDB Galera nodes.
- We first install HA Proxy using the usual
apt-getprocess, as follows:sudo apt-get update sudo apt-get install haproxy
- With HA Proxy installed, we'll simply configure this first proxy server appropriately for our MariaDB Galera cluster. To do this, we edit the
/etc/haproxy/haproxy.cfgfile and insert the following contents:global log 127.0.0.1 local0 log 127.0.0.1 local1 notice maxconn 4096 user haproxy group haproxy daemon defaults log global mode http option tcplog option dontlognull retries 3 option redispatch maxconn 4096 timeout connect 50000ms timeout client 50000ms timeout server 50000ms # MySQL Load Balance Pool listen mysql 0.0.0.0:3306 mode tcp balance roundrobin option tcpka option mysql-check user haproxy server mysql1 172.16.0.191:3306 weight 1 server mysql2 172.16.0.192:3306 weight 1 server mysql3 172.16.0.193:3306 weight 1
- Save and exit the file, and then start up HA Proxy with the following command:
sudo sed -i 's/^ENABLED.*/ENABLED=1/' /etc/defaults/haproxy sudo service haproxy start
- Before we can use this HA Proxy server to access our three MariaDB nodes, we must create the user specified in the
haproxy.cfgfile that is used to do a very simple check to see if MariaDB is up and running. To do this, we add a user into our cluster that is simply able to connect to MariaDB. Using themysqlclient on any of our Galera nodes, create the userhaproxywith no password set. This user is allowed access from the IP address of the HA Proxy server. Run the following commands:mysql -u root -h localhost -e "GRANT ALL ON *.* to haproxy@'172.16.0.248';"
- We carry out a similar step to add in a
rootuser to our MySQL Galera cluster that has permission to run MySQL commands originating from the HA Proxy servers:mysql -u root -h localhost -e "GRANT ALL ON *.* to root@'172.16.0.248' IDENTIFIED BY 'openstack' WITH GRANT OPTION;"
HA Proxy is a very popular and useful proxy and Load Balancer that makes it ideal for fronting a MariaDB cluster to add load-balancing capabilities. It is simple to set up the service to front MariaDB.
The first requirement is listening on the appropriate port, which for MariaDB is 3306. The listen line in the configuration files here also specifies that it will listen on all addresses by using 0.0.0.0 as the address, but you can bind this to a particular address by specifying this to add an extra layer of control in our environment.
To use MariaDB, the mode must be set to tcp and we set keepalived with the tcpka option to ensure long-lived connections are not interrupted and closed when a client opens up a connection to our MariaDB servers.
The Load Balance method used is round robin, which is perfectly suitable for a multi-master cluster where any node can perform reads and writes.
We add in a basic check to ensure that our MariaDB servers are marked offline appropriately. Using the inbuilt mysql-check option (which requires a user to be set up in MariaDB to log in to the MariaDB nodes and quit), when a MariaDB server fails, the server is ignored and traffic passes to a MariaDB server that is alive. Note that it does not perform any checks for whether a particular table exists—though this can be achieved with more complex configurations using a check script running on each MariaDB server and calling the check script as part of our checks.
The final configuration step for HA Proxy is listing the nodes and the addresses that they listen on, which forms the Load Balance pool of servers.