In this article, we’ll build a highly available MySQL database service. We’ll actually use MariaDB (a fork of MySQL) and build two database servers. Then we’ll use Galera to do bidirectional replication, and we’ll put the whole thing behind the HAProxy pair that we built in the last article, Redundant Load Balancers – HAProxy and Keepalived. We can then use this cluster for our high availability OpenStack deployment. I’ve chosen MariaDB because they’ve got a version built for Galera available in an apt repo for Ubuntu 14.04.
So, to start, I’ve built two Ubuntu 14.04 servers and given them the following hostnames and IP addresses:
- MySQL1 (192.168.1.33)
- MySQL2 (192.168.1.34)
Next, we will add the MariaDB repo and install the MariaDB and Galera packages:
Note that there are a number of mirrors for this repo. You can get the optimum mirror by using the MariaDB repo configuration tool. Once the software has been installed, we can create the Galera configuration file:
Notice that we need to set the IP addresses for the wsrep_cluster_address to match the addresses of our two nodes, then on each node, we set use the local hostname and IP address in the last two lines.
Next, we need to edit the MySQL configuration to remove the bind-address statement (not sure why this is necessary but it seems to break the HAProxy configuration if you don’t do it). Just comment out the following line using a #:
Complete all of the above steps on both nodes, and then we should stop the mysql service on both nodes:
Now we need to copy the contents of /etc/mysql/debian.cnf from node 1 to node 2 (there are some passwords in that file that need to match across both nodes once we’ve got them clustered). Once that’s complete, we can create the cluster. On the first node, we start the mysql service with as special argument to create the new cluster:
And on the second node, we start the usual way:
That’s that. We’ve got two MySQL nodes replicating, but we need to do some configuration to put our load balancers out front. If you’ll recall from the HAProxy article, we built two HAProxy nodes (192.168.1.30, 192.168.1.31) and created a virtual IP address (VIP) of 192.168.1.32. HAProxy will check to see that MySQL is up and running by logging onto the service. Therefore, we’ll need to create a MySQL that can logon from either HAProxy node. On one of the MySQL nodes, logon to MySQL as root and enter the following:
Notice that I specified the IP addresses of my HAProxy nodes, and I used the username ‘haproxy’.
Now we need to head over to our HAProxy nodes and do some work over there. The fist thing we need to do is install the mysql client. Remember, HAProxy will logon to MySQL to check that the service is running, so we need the client.
Then, we need to add a stanza to our haproxy.cfg to proxy MySQL traffic. Remember, we’ve already got global,defaults, and stats stanzas in the file from the HAProxy article. Just add the following stanza to the end of the file:
Notice that I’m using the VIP (192.168.1.32) for the listener, and I’m pointing to my two MySQL servers (192.168.1.33 and 34). Also notice, that the mysql-check user is haproxy, that I added to MySQL earlier.
We then just have to reload the HAProxy configuration on both nodes to have it take effect :
Now, you should be able to access MySQL through the load balancer:
That’s that. We now have a load balanced, redundant database service for our OpenStack deployment.