Mastering MySQL High Availability: A Step‑by‑Step MHA Setup Guide

Environment Preparation

Four servers are required: a manager node (192.168.12.223) and three MySQL nodes – one master (192.168.12.218) and two slaves (192.168.12.219, 192.168.12.220). Each node must have MySQL installed and network connectivity.

Configure Password‑less SSH

Run the following commands on each server (example shown for the manager node):

echo -e "
" | ssh-keygen -t dsa -N ""
ssh-copy-id -i .ssh/id_dsa.pub [email protected]
ssh-copy-id -i .ssh/id_dsa.pub [email protected]
ssh-copy-id -i .ssh/id_dsa.pub [email protected]

MySQL Master‑Slave Setup

Create a replication user on all three nodes:

mysql> grant replication slave on *.* to 'repl'@'192.168.12.%' identified by '123456';
mysql> flush privileges;

Configure /etc/my.cnf on the master:

# server_id must be unique
server_id = 1
gtid-mode=ON
log_bin = master-bin
log_bin_index = master-bin.index
relay-log = slave-relay-bin
relay-log-purge = 0

Configure /etc/my.cnf on each slave (server_id 2 and 3):

# server_id must be unique
server_id = 2
gtid-mode=ON
log_bin = master-bin
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index
relay-log-purge = 0
read_only = 1

Explain GTID basics (global unique transaction ID, replaces binlog+position, requires master_auto_position=1, supported from MySQL 5.6).

On the master, obtain the current binlog file and position: show master status; On each slave, set up replication:

CHANGE MASTER TO MASTER_HOST='192.168.12.218', MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=407;

Verify slave status with show slave status \\G.

MHA Installation and Configuration

Install mha4mysql-node on every MySQL server:

yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

Install mha4mysql-manager on the manager node:

yum install -y perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager
rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

Create the MHA configuration directory and file:

mkdir /etc/mha_master
vi /etc/mha_master/mha.cnf

Sample mha.cnf (relevant sections only):

[server default]
user=root
password=123456
repl_user=repl
repl_password=123456
ssh_user=root
ping_interval=1
ping_type=SELECT
manager_log=/data/mha/mha/app1/manager.log
manager_workdir=/data/mha/mha/app1
remote_workdir=/data/mha/mha/tmp
master_binlog_dir="/usr/local/mysql/data"
master_ip_failover_script="/data/mha/scripts/master_ip_failover"
master_ip_online_change_script="/data/mha/scripts/master_ip_online_change"
report_script="/data/mha/scripts/send_report"

[server1]
candidate_master=1
hostname=10.34.24.156
port=3306

[server2]
candidate_master=1
hostname=10.34.24.157
port=3306

[server3]
hostname=10.36.224.110
port=3306

Provide sample Perl scripts for failover, online‑change, and reporting (scripts are placed under /data/mha/scripts/ and must be executable).

Testing and Validation

Test SSH connectivity from the manager: masterha_check_ssh -conf=/etc/mha_manager/mha.cnf Test replication health: masterha_check_repl --conf=/etc/mha_manager/mha.cnf Start the MHA manager (run in background):

nohup masterha_manager --conf=/etc/mha_manager/mha.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /data/mha/mha/app1/manager.log 2>&1 &

Stop the manager when needed: masterha_stop --conf=/etc/mha_manager/mha.cnf Check manager status: masterha_check_status --conf=/etc/mha_manager/mha.cnf Simulate a master failure (stop MySQL on the current master) and observe automatic failover in the manager log. Verify the new master is writable and slaves are synchronized.

Key Advantages of MHA

Fast failover (typically 10‑30 seconds) when slaves are up‑to‑date.

No data loss when combined with semi‑synchronous replication.

Works with native MySQL versions from 5.0 onward; no need to modify existing MySQL configuration.

Requires only one additional server for the manager; nodes themselves act as MHA nodes.

Negligible performance impact (periodic ping checks only).

Compatible with any storage engine, not limited to InnoDB.

Supports GTID‑based replication from MHA 0.56 onward.