PREREQUISITES

update yum

$ sudo yum update

checkout your release

$ cat /etc/redhat-release

CentOS Linux release 7.1.1503 (Core)

Let’s assume we have only 3 nodes;  db1, db2 and db3 nodes

db1: 10.10.0.71

db2: 10.10.0.73

db3: 10.1.1.12 (this one is in another datacenter connected via vpn, it doesn’t matter as long as they are connected to each other)

uninstall mysql-server if already installed (same if you already installed MariaDB-client and MariaDB-server)

$ sudo yum erase mysql-server mysql mysql-devel

remove folder /var/lib/mysql but be careful don’t delete this data dir if you need to keep your data

$ sudo rm -rf /var/lib/mysql/

kill the previous mysql process if exists, first learn mysql pid to kill

$ sudo pidof mysqld
1814
$ sudo kill -9 1814

Now we are ready to install MariaDB 

STEP 1 –  Add MariaDB Repository

select your OS to get the latest MariaDB repo

https://downloads.mariadb.org/mariadb/repositories

you can rename a similar name to MariaDB

$ sudo vi /etc/yum.repos.d/MariaDB.repo

copy paste this (for 64 bit):

# MariaDB 10.0 CentOS repository list - created 2015-07-09 14:56 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

 

STEP 2 – Add hosts to each 3 nodes

$ vi /etc/hosts

10.10.0.71 db1
10.10.0.73 db2
10.1.1.12 db3

ping each other

db1$ ping db2
db1$ ping db3

db2$ ping db1
db2$ ping db3

db3$ ping db1
db3$ ping db2

 

STEP 3 – Set SELinux in permissive mode in each node

set security in permissive mode and check out the status

$ sudo setenforce 0
$ sudo sestatus

STEP 4 – Install MariaDB Galera Cluster 10.0 software

First install socat package. Socat is a command line based utility that establishes two bidirectional byte streams and transfers data between them.

$ sudo yum install socat

now install mariadb galera cluster

$ sudo yum install MariaDB-Galera-server MariaDB-client rsync galera

STEP 5 – Setup MariaDB Security

start mysql

$ sudo service mysql start
Starting MySQL.. SUCCESS!

check out mysql status

$ sudo service mysql status
SUCCESS! MySQL running (26320)

setup mysql security

$ sudo mysql_secure_installation
($ sudo /usr/bin/mysql_secure_installation)

restart apache to phpmyadmin gets effect

$ sudo systemctl restart httpd.service

 

STEP 6 – Create MariaDB Galera Cluster Users

login mysql

$ sudo mysql -u root -p

The cluster_user is the user which a database node will use for authenticating to another database node

mysql>DELETE FROM mysql.user WHERE user=”;

msyql>GRANT ALL ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘YourPassword’;

mysql>GRANT USAGE ON *.* TO cluster_user@’%’ IDENTIFIED BY ‘YourPassword’;

mysql>GRANT ALL PRIVILEGES on *.* to cluster_user@’%’;

mysql>FLUSH PRIVILEGES;

mysql>quit;

verify by connecting db with cluster_user

$ sudo mysql -u cluster_user -p

mysql>show databases;

mysql>quit;

 

STEP 7 – Create the MariaDB Galera Cluster config

stop mysql on all nodes

$ sudo service mysql stop

add cluster config to server.cnf for each node, wsrep_node_name, wsrep_node_address will be different in each node, wsrep_sst_auth stays same because each node has the same authentication

$ sudo vi /etc/my.cnf.d/server.cnf

you will see Gelara-related settings

copy paste the below over after innodb_flush_log_at_trx_commit=0

# Added by Mehmet Sen
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://10.10.0.71,10.10.0.73,10.1.1.12"
wsrep_cluster_name='nau_galera_cluster'
wsrep_node_address=’10.10.0.71'
wsrep_node_name='db1'
wsrep_sst_method=rsync
wsrep_sst_auth=cluster_user:Memo3480$

 

STEP 8 – Initialize the first cluster node

do initialize on db1

$ sudo service mysql start –wsrep-new-cluster

check it out

$ sudo mysql -uroot -p -e “show status like ‘wsrep%’”

if you see the below, then you are good

wsrep_local_state_comment    | Synced
wsrep_incoming_addresses     | 10.10.0.71:3306
wsrep_cluster_conf_id        | 1
wsrep_cluster_size           | 1
wsrep_ready                  | ON

 

STEP 9 – Add other cluster nodes

on db2, do a regular start

$ sudo service mysql start
Starting MySQL………SST in progress, setting sleep higher. SUCCESS!

check wsrep

$ sudo mysql -uroot -p -e “show status like ‘wsrep%'”

you will see the cluster size has increased to 2

wsrep_local_state_comment    | Synced
wsrep_incoming_addresses     | 10.10.0.73:3306,10.10.0.71:3306
wsrep_cluster_conf_id        | 2
wsrep_cluster_size           | 2
wsrep_connected              | ON
wsrep_ready                  | ON

repeat the same step for db3

$ sudo service mysql start
Starting MySQL……….SST in progress, setting sleep higher. SUCCESS!

check wsrep

$ sudo mysql -uroot -p -e “show status like ‘wsrep%'”

you will see the cluster size has increased to 3

wsrep_local_state_comment    | Synced
wsrep_incoming_addresses     | 10.10.0.73:3306,10.10.0.71:3306,10.1.1.12:3306
wsrep_cluster_conf_id        | 3
wsrep_cluster_size           | 3
wsrep_connected              | ON
wsrep_ready                  | ON

 

STEP 10 – Verify replication

login on each node

$ sudo mysql -u root -p

on db1 create a db

mysql> CREATE DATABASE clustertest;

on db2 and db3 verify clustertest

mysql> show databases;

on db1 create a new table mycluster

mysql>CREATE TABLE clustertest.mycluster ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), ipaddress VARCHAR(20), PRIMARY KEY(id));

on db2, db3 verify tables

mysql>show tables in clustertest;

on db1 insert a new record

mysql> INSERT INTO clustertest.mycluster (name, ipaddress) VALUES (“db1”, “10.10.0.71”);

on db2, db3 and db4 verify the records

mysql> select * from clustertest.mycluster;

on db3 add another new record

mysql> INSERT INTO clustertest.mycluster (name, ipaddress) VALUES (“db2”, “10.10.0.73”);

on db1, db2  verify the new record

mysql> select * from clustertest.mycluster;

on db2 add another new record

mysql> INSERT INTO clustertest.mycluster (name, ipaddress) VALUES (“db3″, “10.1.1.12”);

on db1, db3  verify

mysql> select * from clustertest.mycluster;