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

Bhuvanesh
Bhuvanesh
Nov 19, 2018 · 10 min read

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

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

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

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;

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

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

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

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';

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

Connect to ProxySQL:

mysql -h 127.0.0.1 -uadmin -p -P6032 --prompt='ProxySQL> '
Enter Password: admin

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);

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';

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);

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

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

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"

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

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.

Searce Engineering

We identify better ways of doing things!

Bhuvanesh

Written by

Bhuvanesh

Cloud | BigData | Database Architect | blogger thedataguy.in

Searce Engineering

We identify better ways of doing things!

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade