MySQL MHA Deployment Guide

This article explains the supported boundaries and basic usage of mha.yml.

1. Support boundaries

  • Currently maintained only for CentOS 7 / Red Hat 7
  • Target architecture: one master, multiple replicas + MHA Manager
  • Applicable version: MySQL 5.7
  • Switchover scope: automatic failover after the current primary becomes unavailable only; planned online switchover while the primary is alive is not supported

mha.yml belongs to the traditional high availability solution. For new clusters, it is recommended to use MGR or InnoDB Cluster.

192.168.161.* is the default dbbot three-node regression subnet, not a legacy MHA-specific environment. To validate mha.yml, prepare a separate CentOS 7 or Red Hat 7 environment and use MySQL 5.7.

2. inventory and roles

mha.yml still uses the [dbbot_mysql] host group, and the actual roles are distinguished by variables:

[dbbot_mysql]
192.0.2.131 ansible_user=root ansible_ssh_pass="'<your_password>'"
192.0.2.132 ansible_user=root ansible_ssh_pass="'<your_password>'"
192.0.2.133 ansible_user=root ansible_ssh_pass="'<your_password>'"

3. Key variables

Edit mysql_ansible/playbooks/common_config.yml:

mysql_version: "5.7.44"
mysql_port: 3306
fcs_allow_dbbot_default_passwd: true

Description:

  • mha.yml supports only MySQL 5.7; do not keep the default MySQL 9.7.x configuration.
  • fcs_allow_dbbot_default_passwd: true is suitable only for temporary lab environments. If you keep dbbot’s public default passwords, this switch must be enabled explicitly; production environments should use custom passwords and keep this switch false.

Edit mysql_ansible/playbooks/vars/var_mha.yml:

master_ip: 192.0.2.131
slave_ips:
  - 192.0.2.132
  - 192.0.2.133
manager_ip: 192.0.2.133
vip: 192.0.2.130
vip_netmask: "32"
net_work_interface: ens33
mha_secondary_check_script: ""

Description:

  • manager_ip cannot be the same as master_ip
  • manager_ip should be placed on the replica node
  • vip, vip_netmask, net_work_interface need to be consistent with the actual network environment
  • ens33 is only an example interface name. Before running a VIP-related playbook, confirm the real interface with ip route get <gateway> or ip -o -4 addr show. CentOS 7 KVM test environments may also use eth0.
  • mha_secondary_check_script is empty by default, which keeps the classic MHA default behavior. For production, configure a topology-specific multi-path check command to reduce false positives from a single manager path.

3.1 VIP sudo policy

mha.yml creates /etc/sudoers.d/dbbot-mha-vip on every database node. The file is owned by root:root, has mode 0440, and allows mysql_user (mysql by default) to run only three fully rendered commands as root:

<mysql_user> ALL=(root) NOPASSWD: /usr/sbin/ip addr add <vip>/<prefix> dev <interface>
<mysql_user> ALL=(root) NOPASSWD: /usr/sbin/ip addr del <vip>/<prefix> dev <interface>
<mysql_user> ALL=(root) NOPASSWD: /sbin/arping -c 5 -U -I <interface> <vip>

master_ip_failover uses sudo -n; if the policy does not match, the sudo command returns an error immediately instead of waiting for a password. MHA transfers differential logs between nodes with ordinary scp and the configured ssh_user; it neither needs nor receives sudo scp. The playbook does not authorize unrestricted ip or arping arguments.

When the current classic MHA role runs, it installs and validates the exact policy before removing this broad rule created by older dbbot versions from /etc/sudoers:

mysql ALL=(ALL) NOPASSWD: /usr/sbin/ip,/sbin/arping,/usr/bin/scp

Use visudo -c to validate the complete sudo configuration. Do not manually broaden the managed policy.

4. Execution entry

cd /usr/local/dbbot/mysql_ansible/playbooks
ansible-playbook mha.yml

During execution, enter confirm as prompted.

For non-interactive execution, append the confirmation variable explicitly:

python3 /usr/local/dbbot/portable-ansible/ansible-playbook mha.yml -e dbbot_confirmation_input=confirm

If you also override boolean variables on the command line, use JSON extra-vars so key=true is not parsed as a string:

python3 /usr/local/dbbot/portable-ansible/ansible-playbook mha.yml \
  -e dbbot_confirmation_input=confirm \
  -e '{"fcs_allow_dbbot_default_passwd": true}'

4.1 Changing the VIP after deployment

There is currently no dedicated classic MHA VIP reconfiguration playbook. mha.yml also manages MySQL, replication, SSH, and MHA deployment, so do not use it as a shortcut solely to change the VIP on a running cluster. mha_unsafe_uninstall.yml removes MySQL data directories and must not be used for this operation.

A VIP-only change does not require editing /etc/masterha/app.cnf. That file references /usr/local/bin/master_ip_failover but does not store the VIP. Node membership or replication topology changes are outside this procedure; they require a separate full reconfiguration that also updates app.cnf. Keep these four states synchronized during the VIP change:

  1. The source configuration in mysql_ansible/playbooks/vars/var_mha.yml
  2. /usr/local/bin/master_ip_failover on every database node
  3. /etc/sudoers.d/dbbot-mha-vip on every database node
  4. The live VIP attached to the database node interface

Use the following sequence during a maintenance window:

Before stopping Manager, use IPAM, network management, or an equivalent authoritative source to confirm that the new VIP is reserved and that no other device is using it on the relevant Layer 2 network. A failed ping does not prove that an address is unused; do not continue if ownership cannot be confirmed.

  1. Stop MHA Manager on the manager_ip node and confirm that its process has exited:

    su - <mysql_user> -c 'masterha_stop --conf=/etc/masterha/app.cnf'
pgrep -af 'masterha_manager.*--conf=/etc/masterha/app.cnf'

  2. Identify the actual writable primary and the only node holding the old VIP. After a previous failover, do not assume that it is still the configured master_ip.

  3. Record the live VIP, back up the source configuration, failover script, and sudoers policy, then update var_mha.yml. Prepare the new master_ip_failover and exact sudoers policy in temporary files on every database node; validate them with perl -c <temporary-script> and visudo -cf <temporary-sudoers> before replacing the live files. Keep the sudoers file at root:root 0440, then run visudo -c after replacement.

  4. As root, remove the old VIP from its actual holder, bind the new VIP to the current primary, and send a gratuitous ARP announcement:

    /usr/sbin/ip addr del <old-vip>/<old-prefix> dev <old-interface>
/usr/sbin/ip addr add <new-vip>/<new-prefix> dev <new-interface>
/sbin/arping -c 5 -U -I <new-interface> <new-vip>

  5. Check every database node and confirm that the old VIP is absent and exactly one copy of the new VIP exists on the current primary.

  6. Run the MHA checks on the Manager node and start it again:

    su - <mysql_user> -c 'masterha_check_ssh --conf=/etc/masterha/app.cnf'
su - <mysql_user> -c 'masterha_check_repl --conf=/etc/masterha/app.cnf'
/usr/local/start_mha.sh
su - <mysql_user> -c 'masterha_check_status --conf=/etc/masterha/app.cnf'

If any step fails, keep Manager stopped, restore the previous source configuration, failover script, sudoers policy, and live VIP, validate the restored state, and only then restart Manager.

4.2 Replica read-only state after restart

Classic MHA uses the same asynchronous replication base configuration as standalone and ordinary primary-replica deployments, so my.cnf keeps super_read_only=OFF. When mha.yml first creates replication, it sets super_read_only=ON on replicas at runtime, but the base template does not persist that state. Classic MHA Manager also does not automatically repair replica read-only state after mysqld restarts.

After every database process or host restart, identify the actual current primary and all replicas again before restoring application traffic. Run the following only on a confirmed replica:

SHOW SLAVE STATUS\G
SELECT @@GLOBAL.read_only, @@GLOBAL.super_read_only;
SET GLOBAL super_read_only = ON;
SELECT @@GLOBAL.read_only, @@GLOBAL.super_read_only;

Validation must show both read_only and super_read_only as 0 on the current primary and as 1 on every replica. Run masterha_check_repl again afterward. Do not infer the live role from the old master_ip variable, and never run SET GLOBAL super_read_only=ON on the actual current primary.

The asynchronous topology deliberately does not hard-code super_read_only=ON in the shared my.cnf. Otherwise, a crashed primary that restarts before a failover completes could return in read-only mode. Maintaining replica read-only state after restarts is therefore part of the operational inspection and recovery procedure; the current playbook guarantees it only during initial deployment.

4.3 Switchover boundary and failover drills

The dbbot classic MHA implementation does not support planned online switchover with masterha_master_switch --master_state=alive. The presence of /usr/local/bin/master_ip_online_change and its reference in /etc/masterha/app.cnf does not make that hook usable. It is not a public dbbot execution entry and must not be invoked on a running cluster.

The supported path is failure-triggered failover: after MHA Manager determines that the current primary’s mysqld remains unavailable, it selects a candidate replica, performs failover, and calls master_ip_failover to move the VIP. Validate this path only in an isolated test environment or an approved failover exercise window:

  1. Run masterha_check_ssh and masterha_check_repl on the Manager node, confirm that Manager is running, and record the actual primary, replication state, and current VIP holder.

  2. Stop application writes or isolate the drill environment, and ensure that the old primary cannot resume writes during failover. The MySQL systemd unit generated by dbbot uses Restart=on-failure; do not send kill -9 to mysqld and assume that it will remain offline. If a process crash must be simulated, first use an approved fencing method or temporarily prevent automatic restart.

  3. For a controlled drill using the default port 3306, run the following command on the actual current primary so Manager handles it through the failure path:

    systemctl stop mysql3306

    For a non-default port, use the effective mysql_service_name. Never run this command directly in a production environment that has not been isolated for the drill.

  4. Follow /var/log/masterha/manager.log on the Manager node until failover completes. Classic MHA Manager exits after a failover; do not restart it until the old primary has been fenced, rebuilt, or safely reintroduced.

  5. Validate the database and writer endpoint independently: the old primary must remain non-writable or fenced, the new primary must be writable, and the remaining replicas must replicate normally. Run ip -o -4 addr show on every database node, confirm that exactly one VIP exists on the new primary, and verify a real connection and write through the VIP.

Output or exit status from master_ip_failover is not a substitute for checking the live VIP. A remote network command failure might not be reflected reliably by the hook result, so every failover must include inspection of the actual address state on all nodes. For a planned switchover while the primary remains alive, use a controlled manual procedure outside dbbot or an architecture that explicitly supports online switchover.

5. Uninstall

mha_unsafe_uninstall.yml stops the classic MHA manager, removes the configured VIP and MHA runtime files, and then removes the MySQL instance data, log, and runtime directories for the current mysql_port:

cd /usr/local/dbbot/mysql_ansible/playbooks
ansible-playbook mha_unsafe_uninstall.yml

During execution, enter confirm as prompted. For non-interactive execution, append -e dbbot_confirmation_input=confirm.

By default, the playbook does not remove the MHA RPM packages. If you need to remove mha4mysql-manager and mha4mysql-node, append -e '{"mha_uninstall_remove_packages": true}'.

6. Things to note

  • The current mha.yml document only covers CentOS 7 and Red Hat 7 prerequisites.
  • Three-node regression environments that do not meet the CentOS 7 / Red Hat 7 and MySQL 5.7 requirements are expected to be blocked by the mha.yml preflight checks.
  • If you use it on other distributions, you need to verify Perl dependencies and MHA RPM compatibility yourself first.
  • secondary_check_script is not configured by default. Production environments should add MHA secondary checks based on their network topology.
  • If you need to rerun after a failure, it is recommended to confirm the MHA Manager configuration, VIP status, and MySQL instance status before deciding whether to clean up the residual files.