Multi-master MySQL with Galera

By Curtis Collicutt, Cloud Developer, Edmonton

network patch smallAs we are moving our '€œvclcloud'€ compute nodes to run on top of a disk stripe using solid state drives, I think it'€™s important to look at what the stateful pieces of a system are and how to keep them reasonably available. Our virtual classroom system system, based on Apache VCL and back-ended by its own OpenStack cluster, has only a few stateful parts:

  1. OpenStack glance images

  2. OpenStack nova MySQL database

  3. Files in/etc/vcl on the management node

  4. Apache VCL MySQL database

Everything not listed above can be recreated via our configuration management system (using either Chef or Ansible, as we have implemented deploying Apache VCL in both), and all but point number four have already been dealt with in terms of availability.

That leaves the Apache VCL MySQL database. We are considering solving that problem by replicating the MySQL database across three different virtual machines on three different compute nodes using Galera '€” a synchronous multi-master cluster for MySQL/InnoDB databases, one which theoretically provides availability and consistency within the CAP theorem. In this case I'€™m using the Percona XtraDB Cluster distribution of MySQL and Galera. Another option might be MariaDB and Galera.

Installation of Percona XtraDB Cluster

There is a good '€” and short '€” installation in CentOS document provided by Percona. I'€™ve also put up a basic Ansible playbook on github to install and configure a three (or more) node cluster, and that is what I'€™ll be using in this blog post.

The basic steps being completed by the Ansible playbook are:

  1. Install the required packages (including ones from the Percona repository)
  2. Create the configuration files on each node
  3. Start mysql in bootstrapped mode on one of the nodes (in the playbook the first node is used)

             3. (A) Essentially start the first node'€™s mysqld with: /etc/init.d/mysql start –wsrep-cluster-address="gcomm://"

4. Start mysql server on the remaining nodes

5. Restart the bootstrapped node'€™s mysql normally

First we'€™ll create three instances in our OpenStack environment.

$ for i in 0 1 2; do nova boot --flavor <someflavor> --image <someimage> --key_name <somekey> percona$i; done

Before going further we should make sure that each of these instances '€œlanded'€ on a different compute node. It would not be good to build a multi-master MySQL cluster when all the virtual machines are on the same compute node, because if that hardware node failed all this replication would be for naught.

$ for i in 0 1 2; do nova show percona$i | grep hostId; done
| hostId  | 5732c264fbc3975f4c7c2d7d0df180123a3e3faf2373b5b21070e81b |
| hostId  | fa1bb329b95d691278f7ab116a3329be3de00710e71793af89719aa0 |
| hostId  | a41f8327c98b6fd2527c9a2ce21fc27977d6e23bfdf5bdbda248b2e2 |

The above shows that each percona instance is running on a different compute node/host.

Once those instances are built in OpenStack, we'€™ll configure ansible_hosts with their IP address. (This could done with an Ansible inventory script.)

$ nova list | grep percona
| 1ada8ee6-b3bb-43e2-a39f-20d4addb015a | percona1 | ACTIVE | private_0= |
| 21135570-b76c-44ec-a983-84d4170d204e | percona0 | ACTIVE | private_0= |
| ace6a9eb-c045-4c9d-b904-a75554d4f395 | percona2 | ACTIVE | private_0= |
$ export ANSIBLE_HOSTS='pwd'/ansible_hosts
$ cat ansible_hosts
percona0 ansible_ssh_host=
percona1 ansible_ssh_host=
percona2 ansible_ssh_host=

Let'€™s make sure we can access all the hosts with ansible.

$ ansible -m ping -u root percona
percona1 | success >> {
"changed": false,
"ping": "pong"

percona2 | success >> {
"changed": false,
"ping": "pong"

percona0 | success >> {
"changed": false,
"ping": "pong"

And now run site.yml.  (Note that the playbook is setup to use percona0 as the bootstrap node.)

$ ansible-playbook site.yml
PLAY RECAP *********************
percona0                       : ok=19   changed=17   unreachable=0    failed=0   
percona1                       : ok=12   changed=10   unreachable=0    failed=0   
percona2                       : ok=12   changed=10   unreachable=0    failed=0

Once that completes we can ssh into any of the nodes and check the status of the various MySQL wsrep variables.

$ ssh root@
[root@percona0 ~]# mysql

mysql> show status like 'wsrep_cluster_size';
| Variable_name      | Value |
| wsrep_cluster_size | 3     |
1 row in set (0.00 sec)

To test replication, on percona2 we can create a database:

$ ssh root@
[root@percona2 ~]# mysql
mysql> create database percona;
Query OK, 1 row affected (0.00 sec)

Back on percona0 we can install the new database:

$ ssh root@
Last login: Tue May 28 22:35:32 2013 from
[root@percona0 ~]# mysql
mysql> show databases;
| Database           |
| information_schema |
| mysql              |
| percona            |
| performance_schema |
| test               |
5 rows in set (0.00 sec)

At this point we have a working multi-master MySQL cluster to test Apache VCL with. Fun stuff!

Note that there are some caveats to Galera'€™s use. In some cases there may be changes required to a database if it'€™s being migrated from an existing system, especially with regards to each table having a primary key and using the InnoDB engine.

As usual, if you have any questions, concerns, or criticisms, let me know in the comments. :)