Mastering MySQL High Availability with MHA: Step‑By‑Step Setup Guide

Introduction

MHA (Master High Availability) is a mature solution for MySQL high availability, used by large e‑commerce sites such as Taobao and JD. It can perform failover within 0‑30 seconds, ensuring data consistency and true high availability.

The software consists of two parts: MHA Manager (management node) and MHA Node (data node). The manager can be deployed on a separate machine or on a slave node and periodically probes the cluster; when the master fails it promotes the latest slave to master and redirects other slaves, all transparently to applications.

During automatic failover MHA tries to save binary logs from the failed master; if the master is unreachable, data loss may occur. Combining MHA with MySQL semi‑synchronous replication greatly reduces this risk.

MHA currently supports a one‑master‑multiple‑slaves architecture and requires at least three servers (one master, one standby master, one slave). Some sites have adapted it to a one‑master‑one‑slave setup.

MHA Architecture Diagram

Experimental Setup and Requirements

IP Address Planning

Configuration Requirements

All nodes must be able to communicate via hostnames.

Install mha4mysql-manager and mha4mysql-node on the MHA host.

Manually create configuration directories and files.

Practical MHA Configuration

MySQL Node Configuration

Configure the master (node2) by editing /etc/my.cnf:

innodb_file_per_table = 1
skip_name_resolve = 1
log-bin = master-bin
relay-log = relay-bin
server-id = 1

Start the MySQL service: systemctl start mariadb.service Create a privileged account and a replication account:

grant all on *.* to 'mhauser'@'10.1.10.%' identified by 'cncn';
grant replication slave,replication client on *.* to 'repluser'@'10.1.10.%' identified by 'cncn';
flush privileges;

Configure slave1 (node3) with similar /etc/my.cnf settings, setting server-id = 3, read-only = 1, relay-log-purge = 0, then start the service and point it to the master:

systemctl start mariadb.service
change master to MASTER_HOST='10.1.10.66',MASTER_USER='repluser',MASTER_PASSWORD='cncn',MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=245;
start slave;

Configure slave2 (node4) similarly, using server-id = 4.

Create MHA Configuration Files

Create the configuration directory and file:

mkdir /etc/masterha
vim /etc/masterha/app1.conf

Example app1.conf:

[server default]
user=mhauser
password=cncn
manager_workdir=/data/masterha/app1
manager_log=/data/masterha/app1/manager.log
remote_workdir=/data/masterha/app1
ssh_user=root
repl_user=repluser
repl_password=cncn
ping_interval=1

[server1]
hostname=10.1.10.66
candidate_master=1

[server2]
hostname=10.1.10.67
candidate_master=1

[server3]
hostname=10.1.10.68

Verify Node Communication

Check that each node can ping the others and that health status is OK.

Start MHA Manager

Run the manager (background for production, foreground for debugging): masterha_manager --conf=/etc/masterha/app1.conf Sample output shows configuration loading and possible error messages if files are missing.

Conclusion

By simulating node failures you can verify that the master is promoted correctly and new nodes join the cluster, confirming that MHA dramatically reduces mean time to failure and improves database availability. For highly sensitive data, manual recovery is often safer than fully automated scripts.