A MariaDB Howto authored by: Erkan Yanar.

This is a Howto about installing MariaDB Galera Cluster on Debian/Ubuntu. Because a lot of people were having problems installing MariaDB Galera Cluster, elenst from #maria on freenode forced me to write this Howto :)

Installing MariaDB Galera Cluster is in fact quite easy and actually kind of boring in the end. This Howto is written for (and tested on) on Debian 7.1 (Wheezy) and Ubuntu 12.04 (Precise).

What we need

In our setup we assume 3 nodes (node01, node02, node03) with one interface each. We assume following IP addresses: 172.16.8.5, 172.16.8.6, and 172.16.8.4. We need three packages installed on all nodes:

  • rsync
  • galera
  • mariadb-galera-server

As Galera does not ship with the distribution repositories, go for the repo configurator and follow the instructions to include the repository fitting your system. Keep in mind to Choose “5.5″ in Step 3 (Choose a Version). Doing this you can jump directly to Install Packages

Adding the Repository

Alternatively you can just take following steps.

Debian Wheezy

Ubuntu Precise

Yes, they are nearly identical. :)

Install Packages

(Just another shortcut for the impatient)

After installing the packages you will have a running MariaDB server on each node. But none of them will be configured to run as a node in a MariaDB Galera Cluster.

Configuring Galera

So we have to do some configuration next. There is a MariaDB configuration part and one part to configure Galera (starting with wsrep_). As we do the most basic and simple installation in this Howto, it is sufficient you just change the IP’s (Remember: 172.16.8.5, 172.16.8.6, 172.16.8.4) with your IP’s.
This will be needed to define the wsrep_cluster_address Variable (the list of nodes a starting mysqld contacts to join the cluster).

The following configuration file has to be distributed on all nodes. We use a separate configuration file /etc/mysql/conf.d/galera.cnf with the following settings:

FYI: The shared library for wsrep_provider is provided by the installed galera package.

We could also change the cluster name by changing the value of wserp_cluster_name to fit our style. This setting also works as a shared secret to control the access to the cluster. With wsrep_cluster_address you see the IP addresses of our setup. The wsrep_sst_method tells what method to use to synchronise the nodes. While there are also mysqldump and xtrabackup available, I prefer rsync because it is easy to configure (i.e. it does not need any credentials set on the nodes). If you are considering using the xtrabackup method, don’t forget to install xtrabackup.

Starting the Galera Cluster

First we stop mysqld on all nodes.

The configuration file (galera.cnf) is already distributed to all nodes, so we next start the first mysqld. This node initializes/starts the cluster (creates a GTID).

To have a look and see if everything really worked we’ll check the cluster size status variable.

If you see the above, great! That’s what we would expect. Now that the Cluster already exists, we let the next nodes just start and join the cluster.

We can ignore the above error for now. This node is still starting fine.

Let’s pause here and do a quick check. As we are running a cluster it is not important if we execute the following on node01 or node02.

If you see the above, very nice! Now let’s start the third node:

Ok we are finished. We have a running MariaDB Galera Cluster \o/

Having fun with Debian/Ubuntu init scripts

But we’ve got to fix some things because of some Debian/Ubuntu oddities.

Remember the error we saw when starting node02 and node03? What happened? Well, Debian/Ubuntu uses a special user ('debian-sys-maint'@'localhost') in their init script and the credentials for that user are stored in /etc/mysql/debian.cnf. This user is used to make some checks starting MySQL. Checks I don’t think belong into a service script anyway.

We could simply ignore it, but the user user is also used to shutdown mysqld. This is also not required, as a SIGTERM is sufficient to shutdown the mysqld :/

As we copied the data from node01 to all other nodes, the credentials in /etc/mysql/debian.cnf don’t match on node02 and node03. So we will not be able to shutdown mysqld on either of these nodes.

So we’ve got to fix it, by copying /etc/mysql/debian.cnf from the first node (node01) to all other nodes. So the data and configuration files have the same data again.

After that we are able to shutdown the daemon:

Great.

So if we would have a proper init script the Howto would have been even shorter ;)

Follow the Bug :)

Enjoy your MariaDB Galera Cluster and have fun!

– Erkan Yanar

Thx to teuto.net for providing me an OpenStack tenant, so I could run the tests for this Howto.

The MariaDB developers have made several releases in the past week. Rather than post about all of them separately, we decided to combine them into one post. Details for each release are available on their individual Release Notes and Changelog pages.

MariaDB 5.5.36

First up is MariaDB 5.5.36. This is a Stable (GA) release. Apart from general maintenance, bug fixes, and updates, TokuDB is now included in RPM packages for CentOS 6 on x86-64.

Download MariaDB 5.5.36

Release Notes Changelog What is MariaDB 5.5?


MariaDB Galera Cluster 10.0.7

Next is MariaDB Galera Cluster 10.0.7. This is an Alpha release. It is being released now to get it into the hands of any who might want to test it. Do not run Alpha releases on production systems! This version of MariaDB Galera Cluster supports wsrep API v25 which means MariaDB Galera Cluster can be used with either a 25.2.x or 25.3.x Galera wsrep provider. A 25.3.x wsrep provider is included in the MariaDB repositories. And both 25.3.x and 25.2.x wsrep providers are available on the downloads page.

Download MariaDB Galera Cluster 10.0.7

Release Notes Changelog What is MariaDB Galera Cluster?


MariaDB Java Client 1.1.6

The last release in the roundup is MariaDB Java Client 1.1.6. This is a Stable (GA) release. Several bugs were fixed and a new connection parameter, serverTimezone, was added. If set, timezone conversions will occur when storing temporal data with preparedStatement and when reading data using ResultSet. The effect is similar to setting the time_zone session variable. The difference is better cross-platform compatibility (i.e timezone names like “Asia/Omsk” can also be used when the server is on Windows). Also, this option works for both datetime and timestamp datatypes, while the server-side option has no effect on datetime.

Download Java Client 1.1.6

Release Notes Changelog Java Client Overview


Thanks, and enjoy MariaDB!

Lets start by considering a scenario where records are being inserted in a single auto-increment table via different nodes of a multi-master cluster. One issue that might arise is ‘collision’ of generated auto-increment values on different nodes, which is precisely the subject of this article.

As the cluster is multi-master, it allows writes on all master nodes. As a result of which a table might get same auto-incremented values on different nodes on INSERTs. This issue is discovered only after the writeset is replicated and that’s a problem!

Galera cluster suffers with the similar problem.

Lets try to emulate this on a 2-node Galera cluster :

As expected, the second commit could not succeed because of the collision.

So, how do we handle this issue? Enter @@auto_increment_increment and @@auto_increment_offset! Using these two system variables one can control the sequence of auto-generated values on a MySQL/MariaDB server. The trick is to set them in such a way that every node in the cluster generates a sequence of non-colliding numbers.

For instance, lets discuss this for a 3-node cluster (n=3):

As you can see, by setting each node’s auto_increment_increment to the total number of nodes (n) in the cluster and auto_increment_offset to a number between [1,n], we can assure that auto-increment values, thus generated, would be unique across the cluster, thus, would avoid any conflict or collision.

In Galera cluster this is already taken care of by default. As and when a node joins the cluster, the two auto-increment variables are adjusted automatically to avoid collision. However, this capability can be controlled by using wsrep_auto_increment_control variable.

With this setting the last COMMIT in the above example would succeed.