Deploying MHA for MySQL High Availability – Part 1

This article explains how to deploy MHA (Master High Availability) on a MySQL cluster to guarantee that data is not lost during master failover.

1. Install required packages on every server

[root@master ~]# yum install -y perl-DBD-MySQL ncftp perl-DBI

2. Install additional Perl modules on the slave1, slave2 and mha4 nodes

[root@slave1 yum.repos.d]# yum install -y perl-Config-Tiny.noarch perl-Time-HiRes.x86_64 perl-Parallel-ForkManager perl-Log-Dispatch.noarch

3. Install the MHA node package on all four servers

[root@mha ~]# scp -r mha4mysql-node-0.58-0.el7.centos.noarch.rpm master:/root

[root@mha ~]# scp -r mha4mysql-node-0.58-0.el7.centos.noarch.rpm slave1:/root

[root@mha ~]# scp -r mha4mysql-node-0.58-0.el7.centos.noarch.rpm slave2:/root

4. Install the MHA manager package on the master node

[root@mha ~]# rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

5. Create a MySQL user for MHA and grant privileges

mysql> create user dba_mha@'192.168.236.%' identified by '123456';

mysql> grant all privileges on *.* to dba_mha@'192.168.236.%';

6. Edit the MHA configuration file and test SSH connections

[root@mha mha]# vim mha_mysql.conf

[root@mha mha]# masterha_check_ssh --conf=/etc/mha/mha_mysql.conf

When the output contains All SSH connection tests passed successfully , the configuration is correct.

7. Verify master‑slave replication settings

[root@mha mha]# chmod +x /usr/bin/master_ip_failover

[root@mha mha]# masterha_check_repl --conf=/etc/mha/mha_mysql.conf

8. Review the failover script ( /usr/bin/master_ip_failover )

#!/usr/bin/env perl use strict; use warnings FATAL => 'all'; use Getopt::Long; my ($command, $orig_master_host, $orig_master_ip,$ssh_user, $orig_master_port, $new_master_host, $new_master_ip,$new_master_port, $orig_master_ssh_port,$new_master_ssh_port,$new_master_user,$new_master_password); my $vip = '192.168.236.191/24'; my $key = '1'; my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip"; my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down"; my $ssh_Bcast_arp = "/sbin/arping -I eth0 -c 3 -A 192.168.236.191"; GetOptions( 'command=s' => \$command, 'ssh_user=s' => \$ssh_user, 'orig_master_host=s' => \$orig_master_host, 'orig_master_ip=s' => \$orig_master_ip, 'orig_master_port=i' => \$orig_master_port, 'orig_master_ssh_port=i' => \$orig_master_ssh_port, 'new_master_host=s' => \$new_master_host, 'new_master_ip=s' => \$new_master_ip, 'new_master_port=i' => \$new_master_port, 'new_master_ssh_port'=> \$new_master_ssh_port, 'new_master_user' => \$new_master_user, 'new_master_password'=> \$new_master_password ); exit &main(); sub main { $ssh_user = $ssh_user // 'root'; if ($command eq 'stop' || $command eq 'stopssh') { eval { print "Disabling the VIP on old master: $orig_master_host \n"; &stop_vip(); }; return $@ ? (warn "Got Error: $@\n", 1) : 0; } elsif ($command eq 'start') { eval { print "Enabling the VIP - $vip on the new master - $new_master_host \n"; &start_vip(); &start_arp(); }; return $@ ? (warn $@, 1) : 0; } elsif ($command eq 'status') { print "Checking the Status of the script.. OK \n"; return 0; } else { &usage(); return 1; } } sub start_vip { `ssh $ssh_user\@$new_master_host " $ssh_start_vip "`; } sub stop_vip { `ssh $ssh_user\@$orig_master_host " $ssh_stop_vip "`; } sub start_arp { `ssh $ssh_user\@$new_master_host " $ssh_Bcast_arp "`; } sub usage { print "Usage: master_ip_failover --command=start|stop|stopssh|status --ssh_user=user --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n"; }

The script accepts commands start , stop , stopssh and status , manipulates the virtual IP on the old and new masters via SSH, and updates ARP tables.

9. Verify the slave hosts in MySQL

mysql> show slave hosts;

Typical output shows the server IDs, host, port, master ID and UUID of each slave.