MYSQL--MHA principle

##==============================================================##
MHA (Master high availability) is a MySQL highly available solution developed by the Japanese Dena Company, which is used to quickly switch between failover and master-slave promotion, and to ensure data consistency to a maximum extent.
The MHA consists mainly of two parts:
1, MHA Manager (Management node), management node deployed outside the cluster, mainly used to regularly monitor the primary node in the cluster, and in the master-slave switch is responsible for managing the scheduling switching process.
2. MHA node (data node), the data node is deployed on each cluster node and is responsible for comparing and applying the differential log during master-slave switchover.


The management node mainly contains the following tools:
masterha_check_ssh checking SSH configuration status of MHA
masterha_check_repl check MySQL replication status
Masterha_manger Start MHA
Masterha_check_status Detecting Current MHA running state
masterha_master_monitor checking Master for downtime
Masterha_master_switch control failover (automatic or manual)
Masterha_conf_host Adding or removing configured server information

The data node mainly contains the following tools:
Save_binary_logs Saving and copying the binary log of master
Apply_diff_relay_logs Identify the difference between the relay log events and apply their differential events to other slave
filter_mysqlbinlog removal of unnecessary rollback events (MHA no longer uses this tool)
purge_relay_logs clearing the trunk log (does not block SQL threads)

##==============================================================##
MHA Knowledge Points:
1. Use show SLAVE status to obtain the progress of each copy from the library, and to determine the most complete binlog from the library with the main library
A) master_log_file+read_master_log_pos determine binlog location from library read to main library
B) relay_log_file+relay_log_pos determine the location of the main library binlog from the inventory
C) Relay_master_log_file+exec_master_log_pos determine where to perform the main library binlog from the library


2, in the head of each relay-log from the library, record the main library and the version information from the library, and also record the Relay-log store binlog start location of the main library
 

2. Each Binlog event from the Relay-log of the library contains the location information of the event on the main library, and two difference logs from the library can be obtained from relay-log based on the two main library Binlog location points. Each binlog transaction always begins with a begin flag, ends with Commi, and if the full Binlog transaction from the library is not obtained, the transaction is not applied from the library to guarantee data consistency.
 

3. If the primary library server can still use SSH access when replicating the primary library instance, you can use Mysqlbinlog to obtain the final binlog of the main library.

4. Use Select Master_pos_wait (master_log_file,read_master_log_pos) to wait from the library to apply the main library Binlog to the specified location.

5, MHA will turn off Relaylog cleanup by default, if you want to clean ralaylog, you can use the following command:
SET GLOBAL relay_log_purge=1;
FLUSH LOGS;
SET GLOBAL relay_log_purge=0;


##==============================================================##
MHA Working principle:
 

##==============================================================##
MHA Main Features:
1, MHA switch does not rely on instances using the storage engine and binlog format;
2, MHA will not increase the performance cost of MySQL server, in addition to MHA management node without adding additional servers;
3, deploying MHA data node on MySQL server will not affect the current instance running;
4, MHA to achieve automatic failover, can also be triggered by manual online switching;
5, MHA can realize the second level of failover;
6, MHA can be any slave upgrade master, you can also specify the master candidate node when switching;
7. MHA provides an extension interface that allows user-defined scripts to be executed at a specific point in time during the MHA switchover process.

##==============================================================##
MHA Extensibility
A) Seconary_check_script
called when a master node connection failure is detected, from multiple network paths to determine if master has been down.

B) Shutdown_script
called before failover, you can log on to the master node via SSH for database shutdown and server shutdown.

C) Master_ip_failover_script
called before and after failover to the new master node to switch the VIP or domain name or other operations used by the cluster.

D) Report_script
called after failover is complete due to notification of failover execution results.

##==============================================================##
MHA Support and limitations:
1, only support Binlog V4 version, require MySQL 5.0 or later.
2. The candidate master node must have the Log-bin parameter turned on, and if all the slave nodes are turned on, no failover is performed.
3. Multi-master mode is not supported before MHA 0.52 version
4, MHA default does not support multi-level master-slave replication, by modifying the configuration file and setting Multi_tier_slave parameters
##==============================================================##

MHA principle: Https://code.google.com/p/mysql-master-ha/wiki/HowMHAWorks
MHA principle Ppt:http://www.slideshare.net/matsunobu/automated-master-failover
MHA installation package: https://github.com/linyue515/mysql-master-ha/

##==============================================================##

MYSQL--MHA principle