30 March 2014

How to Setup MariaDB Galera Cluster 5.5 in CentOS, RHEL & Fedora

Written by Rahul K.

MariaDB is an relational database management system (RDBMS). Generally we use single node of database server for small application but think about application which have thousands of users keep online at a time, In that situation we need a structure which will capable to handle this load and provides high availability. So we need to add multiple database servers interconnected with each other and keep synchronized, so in case any server goes down other servers can take place of them and provide services to users.

MariaDB Galera Cluster is an synchronous Active-Active multi-master cluster of MariaDB databases. Which keeps all nodes synchronized. MariaDB Galera cluster provides synchronus replication which is always highly available (there is no data loss when one of the nodes crashes, and data replicas are always consistent). Currently it only supports XtraDB/InnoDB storage engines and available for Linux platform only.

This article will help you to setup MariaDB Galera Cluster with 3 servers running with CentOS. Cluster server details are as following.

Cluster DB1: 192.168.1.10 ( HostName: db1 )
Cluster DB2: 192.168.1.20 ( HostName: db2 )
Cluster DB3: 192.168.1.30 ( HostName: db3 )

Note: Step 1/2/3 has to be done on all cluster nodes and remaining steps are node specific.

Step 1: Add MariaDB Repositories

Create a mariadb repository /etc/yum.repos.d/mariadb.repo using following content in your system. Below repository will work on CentOS 6.x systems, For other system use repository generation tool and add to your system.

For CentOS 6 – 64bit

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

For CentOS 6 – 32bit

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-x86
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Step 2: Install MariaDB and Galera

Before installing MariaDB Galera cluster packages, remove any existing MySQL or MariaDB packages installed on system. After that use following command to install on all nodes.

# yum install MariaDB-Galera-server MariaDB-client galera

Step 3: Initial MariaDB Configuration

After successfully installing packages in above steps do the some initial MariaDB configurations. Use following command and follow the instructions on all nodes of cluster. If will prompt to set root account password also.

# mysql_secure_installation
# service mysql start

After that create a user in MariaDB on all nodes, which can access database from your network in cluster.

# mysql -u root -p

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit

and stop MariaDB service before starting cluster configuration

# service mysql stop

Step 4: Setup Cluster Configuration on DB1

Lets start setup MariaDB Galera cluster from DB1 server. Edit MariaDB server configuration file and add following values under [mariadb] section.

[[email protected] ~]# vim /etc/my.cnf.d/server.cnf

query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.20,192.168.1.30
wsrep_cluster_name='cluster1'
wsrep_node_address='192.168.1.10'
wsrep_node_name='db1'
wsrep_sst_method=rsync
wsrep_sst_auth=root:password

Start cluster using following command.

[[email protected] ~]# /etc/init.d/mysql bootstrap
Bootstrapping the clusterStarting MySQL.... SUCCESS!

If you get any problem during startup check MariaDB error log file /var/lib/mysql/<hostname>.err

Step 5: Add DB2 in MariaDB Cluster

After successfully starting cluster on DB1. Start configuration on DB2. Edit MariaDB server configuration file and add following values under [mariadb] section. All the settings are similar to DB1 except wsrep_node_address, wsrep_cluster_address and wsrep_node_name.

[[email protected] ~]# vim /etc/my.cnf.d/server.cnf


query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.10,192.168.1.30
wsrep_cluster_name='cluster1'
wsrep_node_address='192.168.1.20'
wsrep_node_name='db2'
wsrep_sst_method=rsync
wsrep_sst_auth=root:password

Start cluster using following command.

[[email protected] ~]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!

Step 6: Add DB3 in MariaDB Cluster

This server is optional, If you want only two server in cluster, you can ignore this step, but you need to remove third server ip from DB1/DB2 configuration files. To add this server make changes same as DB2.

[[email protected] ~]# vim /etc/my.cnf.d/server.cnf

query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.10,192.168.1.20
wsrep_cluster_name='cluster1'
wsrep_node_address='192.168.1.30'
wsrep_node_name='db2'
wsrep_sst_method=rsync
wsrep_sst_auth=root:password

Start cluster using following command.

[[email protected] ~]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!

Step 7: Test MariaDB Galera Cluster Setup

At this stage your cluster setup has been completed and running properly. Now you can test the cluster setup by creating database and tables at any server in cluster, it will replicate immediately to all servers in cluster.

Above GIF image is showing that databases are replicating properly to all nodes of cluster.

Rahul K.

I, Rahul Kumar is the founder and chief editor of TecAdmin.net. I am Red Hat Certified Engineer (RHCE) and working as IT professional since 2009.

11 Comments

nishu Reply to nishu

August 3, 2014 at 12:10 pm

thanks for istalling maria DB blog
M.Pasha Reply to M.Pasha

July 11, 2014 at 10:24 am

Hi,

I am getting same “Connection timed out” errors, while my configurations are below

####################
[server]
#general_log_file=/var/log/mysql/mysqld.log
#general_log=1
log_warning=2
log-error=/var/log/mysql/error.log
#log-bin=/var/log/mysql-bin.log
#log-slow-queries=/var/log/mysql/mysql-slow-queries.log
####################
[mysqld]
datadir=/var/lib/mysql
####################
[mariadb]
wsrep_cluster_address=gcomm://hostname1,hostname2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_node_address = hostname1
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
wsrep_cluster_name=corp-zabbix-server
wsrep_debug=on
wsrep_sst_auth=mariadb-user:cerner
wsrep_sst_method=mysqldump
wsrep_on=ON
####################

but my side everything looks fine. its in still first node, I haven’t started in second node.
- gg Reply to gg
  
  September 16, 2014 at 10:23 am
  
  Have a look in the error-log in /var/lib/mysql/
  In my case the path for libgalera_smm.so on the first server was wrong.
Anant Reply to Anant

July 8, 2014 at 2:38 am

How would you define that which is your master/bootstrepnode after restart ?

i.e if I restart all the servers then how nodes will decide that which will be bootstrep node.
jose Reply to jose

June 19, 2014 at 9:30 pm

There is somthing i am missing from the article. In my php code, to get advantage of the multi-master cluster, where is the load balancer? Who decide which server to use? In the perfect world i will connect to a virtual ip of the database and internally, every request is load balance. Can you please explain a little about this? Thank you
Radzikowski Reply to Radzikowski

June 18, 2014 at 9:34 am

Hi, great tutorial, but i have problem while trying to bootstrap repliaction. Could you help me? Here is my error log:

140618 10:11:50 [ERROR] WSREP: Permission denied
140618 10:11:50 [ERROR] WSREP: failed to open gcomm backend connection: 13: error while trying to listen ‘tcp://0.0.0.0:4567?socket.non_blocking=1’, asio error ‘Permission denied’: 13 (Permission denied)
at gcomm/src/asio_tcp.cpp:listen():814
140618 10:11:50 [ERROR] WSREP: gcs/src/gcs_core.c:gcs_core_open():202: Failed to open backend connection: -13 (Permission denied)
140618 10:11:50 [ERROR] WSREP: gcs/src/gcs.c:gcs_open():1291: Failed to open channel ‘cluster1’ at ‘gcomm://XXX.XXX.XXX.9’: -13 (Permission denied)
140618 10:11:50 [ERROR] WSREP: gcs connect failed: Permission denied
140618 10:11:50 [ERROR] WSREP: wsrep::connect() failed: 7
140618 10:11:50 [ERROR] Aborting

There is no firewall on both servers. I would be grateful for any ideas.
- GEX Reply to GEX
  
  June 6, 2015 at 6:26 pm
  
  this is probably because SElinux is enabled , to disable it :
  
  – setenforce 0
TheIgor Reply to TheIgor

June 14, 2014 at 3:43 am

This is a 32 bit server, for 64 bit you need to change ‘wsrep_provider=/usr/lib/galera/libgalera_smm.so’ to ‘wsrep_provider=/usr/lib64/galera/libgalera_smm.so.’
Massimiliano Reply to Massimiliano

April 14, 2014 at 8:01 am

to be more precise, let’s imagine we have node1, node2, node3:
we create a user ‘sstuser’, with password ‘sstpassword’ and than:
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘sstuser’@’node1’ IDENTIFIED BY ‘sstpassword’;
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘sstuser’@’node2’ IDENTIFIED BY ‘sstpassword’;
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘sstuser’@’node3’ IDENTIFIED BY ‘sstpassword’;

p.s.: I am not sure if root access between the nodes is necessary…. I need to check this. it might be unnecessary.
- Rahul Reply to Rahul
  
  April 16, 2014 at 3:28 am
  
  Hi Massimiliano,
  
  Thanks for your valuable suggestion’s.
  
  I will this if root user is not necessary and update article accordingly.
Massimiliano Reply to Massimiliano

April 14, 2014 at 7:57 am

This is not a proper set-up => GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’