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)
