Tutorial

How To Configure a Galera Cluster with MariaDB on Ubuntu 12.04 Servers

Published on January 20, 2014
How To Configure a Galera Cluster with MariaDB on Ubuntu 12.04 Servers

Status: Deprecated

This article covers a version of Ubuntu that is no longer supported. If you are currently operate a server running Ubuntu 12.04, we highly recommend upgrading or migrating to a supported version of Ubuntu:

Reason: Ubuntu 12.04 reached end of life (EOL) on April 28, 2017 and no longer receives security patches or updates. This guide is no longer maintained.

See Instead: This guide might still be useful as a reference, but may not work on other Ubuntu releases. If available, we strongly recommend using a guide written for the version of Ubuntu you are using. You can use the search functionality at the top of the page to find a more recent version.

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:

<pre> [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://<span class=“highlight”>first_ip</span>,<span class=“highlight”>second_ip</span>,<span class=“highlight”>third_ip</span>”

Galera Synchronization Congifuration

wsrep_sst_method=rsync #wsrep_sst_auth=user:pass

Galera Node Configuration

wsrep_node_address=“<span class=“highlight”>this_node_ip</span>” wsrep_node_name=“<span class=“highlight”>this_node_name</span>” </pre>

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:

<pre> mysql -u root -p<span class=“highlight”>mariadb_admin_password</span> -e ‘CREATE DATABASE playground;’ mysql -u root -p<span class=“highlight”>mariadb_admin_password</span> -e ‘CREATE TABLE playground.equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));’ </pre>

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:

<pre> mysql -u root -p<span class=“highlight”>mariadb_admin_password</span> -e ‘INSERT INTO playground.equipment (type, quant, color) VALUES (“slide”, 2, “blue”)’ </pre>

We now have one value in our table.

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

<pre> mysql -u root -p<span class=“highlight”>mariadb_admin_password</span> -e ‘SELECT * FROM playground.equipment;’ </pre>


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

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

<pre> mysql -u root -p<span class=“highlight”>mariadb_admin_password</span> -e ‘INSERT INTO playground.equipment (type, quant, color) VALUES (“swing”, 10, “yellow”);’ </pre>

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

<pre> mysql -u root -p<span class=“highlight”>mariadb_admin_password</span> -e ‘SELECT * FROM playground.equipment;’ </pre>


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

Again, we can add another value from this node:

<pre> mysql -u root -p<span class=“highlight”>mariadb_admin_password</span> -e ‘INSERT INTO playground.equipment (type, quant, color) VALUES (“seesaw”, 3, “green”);’ </pre>

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

<pre> mysql -u root -p<span class=“highlight”>mariadb_admin_password</span> -e ‘SELECT * FROM playground.equipment;’ </pre>


+----+--------+-------+--------+
| 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.

Conclusion


At this point, you should have a Galera cluster configured on your servers. This can help quite a bit with balancing load in write-intensive application environments.

If you plan on using a Galera cluster in a production situation, you may want to take a look at some of the other state snapshot transfer (sst) agents like “xtrabackup”. This will allow you to set up new nodes very quickly and without large interruptions to your active nodes. This does not affect the actual replication, but is a concern when nodes are being initialized.

<div class=“author”>By Justin Ellingwood</div>

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us


About the authors

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
10 Comments


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

also works in version 14.04

There is a missing step after ensuring /etc/mysql/debian.cnf is the same across all nodes.

You also need to update the debian-sys-maint user’s grants on all but the first node using something like:

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '03P8rdlknkXr1upf'; FLUSH PRIVILEGES;

Galera Cluster puts some requirements on the database (e.g. avoid MyISAM, make sure tables have pkeys), it might be worth reading through this tutorial.

http://www.severalnines.com/clustercontrol-mysql-galera-tutorial

You can also check out this webinar replay on how to migrate to Galera: http://www.severalnines.com/blog/webinar-replay-slides-migrating-mysql-mariadb-galera-percona-xtradb-cluster

If someone wants the exact above steps as Ansible playbook (ubuntu) - https://github.com/bingoarunprasath/ansible-mariadb-galera-web

Hi guys! I followed this tutorial on Ubuntu 14.04 but when I try to start Galera using this command: sudo service mysql start --wsrep-new-cluster I got this error:

start: invalid option: --wsrep-new-cluster Try `start --help’ for more information.

Anyone knows what is the problem? Do I have to install something else? Thank you.

I am currently testing this on Ubuntu Server 15 (Vivid Vervet). So far so good.

Thanks for the article, it has been really useful.

How can I add a slave node to this cluster?

I had problems for ages getting my nodes to join. I believe what resolved the issue was the need to update the last two variables in the /etc/mysql/conf.d/cluster.cnf file for each node

e.g.

* wsrep_node_address
* wsrep_node_name

It makes sense if you think about it.

Probably worth mentioning the known limitations of Galera Cluser (and other similar cluster systems), with one big one being that table locking is not supported. They state that “Using transactions properly should be able to overcome these limitations.”, but I’m not so sure.

https://mariadb.com/kb/en/mariadb/documentation/replication-cluster-multi-master/galera/mariadb-galera-cluster-known-limitations/

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel