High availability architecture of MySQL: MHA

Time:2020-10-16

High availability architecture of MySQL: MHA

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

1、 Composition of MHA

(1) Role in MHA

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

  • MHA Manager: usually deployed on an independent machine to manage multiple master / slave clusters (groups). Each master / slave cluster is called an application, which is used to manage and coordinate the whole cluster.
  • MHA node: runs on each MySQL server (Master / slave / Manager), and it speeds up the fail over by monitoring scripts with functions of parsing and cleaning logs. It is mainly the agent that receives the instructions from the management node, and the agent needs to run on each MySQL node. Simply put, node is used to collect bin logs generated from the node server. Compare whether the slave node planned 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 provides many tools and programs, the common ones are as follows:

  • Manager node

High availability architecture of MySQL: MHA

  • Node node (these tools are usually triggered by the script of MHA manager and do not need human operation)

High availability architecture of MySQL: MHA

2、 Working principle of MHA

High availability architecture of MySQL: MHA

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

3、 Deploy MHA

(1) Prepare the experimental environment

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

(2) Building a 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: set different server ID for each MySQL)

show variables like '%log_bin%';
  • On all machines, create a master-slave replica account
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 an administrative account
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 the master-slave switch and failure of MySQL. The gtid is used to uniquely identify a transaction in the binlog. When the transaction is committed, MySQL server will first write a special binlog event with the type of gtid when writing binlog_ Event, specify the gtid of the next transaction, and then write the binlog of the transaction. Gtid of master slave synchronization_ The binlog of both event and transaction will be passed to the slave library, and the slave library will write the binlog with the same gtid when executing. In this way, after master-slave synchronization, the location of slave database synchronization can be determined by gtid. That is to say, no matter in the case of cascading or in the case of one master and many slaves, the gtid can be used to find points automatically, instead of using file as before_ Name and file_ Position.

  • The master-slave replication command is configured on the slave database and the master-slave synchronization is enabled
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;

High availability architecture of MySQL: MHA

  • Test master slave replication

(3) Installing MHA

  • All nodes install node software dependency package and node software
yum -y install perl-DBD-MySQL 
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
  • Install and install 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 configuration file for the manager

#Create configuration file directory and log directory
mkdir -p /etc/mha
mkdir -p /var/log/mha/log

#Edit 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 the MHA

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

High availability architecture of MySQL: MHA

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

High availability architecture of MySQL: 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

High availability architecture of MySQL: MHA

  • Test ha high availability automatic switching
On mysql111, perform the shutdown operation
mysqladmin -uroot -pWelcome_1 shutdown

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

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

High availability architecture of MySQL: MHA

High availability architecture of MySQL: MHA