TECHIES WORLD

For Techs.... Techniques.... Technologies....

CpanelLinuxMysql

How to configure Mariadb Galera cluster

admin

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).

This article explains the steps to set up MariaDB Galera Cluster 10.0.12 with 3 nodes running with CentOS6 .

Cluster server details are as follows.

Cluster Server1: 192.168.1.10
Cluster Server2: 192.168.1.20
Cluster Server3: 192.168.1.30

Step1: Add MariaDB Repositories in all the three servers

Create a mariadb repository /etc/yum.repos.d/mariadb.repo and add the below contents.

For CentOS6 64bit,

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

For CentOS6  32bit,

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

Step2: Install MariaDB and Galera in all the three servers

Note that 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

Step3: Complete initial MariaDB configuration in all the three servers

Follow the below commands and configure initial settings.

#service mysql start
#mysql_secure_installation

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 'cluster'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit

and stop MariaDB service in all three servers before starting cluster configuration

#service mysql stop

Step4: Setup MariaDB Galera Cluster on Server1

Edit MariaDB configuration file in Server1 and add following values under [mariadb] section.

[root@server1 ~]# 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,192.168.1.30"
wsrep_cluster_name='cluster1'
wsrep_node_address='192.168.1.10'
wsrep_node_name='server1'
wsrep_sst_method=rsync
wsrep_sst_auth=cluster:password

Start cluster using following command.

[root@server1 ~]# /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

Step5: Add Server2 in MariaDB Cluster

Edit MariaDB server configuration file in Server2 and add following values under [mariadb] section. All the settings are similar to Server1 except wsrep_node_address, wsrep_cluster_address and wsrep_node_name.

[root@server2 ~]# 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,192.168.1.30"
wsrep_cluster_name='cluster1'
wsrep_node_address='192.168.1.20'
wsrep_node_name='server2'
wsrep_sst_method=rsync
wsrep_sst_auth=cluster:password

Start cluster using following command.

[root@server2 ~]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!

Step6: Add server3 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 Server1/Server2 configuration files. To add this server make changes same as Server2.

[root@server3 ~]# 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,192.168.1.30"
wsrep_cluster_name='cluster1'
wsrep_node_address='192.168.1.30'
wsrep_node_name='server3'
wsrep_sst_method=rsync
wsrep_sst_auth=cluster:password

Start cluster using following command.

[root@server3 ~]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!

Step7: Test MariaDB Galera Cluster Setup (Replication testing)

To test that Galera Cluster is working as expected, complete the following steps:

On the database client, verify that all nodes have connected to each other:

SHOW STATUS LIKE 'wsrep_%';

+---------------------------+------------+
| Variable_name | Value |
+---------------------------+------------+
| wsrep_local_state_comment | Synced (6) |
| wsrep_cluster_size | 3 |
| wsrep_ready | ON |
+---------------------------+------------+

wsrep_local_state_comment: The value Synced indicates that the node is connected to the cluster and operational.
wsrep_cluster_size: The value indicates the nodes in the cluster.
wsrep_ready: The value ON indicates that this node is connected to the cluster and able to handle transactions.

On the database client of Server1, create a table and insert data:

CREATE DATABASE galeratest;
USE galeratest;
CREATE TABLE test_table (
id INT PRIMARY KEY AUTO_INCREMENT,
msg TEXT ) ENGINE=InnoDB;
INSERT INTO test_table (msg)
VALUES ("Hello my dear cluster.");
INSERT INTO test_table (msg)
VALUES ("Hello, again, cluster dear.");

On the database client of Server2/Server3, check that the data was replicated correctly:

USE galeratest;
SELECT * FROM test_table;

+----+-----------------------------+
| id | msg |
+----+-----------------------------+
| 1 | Hello my dear cluster. |
| 2 | Hello, again, cluster dear. |
+----+-----------------------------+

The results given in the SELECT query indicates that data you entered in Server1 has replicated into Server2/Server3.

Step8: Test MariaDB Galera Cluster Setup (Failover testing)

You can also test Galera Cluster by simulating various failure situations on three nodes as follows:

To simulate a crash of a single mysqld process, run the command below on one of the nodes:

#killall -9 mysqld

To simulate a network disconnection, use iptables or netem to block all TCP/IP traffic to a node.

To simulate an entire server crash, run each mysqld in a virtualized guest, and abrubtly terminate the entire virtual instance.

That's all......

Leave a Reply