Mastering MySQL HA: How MHA Automates Failover and Online Master Switching

Overview

MHA provides automated master monitoring and failover, promoting a slave to a new master within 10–30 seconds without load or configuration changes.

Key Features

Automatic failover with data consistency ensured by relay‑log differences.

Online master switch (0.5–2 s write block) for routine maintenance.

Supports manual interactive, non‑interactive, and scheduled master switches.

Extensible via custom scripts such as secondary_check_script, master_ip_failover_script, shutdown_script, report_script, and others.

Failover Modes

Interactive manual failover requires user confirmation; non‑interactive mode runs automatically; online switch changes master while it stays alive, useful for planned maintenance.

Other HA Solutions

Manual master‑slave repair, single‑master‑single‑slave limitations, multi‑slave scaling challenges, semi‑synchronous replication, GTID‑based HA, and official MySQL failover tools (with limitations) are discussed.

MHA Architecture

MHA consists of a Manager that monitors the master and Nodes that execute failover scripts such as parsing binary/relay logs and applying differences.

Advantages

Fast master failover and slave promotion.

No data inconsistency after master crash.

No changes to replication configuration required.

Minimal additional hardware needed.

No performance impact on running servers.

Works with any storage engine.

Workflow

Validate replication settings and identify the current master.

Monitor the master until a failure is detected.

Detect failure after three consecutive connection attempts.

Optional secondary check script for double verification.

Re‑verify slave configurations; stop if inconsistencies are found.

Optionally shut down the dead master (using master_ip_failover_script or shutdown_script).

Promote a new master, applying binary‑log differences from the crashed master.

Make the new master writable (reset read‑only flag, create users if needed).

Re‑point other slaves to the new master and start replication in parallel.

Optional notification scripts (report_script) to send emails or adjust backup jobs.

Important Commands

Examples of commonly used MHA commands:

# masterha_manager --conf=/etc/conf/masterha/app1.cnf

# masterha_master_switch --master_state=dead --conf=/etc/app1.cnf --dead_master_host=host1

# masterha_master_switch --master_state=dead --conf=/etc/conf/masterha/app1.cnf --dead_master_host=host1 --new_master_host=host2 --interactive=0

# masterha_master_switch --master_state=alive --conf=/etc/app1.cnf --new_master_host=host2

# masterha_check_status --conf=/path/to/app1.cnf

# masterha_check_repl --conf=/etc/app1.cnf

# masterha_stop --conf=/etc/app1.cnf

# masterha_conf_host --command=add --conf=/etc/conf/masterha/app1.cnf --hostname=db101

# masterha_conf_host --command=delete --conf=/etc/conf/masterha/app1.cnf --block=server100

Command Parameters

--conf: full path to configuration file.

--global_conf: path to global configuration file.

--manager_workdir / --workdir: directory for manager state files.

--manager_log / --log_output: log destination.

--wait_on_monitor_error: seconds to wait before exiting on monitor error.

--ignore_fail_on_start: continue monitoring even if some slaves are down.

--last_failover_minute: minimum interval between failovers.

--ignore_last_failover: ignore the interval restriction.

--remove_dead_master_conf: delete dead master entries from config after failover.

--new_master_host: specify the host to become the new master.

--interactive: 1 for interactive (default), 0 for non‑interactive.

--skip_change_master: skip CHANGE MASTER step.

--skip_disable_read_only: keep new master in read‑only mode.

--running_updates_limit: max allowed write latency on master.

--skip_lock_all_tables: avoid FLUSH TABLES WITH READ LOCK during switch.

Additional Utilities

masterha_check_ssh verifies SSH trust between all nodes; purge_relay_logs safely removes old relay logs with optional user/password parameters and can be scheduled via cron.