Mastering MySQL HA: Step‑by‑Step MHA Setup and Failover Guide

1. Environment Preparation

Four servers are used: manager (192.168.12.223) runs the MHA controller, master (192.168.12.218) is the MySQL primary, candidate master (192.168.12.219) and slave (192.168.12.220) act as replicas.

2. Configure Password‑less SSH

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]

3. MySQL Master‑Slave Setup

Create 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 for 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 for each slave (server_id 2, 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 (global transaction ID) concepts and workflow.

Show master status and change master commands:

show master status;

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;

4. Install MHA Nodes

Install required Perl DBI package and the mha4mysql-node RPM on every MySQL server.

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

5. Define MHA Configuration

[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"
secondary_check_script="masterha_secondary_check -s 10.34.24.157 -s 10.36.224.110"

[server1]
candidate_master=1
client_bindir=/usr/local/mysql/bin/
client_libdir=/usr/local/mysql/lib/
hostname=10.34.24.156
port=3306

[server2]
candidate_master=1
client_bindir=/usr/local/mysql/bin/
client_libdir=/usr/local/mysql/lib/
hostname=10.34.24.157
port=3306

[server3]
client_bindir=/usr/local/mysql/bin/
client_libdir=/usr/local/mysql/lib/
hostname=10.36.224.110
port=3306

6. Sample Scripts

Perl scripts for failover, online change, and reporting are provided (e.g., master_ip_failover, master_ip_online_change, report_script). They handle VIP activation/deactivation and send email notifications.

7. Test SSH Connectivity and Replication

masterha_check_ssh -conf=/etc/mha_manager/mha.cnf

masterha_check_repl --conf=/etc/mha_manager/mha.cnf

8. Run MHA Manager

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

Stop and status commands:

masterha_stop --conf=/etc/mha_manager/mha.cnf
masterha_check_status --conf=/etc/mha_manager/mha.cnf

9. Failover Verification

Stop the original master ( service mysqld stop) and observe manager logs confirming automatic promotion of 10.34.24.157 to master. Verify new master status with show slave status \\G and confirm read_only is OFF.

10. Advantages

Fast failover (typically 10‑30 seconds).

Data consistency when combined with GTID and semi‑synchronous replication.

No changes required to existing MySQL configuration.

Only one additional server (the MHA manager) is needed.

Negligible performance impact; MHA pings every few seconds.

Works with any MySQL storage engine (InnoDB, MyISAM, etc.).

MHA 0.56 adds native GTID replication support.