Understanding MHA Failover

MHA (Master High Availability) is a comprehensive solution for achieving high avaliability in MySQL environments. Here's how it works:

Master Monitoring:

• MHA uses masterha_manager to start the monitoring process
• Pre-checks include SSH connectivity validation and replication health verification
• The master node is monitored every ping_interval seconds
• If no response is received after 3 consecutive checks, MHA triggers failover

Master Selection Algorithms:

1. Check for forced master selection parameters
2. Assess replication lag across all slaves
3. Follow the order defined in the configuration file

Key Configuration Parameters:

• ping_interval=1 - Frequency of master checks
• priority=1 - Designates a prefered master candidate
• ignore_replication_lag=1 - Override replication lag checks

Failover Process:

1. Master node detection
2. Slave selection based on replication status
3. Data synchronization
4. Slave promotion
5. Replication reconfiguration
6. Application transparency

Setting Up MHA

1. Verify MHA User Access:

mysql -uadmin -padmin -h192.168.1.100 -e "SHOW VARIABLES LIKE 'server_id'"
mysql -uadmin -padmin -h192.168.1.101 -e "SHOW VARIABLES LIKE 'server_id'"
mysql -uadmin -padmin -h192.168.1.102 -e "SHOW VARIABLES LIKE 'server_id'"

2. Configrue VIP Failover:

VIP Address: 192.168.1.200/24
VIP Interface: eth0:1
VIP Management Script:
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';

# Main script logic
exit &main();

sub main {
    # Command handling logic
}

sub start_vip() {
    # Start VIP on new master
}

sub stop_vip() {
    # Stop VIP on old master
}

3. Implement Email Notifications:

# Configure email report script
report_script=/usr/local/bin/send_email

Binlog Server Setup

1. Configuration Requirements:

• MySQL 5.6+ version
• GTID replication enabled
• Binary logs stored in a shared directory

2. Configure Binlog Server:

[binlog1]
no_master=1
hostname=192.168.1.102
master_binlog_dir=/var/log/mysql/binlog

Testing MHA Failover

Simulate Master Failure:

# Stop master node
systemctl stop mysqld

Restore Master Node:

# Start replication on recovered node
CHANGE MASTER TO 
MASTER_HOST='192.168.1.101',
MASTER_PORT=3306, 
MASTER_AUTO_POSITION=1, 
MASTER_USER='repl', 
MASTER_PASSWORD='securepass';
START SLAVE;

Reconfigure MHA:

# Update MHA configuration
[server1]
hostname=192.168.1.100
port=3306
[server2]
hostname=192.168.1.101
port=3306
[server3]
hostname=192.168.1.102
port=3306

Restart MHA:

# Stop existing MHA instance
masterha_stop --conf=/etc/mha/app1.cnf

# Start new MHA instance
nohup masterha_manager --conf=/etc/mha/app1.cnf --ignore_last_failover > /var/log/mha/app1/manager.log 2>&1 &

Additional Notes

• Ensure proper file permissions: chmod +x /usr/local/bin/master_ip_failover
• Convert scripts to Unix format: dos2unix /usr/local/bin/master_ip_failover
• Monitor failover status: masterha_check_status --conf=/etc/mha/app1.cnf