Design A Highly Available MySQL Clusters With Orchestrator And ProxySQL In GCP — Part 2

In part1, we explained how we are going to approach the HA setup. Here we can see how to install and configure Orchestrator and ProxySQL, then do the failover testing.

Install and configure MySQL Replication:

We need a MySQL with 4 Read replica and the 4'th replica will have a replica for it. And we must have to use GTID replication. Because once the master failover done, the remaining replicas will start replicating from the new master. Without GTID its not possible, but as an alternate Orchestrator provides Pseudo-GTID.

VM Details:

  • Subnet: 10.142.0.0/24
  • OS: Ubuntu 18.04LTS

Installing MySQL on all servers:

wget https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb
dpkg -i mysql-apt-config_0.8.10-1_all.deb
apt-get update
apt-get install -y mysql-server

Enable GTID & Other settings:

Do the below changes on all the servers on my.cnf file and restart mysql service.
Note: server-id must be unique for all the servers. So use different ids for other servers.

vi /etc/mysql/mysql.conf.d/mysqld.cnf
server-id                = 101
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
binlog-format = ROW
log_bin = /var/log/mysql/mysql-bin.log
master_info_repository = TABLE
service mysql restart

Create a database with sample data:

Run the below queries on mysql-01

mysql> create database sqladmin;
mysql> use sqladmin
mysql> create table test (id int );
mysql> insert into test values(1);

Backup the database:

Run the command on mysql-01

mysqldump -u root -p --databases sqladmin --routines --events --triggers > sqladmin.sql

Create the user for replication:

Run the query on mysql-01

create user 'rep_user'@'10.142.0.%' identified by 'rep_password';
GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'10.142.0.%';
flush privileges;

Establish the Replication:

Restore the database on the below servers and run the below query.

  1. mysql-ha
  2. replica-01
  3. replica-02
  4. report-01
-- Restore the database
mysql -u root -p < sqladmin.sql
-- Start replication
CHANGE MASTER TO MASTER_HOST='10.142.0.13',
MASTER_USER='rep_user',
MASTER_PASSWORD='rep_password',
MASTER_AUTO_POSITION = 1;
start slave;
-- Check the replication status
show slave status\G
                  Master_Host: 10.142.0.13
Master_User: rep_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1381
Relay_Log_File: replica-03-relay-bin.000002
Relay_Log_Pos: 1033
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Setup Replication for Report Server:

Take the dump of report-01server and restore it on report-ha

mysqldump -u root -p --databases sqladmin --routines --events --triggers > sqladmin.sql
mysql -u root -p < sqladmin.sql
-- from mysql shell
CHANGE MASTER TO MASTER_HOST='10.142.0.21',
MASTER_USER='rep_user',
MASTER_PASSWORD='rep_password',
MASTER_AUTO_POSITION = 1;
start slave;

Enable SEMI-SYNC replication:

To prevent the dataloss and make sure the pending binlogs on the failover instances during the failover process, we need to enable Semi_sync between mysql-01 and mysql-ha.

Install the plugin on both servers.

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

Enable Semi-Sync on Master:

rpl_semi_sync_master_timeout — Master will wait for the acknowledgment till this value. Please give this value in milliseconds.

SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 5000;

Enable Semi-Sync on Slave (replica-03):

SET GLOBAL rpl_semi_sync_slave_enabled = 1;
STOP SLAVE IO_THREAD; 
START SLAVE IO_THREAD;

We need to add this parameter in my.cnf as well.

On the master:
[mysqld]
rpl_semi_sync_master_enabled =1
rpl_semi_sync_master_timeout = 5000

On each slave:
[mysqld]
rpl_semi_sync_slave_enabled = 1

Sometimes it’ll say invalid parameter then use the below lines instead.

loose-rpl_semi_sync_master_enabled = 1
loose-rpl_semi_sync_slave_enabled = 1

Replication part has been done. Now its time to play with Orchestrator.

Install Orchestrator:

Orchestrator VM’s IP address: 10.142.0.4

wget https://github.com/github/orchestrator/releases/download/v3.0.13/orchestrator_3.0.13_amd64.deb
dpkg -i orchestrator_3.0.13_amd64.deb

It’s installed on /usr/local/orchestrator

Configuring Orchestrator:

We have a sample conf file the orchestrator’s home location. We need to copy that file as the main config file.

cd /usr/local/orchestrator
cp orchestrator-sample.conf.json orchestrator.conf.json

MySQL Backend:

Orchestrator needs a backend database either SQLite or MySQL. I prefer MySQL. And to make sure this will be in HA, we are going to use CloudSQL with Failover. But for this PoC I have installed MySQL on the server where I have Orchestrator.

So install MySQL and create a database and user for orchestrator.

apt-get install -y mysql-server
mysql -u root -p
CREATE DATABASE IF NOT EXISTS orchestrator;
CREATE USER 'orchestrator'@'127.0.0.1' IDENTIFIED BY '0rcP@sss';
GRANT ALL PRIVILEGES ON `orchestrator`.* TO 'orchestrator'@'127.0.0.1';

Orchestrator needs to login on all of your nodes to detect the topology and perform seamless failover and etc. So we need to create a user for Orchestrator on all the servers. Run the below query on mysql-01 it’ll replicate it to all other slaves.

We are using orchestrator in autoscaling, so while creating the user use subnet range for the host.

CREATE USER 'orchestrator'@'10.142.0.%' IDENTIFIED BY '0rcTopology';
GRANT SUPER, PROCESS, REPLICATION SLAVE, RELOAD ON *.* TO 'orchestrator'@'10.142.0.%';
GRANT SELECT ON mysql.slave_master_info TO 'orchestrator'@'10.142.0.%';

Edit the Conf file:

Now we need to make the below changes on /usr/local/orchestrator/orchestrator.conf.json

Orchestrator backend details:

...
"MySQLOrchestratorHost": "127.0.0.1",
"MySQLOrchestratorPort": 3306,
"MySQLOrchestratorDatabase": "orchestrator",
"MySQLOrchestratorUser": "orchestrator",
"MySQLOrchestratorPassword": "0rcP@sss",
...

MySQL Topology User:

"MySQLTopologyUser": "orchestrator",
"MySQLTopologyPassword": "0rcTopology",

Promotion Node filters:

We want to Promote replica-03 when mysql-01 went down. It should not promote any other replica. So we need to tell don’t promote these nodes.

"PromotionIgnoreHostnameFilters": ["replica-01","replica-02","report-01","report-ha"],

Other Parameters for failover:

  "DetachLostSlavesAfterMasterFailover": true,
"ApplyMySQLPromotionAfterMasterFailover": true,
"MasterFailoverDetachSlaveMasterHost": false,
"MasterFailoverLostInstancesDowntimeMinutes": 0,

Then start the Orchestrator.

service orchestrator start

The Web UI will run on port 3000.

To Read about the exact meaning of all parameters read the link.

Add the Topology to Orchestrator:

Open the web UI. In Clusters select Discovery. In IP address provide the mysql-01 IP address and Click submit. You’ll get a notification that its detected.

To view your topology, Click on Clusters -> mysql-01:3306

ProxySQL Setup:

Now we can move to ProxySQL setup. Lets install and configure it.

Install:

wget https://github.com/sysown/proxysql/releases/download/v1.4.12/proxysql_1.4.12-ubuntu16_amd64.deb
dpkg -i proxysql_1.4.12-ubuntu16_amd64.deb
service proxysql start

Connect to ProxySQL:

mysql -h 127.0.0.1 -uadmin -p -P6032 --prompt='ProxySQL> '
Enter Password: admin
--This is default password. you can change it.

Add MySQL servers to proxySQL:

INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (10, '10.142.0.13', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, '10.142.0.16', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, '10.142.0.17', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, '10.142.0.20', 3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Create ProxySQL Monitor User:

ProxySQL needs an user to check read_only flag on all the mysql servers. So we need to create this user on mysql-01 then it’ll replicate to all the servers.

Create user 'monitor'@'10.142.0.%' identified by 'moniP@ss';
Grant REPLICATION CLIENT on *.* to 'monitor'@'10.142.0.%';
Flush privileges;

Update the Proxysql with monitor user’s password.

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='moniP@ss' WHERE variable_name='mysql-monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Add Read/Write host groups:

For us HostGroup ID 10 is writer and 20 is reader.

INSERT INTO mysql_replication_hostgroups VALUES (10,20,'mysql-01');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Once its added, Proxysql will continually check the read_only flag on all the servers and those records will be logged into monitor.mysql_server_read_only_log.

mysql> select hostname, success_time_us, read_only from monitor.mysql_server_read_only_log ORDER BY time_start_us DESC limit 10;
+-------------+-----------------+-----------+
| hostname | success_time_us | read_only |
+-------------+-----------------+-----------+
| 10.142.0.20 | 644 | 1 |
| 10.142.0.17 | 596 | 1 |
| 10.142.0.13 | 468 | 0 |
| 10.142.0.16 | 470 | 1 |
| 10.142.0.20 | 474 | 1 |
| 10.142.0.17 | 486 | 1 |
| 10.142.0.16 | 569 | 1 |
| 10.142.0.13 | 676 | 0 |
| 10.142.0.17 | 463 | 1 |
| 10.142.0.13 | 473 | 0 |
+-------------+-----------------+-----------+

MySQL Users for benchmark test:

Create an user for sysbench test on mysql-01 It’ll replicate the user to all the nodes.

Create user 'sysdba'@'10.142.0.%' identified by 'DB@dmin';
Grant all privileges on *.* to 'sysdba'@'10.142.0.%';
Flush privileges;

But we are connecting the DB via ProxySQL right, then we need to added this user to ProxySQL as well. And this user should connect to Writer host (HostID 10)

INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('sysdba', 'DB@dmin', 10);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Sysbench for HA test:

We are going to use sysbench. To install sysbench, run the below command.

sudo apt-get install sysbench

Create a database for benchmark on mysql-01.

create database sbtest;

Prepare the sysbech database:

Before running the sysbench we need to create tables. Sysbench will do that for us.

sysbench --test=/usr/share/sysbench/oltp_read_write.lua \
--mysql-host=127.0.0.1 \
--mysql-port=6033 \
--mysql-user=sysdba \
--mysql-password=DB@dmin \
prepare
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...

Test HA for mysql-01

Now start inserting the data for 1min and after 10s stop mysql service on mysql-01

sysbench --test=/usr/share/sysbench/oltp_read_write.lua \
--time=60 \
--mysql-host=127.0.0.1 \
--mysql-port=6033 \
--mysql-user=sysdba \
--mysql-password=DB@dmin \
--report-interval=1 \
run
[ 9s ] thds: 1 tps: 89.01 qps: 1780.18 (r/w/o: 1246.13/356.04/178.02) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 1 tps: 17.00 qps: 320.96 (r/w/o: 223.98/63.99/33.00) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 11s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 13s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 14s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 1.00
[ 16s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 17s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 19s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 1 tps: 52.00 qps: 1058.96 (r/w/o: 741.97/211.99/105.00) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[ 21s ] thds: 1 tps: 95.00 qps: 1888.91 (r/w/o: 1322.94/375.98/189.99) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[ 22s ] thds: 1 tps: 95.01 qps: 1900.11 (r/w/o: 1330.08/380.02/190.01) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00

Within 9 seconds it came up. So proxysql detected that there read_only flag changed for mysql-ha Then immediately moved the Hostgroup ID to 10.

mysql> select hostgroup,srv_host,status from stats_mysql_connection_pool;
+-----------+-------------+--------+
| hostgroup | srv_host | status |
+-----------+-------------+--------+
| 10 | 10.142.0.20 | ONLINE |
| 20 | 10.142.0.16 | ONLINE |
| 20 | 10.142.0.17 | ONLINE |
| 20 | 10.142.0.20 | ONLINE |
| 20 | 10.142.0.13 | ONLINE |
+-----------+-------------+--------+

HA for Report-01:

The main master HA part is done. Now we can work on report-01 HA. For this we need to use a VIP (Alias IP). Your application will talk to that VIP.

Im going 10 add the IP 10.142.0.142 to Report-01 node.

VIP for report-01

During the replica-01 downtime, Orchestrator will trigger a hook to remove the Alias IP from the failed node and attach that IP to the Failover node. In our case, we VIP will switch from replica-01 to replica-ha

Create the hook in /opt/report-hook.sh

echo "Removing VIP"
gcloud compute instances network-interfaces update report-01 \
--zone us-east1-b \
--aliases ""
echo "Done"
echo "attaching IP"
gcloud compute instances network-interfaces update report-ha \
--zone us-east1-b \
--aliases "10.142.0.142/32"
echo "Done"

Add this hook to Orchestrator’s conf file under PostIntermediateMasterFailoverProcesses

"PostIntermediateMasterFailoverProcesses": [
"/opt/report-hook.sh >> /tmp/recovery.log",
"echo 'Recovered from {failureType} on {failureCluster}. Failed: {failedHost}:{failedPort}; Successor: {successorHost}:{successorPort}' >> /tmp/recovery.log"
],

Now test the failover.

sysbench \
--test=/usr/share/sysbench/oltp_read_write.lua \
--time=60 \
--mysql-host=10.142.0.142 \
--mysql-user=admin \
--mysql-password=admin \
--report-interval=1 \
--mysql-ignore-errors=all \
run
[ 3s ] thds: 1 tps: 71.00 qps: 1439.08 (r/w/o: 1008.05/288.02/143.01) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 1 tps: 79.00 qps: 1580.00 (r/w/o: 1106.00/316.00/158.00) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 1 tps: 16.00 qps: 309.00 (r/w/o: 217.00/60.00/32.00) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
........
........
[ 30s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 31s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 32s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 33s ] thds: 1 tps: 70.00 qps: 1406.93 (r/w/o: 985.95/279.99/140.99) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 1.00
[ 34s ] thds: 1 tps: 84.00 qps: 1685.98 (r/w/o: 1181.99/336.00/168.00) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 1 tps: 82.01 qps: 1646.19 (r/w/o: 1150.13/332.04/164.02) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00

This time it took 25Sec, but I have reproduced this many times, each time I got different values. But the average value is 40second.

report-ha got the VIP

Conclusion:

Finally we achieved what were thinking. But in this solution is also having few bugs. But its production ready HA solution. In the next part I’ll explain the bugs in this solution and possible workaround for them.