In a previous tutorial, we explained how to use Master-Slave replication in MySQL and MariaDB to migrate a database or prepare a warm spare that can be failed over to if the primary database server is unavailable. This tutorial builds on that concept by introducing Master-Master replication with two or more database nodes. To better understand Master-Master replication, consider each database node as both master and slave.
Master-Master replication is useful for scaling out busy database clusters, by load balancing read & write queries across multiple database servers. Contrary to sharding, every key is replicated across to every server. When the cluster is consistent with itself, it should contain an identical replica of the database data on every node.
Much like Master-Slave replication, Master-Master replication is achieved by enabling the binary log. The binary log keeps track of the coordinates to facilitate the replication of write operations, by replaying the executed SQL queries on each node that is part of the replication pool. A minimal Master-Master setup involves two masters, but this architecture can easily be extended to three or more masters by implementing circular replication.
- Master 1 replicates to Master 2
- Master 2 replicates to Master 3
- Master 3 replicates to Master 1
Using an odd number of clusters such as three can eliminate the likelihood of a “split brain” scenario where one database server is inconsistent with the other (e.g. due to a network partition), and you have to choose which copy of the data to keep. If you have a reliable network connection between the database nodes, and your application can tolerate resolving “split brains” by simply keeping the more recent writes, using two masters can be adequate.
To realize the benefits of spreading out your database workloads across multiple database servers, you must have a load balancer in front of your MySQL masters. The load balancer must support TCP, port 3306 if using the default MySQL port. It should also be capable of performing health checks to stop routing queries to database instances that are slow or unresponsive, and add them back to the pool once they’re back online.
HAProxy, an open source load balancer, fulfills all of these requirements so its commonly used for load balancing MySQL or MariaDB. Because HAProxy is open source and self hosted, it is vendor agnostic. You can construct this Master-Master database cluster on virtually any cloud or VPS provider, making it a versatile and cost-effective option compared to using Amazon RDS or other managed database solutions.
In this tutorial, we will build a Master-Master database cluster with MySQL Community Server 5.7 consisting of two database nodes, load balanced by HAProxy. As a minimal configuration, we recommend a 2GB/1 Core server for HAProxy and 4GB/2 Core server for the two x MySQL servers.
First, create 3 virtual servers with private networking capability. We use DigitalOcean because they support internal networking out of the box between droplets (i.e. servers) in the same datacenter, but the same architecture can be deployed to AWS or one of the other major cloud providers, by creating a VPC and security group for the MySQL EC2 instances.
Step 1 – Set up virtual servers.
For our example, we will use CentOS 7 for all of the servers, and the below hostnames. Please note down the internal IPs assigned to each instance by the cloud provider and use them in place of the example IPs in the following steps.
- Hostname: haproxy – 2 GB virtual server – internal IP: 198.51.100.1
- Hostname: mysql-1 – 4 GB virtual server – internal IP: 198.51.100.2
- Hostname: mysql-2 – 4 GB virtual server – internal IP: 198.51.100.3
If your cloud provider does not isolate traffic for each team or account, and uses a single shared private network for all of its customers, take special care to use a firewall such as ufw, firewalld, or iptables to limit traffic to authorized servers by internal IP address. This is the case for Linode. For throughput purposes, this tutorial does not encrypt traffic between the MySQL masters using SSL, assuming that the internal network that carries the replication data is safe from interception by other users. If you want to encrypt your MySQL replication with SSL, contact our cloud architects or you can adapt the instructions in our previous guide about Master-Slave replication.
On mysql-1 and mysql-2 create the following firewall rules:
[root@mysql-1 ~]# yum update
[root@mysql-1 ~]# yum install firewalld
[root@mysql-1 ~]# systemctl enable firewalld && systemctl start firewalld
[root@mysql-1 ~]# firewall-cmd --permanent --zone=internal --add-source=198.51.100.1/32
[root@mysql-1 ~]# firewall-cmd --permanent --zone=internal --add-source=198.51.100.2/32
[root@mysql-1 ~]# firewall-cmd --permanent --zone=internal --add-source=198.51.100.3/32
[root@mysql-1 ~]# firewall-cmd --permanent --zone=internal --add-port=3306/tcp
[root@mysql-1 ~]# firewall-cmd --reload
If you have an application that will connect to the MySQL cluster from 198.51.100.4, create the following rules on haproxy:
[root@haproxy ~]# yum update
[root@haproxy ~]# yum install firewalld
[root@haproxy ~]# systemctl enable firewalld && systemctl start firewalld
[root@haproxy ~]# firewall-cmd --permanent --zone=internal --add-source=198.51.100.1/32
[root@haproxy ~]# firewall-cmd --permanent --zone=internal --add-source=198.51.100.4/32
[root@haproxy ~]# firewall-cmd --reload
Step 2 – Install MySQL on mysql-1 and mysql-2.
Add the yum repo for MySQL Community Server, install the 5.7 branch on mysql-1 and mysql-2, and secure it by changing the default MySQL root password, disabling remote root login, and removing the default database and users. mysql_secure_installation.
Note that all user passwords (including the root user) in MySQL Community Server must contain at least one special character ($, #, *, ?, etc.) to fulfil the password strength requirement. This requirement does not apply with MariaDB.
[root@mysql-1 ~]# yum update
[root@mysql-1 ~]# rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
[root@mysql-1 ~]# sudo yum-config-manager --disable mysql80-community
[root@mysql-1 ~]# sudo yum-config-manager --enable mysql57-community
[root@mysql-1 ~]# yum install mysql-community-server
[root@mysql-1 ~]# systemctl enable mysqld && systemctl start mysqld
# Obtain the default root password from the mysqld log.
[root@mysql-1 ~]# cat /var/log/mysqld.log | grep password
[root@mysql-1 ~]# mysql_secure_installation
Repeat on mysql-2 until both servers are running MySQL 5.7 and have it enabled as a service.
Step 3 – Edit the MySQL configuration file on Master 1.
[root@mysql-1 ~]# yum install nano
[root@mysql-1 ~]# nano /etc/my.cnf
In the [mysqld] block, add the following lines to bind the MySQL daemon to the local network adapter, set the server ID to 1, and enable the binary log (required for SQL replication).
server_id = 1
bind-address = 198.51.100.2
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 1
Reboot the MySQL server.
[root@mysql-1 ~]# systemctl restart mysqld
Step 4 – Edit the MySQL configuration file on Master 2.
[root@mysql-2 ~]# yum install nano
[root@mysql-2 ~]# nano /etc/my.cnf
In the [mysqld] block, add the following lines to bind the MySQL daemon to the local network adapter, set the server ID to 1, and enable the binary log (required for SQL replication).
server_id = 2
bind-address = 198.51.100.3
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 1
Reboot the MySQL server.
[root@mysql-2 ~]# systemctl restart mysqld
Step 5 – Create the replication users and initiate replication on Master 1 and Master 2.
[root@mysql-1 ~]# mysql -uroot -p
mysql > create user 'replication'@'%' identified by 'password!';
mysql > grant replication slave on *.* to 'replicator'@'%';
mysql > show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 100 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Note down the bin log position from mysql-1 displayed by show master status; in this case 000001 and 100.
[root@mysql-2 ~]# mysql -uroot -p
mysql > create user 'replication'@'%' identified by 'password!';
mysql > grant replication slave on *.* to 'replicator'@'%';
mysql > show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 110 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Note down the bin log position from mysql-2 displayed by show master status; in this case 000001 and 110.
Initiate replication on mysql-1, using the bin log coordinates 000001 and 110 from mysql-2.
[root@mysql-1 ~]# mysql -uroot -p
mysql > stop slave;
mysql > CHANGE MASTER TO MASTER_HOST = '198.51.100.3', MASTER_USER = 'replication', MASTER_PASSWORD = 'password!', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 110;
mysql > start slave;
Initiate replication on mysql-2, using the bin log coordinates 000001 and 100 from mysql-1.
[root@mysql-2 ~]# mysql -uroot -p
mysql > stop slave;
mysql > CHANGE MASTER TO MASTER_HOST = '198.51.100.2', MASTER_USER = 'replication', MASTER_PASSWORD = 'password!', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 100;
mysql > start slave;
Run the following query on both MySQL servers to ensure replication is up.
mysql > show slave status \G;
From the output, the Slave_IO_State: should read Waiting for master to send event and the Seconds_Behind_Master: should read 0.
To test MySQL Master-Master replication you can create a test database and table on mysql-1 and see it reflected on mysql-2 (or vice versa).
mysql > create database test1;
mysql > create table test1.table1 (`id` varchar(10));
mysql > show tables in test;
+-------------------+
| Tables_in_test1 |
+-------------------+
| table1 |
+-------------------+
1 row in set (0.00 sec)
If you made it this far you successfully set up MySQL or MariaDB Master-Master replication between two master nodes. Next we will proceed to load balance the two MySQL masters using HAProxy.
Step 6 – Add the MySQL users for HAProxy to mysql1 and mysql2.
Where 198.51.100.1 is the internal IP of your HAProxy load balancer. The haproxy_check MySQL user has a blank password because its simply used by haproxy to check whether the database server is alive or not – it has no permission to manipulate databases or database users.
On the other hand, protect the haproxy_root password as you would the root passwords for mysql1 and mysql2, as that is the superuser account when accessing the cluster from the load balancer.
[root@mysql-1 ~]# mysql -uroot -p
mysql > use mysql;
mysql > INSERT INTO user (Host,User,ssl_cipher,x509_issuer,x509_subject) values ('198.51.100.1','haproxy_check','','','');
mysql > GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'198.51.100.1' IDENTIFIED BY 'password!' WITH GRANT OPTION;
mysql > flush privileges;
[root@mysql-2 ~]# mysql -uroot -p
mysql > use mysql;
mysql > INSERT INTO user (Host,User,ssl_cipher,x509_issuer,x509_subject) values ('198.51.100.1','haproxy_check','','','');
mysql > GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'198.51.100.1' IDENTIFIED BY 'password!' WITH GRANT OPTION;
mysql > flush privileges;
Step 7 – Install HAProxy and MySQL client on haproxy.
The MySQL client is needed on the haproxy server to perform health checks of the MySQL masters. However, please ensure you disable and stop the MySQL daemon so haproxy so there are no port conflicts.
[root@haproxy ~]# yum install haproxy
[root@haproxy ~]# systemctl enable haproxy && systemctl start haproxy
[root@haproxy ~]# rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
[root@haproxy ~]# sudo yum-config-manager --disable mysql80-community
[root@haproxy ~]# sudo yum-config-manager --enable mysql57-community
[root@haproxy ~]# yum install mysql-community-server
[root@haproxy ~]# systemctl disable mysqld && systemctl stop mysqld
Add the HAProxy configuration.
[root@haproxy ~]# yum install nano
[root@haproxy ~]# nano /etc/haproxy/haproxy.cfg
global
log /dev/log local0 notice
user haproxy
group haproxy
defaults
log global
retries 2
timeout connect 3000
timeout server 5000
timeout client 5000
listen mysql-cluster
bind 198.51.100.1:3306
mode tcp
option mysql-check user haproxy_check
balance roundrobin
server mysql-1 198.51.100.2:3306 check
server mysql-2 198.51.100.3:3306 check
Reboot the HAProxy load balancer.
[root@haproxy ~]# systemctl restart haproxy
Step 8 – Test the HAProxy load balancer.
You can test the HAProxy load balancing of the Master-Master database cluster by connecting using the MySQL client on the haproxy server to the cluster endpoint which is 198.51.100.1. Because HAProxy is set to balance traffic round-robin, you should get server_id 1 followed by server_id 2, then back to server_id 1, and so on.
[root@haproxy ~]# mysql -h 198.51.100.1 -u haproxy_root -p -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
[root@haproxy ~]# mysql -h 198.51.100.1 -u haproxy_root -p -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
If you shutdown either of the MySQL masters by running systemctl disable mysqld against either mysql-1 or mysql-2, all traffic should be routed exclusively to the remaining database server.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
