Official Mafiree Blogs

        In a typical production environment, setting up high availability is one of the most crucial tasks. Having said that most of the setups do not have a HA solution. This is because a traditional MySQL setup doesn’t have an inbuilt failover solution. Luckily we have few options to go for HA.
 

HA based on synchronous replication

    Cluster based solution would always provide HA. MySQL Cluster, Galera Cluster, InnoDB Cluster compromises of 2 or more servers (nodes) which can act as a HA. There are setups where implementing cluster would bring a lot of issues like deadlocks, increased transaction time as data volume goes up. These setup would have higher transactions flowing between the nodes which would cause a negative performance impact . 
 

HA based on asynchronous replication

    For a traditional MySQL setups one can use a replication manager / load balancer with failover support and achieve high availability. MHA, orchestrator are some of the replication managers. Orchestrator has lot of support and advanced features comparted to MHA.

In this blog, let us see how we have achieved HA using Orchestrator and ProxySQL

Background of the setup

• 3 MySQL servers, 1 master and 2 replicas

• 3 servers running with Orchestrator/raft  and ProxySQL
   

How orchestrator works?

Orchestrator has three different phases namely, 

• Discovery

• Refactoring

• Recovery

Discovery phase of orchestrator detects the topology and understands the configuration of backend servers. It reads the replication status, configuration and understands the topology.

Refactoring phase will take care of replication rules, it will understand the binary log ordinates, GTID position and it initiates a recovery process.

Recovery phase of orchestrator will pick the ideal candidate as a master and will apply the promotion rules such as disabling read_only mode and repointing the available replica’s beneath the newly promoted master server.  

Broadcasting the topology changes

    Orchestrator can successfully detect the master failure and promoted a new master. Now application has to identify the changes and send the traffic to the current master. We have few options to notify the application about the changes.

1. By using VIP we can detach the machine from the old master and attach to the new master.
2. By using a Load balancer like ProxySQL.

Problem with VIP’s are bound to be plenty, we are not covering about this in the blog. So we have chosen another approach to use the Load balancer to broadcast the changes.

Orchestrator have lot of hooks which can help the DBA’s to execute sequentials commands whenever there are topology changes. Most importantly couple of hooks we can play with is to broadcast the changes.
 

Prefailover  - This hook will be initiated before the failover which will put the current master server to OFFLINE_SOFT thus avoid sending the new connection.

UPDATE MYSQL_SERVERS SET STATUS=’OFFLINE_SOFT’ WHERE IP=$ORC_FAILED_HOST

Postfailover  - This hook will be initiated after the failover which would remove the current master from the ProxySQL config and insert the new master as a writer group

DELETE FROM MYSQL_SERVERS WHERE HOSTNAME=$ORC_FAILED_HOST

INSERT INTO MYSQL_SERVERS(HOSTGROUP_ID,HOSTNAME,PORT,STATUS) VALUES (10, "'"$ORC_FAILED_HOST"'", '$ORC_SUCCESSOR_PORT', "ONLINE");
 

Note : ORC_SUCCESSOR_PORT and ORC_FAILED_HOST are variables exported by orchestrator.

We had simulated the test by running sysbench and forcefully killed the master node. As expected orchestrator detects the topology change and it updates the load balancer about the new master. 

With our configured hooks, all these topology changes are  updated by ProxySQL and application continues to connect the database without any issues.