Running MariaDB Galera Cluster On CentOS

In this quick tutorial I will list out some necessary steps to setting up mariadb galera cluster on CentOS. The selected CentOS version is 6.5 and the MariaDB is running with the 10.0 version.

Preparation

We will need to install required softwares as regular:

1. Disable SELinux:

   [bash]setenforce 0[/bash]

2. Add MariaDB repo to /etc/yum.repos.d/mariadb.repo using MariaDB Repository Configuration tool. For CentOS 6 64-bit:

   [bash]# MariaDB 10.0 CentOS repository list – created 2015-03-16 12:01 UTC
   # http://mariadb.org/mariadb/repositories/
   [mariadb]
   name = MariaDB
   baseurl = http://yum.mariadb.org/10.0/centos6-amd64
   gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
   gpgcheck=1[/bash]

3. Add Epel repository:

   [bash]rpm -ivh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm[/bash]

4. Install socat:

   [bash]yum install socat[/bash]

5. Install MariaDB Glera:

   [bash]yum install MariaDB-Galera-server MariaDB-client galera which rsync[/bash]

6. Start MySQL and Secure MariaDB after installation:

   [bash]/usr/bin/mysql_secure_installation[/bash]

Setting up MariaDB Galera Cluster

There is an assumption that the IP and node name of the cluster’s nodes are as follows

• Cluster node 1: hostname c1 and IP address 192.168.0.1
• Cluster node 2: hostname c2 and IP address 192.168.0.2
• Cluster node 3: hostname c3 and IP address 192.168.0.3

1. Create a new MariaDB Galera Cluster user. In this case, we can choose to create a new user which is allowed to connect from any host (%) or just some pre-defined host. Of course the later option is more secure. However to be simple, I use % as the host so that the user can be connected from anywhere. The IP restriction will be done via network firewall, not inside the MariaDB configuration. Steps:

   [bash][root@c1 ~]# mysql -u root -p

   MariaDB [(none)]> GRANT USAGE ON *.* to sst_user@’%’ IDENTIFIED BY ‘STT_PASSWORD’;
   MariaDB [(none)]> GRANT ALL PRIVILEGES on *.* to sst_user@’%’;
   MariaDB [(none)]> FLUSH PRIVILEGES;
   MariaDB [(none)]> quit;
   [/bash]

2. Stop MariaDB in ALL nodes:

   [bash]service mysql stop[/bash]

3. Create MariaDB cluster config, just need to edit /etc/my.cnf.d/server.cnf and add the following configuration under [mariadb-10.0]:

   [bash][mariadb-10.0]
   binlog_format=ROW
   default-storage-engine=innodb
   innodb_autoinc_lock_mode=2
   innodb_locks_unsafe_for_binlog=1
   query_cache_size=0
   query_cache_type=0
   bind-address=0.0.0.0
   datadir=/var/lib/mysql
   innodb_log_file_size=100M
   innodb_file_per_table
   innodb_flush_log_at_trx_commit=2
   wsrep_provider=/usr/lib64/galera/libgalera_smm.so
   wsrep_cluster_address="gcomm://192.168.0.1,192.168.0.2,192.168.0.3"
   wsrep_cluster_name=’MY-Cluster’
   wsrep_node_address=’192.168.0.1′
   wsrep_node_name=’c1′
   wsrep_sst_method=rsync
   wsrep_sst_auth=stt_user:STT_PASSWORD[/bash]

4. Apply the configure in the master node for all 2nd and 3rd nodes. Remember that we need to change wsrep_node_address and wsrep_node_name to match information on these nodes.
5. Start the first node:

   [bash]/etc/init.d/mysql start –wsrep-new-cluster[/bash]

   Remember that in case you restart the mysql server in this first node, if other nodes are currently up and running, do not use –wsrep-new-cluster again or it will create a new cluster and other running nodes cannot connect to this (until you restart all other nodes). This parameter is only be used when the node to start is the FIRST / ONLY node in the cluster.

6. Start 2nd and 3rd nodes:

   [bash]service mysql start[/bash]

7. Test the cluster configuration on each node:

   [bash]mysql -uroot -p -e "show status like ‘wsrep%’"[/bash]

8. Well, we can then verify the cluster by adding database, data to any node and test the data in the remaining nodes.

Troubleshot

1. Always remember to have rsync and which installed in each of your node. I did have a problem of starting slave nodes just because of the lack of which so it generated the error relates to wsrep_sst_rsync.
2. In Debian/Ubuntu, we might face stupid error due to the incompatible dependencies. E.g.

   [bash]root@euonline:~# apt-get install -y rsync galera mariadb-galera-server
   …
   The following packages have unmet dependencies:
   mariadb-galera-server : Depends: mariadb-galera-server-10.0 (= 10.0.17+maria-1~wheezy) but it is not going to be installed
   E: Unable to correct problems, you have held broken packages.[/bash]

   . In this case, just install the exact version of mariadb galera such as

   [bash]apt-get install -y rsync mariadb-galera-server-10.0[/bash]

3. Ports to be opened in both IN and OUT traffic: 3306,4444,4567,4568
4. Be careful when playing with auto-increment value since this kind of value will not increased by 1, but by the number of node. This feature helps concurrent writes on any node so just aware of it :-). Reference: https://blog.mariadb.org/auto-increments-in-galera/
5. If you are on CentOS 7 and selinux is on, remember to turn selinux to permissive for mysqld
   [bash]semanage permissive -a mysqld_t[/bash]
   . Otherwise, you will not be able to start galera cluster since other node cannot connect to mysqld service in this server.
6. If you use MariaDB from 10.1, MariaDB-Galera-server is not available. The galera is included in the MariaDB-server itself, so you just need to install MariaDB-server package.