High Availability (HA) with MySQL Group Replication and ProxySQL
We use 1 Load Balancer, 2 ProxySQLs, and 2 MySQL Group Replications. We use load balancer as a gateway to system and to avoid single point of failure for ProxySQL.
Node Configuration
| Node | IP Private | Have IP Public? | Description |
|---|---|---|---|
| Node 1 | 172.16.0.1 | yes | MySQL 1 (Master) |
| Node 2 | 172.16.0.2 | yes | MySQL 2 (Master) |
| Node 3 | 172.16.0.3 | yes | ProxySQL 1 |
| Node 4 | 172.16.0.4 | yes | ProxySQL 2 |
| Node 5 | ? | yes (eg: 50.50.50.1) | Load Balancer |
Configuration Step
- Configure Group Replication on node 1 and node 2
- Configure ProxySQL on node 3 and node 4
- Configure Load Balancer
Test HA with Some Scenarios
1. All Nodes is Up
Test to access the user and database using load balancer Public IP address (eg: 50.50.50.1). If we use shell to access mysql use this command: mysql -u sbuser -psbpassword -h 50.50.50.1 -P 60331
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24Setting environment for using XAMPP for Windows.
user@DESKTOP-P20ELHV d:\xampp
# mysql -u sbuser -psbpassword -h 50.50.50.1 -P 6033
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 367836
Server version: 5.5.30 (ProxySQL)
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> select * from playground.equipment;
+----+-------+-------+--------+
| id | type | quant | color |
+----+-------+-------+--------+
| 1 | slide | 2 | blue |
| 2 | swing | 10 | yellow |
| 9 | slide | 3 | green |
| 15 | pen | 5 | purple |
| 16 | watch | 6 | black |
+----+-------+-------+--------+
5 rows in set (0.06 sec)
MySQL [(none)]>
2. One of ProxySQL Node is Down
Stop proxySQL service in one node.1
root@proxysqlnode1:~# service proxysql stop
Test to access the user and database using load balancer Public IP address.
3. One of MySQL Group Replication Node is Down
Stop mysql service in one of MySQL Group Replication node.1
root@mysql1:~# systemctl stop mysql.service
Test to access the user and database using load balancer Public IP address.