MariaDB Galera Cluster


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

Install software
yum install epel-release -y

cat < EOF > /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB 10.3 Repository
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
enabled = 1
gpgcheck = 0
EOF

cat < EOF > /etc/yum.repos.d/percona.repo
[percona-release-$basearch]
name = Percona-Release YUM repository - $basearch
baseurl = http://repo.percona.com/release/$releasever/RPMS/$basearch
enabled = 1
gpgcheck = 0

[percona-release-noarch]
name = Percona-Release YUM repository - noarch
baseurl = http://repo.percona.com/release/$releasever/RPMS/noarch
enabled = 1
gpgcheck = 0

[percona-release-source]
name = Percona-Release YUM repository - Source packages
baseurl = http://repo.percona.com/release/$releasever/SRPMS
enabled = 0
gpgcheck = 0
EOF

cd /tmp
wget https://downloads.mariadb.com/MaxScale/2.2.9/centos/7/x86_64/maxscale-2.2.9-1.centos.7.x86_64.rpm
rpm -Uvh maxscale-2.2.9-1.centos.7.x86_64.rpm

yum makecache
yum install -y MariaDB-server MariaDB-client MariaDB-compat MariaDB-backup galera
yum install -y percona-xtrabackup-24 qpress

cat /etc/my.cnf

[server]

[mysqld]
user=mysql
basedir=/usr/
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
pid_file=/var/lib/mysql/mysql.pid
port=3306
log_error=/var/log/mysql/mysqld.log
log_warnings=2
# log_output = FILE

#Slow logging
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=2
slow_query_log=OFF
log_queries_not_using_indexes=OFF

### INNODB OPTIONS
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
innodb_data_file_path = ibdata1:100M:autoextend
## You may want to tune the below depending on number of cores and disk
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_doublewrite=1
innodb_log_file_size=512M
innodb_log_buffer_size=64M
innodb_buffer_pool_instances=1
innodb_log_files_in_group=2
innodb_thread_concurrency=64
# innodb_file_format = barracuda
innodb_flush_method = O_DIRECT
# innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode=2
## avoid statistics update when doing e.g show tables
innodb_stats_on_metadata=0
default_storage_engine=innodb

# CHARACTER SET
# collation_server = utf8_unicode_ci
# init_connect = 'SET NAMES utf8'
# character_set_server = utf8

# REPLICATION SPECIFIC
server_id=1
binlog_format=ROW

# OTHER THINGS, BUFFERS ETC
# key_buffer_size = 24M
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 512M
# sort_buffer_size = 256K
# read_buffer_size = 256K
# read_rnd_buffer_size = 512K
# myisam_sort_buffer_size = 8M
skip_name_resolve
memlock=0
sysdate_is_now=1
max_connections=500
thread_cache_size=512
query_cache_type = 0
query_cache_size = 0
table_open_cache=1024
lower_case_table_names=0

performance_schema = ON
performance-schema-max-mutex-classes = 0
performance-schema-max-mutex-instances = 0

## WSREP options
##

wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so

wsrep_node_name="db1"
wsrep_node_address="10.0.0.11"
wsrep_cluster_name="db-cluster-name"
wsrep_cluster_address="gcomm://10.0.0.11,10.0.0.12,10.0.0.13"

# How many threads will process writesets from other nodes
wsrep_slave_threads=4

# Generate fake primary keys for non-PK tables (required for multi-master
# and parallel applying operation)
wsrep_certify_nonPK=1

# Location of the directory with data files. Needed for non-mysqldump
# state snapshot transfers. Defaults to mysql_real_data_home.
#wsrep_data_home_dir=

# Maximum number of rows in write set
wsrep_max_ws_rows=131072

# Maximum size of write set
wsrep_max_ws_size=1073741824

# to enable debug level logging, set this to 1
wsrep_debug=0

# convert locking sessions into transactions
wsrep_convert_LOCK_to_trx=0

# how many times to retry deadlocked autocommits
wsrep_retry_autocommit=1

# change auto_increment_increment and auto_increment_offset automatically
wsrep_auto_increment_control=1

# replicate myisam
wsrep_replicate_myisam=1

# retry autoinc insert, which failed for duplicate key error
wsrep_drupal_282555_workaround=0

# enable "strictly synchronous" semantics for read operations
wsrep_causal_reads=0

# SST auth
wsrep_sst_auth=backupuser:gklqtiGy07MuWYjB

# State Snapshot Transfer method
# [ mariabackup | rsync | xtrabackup-v2 | mysqldump ]
wsrep_sst_method=mariabackup

# Desired SST donor name.
#wsrep_sst_donor=

# log conflicts
wsrep_log_conflicts=1

[MYSQL]
socket=/var/lib/mysql/mysql.sock
# default_character_set = utf8

[client]
socket=/var/lib/mysql/mysql.sock
# default_character_set = utf8

[mysqldump]
user=backupuser
password=gklqtiGy07MuWYjB
max_allowed_packet = 512M
# default_character_set = utf8

# IMPORTANT: The user/password in wsrep_sst_auth must match
# user/password in [xtrabackup]
[xtrabackup]
user=backupuser
password=gklqtiGy07MuWYjB
databases-exclude=lost+found
ssl_mode=DISABLED

[MYSQLD_SAFE]
# log_error = /var/log/mysqld.log
basedir=/usr/
# datadir = /var/lib/mysql

# 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
# 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,10.0.0.12,10.0.0.13"
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
PASSWORD=P@ssw0rd
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

one more Maxscale configuration
[maxscale]
threads=4

[GaleraMonitor]
type=monitor
module=galeramon
servers=DB_4,DB_6,DB_7
user=root
passwd=8E1263561EE4A80FF1509D6400C7D2D5
monitor_interval=10000
disable_master_failback=1

[qla]
type=filter
module=qlafilter
filebase=/tmp/QueryLog

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

[RW]
type=service
router=readwritesplit
servers=DB_4,DB_6,DB_7
user=root
passwd=8E1263561EE4A80FF1509D6400C7D2D5
max_slave_connections=100%
router_options=slave_selection_criteria=LEAST_CURRENT_OPERATIONS

[RR]
type=service
router=readconnroute
router_options=synced
servers=DB_4,DB_6,DB_7
user=root
passwd=8E1263561EE4A80FF1509D6400C7D2D5

[DebugInterface]
type=service
router=debugcli

[CLI]
type=service
router=cli

[RWlistener]
type=listener
service=RW
protocol=mariadbclient
address=10.33.1.51
port=4008

[RRlistener]
type=listener
service=RR
protocol=mariadbclient
address=10.33.1.51
port=4006

[DebugListener]
type=listener
service=DebugInterface
protocol=telnetd
address=127.0.0.1
port=4442

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

[DB_4]
type=server
address=10.33.1.51
port=3306
protocol=mariadbbackend

[DB_6]
type=server
address=10.33.1.52
port=3306
protocol=mariadbbackend

[DB_7]
type=server
address=10.33.1.53
port=3306
protocol=mariadbbackend
ProxySQL example
cat /etc/proxysql-admin.cnf

# proxysql admin interface credentials.
export PROXYSQL_DATADIR='/var/lib/proxysql'
export PROXYSQL_USERNAME="admin"
export PROXYSQL_PASSWORD="P@ssw0rd"
export PROXYSQL_HOSTNAME='localhost'
export PROXYSQL_PORT="6032"

# PXC admin credentials for connecting to pxc-cluster-node.
export CLUSTER_USERNAME='admin'
export CLUSTER_PASSWORD='admin'
export CLUSTER_HOSTNAME='localhost'
export CLUSTER_PORT='3306'

# proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.
export MONITOR_USERNAME="monitor"
export MONITOR_PASSWORD="P@ssw0rd"

# Application user to connect to pxc-node through proxysql
export CLUSTER_APP_USERNAME='proxysql_user'
export CLUSTER_APP_PASSWORD='P@ssw0rd'

# ProxySQL read/write hostgroup.
export WRITE_HOSTGROUP_ID="10"
export READ_HOSTGROUP_ID="20"

# ProxySQL read/write configuration mode.
export MODE="singlewrite"

# ProxySQL Cluster Node Priority File
export HOST_PRIORITY_FILE=$PROXYSQL_DATADIR/host_priority.conf
cat /etc/proxysql.cnf
#file proxysql.cfg

# This config file is parsed using libconfig , and its grammar is described in:
# http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar
# Grammar is also copied at the end of this file

datadir="/var/lib/proxysql"

admin_variables=
{
        admin_credentials="admin:P@ssw0rd"
        mysql_ifaces="0.0.0.0:6032;/tmp/proxysql_admin.sock"
#       refresh_interval=2000
#       debug=true
}

mysql_variables=
{
        threads=4
        max_connections=2048
        default_query_delay=0
        default_query_timeout=36000000
        have_compress=true
        poll_timeout=2000
        interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
        default_schema="information_schema"
        stacksize=1048576
        server_version="5.1.30"
        connect_timeout_server=10000
        monitor_history=600000
        monitor_connect_interval=60000
        monitor_ping_interval=30000
        ping_interval_server=10000
        ping_timeout_server=200
        commands_stats=true
        sessions_sort=true
}

# defines all the MySQL servers
mysql_servers =
(
#       {
#               address = "127.0.0.1" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
#               port = 3306           # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
#               hostgroup = 0           # no default, required
#               status = "ONLINE"     # default: ONLINE
#               weight = 1            # default: 1
#               compression = 0       # default: 0
#   max_replication_lag = 10  # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned
#       },
#       {
#               address = "/var/lib/mysql/mysql.sock"
#               port = 0
#               hostgroup = 0
#       },
#       {
#               address="127.0.0.1"
#               port=21891
#               hostgroup=0
#               max_connections=200
#       },
#       { address="127.0.0.2" , port=3306 , hostgroup=0, max_connections=5 },
#       { address="127.0.0.1" , port=21892 , hostgroup=1 },
#       { address="127.0.0.1" , port=21893 , hostgroup=1 }
#       { address="127.0.0.2" , port=3306 , hostgroup=1 },
#       { address="127.0.0.3" , port=3306 , hostgroup=1 },
#       { address="127.0.0.4" , port=3306 , hostgroup=1 },
#       { address="/var/lib/mysql/mysql.sock" , port=0 , hostgroup=1 }
)

# defines all the MySQL users
mysql_users:
(
#       {
#               username = "username" # no default , required
#               password = "password" # default: ''
#               default_hostgroup = 0 # default: 0
#               active = 1            # default: 1
#       },
#       {
#               username = "root"
#               password = ""
#               default_hostgroup = 0
#               max_connections=1000
#               default_schema="test"
#               active = 1
#       },
#       { username = "user1" , password = "password" , default_hostgroup = 0 , active = 0 }
)



#defines MySQL Query Rules
mysql_query_rules:
(
#       {
#               rule_id=1
#               active=1
#               match_pattern="^SELECT .* FOR UPDATE$"
#               destination_hostgroup=0
#               apply=1
#       },
#       {
#               rule_id=2
#               active=1
#               match_pattern="^SELECT"
#               destination_hostgroup=1
#               apply=1
#       }
)

# http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar
#
# Below is the BNF grammar for configuration files. Comments and include directives are not part of the grammar, so they are not included here.
#
# configuration = setting-list | empty
#
# setting-list = setting | setting-list setting
#
# setting = name (":" | "=") value (";" | "," | empty)
#
# value = scalar-value | array | list | group
#
# value-list = value | value-list "," value
#
# scalar-value = boolean | integer | integer64 | hex | hex64 | float
#                | string
#
# scalar-value-list = scalar-value | scalar-value-list "," scalar-value
#
# array = "[" (scalar-value-list | empty) "]"
#
# list = "(" (value-list | empty) ")"
#
# group = "{" (setting-list | empty) "}"
#
# empty =