You are here
MariaDB Master/Master GTID based Replication with keepalived VIP
Important: FromDual does NOT recommend to use an automated VIP failover technology as described below for a MariaDB/MySQL Master/Master Replication. In such a topology we recommend a manual VIP failover with prepared scripts!
Some of our customers still want to have old-style MariaDB Master/Master Replication Clusters. Time goes by, new technologies appear but some old stuff still remains.
The main problem in a Master/Master Replication set-up is to make the service highly available for the application (applications typically cannot deal with more than one point-of-contact). This can be achieved with a load balancer (HAproxy, Galera Load Balancer (GLB), ProxySQL or MaxScale) in front of the MariaDB master/master replication cluster. But the load balancer by it-self should also become highly available. And this is typically achieved by a virtual IP (VIP) in front of one of the load balancers. To make operations of the VIP more handy the VIP is controlled by a service like keepalived or corosync.
Because I like simple solutions (I am a strong believer in the KISS principle) I thought about avoiding the load balancer in the middle and attach the VIP directly to the master/master replication servers and let them to be controlled by keepalived as well.
Important: A master/master replication set-up is vulnerable to split-brain situations. Neither keepalived nor the master/master replication helps you to avoid conflicts and in any way to prevent this situation. If you are sensitive to split-brain situations you should look for Galera Cluster. Keepalived is made for stateless services like load balancers, etc. but not databases.
Set-up a MariaDB master/master replication cluster
Because most of the Linux distributions have a bit old versions of software delivered we use the MariaDB 10.2 repository from the MariaDB website:
# # /etc/yum.repos.d/MariaDB-10.2.repo # # MariaDB 10.2 CentOS repository list - created 2017-11-08 20:32 UTC # http://downloads.mariadb.org/mariadb/repositories/ # [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.2/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1
Then we install the MariaDB server and start it:
shell> yum makecache shell> yum install MariaDB-server MariaDB-client shell> systemctl start mariadb shell> systemctl enabled mariadb
For the MariaDB master/master replication set-up configuration we use the following parameters:
# # /etc/my.cnf # [mysqld] server_id = 1 # 2 on the other node log_bin = binlog-m1 # binlog-m2 on the other node log_slave_updates = 1 gtid_domain_id = 1 # 2 on the other node gtid_strict_mode = On auto_increment_increment = 2 auto_increment_offset = 1 # 2 on the other node read_only = On # super_read_only for MySQL 5.7 and newer
Then we close the master/master replication ring according to: Starting with empty server.
mariadb> SET GLOBAL gtid_slave_pos = ""; mariadb> CHANGE MASTER TO master_host="192.168.56.101", master_user="replication" , master_use_gtid=current_pos; mariadb> START SLAVE;
Installing keepalived
Literature:
The next step is to install and configure keepalived. This can be done as follows:
shell> yum install keepalived shell> systemctl enable keepalived
Important: In my tests I got crashes and core dumps with keepalived which disappeared after a full upgrade of CentOS 7.
Configuring keepalived
The most important part is the keepalived configuration file:
With the command:
shell> systemctl restart keepalived
the service is started and/or the configuration is reloaded.
The scripts we used in the configuration file are the following:
chk_failover.sh keepalived_backup.sh keepalived_fault.sh keepalived_master.sh keepalived_notify.sh keepalived_stop.sh
#!/bin/bash # # /etc/keepalived/keepalived_notify.sh # TYPE=${1} NAME=${2} STATE=${3} PRIORITY=${4} TS=$(date '+%Y-%m-%d_%H:%M:%S') LOG=/etc/keepalived/keepalived_notify.log echo $TS $0 $@ >>${LOG}
#!/bin/bash # # /etc/keepalived/chk_failover.sh # /usr/bin/stat /etc/keepalived/failover 2>/dev/null 1>&2 if [ ${?} -eq 0 ] ; then exit 1 else exit 0 fi
To make MariaDB master/master replication more robust against replication problems we took the following (configurable) actions on the database side:
Getting the MASTER role:
- Waiting for catch-up replication
- Make the MariaDB instance read/write
Getting the BACKUP role:
- Make the MariaDB instance read-only
- Kill all open connections
Testing scenarios
The following scenarios where tested under load (insert_test.sh
):
- Intentional failover for maintenance:
shell> touch /etc/keepalived/failover shell> rm -f /etc/keepalived/failover
- Stopping keepalived:
shell> systemctl stop keepalived shell> systemctl start keepalived
- Stopping MariaDB node:
shell> systemctl stop mariadb shell> systemctl start mariadb
- Reboot server:
shell> reboot
- Simulation of split-brain:
shell> ip link set enp0s9 down shell> ip link set enp0s9 up
Problems
Problems we faced during set-up and testing were:
- SElinux/AppArmor
- Firewall
Keepalived controlling 2 virtual IPs
A second scenario we wanted to build is a MariaDB master/master GTID based replication cluster with 2 VIP addresses. This is to achieve either a read-only VIP and a read/write VIP or to have half of the load on one master and half of the load on the other master:
For this scenario we used the same scripts but a slightly different keepalived configuration:
- Shinguz's blog
- Log in or register to post comments