MariaDB-10.1 Galera Cluster on CentOS 7

Some times back, i posted two articles on MariaDB Master-Master replication and MariaDB Master-Slave replication. Well, after several requests from friends, i was asked to blog on MariaDB Galera Cluster. 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 (although there is experimental support for MyISAM. - Mariadb.com. You can easily download Galera Cluster using the Repository Configuration tool. There is also a slight difference between oldest version of MariaDB compared to that of the 10.1 series. Recently, a question was asked on ServerFault to which i clarified same. Check it out for more information. Lets now have some funs with MariaDB Galera Clustering.

Photo credits:Mariadb.org
Photo credits:Mariadb.org

I have created 3 CentOS7 virtual machines on Virtual Box. Lets called each Server as Galera1 [192.168.1.9], Galera2 [192.168.1.10] and Galera3[192.168.1.11]. You need to edit the /etc/hosts file to allow each machine to ping each others via hostname. For testing purposes port 3306 which is by default listening on MySQL should be open. I have also disable SELinux and Firewalld as same are for testing purposes. Lets now install MariaDB Galera Cluster.

1.Edit the repository by using configuration as mentioned on the official MariaDB website.

# MariaDB 10.1 CentOS repository list - created 2015-11-08 17:34 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

2. Lets install some Pre-requisites and other interesting tools.

yum install rsync nmap lsof perl-DBI nc

3. Install MariaDB-server and start the Mariadb service

yum install MariaDB-server MariaDB-client MariaDB-compat galera socat jemalloc
service mysql start

4. Launch the mysql_secure_installation command to set up the username and password. At this point you can start MariaDB normally by using the mysql command and create a database.

5. After the normal procedure to set up the first database on galera1, you can create a database and dump it, then import the database on galera2 and galera3. You have now 3 independant databases on 3 different servers. I would adivce you to dump the whole database with the command

mysqldump --all-databases > db.sql

6. We will now start setting up the Galera clustering. On galera1, edit the /etc/my.cnf.d/server.cnf file on galera1 and configure it as follows. The server galera1 is the being setup as the first primary cluster. So every other cluster is going to be set up in the network that is galera2 and galera3 is going to replicate itself from galera1. Add the following parameter under the [galera] option.

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://'
wsrep_cluster_name='galera'
wsrep_node_address='192.168.1.9'
wsrep_node_name='galera1'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
7. Once this is added, you can now start the first Galera cluster with the command. Make sure that the mysql service is stopped first. as at step5 we have started mysql to create a test databaseservice mysql start8. You would noticed that the ports 3306 and 4567 should be listeningScreenshot from 2016-03-27 11-23-599. Now you would like to make galera2 joined the primary cluster i.e galera1. Simple set up the galera2 /etc/my.cnf.d/server.cnf file and under [galera] enter the following parameter. The only difference is the wsrep_node_name, wsrep_node_address and the gcomm value where you need to add the first cluster.
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://192.168.1.9'
wsrep_cluster_name='galera'
wsrep_node_address='192.168.1.10'
wsrep_node_name='galera2'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

10. Once mysql is started with the command service mysql start, you would noticed that galera2 is now on replicating from cluster1. You should noticed something similar to this on your log.Screenshot from 2016-03-27 12-33-5611. Repeat same procedure for galera3 by tweaking the gcomm value and modifying the wsrep_node_address etc..If you interested in having some more ideas about the MariaDB improved security features, do check out the following link where i attended a conference on MariaDB organised by the MSCC and Hackers Mauritius.

Tips:

  • If  you are using the 10.0, you would need MariaDB-Galera-server package compared to 10.1 where you just need MariaDB-server.
  • There are no longer separate MariaDB Galera Cluster releases for MariaDB 10.1 and above. Simply download MariaDB (10.1 or above) and configure your cluster as normal. MariaDB-10.1 is just 'Galera ready'.
  • If you are migrating from MariaDB-server to Galera make sure you have clean up all the residues belonging to the old versions. See http://serverfault.com/questions/646973/installing-mariadb-galera-via-yum-fails-with-no-package-mariadb-galera-server.
  • The galera package will provide you the /usr/lib64/galera/libgalera_smm.so  and several binaries for the cluster configuration. There are several cluster configuration under ws_rep see the documentation.
  • The socat package establishes two birectional byte streams and transfers data between them.
  • I would also recommend to disable SELinux on the machines.
  • When you start the mysql service, you can tail the log at /var/log/messages otherwise you can set up mysql log.
  • Use this command show status like 'wsrep_%'; on your mysql prompt to understand the replication.
  • To know how much node in the cluster use the following command show status like 'wsrep_cluster_size';
  • Cowpocalypse_Now

    Thanks for the tutorial. You seem to be missing the part about setting up database users. So you must just have root setup with no password and access from all hosts (‘%’) with no firewall blocking 4567 in addition to 3306. Fine for quick and dirty testing but perhaps it deserves a mention.

    • Yes you are right. Like i said, for testing purpose i have disable iptables and selinux which are not supposed to be block, but proper rules set up accordingly. For the database users set up, i have already mentioned it in 2 article that are the MariaDB master/master and master/slave replication. Thanks for enlightening us on the issue so that other users do not read blindly.. 🙂 regards TheTunnelix

      • Cowpocalypse_Now

        What I ended up doing was blocking the ports and then only allowing the IP addresses of the other database servers. It’s the simplest way I could think of. Surprised there is no real way of securing it other than that. No even with database usernames/passwords as far as I can tell.

  • Ljr Yang

    Hi, we use one master and two slaves in mariadb 10.1.13 . database size about 700GB, if we want switch to mariadb galera cluster, must start master node to galera cluster, then execute mysqldump backup at master node and last restore to two slave node ?
    because database is too big , Is there a simple way and must not backup and restore database ?

  • disqus_O3sz29JTMT

    hi,

    Thanks a lot for the clear explanation and making it simple. Just one question, Galera3 will replicate from Galera2 or Galera1?

    So would I be right to say that the below settings is fine for Galera3?
    wsrep_cluster_address=’gcomm://192.168.1.10′
    wsrep_cluster_name=’galera’
    wsrep_node_address=’192.168.1.11′
    wsrep_node_name=’galera3′

    Cheers,
    M.M

  • Pingback: Database Replication | Welcome to daidungsi's blog()

  • Ruben Gonzalez

    Hi,
    Is possible to specify only one database instead of all databases of the server?
    I did not find information about it.

    Thank you.