How to setup MariaDB Galera Cluster 10.0 on CentOS

Introduction

MariaDB is a drop-in replacement for MySQL. MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB. It is available on Linux only, and only supports the XtraDB/InnoDB storage engines.

This blog post explains step by step how to setup MariaDB Galera Cluster 10.0 with 3 nodes running on CentOS 6.5 x86_64 resulting in a HA (high-availability) database cluster.

Cluster details

I’m using 3 freshly deployed VMs running a minimal install of CentOS 6.5 x86_64.

Cluster node 1 has hostname db1 and IP address 10.0.0.9
Cluster node 2 has hostname db2 and IP address 10.0.0.11
Cluster node 3 has hostname db3 and IP address 10.0.0.13

Step 1 – set SELinux in permissive mode

It’s currently not possible to have SELinux in enforcing mode because there are no policies (yet) for MariaDB Galera Cluster 10.0. I have filed BZ1117233. Hopefully the SELinux developers will soon add proper policies. For now put SELinux into permissive mode on all nodes:

Update: SELinux policies for MariaDB Galera 10 will be in RHEL 6.6 and CentOS 6.6.

Step 2 – configure the firewall

MariaDB Galera Cluster on each cluster node must be able to talk to MariaDB Galera Cluster on the other nodes so we need to open some firewall ports. Execute the following commands on all nodes:

Please not that these rules allow any host on your local IP network to access the database nodes. For production make it more secure by specifying the source IP range from which connections to the database nodes are accepted.

Step 3 – install the MariaDB repository

I’m using the MariaDB Galera Cluster RPM packages from mariadb.org. Add the MariaDB RPM repository by executing the following command on all nodes:

Step 4 – install MariaDB Galera Cluster 10.0 software

If you did a CentOS 6 minimal installation then make sure you install the socat package from the EPEL repository before proceeding with installing the MariaDB Galera Cluster 10.0 software. You can find the socat package at http://dl.fedoraproject.org/pub/epel/6/x86_64/repoview/socat.html.
Either install it directly from EPEL with the following command (for x86_64):

Or install the EPEL repository and then install it the regular way:

On CentOS 7 the socat package is part of the distro and is installed as part of the minimal install so should already be there. If it’s not then just install it with:

Install the MariaDB Galera Cluster 10.0 software by executing the following command on all nodes:

Step 5 – setup MariaDB security

The default installation is not very secure so let’s improve that. Start the mysql service (yes the init script in MariaDB 10.0 is still called mysql) on all nodes:

Next we need to run the mysql_secure_installation script so we can improve the security. Execute the following command on all nodes:

For this tutorial I changed the password to ‘dbpass’ and accepted all defaults (so answered yes to all questions).

Step 6 – setup MariaDB Galera Cluster users

Now we need to setup some users that must be able to access the database. The ‘sst_user’ is the user which a database node will use for authenticating to another database node in the State Transfer Snapshot (SST) phase. Execute the following command on all nodes:

Please not that the ‘%’ means that the root or sst_user is allowed to access the database from any host. For production make it more secure by specifying the hostname(s) or IP addresses from which those users can access the database.

Step 7 – create the MariaDB Galera Cluster config

First stop the mysql services on all nodes:

Now let’s create the MariaDB Galera Cluster configuration by executing the following command on all nodes (read the IMPORTANT NOTE after the config about required changes for db2, and db3):

IMPORTANT NOTE: when executing this command on db2 and db3 do not forget to adjust the wsrep_node_address and wsrep_node_name variables.

On db2 they should be:

On db3 they should be:

Step 8 – initialize the first cluster node

On node db1 only start MariaDB with the special ‘‐‐wsrep-new-cluster’ option so the primary node of the cluster is initialized:

Check if everything went ok by executing the following command on db1 only:

And the output:

The important information are the following lines:

There is also a lot of information in the logfile which is located in /var/lib/mysql and it’s called db1.<FQDN>.err. Here are the last couple of lines after initializing db1:

Step 9 – add the other cluster nodes

Make 100% sure that both nodes db2 and db3 have the correct configuration in /etc/my.cnf.d/server.cnf under the [mariadb-10.0] heading as described in step 7.

With the correct configuration in place, all that is required to make db2 and db3 a member of the cluster is to start them like you would start any regular service. On db2 issue the following command:

On node db2 check /var/lib/mysql/db2.<FQDN>.err and you should see:

And on node db1 check /var/lib/mysql/db1.<FQDN>.err and you should see:

Check what has changed in the cluster status by executing the following command on db1 or db2:

And you should see that node db2 is now known as the cluster size is ‘2’ and the IP address of node db2 is listed:

Repeat the same step for node db3. On node db3 only execute the following command:

On node db3 check /var/lib/mysql/db3.<FQDN>.err and you should see something like this:

Check what has changed in the cluster status by executing the following command on for example db1:

And you should see that node db3 is now known as the cluster size is ‘3’ and the IP address of node db3 is listed:

Step 10 – verify replication

The cluster is now up and running. Let’s check if it’s actually working. On db1 create a database ‘clustertest’ by issuing the following command:

Check if the database, table and data exists:

That looks good. Now do the check on node db2 to see if it good replicated:

Everything was successfully replicated from db1 to db2. Now do the same check on node db3:

As you can see everything was successfully replicated by node db1 across all other nodes.

Step 11 – starting a cluster that is completely down

If the entire cluster (each node) is down then this is how you start the cluster.

First decide which node you consider the primary node. Normally that would be the node with the database which you consider (the most) correct. Then start that node with the following command. Note that the command will not touch or delete data. It will just start the first node as the primary cluster node.

Once the primary node is successfully started you can start the other nodes in the cluster with the regular command:

And it’s probably a good idea to check if the cluster is fully operational by looking on all cluster nodes at the output of this command:

Step 12 – MariaDB Galera limitations

MariaDB Galera Cluster software has some known limitations. Make sure to read them.

Severalnines has an excellent explanation what those limitations mean in practical terms and how they can be dealt with in their “Avoiding Deadlocks in Galera…” article.

A workaround to prevent the rollbacks and deadlocks is to use a single node for high concurrency writes and send updates to only that node while using multiple nodes for reads. This can be realized by adding HAProxy & Keepalived to the setup described in this tutorial and is also available on this blog.

23 thoughts on “How to setup MariaDB Galera Cluster 10.0 on CentOS”

  1. Question on modifying that init script.

    I am considering using HA proxy configuration explained here (http://blog.laimbock.com/2014/10/01/howto-setup-high-available-haproxy-with-keepalived/) to force my writes to a primary with fail over writes to other nodes and just have my reads from whatever server.

    So a couple questions come up.

    1. is there a way to do a simple check on each node to see who has the “latest” version?
    2. with the init script, how would you check for other nodes prior to starting?

    Great tutorial! I set it up in my esx test lab and it worked like a champ! very very cool.

    Thanks!!

    1. Hi Jason,

      You could look at ‘wsrep_last_committed’ on each node to see which has the highest version:
      $ mysql -u root -p -e “show status like ‘wsrep%'” | grep wsrep_last_committed
      The init script only does regular start & stop and does not know about other nodes. It’s the MariaDB Galera configuration on each node that lets the node know that there are other nodes in a cluster which it should join. So if your *entire* cluster is down you should manually restart the first node (see Step 11 – starting a cluster that is completely down) and then on the other nodes you could use the regular ‘service mariadb start’. Once the first node is started manually and the other nodes after that, upon joining the cluster, they sync the latest commit(s). You can test this by stopping db1 and db2, then add more data to db3, then stop db3 and finally manually start db1, then start db2 and db3 and check the logs to see what happens.

      Cheers,
      Patrick

  2. Hi Patrick, I am very new to MariaDB and Galera Cluster, thanks to your tutorial for creatting MariaDB Galera Cluster setup in easy and efficient way.

  3. Did you try to remove/insert rows from mysql.user . is replication working for this table , as default engine for this database/table MyISAM in place of Innodb.

    i tried Insert/delete but no luck.

  4. Patrick –

    First off, freaking fantastic tutorial. I’ve been working with trying to get MySQL to be a cluster with nodes and a management server, then heard about Galera and MariaDB from a colleague, so I started Googling looking for ‘how-tos’ for how do implement Maria. Your steps are fantastic.

    One comment I would add to the article is this. In scenarios where the entire cluster needs to come down (moving VMs to other hardware, power outage, etc.) it would be helpful to know how to START the cluster back up. I read in your article about using ‘–wsrep-new-cluster’ to initialize the cluster. Now, whenever I see the word ‘initialize’, to me that means ‘zero out’ or ‘start over’. In this case, when you want to start the cluster back up after all of the machines are down, you need to have whichever node is the first one to start have this parameter on it to get the cluster ‘up and going again’. No data is lost – the cluster is just restarted and available again. After one system is up, the rest can start MySQL normally: service mysql start

    My only question would be this then: How do you recommend ‘auto-starting’ the cluster? Just pick a node to be the ‘primary’ and run this extra parameter and then let the other ones start normally with chkconfig? Keep all of the nodes on manual startup so you don’t get yourself into trouble in case the node you’re having problems with is the one that has this extra parameter? What’s your opinion on this?

    Again, great article, and thank you. I just wanted to throw this in as it took me a couple hours to figure this out on my own.

    Charles

    1. Hi Charles,

      Thank you for your comment. It’s great to hear that the tutorial was useful. Note added about starting up an already initialized cluster (step 11). About auto-starting the cluster: as far as I know the idea behind the current situation (the manual startup) is that starting up the cluster is considered such a significant event that a human should handle it. If a split brain caused each node to have slightly different database data then you will need to decide which node has the correct database data and start that one as the primary. Obviously that decision can not be automated hence the need for a manual startup. However, if your application architecture always considers for example node1 as the primary one true database then you can off course add –wsrep-new-cluster to the init script on node1. Or add to the init script a test for the presence of the other nodes and if there are none (so the entire cluster is down) then automatically enable the –wsrep-new-cluster.

      Best,
      Patrick

  5. Very good manual! I tried to install galera at CentOS 7 and get a lot of problems. Can you give an advice about intallation Galera at CentOS 7?

    1. Hi Evgen. I haven’t yet deployed MariaDB Galera 10 on CentOS 7 so I don’t have any advice right now. I’ll probably have a go at it in the next month or so and I’ll document the process on this site again. Best, Patrick

  6. Hi Partick,

    Wonderful document… This one works for me awesome… (I’m still trying to make the 5.6 galera work)

    I would like to point out that on OS Centos6.5 minimal version. The mariadb installation fails because its unable to find package socat.

    Installing package socat will fix the problem..

    Best
    rmohamme

      1. Hi Partick,

        For Centos7 – I have verified that there is no need to install the socat package even on minimal install….

        [root@templatecentos7m yum.repos.d]# yum list | grep socat
        socat.x86_64 1.7.2.2-5.el7 base

    1. Hi Fatima. In the setup described in this tutorial a single query goes to a single node. Multiple queries can go to different nodes. It depends on how HAProxy is configured. For example you could have query #1 go to node #1 and query #2 to node #2. Or you can have each query sent to the node with the lowest load. It all depends on how you setup HAProxy. To read more about the possibilities have a look at the ‘balance’ parameter at http://cbonte.github.io/haproxy-dconv/configuration-1.5.html#4.2-balance

        1. I’ve never worked with Hive so don’t about that. As far as I know MariaDB executes a query on a single node. However, I did come across something called Shard-Query and maybe that’s what you are looking for:

          “Shard-Query is a high performance MPP (massively parallel processing) query engine for MariaDB and MySQL which offers increased parallelism compared to stand-alone servers. This increased parallelism is achieved by taking advantage of MariaDB/MySQL partitioning, sharding, common query clauses like BETWEEN and IN, or some combination of the above. Shard-Query is implemented with PHP and Gearman. Shard-Query is targeted mainly at big data problems, and OLAP queries in general.”

          You can read more about it here: https://mariadb.com/kb/en/mariadb/documentation/clients-and-utilities/shard-query/

  7. Great tutorial.

    I was getting this trying to connect to cluster 1.

    Starting MySQL…..SST in progress, setting sleep higher…………………………………………………………

    It worked after open port 4444 on db2 and db3.

    -A INPUT -m state –state NEW -m tcp -p tcp –dport 4444 -j ACCEPT

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.