At Citymobil, we use a MySQL database as the primary storage for persistent data. We have several database clusters for various services and purposes.

The constant availability of the master is a critical indicator of the overall system and its individual components' performance. Automatic recovery of the cluster in case of master failure significantly reduces incident response time and system downtime. In this article, I will discuss the high availability (HA) scheme for a MySQL cluster based on MySQL Orchestrator and virtual IP addresses (VIP).

HA solution based on VIP

First, I will briefly explain what our data storage system looks like.

We use a classic replication scheme with one writeable master and multiple replicas that are used only for reading. The cluster can contain an intermediate master— a node that is both a replica and a master for others. Clients interact with the replicas through HAProxy, which allows for even load distribution and easy scaling. The use of HAProxy is due to historical reasons, and we are currently in the process of migrating to ProxySQL.

Replication is performed in semi-synchronous mode based on GTID. This means that at least one replica must write the transaction to the log before it is considered successful. This mode of replication provides an optimal balance between performance and data integrity in case of the primary node's failure. Mainly, all changes are transferred from the master to the replicas using Row Based Replication (RBR), but some nodes may have mixed binlog format.

The orchestrator periodically updates the cluster topology state, analyzes the received information, and can initiate the automatic recovery procedure in case of problems. The procedure itself is the responsibility of the developer, as it can be implemented in various ways: based on VIP, DNS, using service discovery services, or bespoke mechanisms.

One simple way to restore the master in case of its failure is to use floating VIP addresses.

What you need to know about this solution before moving forward:

A VIP is an IP address that is not bound to a specific physical network interface. In the event of a node failure or during scheduled maintenance, we can switch the VIP to another resource with minimal downtime.
Releasing and issuing a virtual IP address are cheap and quick operations.
To work with VIP, SSH access to the server is required, or special utilities must be used, for example, keepalived.

Let's consider potential issues with our master and illustrate how the automatic recovery mechanism should operate.

There is no network connectivity to the master, or there is a hardware-level problem, and the server is inaccessible.

The orchestrator updates the cluster topology, each replica reports the master is unavailable. The orchestrator initiates the process of electing a replica suitable for the role of a new master and begins the recovery.
We attempt to release the VIP from the old master—without success.
The replica switches to the role of master. The topology is reconfigured.
We add a new network interface with the VIP. Since we couldn't release the VIP, we start periodically sending requests in the background. gratuitous ARP. This type of request/response allows us to update the IP-MAC address mapping table on connected switches, thereby notifying them of our VIP’s relocation. This minimizes the likelihood of a split brain when reverting to the old master.
All new connections are immediately redirected to the new master. Old connections fail, leading to re-attempts to the database at the application level.

The server operates normally, with a failure at the DBMS level.

The algorithm is similar to the previous case: the topology is updated and the recovery process is initiated. As the server is accessible, we successfully release the VIP on the old master, transfer it to the new one, and send several ARP requests. A possible return of the old master should not affect the rebuilt cluster or the operation of the application.

Other issues

Failures of replicas or intermediate masters do not lead to automatic actions and require manual intervention.

The virtual network interface is always added temporarily, meaning that after a server reboot, the VIP is not automatically assigned. Each database instance starts in read-only mode by default, and the orchestrator automatically switches the new master to write and attempts to establish read only on the old master. These actions are aimed at reducing the likelihood of split brain.

During the recovery process, issues may arise that should also be reported through the orchestrator's UI in addition to standard monitoring tools. We have expanded the REST API to include this capability (PR is currently under review).

The overall scheme of the HA solution is presented below.

Choosing a new master

The orchestrator is smart enough and tries to choose the most suitable replica as the new master based on the following criteria:

the replica's lag behind the master;
the MySQL version of the master and the replica;
the type of replication (RBR, SBR, or mixed);
the location in the same or different data centers;
the presence of errant GTID — transactions that have been executed on the replica but are missing on the master;
custom selection rules are also considered.

Not every replica is an ideal candidate for the master role. For example, a replica may be used for data backup, or the server may have a weaker hardware configuration. The orchestrator the properties manual rules can be used to customize your preferences for selecting candidates from the most preferred to ignored.

Response and recovery time

In the event of an incident, it is important to minimize system downtime, so let's consider MySQL parameters that affect the orchestration of cluster topology building and updating:

slave_net_timeout — the number of seconds the replica waits for new data or a heartbeat signal from the master before the connection is considered lost and reconnection is performed. The smaller the value, the faster the replica can determine that the connection with the master has been disrupted. We set this value to 5 seconds.
MASTER_CONNECT_RETRY — the number of seconds between reconnection attempts. In case of network issues, a low value for this parameter will allow for quick reconnections and prevent the cluster recovery process from starting. The recommended value is 1 second.
MASTER_RETRY_COUNT — the maximum number of reconnection attempts.
MASTER_HEARTBEAT_PERIOD — the interval in seconds after which the master sends a heartbeat signal. By default, this is equal to half the value of slave_net_timeout.

Orchestrator parameters:

DelayMasterPromotionIfSQLThreadNotUpToDate — if set to true, the master role will not be applied to the candidate replica until the SQL thread of the replica has executed all pending transactions from the Relay Log. We use this option to avoid losing transactions in situations where all candidate replicas are lagging behind.
InstancePollSeconds — the frequency of building and updating the topology.
RecoveryPollSeconds — the frequency of topology analysis. If an issue is detected, topology recovery is triggered. This is a constant, equal to 1 second.

Each cluster node is polled by the orchestrator once every InstancePollSeconds seconds. When a problem is detected, the cluster state is forcibly updated, and then a final decision is made about performing recovery. By experimenting with various database and orchestrator parameters, we managed to reduce the response and recovery time to 30 seconds.

Test Stand

We began testing the HA scheme by developing a local test environment and further implementing it into test and production environments. The local setup is fully automated based on Docker and allows for experimentation with the orchestrator and network configuration, scaling the cluster from 2-3 servers to several dozen, and conducting drills in a safe environment.

During the drills, we choose one of the problem emulation methods: instantly kill the master using kill -9, gracefully terminate the process and stop the server (docker-compose stop), simulate network problems using iptables -j REJECT or iptables -j DROP.We expect the following results:

the orchestrator will detect problems with the master and update the topology in no more than 10 seconds;
the recovery procedure will initiate automatically: the network configuration will change, the master role will switch to the replica, and the topology will be rebuilt;
The new master will be available for writing, and live replicas will not be lost during the rebuild process;
data will start being written to the new master and replicated;
the total recovery time will not exceed 30 seconds.

As you know, the system can behave differently in test and production environments due to variations in hardware and network configuration, differences in synthetic and real load, etc. Therefore, we periodically conduct exercises in real conditions to check how the system behaves during network connectivity loss or degradation of individual parts. In the future, we want to build a fully identical infrastructure for both environments and automate its testing.

Conclusions

The operability of the main node of the storage system is one of the primary tasks of the SRE and operations team. Implementing an orchestrator and HA solution based on a VIP has yielded the following results:

reliable detection of issues with the database cluster topology;
automated and rapid response to incidents related to the master, which reduces system downtime.

However, the solution has its limitations and drawbacks:

scaling the HA scheme across multiple data centers will require a unified L2 network between them;
before assigning the VIP on the new master, we need to free it on the old one. The process is sequential, which increases recovery time;
releasing the VIP requires SSH access to the server, or some other method of invoking remote procedures. Since the server or database is experiencing issues that initiated the recovery process, we cannot be sure that releasing the VIP will be successful. This could lead to two servers having the same virtual IP address and resulting problems. split brain.

To avoid split brain, one can use the method STONITH ('Shoot The Other Node In The Head'), which completely isolates or disconnects the problematic node. There are other ways to implement high availability for a cluster: a combination of VIP and DNS, service discovery and proxy services, synchronous replication, and other methods that have their own drawbacks and advantages.

I spoke about our approach to creating a fault-tolerant MySQL cluster. It is easy to implement and provides an acceptable level of reliability under current conditions. As the entire system and infrastructure develop, this approach will undoubtedly evolve.

Source: habr.com