Introduction

When dealing with relational database systems in a production environment, it is often best to have some kind of replication procedures in place. Replication allows your data to be transferred to different nodes automatically.

A simple master-slave replication is most common in the SQL world. This allows you to use one "master" server to handle all of the application writes, while multiple "slave" servers can be used to read data. It is possible to configure failover and other techniques.

While master-slave replication is useful, it is not as flexible as master-master replication. In a master-master configuration, each node is able to accept writes and distribute them throughout the cluster. MariaDB does not have a stable version of this by default, but a set of patches known as "Galera" implement synchronous master-master replication.

In this guide, we will be creating a Galera cluster using Ubuntu 12.04 VPS instances. We will be using three servers for demonstration purposes (the smallest configurable cluster), but five nodes are recommended for production situations.

Add the MariaDB Repositories

The MariaDB and Galera packages are not available in the default Ubuntu repositories. However, the MariaDB project maintains its own repositories for Ubuntu that contain all of the packages that we need.

On each of the three servers that we will be configuring for this cluster, you need to first install the python-software-properties package. This will give us the commands we need to administer our repositories:

sudo apt-get update
sudo apt-get install python-software-properties

Now, we can add the key files for the MariaDB repository. This will tell our server that we trust the maintainers of the repositories and that we can install the packages within them without a problem.

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db

This will accept the key file. Now that we have the trusted key in the database, we can add the actual repository:

sudo add-apt-repository 'deb http://mirror.jmu.edu/pub/mariadb/repo/5.5/ubuntu precise main'

Install MariaDB with Galera Patches

We can now easily install MariaDB with the Galera patches through the apt interface. Remember to update the database first:

sudo apt-get update
sudo apt-get install mariadb-galera-server galera

During the installation, you will be asked to set a password for the MariaDB administrative user. You can set the same password across all of the server instances.

If, for some reason, you do not already have rsync installed on your machines, you should install it now by typing:

sudo apt-get install rsync

We now have all of the pieces necessary to begin configuring our cluster.

Configure MariaDB and Galera

Now that we have installed the MariaDB and Galera on each of our three servers, we can begin configuration.

The cluster will actually need to share its configuration. Because of this, we will do all of the configuration on our first machine, and then copy it to the other nodes.

On your first server, we're going to create a separate file with settings for our cluster.

By default, MariaDB is configured to check the /etc/mysql/conf.d directory for additional files to augment its behavior. We can create a file in this directory with all of our cluster-specific directives:

sudo nano /etc/mysql/conf.d/cluster.cnf

Copy and paste the following configuration into the file. We will explain what you need to change and what each piece means:

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://first_ip,second_ip,third_ip"

# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

# Galera Node Configuration
wsrep_node_address="this_node_ip"
wsrep_node_name="this_node_name"

The first section modifies or re-asserts some MariaDB/MySQL settings that will allow MySQL to function correctly.

The section labeled "Galera Provider Configuration" is used to to configure the MariaDB components that provide a WriteSet replication API. This means Galera in our case, since Galera is a wsrep (WriteSet Replication) provider.

We can specify general parameters to configure the initial replication environment. You can find more about Galera configuration options here. Generally, you don't need to do too much to get a working set though.

The "Galera Cluster Configuration" section defines the cluster that we will be creating. It defines the cluster members by IP address or resolvable domain names and it creates a name for the cluster to ensure that members join the correct group.

The "Galera Synchronization Configuration" section defines how the cluster will communicate and synchronize data between members. This is used only for the state transfer that happens when a node comes online. For our initial setup, we are simply using rsync, because it pretty much does what we want without having to use exotic components.

The "Galera Node Configuration" section is used simply to clarify the IP address and the name of the current server. This is helpful when trying to diagnose problems in logs and to be able to reference each server in multiple ways. The name can be anything you would like.

When you are satisfied with your cluster configuration file, you should copy the contents to each of the individual nodes.

Remember to change the "Galera Node Configuration" section on each individual server.

When you have this configuration on each server, with the "Galera Node Configuration" section customized, you should save and close the files.

Copying Debian Maintenance Configuration

Currently, Ubuntu and Debian's MariaDB servers use a special maintenance user to do routine maintenance. Some tasks that fall outside of the maintenance category also are run as this user, including important functions like stopping MySQL.

With our cluster environment being shared between the individual nodes, the maintenance user, who has randomly generated login credentials on each node, will be unable to execute commands correctly. Only the initial server will have the correct maintenance credentials, since the others will attempt to use their local settings to access the shared cluster environment.

We can fix this by simply copying the contents of the maintenance file to each individual node:

On one of your servers, open the Debian maintenance configuration file:

sudo nano /etc/mysql/debian.cnf

You will see a file that looks like this:

[client]
host     = localhost
user     = debian-sys-maint
password = 03P8rdlknkXr1upf
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = 03P8rdlknkXr1upf
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

We simply need to copy this information and paste it into the same file on each node.

On your second and third nodes, open the same file:

sudo nano /etc/mysql/debian.cnf

Delete the current information and paste the parameters from the first node's configuration file into these other servers' files:

[client]
host     = localhost
user     = debian-sys-maint
password = 03P8rdlknkXr1upf
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = 03P8rdlknkXr1upf
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

They should be exactly the same now. Save and close the files.

Start the Cluster

To begin, we need to stop the running MariaDB service so that our cluster can be brought online.

This is easily done by typing this on each of the nodes:

sudo service mysql stop

When all processes have ceased running, you must start up your first node again with a special parameter:

sudo service mysql start --wsrep-new-cluster

With our cluster configuration, each node that comes online tries to connect to at least one other node specified in its configuration file to get its initial state. Without the --wsrep-new-cluster parameter, this command would fail because the first node is unable to connect with any other nodes.

On each of the other nodes, you can now start MariaDB as you normally would. They will search for any member of the cluster list that is online. When they find the first node, they will join the cluster.

sudo service mysql start

Your cluster should now be online and communicating.

Test Master-Master Replication

We've gone through the steps up to this point so that our cluster can perform master-master replication. We need to test this out to see if the replication is working as expected.

On one of our our nodes, we can create a database and table like this:

mysql -u root -pmariadb_admin_password -e 'CREATE DATABASE playground;'
mysql -u root -pmariadb_admin_password -e 'CREATE TABLE playground.equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));'

This will create a database called playground and a table inside of this called equipment.

We can then insert our first item into this table by executing:

mysql -u root -pmariadb_admin_password -e 'INSERT INTO playground.equipment (type, quant, color) VALUES ("slide", 2, "blue")'

We now have one value in our table.

From another node, we can read this data by typing:

mysql -u root -pmariadb_admin_password -e 'SELECT * FROM playground.equipment;'

+----+-------+-------+-------+
| id | type  | quant | color |
+----+-------+-------+-------+
|  1 | slide |     2 | blue  |
+----+-------+-------+-------+

From this same node, we can write data to the cluster:

mysql -u root -pmariadb_admin_password -e 'INSERT INTO playground.equipment (type, quant, color) VALUES ("swing", 10, "yellow");'

From our third node, we can read all of this data by querying the again:

mysql -u root -pmariadb_admin_password -e 'SELECT * FROM playground.equipment;'

+----+-------+-------+--------+
| id | type  | quant | color  |
+----+-------+-------+--------+
|  1 | slide |     2 | blue   |
|  2 | swing |    10 | yellow |
+----+-------+-------+--------+

Again, we can add another value from this node:

mysql -u root -pmariadb_admin_password -e 'INSERT INTO playground.equipment (type, quant, color) VALUES ("seesaw", 3, "green");'

Back on the first node, we can see that our data is available everywhere:

mysql -u root -pmariadb_admin_password -e 'SELECT * FROM playground.equipment;'

+----+--------+-------+--------+
| id | type   | quant | color  |
+----+--------+-------+--------+
|  1 | slide  |     2 | blue   |
|  2 | swing  |    10 | yellow |
|  3 | seesaw |     3 | green  |
+----+--------+-------+--------+

As you can see, all of our servers can be written to. This means that we have master-master replication functioning correctly.