MySQL High Availability: Replication, Monitoring, and Failover Strategies

Replication

To ensure MySQL data is not lost, replication is a good solution, and MySQL provides a powerful replication mechanism. However, for performance reasons, replication uses an asynchronous mode, meaning written data is not synchronously updated to the slave; if the master fails, data loss may still occur.

Semi‑synchronous replication can mitigate this risk. Its principle is simple: after the master processes a transaction, it waits for at least one semi‑synchronous slave to acknowledge that it has received the event and written it to its relay‑log before returning. Thus, even if the master crashes, at least one slave has the complete data.

Although semi‑synchronous replication does not guarantee 100 % data safety—if the master crashes while sending the transaction to the slave, loss is still possible—it greatly improves data security compared with traditional asynchronous replication and does not incur a noticeable performance penalty. The authors of MHA report using it in Facebook production, and MySQL 5.7 introduced loss‑less semi‑synchronous replication, making data loss probability very low.

If absolute data safety is required, a solution such as Galera can be considered; it writes three copies of each transaction to guarantee no loss. The author has no direct experience with Galera but notes it is used in production, though it is more invasive to MySQL code.

Another option is DRBD for MySQL data replication, described in the official MySQL documentation, though the author did not adopt it due to reported issues.

In upcoming projects the author will prioritize semi‑synchronous replication and consider Galera only for extremely critical data.

Monitor

Replication alone does not tell us quickly when the master fails; a monitoring system is essential.

When the master goes down, a monitor can detect the failure promptly and trigger actions such as email alerts or notifying a daemon to perform failover.

Common monitoring approaches use keepalived or heartbeat to switch to a standby node, but they cannot detect service unavailability instantly. The author's current setup uses keepalived, while future plans favor Zookeeper for comprehensive MySQL cluster monitoring and failover.

Each MySQL instance runs an agent on the same host that periodically pings the MySQL server to check its health and registers itself as an ephemeral node in Zookeeper. This allows Zookeeper to detect three situations:

The host crashes, causing both MySQL and the agent to stop; the agent’s Zookeeper session naturally expires.

MySQL crashes; the agent detects a failed ping and voluntarily disconnects from Zookeeper.

The agent crashes while MySQL remains running.

All three cases indicate a problem with the MySQL host, and Zookeeper can react immediately. When the agent disconnects, Zookeeper fires a children‑changed event, which a control service can listen to and perform automatic failover for the first two cases, while the third case may rely on cron or supervisord to restart the agent.

Zookeeper’s advantage is its ability to monitor the whole cluster, obtain real‑time topology changes, and trigger notifications to interested services, something that keepalived or heartbeat cannot achieve easily.

The downside is Zookeeper’s deployment complexity and the challenge of updating application connection strings after a failover.

Deployment is simplified by using Docker to run one MySQL with its own agent. Changing the database address after failover can be handled by dynamic configuration updates, virtual IPs, or a proxy.

If the system is simple (one master, one slave), keepalived may be sufficient and Zookeeper unnecessary.

Failover

With monitoring in place, failover can be performed by selecting the most up‑to‑date slave as the new master. The show slave status command provides two key fields— Master_Log_File and Read_Master_Log_Pos —that indicate how far each slave has replicated the master’s binlog. The slave with the highest file and position becomes the new master.

After promoting a slave (e.g., b) to master, the remaining slave (c) must be re‑pointed to the new master using CHANGE MASTER TO , specifying the appropriate binlog file and position.

GTID

MySQL 5.6 introduced Global Transaction IDs (GTID) in the form uuid:gid , where the UUID uniquely identifies the server and the GID is an incrementing transaction ID. GTID allows failover to be performed by locating the exact transaction in the new master’s binlog.

For example, if slave b has the last GTID 3E11FA47-71CA-11E1-9E33-C80AA9429562:23 and slave c has ...:15 , c can start replication from the event following GTID ...:15 in b’s binlog.

Google’s early Global Transaction ID patch used a simple incrementing integer, which inspired LedisDB’s failover implementation. MariaDB’s GTID differs from MySQL’s, requiring separate handling in the author’s go‑mysql toolset.

Pseudo GTID

Because many deployments still run MySQL 5.5, which lacks GTID, a pseudo‑GTID approach can be used. The author discovered the open‑source project Orchestrator, which creates a pseudo‑GTID by periodically inserting a UUID into a view via a scheduled event:

create database if not exists meta; drop event if exists meta.create_pseudo_gtid_view_event; delimiter ;; create event if not exists meta.create_pseudo_gtid_view_event on schedule every 10 second starts current_timestamp on completion preserve enable do begin set @pseudo_gtid := uuid(); set @_create_statement := concat('create or replace view meta.pseudo_gtid_view as select ''', @pseudo_gtid, ''' as pseudo_gtid_unique_val from dual'); PREPARE st FROM @_create_statement; EXECUTE st; DEALLOCATE PREPARE st; end;; delimiter ; set global event_scheduler := 1;

This event writes a UUID to a view every 10 seconds, which is recorded in the binlog. Although it does not allow direct event lookup like GTID, it narrows the search window to a small interval, enabling comparison of two MySQL binlogs.

During failover, the position of the last UUID on the old slave (s1) is located in the new master (s2); subsequent events are compared until divergence is detected, after which the appropriate binlog file and position are determined for the remaining slave.

Pseudo‑GTID requires the slave to enable log-slave-update , a setting also needed for GTID, making it an acceptable trade‑off.

The author plans to implement a failover tool based on this pseudo‑GTID method.

Another approach described in the book “MySQL High Availability” stores a GTID in a table on each commit, but it requires client support and was not adopted.

Postscript

MySQL HA is a deep field; the author has listed only some recent research topics, many of which will be implemented in the go‑mysql project.

Update

After further study, the author’s HA design now fully embraces GTID, abandoning support for pre‑5.6 MySQL versions. The project has upgraded all MySQL instances to 5.6, enabling GTID.

Unlike Facebook’s modification of mysqlbinlog to support semi‑synchronous replication, the author extended the go‑mysql replication library to handle semi‑synchronous replication, allowing real‑time binlog forwarding to a single node. Traditional slaves continue to use native replication, while MHA monitors the cluster and performs failover.

The author now prefers using MHA, a proven Perl‑based tool, over writing a custom Zookeeper solution, and plans to develop a dedicated agent instead of a full Zookeeper deployment.

However, the HA solution has not been deployed because the author is planning to leave the company soon, and implementing it without a maintainer would be risky.

We are all architects!

Follow the "Architect" subscription account for more technical sharing.

Editor’s personal WeChat: 13511421494

Work email: [email protected]

Join the WeChat group by contacting the editor.

Architect technical exchange QQ group: