Galera Cluster for MySQL – True Multi-Master on CentOS/RHEL 7.x

Galera Cluster for MySQL – The True Multi-Master

Advantages:

  • High Availability
  • Multi-Master – read and write on any cluster node, anytime
  • No slave lag
  • No lost transactions
  • Row level replication
  • Highly Scalable

 

Steps to setup a Cluster:

Install 3 containers with at least 10 GB of disk space

 

Package pre-requisites on all nodes
yum -y install rsync which socat net-tools

 

Add MariaDB repository
for host in {galera01,galera02,galera03}; do ssh $host \
"tee /etc/yum.repos.d/MariaDB.repo << mariadbrepo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
mariadbrepo"; done

 

Install MariaDB
for host in {galera01,galera02,galera03}; do ssh $host \
    "yum -y install MariaDB-Galera-server"; done

 

Start MySQL individually and setup SST user before configuring the cluster

mysql_secure_installation


mysql -u root -p XXXXXXXX

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4827
Server version: 5.5.48-MariaDB-wsrep MariaDB Server, wsrep_25.14.r9949137

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant all privileges on *.* to sst_user@'192.168.1.%' identified by 'kjxhdgewu6dsc3f';

Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.05 sec)

 

After this step is done stop mysql:

/etc/init.d/mysql stop

 

Configure your nodes

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

 

And change to:

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Galera-related settings
#
[galera]
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
bind-address=0.0.0.0
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.1.101,192.168.1.102,192.168.1.103"
wsrep_cluster_name='Galera_Cluster'
wsrep_node_address='192.168.1.101'
wsrep_node_name='galera01'
wsrep_sst_method=rsync
wsrep_sst_auth=sst_user:kjxhdgewu6dsc3f

# this is only for embedded server
[embedded]

# This group is only read by MariaDB-5.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mysqld-5.5]

# These two groups are only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

[mariadb-5.5]

 

Start the nodes

1st node is always special:
/etc/init.d/mysql start --wsrep-new-cluster

The other 2 nodes will just join the cluster:
/etc/init.d/mysql start

 

Check the status of you cluster

mysql -u root -p XXXXXXXX

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4827
Server version: 5.5.48-MariaDB-wsrep MariaDB Server, wsrep_25.14.r9949137

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'wsrep_%';

 

HAProxy monitoring for Galera 

Steps below must be done on all Galera nodes:

wget -O /usr/bin/clustercheck https://raw.githubusercontent.com/olafz/percona-clustercheck/master/clustercheck

chmod +x /usr/bin/clustercheck

yum -y install xinetd

echo "mysqlchk   9200/tcp" | tee -a /etc/services

tee /etc/xinetd.d/mysqlchk << hapcheck
# default: on
# description: mysqlchk
service mysqlchk
{
disable = no
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = nobody
server = /usr/bin/clustercheck
log_on_failure += USERID
only_from = 0.0.0.0/0
per_source = UNLIMITED
}
hapcheck

 

Setup a cluster monitoring MySQL user

[root@galera01 ~]# mysql -u root -p XXXXXXXX
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5615
Server version: 5.5.48-MariaDB-wsrep MariaDB Server, wsrep_25.14.r9949137

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant select on *.* to cmon@'192.168.1.%' identified by 'dshdj4fh44nofd';

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.05 sec)

 

HAProxy configuration

vi /etc/haproxy/haproxy.cfg

And add the lines below:

listen galera *:3306
         mode tcp
         option httpchk
         balance leastconn
         server galera01 192.168.1.101:3306 check port 9200
         server galera02 192.168.1.102:3306 check port 9200
         server galera03 192.168.1.103:3306 check port 9200

 

Now you can connect to 192.168.1.100:3306 (the Virtual IP of the HAProxy Load Balancer)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s