MySQL Multi-Master Load Balancing with HAProxy
MySQL Multi-Master Load Balancing with HAProxy
Preparation
Servers
- 10.10.10.1 (MySQL Server #1)
- 10.10.10.2 (MySQL Server #2)
- 10.10.10.3 (HAProxy Server)
Preparation
- Install MySQL Server version 5.7 on
10.10.10.1
(MySQL Server #1) and10.10.10.2
(MySQL Server #2). - Install HAProxy on
10.10.10.3
(HAProxy Server).
Setup Multi-Master Replication on MySQL Servers
Configuration
- 10.10.10.1 (MySQL Server #1):
my.cnf
configuration:
[mysqld]
bind-address = 0.0.0.0
server_id = 1
log_bin = /var/lib/mysql/mysql-bin.log
log_bin_index = /var/lib/mysql/mysql-bin.log.index
relay_log = /var/lib/mysql/mysql-relay-bin
relay_log_index = /var/lib/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
- 10.10.10.2 (MySQL Server #2):
my.cnf
configuration:
[mysqld]
bind-address = 0.0.0.0
server_id = 2
log_bin = /var/lib/mysql/mysql-bin.log
log_bin_index = /var/lib/mysql/mysql-bin.log.index
relay_log = /var/lib/mysql/mysql-relay-bin
relay_log_index = /var/lib/mysql/mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 2
Restart MySQL service on both servers.
mysql-server-1:~$ sudo systemctl restart mysqld
mysql-server-2:~$ sudo systemctl restart mysqld
Grant replica user to both servers
Add MySQL Server 2 address on MySQL Server 1 & MySQL Server 1 address on MySQL Server 2.
- 10.10.10.1 (MySQL Server #1)
MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'10.10.10.2' IDENTIFIED BY 'strongpassword';
- 10.10.10.2 (MySQL Server #2)
MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'10.10.10.1' IDENTIFIED BY 'strongpassword';
Get binlog file name and position id
- 10.10.10.1 (MySQL Server #1)
MySQL [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 446 | | | |
+------------------+----------+--------------+------------------+-------------------+
- 10.10.10.2 (MySQL Server #2)
MySQL [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 524 | | | |
+------------------+----------+--------------+------------------+-------------------+
Reconfigure Slave
Do not forget to adjust master_log_file
and master_log_pos
on both servers.
- 10.10.10.1 (MySQL Server #1)
MySQL [(none)]> STOP SLAVE;
MySQL [(none)]> CHANGE MASTER TO master_host='10.10.10.2', master_port=3306, master_user='replica', master_password='strongpassword', master_log_file='mysql-bin.000003', master_log_pos=524;
MySQL [(none)]> START SLAVE;
- 10.10.10.2 (MySQL Server #2)
MySQL [(none)]> STOP SLAVE;
MySQL [(none)]> CHANGE MASTER TO master_host='10.10.10.1', master_port=3306, master_user='replica', master_password='strongpassword', master_log_file='mysql-bin.000001', master_log_pos=446;
MySQL [(none)]> START SLAVE;
Testing Replication
- 10.10.10.1 (MySQL Server #1)
mysql-server-1:~$ mysql -u root -p
MySQL [(none)]> CREATE DATABASE test_mmr;
- 10.10.10.2 (MySQL Server #2)
mysql-server-2:~$ mysql -u root -p
MySQL [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test_mmr |
+--------------------+
HAProxy Settings
- 10.10.10.3 (HAProxy Server)
Edit /etc/haproxy/haproxy.cfg
:
listen lbmysqlserver
bind *:3306
balance leastconn
mode tcp
option tcpka
server mysql-master-1 10.10.10.1:3306 check weight 1
server mysql-master-2 10.10.10.2:3306 check weight 1
Restart HAProxy service:
haproxy:~$ sudo systemctl restart haproxy
Testing HAProxy Load Balance
- First
$ mysql -u root -h 10.10.10.3 -p
MySQL [(none)]> SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
- Second
$ mysql -u root -h 10.10.10.3 -p
MySQL [(none)]> SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+