MariaDB Galera Cluster


2 or 3 node MariaDB Galera 10 Cluster + MaxScale + Keepalived

/etc/my.cnf.d/server.cnf
[server]

[mysqld]
port=3305

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name="db-cluster-name"
wsrep_cluster_address="gcomm://10.0.0.11:3333,10.0.0.12:3333,10.0.0.13:3333"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_sst_method=rsync
wsrep_node_address="10.0.0.11:3333"
wsrep_node_name="db1"

[embedded]

[mariadb]

[mariadb-10.1]
# on all nodes
systemctl stop mysql

# on 1st node
galera_new_cluster

# on node 2 & 3
systemctl start mysql

# on 1st node
mysql_secure_installation

# check
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
MaxScale
wget https://downloads.mariadb.com/MaxScale/2.1.0/rhel/7/x86_64/maxscale-2.1.0-1.rhel.7.x86_64.rpm
rpm -Uvh maxscale-2.1.0-1.rhel.7.x86_64.rpm

# on all nodes
maxkeys
maxpasswd /var/lib/maxscale/ you-password-here
chmod 400 /var/lib/maxscale/.secrets
chown maxscale /var/lib/maxscale/.secrets

# from any node
mysql -u root -p
 CREATE USER 'maxscale'@'%' IDENTIFIED BY 'you-password-here';
 GRANT SELECT ON mysql.user TO 'maxscale'@'%'; 
 GRANT SELECT ON mysql.db TO 'maxscale'@'%';
 GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%';
 GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';
 GRANT REPLICATION CLIENT ON *.* TO 'maxscale'@'%';
/etc/maxscale.cnf on all nodes
[maxscale]
threads=4
syslog=0
maxlog=1
log_to_shm=1
log_warning=1
log_notice=1
log_info=0
log_debug=0

[Galera Monitor]
type=monitor
module=galeramon
servers=server1,server2,server3
user=maxscale
passwd=EBCD35E5ACBA982A1AD2A9DA2956834CAE12C996C # from maxpasswd
monitor_interval=2000
disable_master_failback=1
available_when_donor=1

[qla]
type=filter
module=qlafilter
filebase=/tmp/SqlQueryLog # for 2.1.0
#options=/tmp/QueryLog    # for 2.0.4

[fetch]
type=filter
module=regexfilter
match=fetch
replace=select

[RW Split Router]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale
passwd=EBCD35E5ACBA982A1AD2A9DA2956834CAE12C996C # from maxpasswd
max_slave_connections=100%
max_slave_replication_lag=30
enable_root_user=true

[CLI]
type=service
router=cli

[RW Split Listener]
type=listener
service=RW Split Router
protocol=MySQLClient
port=3306

[CLI Listener]
type=listener
service=CLI
protocol=maxscaled
address=127.0.0.1
port=6603

[server1]
type=server
address=10.0.0.11
port=3305
protocol=MySQLBackend

[server2]
type=server
address=10.0.0.12
port=3305
protocol=MySQLBackend

[server3]
type=server
address=10.0.0.13
port=3305
protocol=MySQLBackend
start maxscale on all nodes
systemctl enable maxscale && systemctl start maxscale
For 2 node configuration use 3rd node with garbd - arbitrator daemon for Galera cluster
cat << EOF >> /etc/sysconfig/garb
GALERA_NODES="10.0.0.11:3333,10.0.0.12:3333,10.0.0.13:3333"
GALERA_GROUP="db-cluster-name"
EOF

systemctl enable garb && systemctl start garb
Monitoring
maxadmin -pmariadb list servers
maxadmin -pmariadb list services
maxadmin -pmariadb list listeners
maxadmin -pmariadb show service "RW Split Router"
Backup
#!/bin/bash

USER=backup
[email protected]
HOST=127.0.0.1
PORT=3306

MAXBACKUPS=3
BACKUPSDIR=/data/backups/mysql
DATE=`date +"%Y%m%d.%H%M%S"`

# xtrabackup method
innobackupex --stream=tar --host=$HOST --port=$PORT --user=$USER --password=$PASSWORD --parallel=4 /data/ | gzip - > $BACKUPSDIR/mysql-backup-$DATE.tar.gz 2>&1

# mysqldump method
#mysqldump -h$HOST -P$PORT -u$USER -p$PASSWORD --all-databases --single-transaction | gzip > $BACKUPSDIR/mysqldump-$DATE.sql.gz

# cleanup
cd $BACKUPSDIR
ls -A1t | grep .gz | tail -n +$MAXBACKUPS | xargs rm

# recovery
# gunzip < /path/to/BACKUP-DIR | xargs innobackupex --copy-back

exit 0
Troubleshooting
# 1. Cluster failure
# [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208: Failed to open backend connection: -110 (Connection timed out

# recovery:
# - on the primary node do:
galera_new_cluster

# and restart mariadb on all others