MHA high availability configuration and failover instance of MySQL

Posted by mattbrad on Thu, 24 Feb 2022 06:48:54 +0100

1, MHA overview

1.1 what is MHA

  • MHA (MasterHigh Availability) is a set of excellent software for failover and master-slave replication in MySQL high availability environment.

  • The emergence of MHA is to solve the problem of MySQL single point.

  • During MySQL failover, MHA can automatically complete the failover operation within 0-30 seconds.

  • MHA can ensure the consistency of data to the greatest extent in the process of failover, so as to achieve high availability in the real sense.

1.2 composition of MHA

1) MHA Node

The MHA Node runs on each MySQL server.

2) MHA Manager (management node)

  • MHA Manager can be deployed on an independent machine to manage multiple master slave clusters; It can also be deployed on a slave node.

  • The MHA Manager will periodically probe the master node in the cluster. When the master fails, it can automatically promote the slave of the latest data to the new master, and then point all other slave to the new master again. The entire failover process is completely transparent to the application.

1.3 characteristics of MHA

  • In the process of automatic failover, MHA tries to save binary logs from the down main server to ensure no data loss to the greatest extent;

  • Using semi synchronous replication can greatly reduce the risk of data loss. If only one slave has received the latest binary log, MHA can apply the latest binary log to all other slave servers, so it can ensure the data consistency of all nodes;

  • At present, MHA supports a master - slave architecture, with at least three services, i.e. one master and two slaves

2, MHA construction preparation

1.1 experimental ideas

1.MHA architecture ① database installation ② one master and two slave ③ MHA construction

2. Fault simulation # ① the main library fails ② the alternative main library becomes the main library ③ the original fault main library recovers and rejoins to MHA to become the slave library

1.2 experimental preparation

Node serversystemhost nameIP addressInstallation services and tools
MHA manager node serverCentOS7.4(64 bit)manager192.168.80.129Install MHA node and manager components
Master node serverCentOS7.4(64 bit)mysql1192.168.80.130Install mysql5 7 and MHA node components
Slave 1 node serverCentOS7.4(64 bit)mysql2192.168.80.131Install mysql5 7 and MHA node components
Slave2 node serverCentOS7.4(64 bit)mysql3192.168.80.200Install mysql5 7 and MHA node components

3, Steps of MHA construction

3.1 configuring master-slave replication

1. Initialize the environment

#Initialize the environment on four servers
systemctl stop firewalld
systemctl disable firewalld
setenforce 0

2. Modify the host names of Master, Slave1 and Slave2 nodes

#On Master
hostnamectl set-hostname Mysql1
#In Slave1
hostnamectl set-hostname Mysql2
#In Slave2
hostnamectl set-hostname Mysql3

3. Add domain name resolution in Master, Slave1 and Slave2

vim /etc/hosts Mysql1 Mysql2 Mysql3

4. Configure master-slave synchronization

#Modify the Mysql main configuration file of Master, Slave1 and Slave2 nodes / etc / my cnf 
##Master node##
vim /etc/my.cnf
server-id = 1
log_bin = master-bin
log-slave-updates = true
systemctl restart mysqld
##Slave1 node##
vim /etc/my.cnf
server-id = 2 						#The server IDs of three servers cannot be the same
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
systemctl restart mysqld
###Slave2 node##
vim /etc/my.cnf
server-id = 3 
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
systemctl restart mysqld



5. Create two soft links on master, Slave1 and Slave2 nodes

ln -s /usr/local/mysql/bin/mysql /usr/sbin/
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/
ls /usr/sbin/mysql*

6. Login database master-slave configuration authorization

###Master,Slave1,Slave2 All nodes are authorized###
grant replication slave on *.* to 'myslave'@'192.168.80.%' identified by '123456';
#Authorized master-slave user
grant all privileges on *.* to 'mha'@'192.168.80.%' identified by 'manager';
grant all privileges on *.* to 'mha'@'Mysql1' identified by 'manager';
grant all privileges on *.* to 'mha'@'Mysql2' identified by 'manager';
grant all privileges on *.* to 'mha'@'Mysql3' identified by 'manager';
#Refresh Library
flush privileges;


7. The master node views the binary files and synchronization points, and performs synchronization operations at Slave1 and Slave2 nodes

###stay master upper###
show master status;
###stay slave1,slave2 The node performs synchronization operations##
change master to master_host='',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=1745;
start slave;
show slave status\G;

###On the master ###

###On slave1###

8. Set the read-only mode of two slave nodes

set global read_only=1;

9. Verify master-slave synchronization

##In the Lord master Create libraries, tables, and insert data on##
create database test1;
use test1;
insert into info values('Alline');
select * from;
##stay slave1,slave2 Upper verification##
select * from;

Topics: Operation & Maintenance Database