MHA Routine Management

MHA Routine Management

MHA is the best choice for many enterprises that use MySQL databases for high availability. It is easy to use and powerful, and implements automatic master-slave failover Based on the MySQL replication architecture. This article describes the daily MHA operations and provides MHA connections for your reference.

I. Main MHA script (perl)
1. manager side
Masterha_check_ssh check the SSH configuration of MHA
Masterha_check_repl check MySQL replication status
Masterha_manger start MHA
Masterha_check_status checks the current MHA running status
Masterha_master_monitor
Masterha_master_switch controls Failover (automatic or manual)
Masterha_conf_host: add or delete the server information of the Configuration

2. node
Save_binary_logs stores and copies the binary logs of the master.
Apply_diff_relay_logs identifies differential relay log events and applies the differential events to other slave
Filter_mysqlbinlog remove unnecessary ROLLBACK events (MHA no longer uses this tool)
Purge_relay_logs clears relay logs (SQL threads are not blocked)

Ii. Routine operations
1. Verify ssh equivalent Verification
$ Masterha_check_ssh -- conf =/etc/masterha/app1.cnf

2. Verify mysql Replication
$ Masterha_check_repl -- conf =/etc/masterha/app1.cnf

3. Enable mha monitoring and enable automatic transfer when the master node fails.
$ Nohup masterha_manager -- conf =/etc/masterha/app1.cnf>/tmp/mha_manager.log </dev/null 2> & 1 &

### When a Server Load balancer node goes down, it cannot be started. In addition, -- ignore_fail_on_start can start mha even if a node goes down.
$ Nohup masterha_manager -- conf =/etc/masterha/app1.cnf -- ignore_fail_on_start>/tmp/mha_manager.log </dev/null 2> & 1 &

4. Check the startup status
$ Masterha_check_status -- conf =/etc/masterha/app1.cnf

5. Stop mha
$ Masterha_stop -- conf =/etc/masterha/app1.cnf

6. Multiple failures
After each failover switchover, MHA generates the file app1.failover. complete in the management directory. If the switchover fails due to the short interval, it should be cleared manually.
Rm-rf/var/log/masterha/app1/app1.failover. complete
Or you can ignore this by adding the -- ignore_last_failover parameter.

7. Manual failover
The manual failover scenario is applicable to scenarios where the master node is dead but masterha_manager is not enabled. For example, specify -- master_state = dead
Masterha_master_switch -- conf =/etc/masterha/app1.cnf -- dead_master_host = 192.168.1.6 -- master_state = dead -- new_master_host = 192.168.1.7
-- Ignore_last_failover

8. manually switch online. Specify -- master_state = alive as follows.
Masterha_master_switch -- conf =/etc/masterha/app1.cnf -- master_state = alive -- new_master_host = 192.168.1.6 -- orig_master_is_new_slave

Masterha_master_switch -- conf =/etc/masterha/app1.cnf -- master_state = alive -- new_master_host = 192.168.1.6 -- orig_master_is_new_slave
-- Running_updates_limit = 10000

-- Orig_master_is_new_slave
Indicates that the original master node becomes the slave node of the new master node during the switchover.

-- Running_updates_limit = 10000
If the master node is selected for failover, mha cannot be switched successfully. In addition, this parameter indicates that the latency can be switched over the time range (in seconds ),
However, the duration of switching is determined by the size of the relay log during recover.

Other considerations
Manually switch mha online. During the switchover, you must stop the running mha before switching.
Run DDL in the slave database. Generally, stop slave first. mysql logs are not recorded. You can use set SQL _LOG_BIN = 0.
Then perform a master-slave switchover and then execute DDL on the original master database. This method is suitable for increasing or decreasing indexes. You need to pay extra attention to adding fields.
Run the following command to stop mha: masterha_stop -- conf =/etc/masterha/app1.cnf.

Iii. MHA reference

MySQL MHA introduction and advantages

Typical use cases of MySQL MHA

MHA installation and equivalence Configuration

MHA configuration file sample description

MySQL MHA configuration FAQ

Mha vip switch script

This article permanently updates the link address: