Mastering MySQL High Availability: MHA Failover and MaxScale Load Balancing

MySQL Failover Options

Common failover methods include the MMM approach, which has a single monitor node and risks split‑brain and data loss, and MHA (MySQL High Availability), which provides:

Capture of binary log events from a crashed master.

Identification of the most up‑to‑date slave.

Application of relay logs to other slaves.

Promotion of a slave to master.

Reconfiguration of remaining slaves to replicate from the new master.

Support for MariaDB Replication Manager (MRM) in GTID‑based topologies.

MHA Architecture and Installation

MHA consists of a manager node and multiple data nodes.

Install the package on each node: yum install -y --nogpgcheck MHA4MySQL-* Configure password‑less SSH (e.g., with Ansible).

Edit the MHA configuration files to define master/slave hosts, SSH options, and failover scripts.

Disable relay_log_purge on slaves so MHA can use relay logs for consistency checks.

Key configuration flags: --ignore_last_failover in masterha_manager to avoid repeated failures.

Enable ARP pinging to prevent VIP loss during failover. ignore_fail=1 to force failover even if a slave reports errors.

Adjust SSH timeout to avoid delays.

Testing MHA Failover

Simulate failover by stopping MySQL on the master ( /etc/init.d/mysqld stop) or crashing the host ( echo a > /proc/sysrq-trigger). Network partitions can be tested with iptables rules. Use pt‑heartbeat and pt‑table‑checksum to verify replication health.

MaxScale Overview

MaxScale is a SQL‑aware load balancer offering weighted read/write splitting, SQL firewall, multiple routing strategies, automatic master‑failover detection (when combined with MHA), latency monitoring, and multi‑tenant sharding. It avoids the long‑connection timeout issues of traditional LVS setups.

Deploying MaxScale

Install: yum -y install maxscale Copy the template configuration: cp MaxScale_template.cnf maxscale.cnf Generate passwords: maxkeys /var/lib/maxscale/ and maxpasswd /var/lib/maxscale/ YOUR_PASSWORD Adjust maxscale.cnf as needed for backend servers and services.

After starting the service, use the maxadmin command to view backend status and active services.

Verifying Read/Write Routing

Enable the general log on the master ( SET GLOBAL general_log=1;) and observe query distribution via MaxScale. Reads can be forced to the master by wrapping SELECT statements in a transaction ( BEGIN; SELECT …; COMMIT).

Descriptor Control Block (DCB)

Each client, server, or listener connection in MaxScale is represented by a Descriptor Control Block (DCB), identified by a unique memory address. DCBs track connection state and statistics.

Integrating MaxScale with MHA

Deploy MHA for automated failover and keep the MaxScale configuration unchanged. When MHA promotes a new master, MaxScale automatically redirects traffic without manual reconfiguration.

Additional Resources

GitHub repository for MHA helper scripts: https://github.com/ovaistariq/MHA-helper