[Mr. Zhao Qiang] MySQL high availability architecture: MHA

Time:2021-8-21

[Mr. Zhao Qiang] MySQL high availability architecture: MHA

MHA (Master HA) is an open source MySQL high availability program. It provides the automatic master failover function for MySQL master-slave replication architecture. When MHA monitors a master node failure, it will promote the slave node with the latest data to become a new master node. During this period, MHA will avoid consistency problems by obtaining additional information from other slave nodes. MHA also provides the online switching function of the master node, that is, switching the master / slave node on demand.
MHA is a relatively mature MySQL high availability solution developed by Japanese yoshinorim (formerly worked in Dena and now on Facebook). MHA can achieve failover within 30 seconds and ensure data consistency as much as possible during failover. At present, Taobao is also developing a similar product TMHA, which supports one master and one slave.

1、 Composition of MHA

(1) Roles in MHA

The MHA service has two roles, MHA Manager (management node) and MHA node (data node):

  • MHA Manager: usually deployed separately on an independent machine to manage multiple master / slave clusters (groups). Each master / slave cluster is called an application to manage and coordinate the whole cluster.
  • MHA node: runs on each MySQL server (Master / slave / Manager). It speeds up failover by monitoring scripts with the functions of parsing and cleaning logs. It is mainly the agent that receives the instructions sent by the management node. The agent needs to run on each MySQL node. In short, node is used to collect bin logs generated from the node server. Compare whether the slave node intended to be promoted to the new master node owns and completes the operation. If it is not sent to the new master node, it will be promoted to the master node after local application.

(2) Tools provided by MHA

MHA will provide many tools and programs, the common of which are as follows:

  • Manager node

[Mr. Zhao Qiang] MySQL high availability architecture: MHA

  • Node node (these tools are usually triggered by the script of MHA manager without human operation)

[Mr. Zhao Qiang] MySQL high availability architecture: MHA

2、 How MHA works

[Mr. Zhao Qiang] MySQL high availability architecture: MHA

  • Save binary log events from the master of downtime and crash;
  • Identify the slave with the latest update;
  • Apply different relay logs to other slave;
  • Apply binary log events saved from the master;
  • Upgrade a slave to a new master;
  • Use another slave to connect to the new master for replication.

3、 Deploy MHA

(1) Prepare the experimental environment

  • Three hosts: mysql111, mysql112 and mysql113
  • Operating system: CentOS
  • Configure host name (/ etc / hosts)
  • Configure password free login
  • Install MySQL on each host

(2) Build master-slave environment

  • Start binlog for each MySQL and set the server ID
vi /etc/my.cnf
log-bin=mysql-binlog
Server id = 1 (Note: each MySQL has a different server ID)

show variables like '%log_bin%';
  • Create master-slave copied accounts on all machines
create user 'repl'@'192.168.79.%' identified by 'Welcome_1';
grant replication slave on *.* to 'repl'@'192.168.79.%';
flush privileges;
  • On all machines, create management accounts
create user 'myadmin'@'192.168.79.%' identified by 'Welcome_1';
grant all privileges on *.* to 'myadmin'@'192.168.79.%';
flush privileges;
  • On the master and slave libraries, enable gtid
set @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
set @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
set @@GLOBAL.GTID_MODE = ON;

Parameter Description:
Gtid is a new feature of MySQL 5.6. Its full name is global transaction identifier, which can simplify master-slave switching and failover of MySQL. Gtid is used to uniquely identify a transaction in binlog. When a transaction is committed, MySQL server will first write a special binlog event of type gtid when writing binlog_ Event, specify the gtid of the next transaction, and then write the binlog of the transaction. Gtid during master-slave synchronization_ The binlog of events and transactions will be passed to the slave library. The slave library also writes binlog with the same gtid during execution, so that after master-slave synchronization, the location of synchronization from the library can be determined through gtid. In other words, no matter in the case of cascading or one master and multiple slaves, you can automatically find some through gtid without going through file as before_ Name and file_ Position found something.

  • Configure the master-slave copy command on the slave library and enable the master-slave synchronization
change master to master_host='mysql111',master_user='repl',\
master_password='Welcome_1',master_auto_position=1;

start slave;
  • View the status of master-slave replication on the slave library
show slave status\G;

[Mr. Zhao Qiang] MySQL high availability architecture: MHA

  • Test master-slave replication

(3) Install MHA

  • All nodes install node software dependency packages and node software
yum -y install perl-DBD-MySQL 
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
  • Install the manager software dependency package on mysql111
yum install -y perl-Config-Tiny 
yum install -y epel-release 
yum install -y perl-Log-Dispatch 
yum install -y perl-Parallel-ForkManager 
yum install -y perl-Time-HiRes
  • Install manager software on mysql111
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

(4) Create a profile for the manager

#Create profile directory, log directory
mkdir -p /etc/mha
mkdir -p /var/log/mha/log

#Edit the MHA configuration file VI / etc / MHA / mha.cnf
[server default]
manager_log=/var/log/mha/log/manager
manager_workdir=/var/log/mha/log
master_binlog_dir=/var/lib/mysql
user=myadmin
password=Welcome_1
ping_interval=2
repl_user=repl
repl_password=Welcome_1
ssh_user=root
[server1]
hostname=mysql111
port=3306
[server2]
hostname=mysql112
port=3306
[server3]
hostname=mysql113
port=3306

(5) Check the status and turn on MHA

  • Check mutual trust
masterha_check_ssh --conf=/etc/mha/mha.cnf

[Mr. Zhao Qiang] MySQL high availability architecture: MHA

  • Check master-slave replication status
masterha_check_repl --conf=/etc/mha/mha.cnf

[Mr. Zhao Qiang] MySQL high availability architecture: MHA

  • Open MHA Manager
nohup masterha_manager --conf=/etc/mha/mha.cnf > /var/log/mha/log/manager.log < /dev/null 2>&1 &
  • View MHA status
masterha_check_status --conf=/etc/mha/mha.cnf

[Mr. Zhao Qiang] MySQL high availability architecture: MHA

  • Test the automatic switching of HA high availability
On mysql111, execute the shutdown operation
mysqladmin -uroot -pWelcome_1 shutdown

MHA will automatically switch between master and slave. After the switch is completed, the MHA process will automatically stop running.

Observe mysql112 and mysql113 and execute the following statements:
show slave status\G;

[Mr. Zhao Qiang] MySQL high availability architecture: MHA

[Mr. Zhao Qiang] MySQL high availability architecture: MHA