MHA high availability environment construction

Last article( )Mysql master-slave mode deployment process has been completed. Now use MHA to achieve high availability of database.
Reference website:

1. Software version and environment:

System: CentOS7 system

Software required

MySQL 5.7.30 installation package: mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar

Mha4mysql management end file: mha4mysql-manager-0.58-0.el7 centos.noarch.rpm

Mha4mysql node file: mha4mysql-node-0.58-0.el7 centos.noarch.rpm

2. IP and roles of each machine - Master master database - responsible for data writing – slave from library 1 - responsible for data reading – slave from library 2 - responsible for data reading – MHA server - database node monitoring and automatic switching of master/slave nodes.

2. Construction of MHA high availability environment

1. Grant administrator users remote access on the master node.

grant all on *.* to 'mhaadmin'@'%' identified by 'mhaadmin';

3. Generate key on each node server

SSH keygen - t RSA (path default, no password)

4. Copy public key to other servers

ssh-copy-id -i .ssh/ root@
ssh-copy-id -i .ssh/ root@
ssh-copy-id -i .ssh/ root@
ssh-copy-id -i .ssh/ root@

5. Download MHA installation package address

6. Install mha

yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm   4 All nodes are installed
yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha Server installation

There may be perl errors when installing manager. You can modify the yum image address and install the dependency package.
It can also be solved according to the method in the error record below.

7. Set the mha profile
Create / etc / MHA first_ master,/etc/mha_ Master / work folder

[server default] 			//Configuration for server1,2,3 servers
user=mhaadmin 				//mha management user
password=mhaadmin 			//mha management password
manager_workdir=/etc/mha_master/ 		//mha_master's own work path
manager_log=/etc/mha_master/manager.log 	// mha_master's own log file
remote_workdir=/etc/mha_master/work		//Where is the working directory for each remote host
ssh_user=root 				// Key authentication based on ssh
repl_user=root				//Database user name
repl_password=root		//Database password
ping_interval=1 			//ping interval length
[server1] 					//Node 2
hostname= 	//Node 2 host address
ssh_port=22 				//ssh port of node 2
candidate_master=1 			//Can I be a candidate / master node in the future

8. Check the communication of each node

 masterha_check_ssh -conf=/etc/mha_master/mha.cnf

9. Test cluster connection configuration

masterha_check_repl -conf=/etc/mha_master/mha.cnf

Output log:

Mon Jun 15 05:55:47 2020 - [info] Checking replication health on
Mon Jun 15 05:55:47 2020 - [info]  ok.
Mon Jun 15 05:55:47 2020 - [info] Checking replication health on
Mon Jun 15 05:55:47 2020 - [info]  ok.
Mon Jun 15 05:55:47 2020 - [warning] master_ip_failover_script is not defined.
Mon Jun 15 05:55:47 2020 - [warning] shutdown_script is not defined.
Mon Jun 15 05:55:47 2020 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.
//No error message indicates successful startup.

10. Enable MHA

nohup masterha_manager -conf=/etc/mha_master/mha.cnf &> /etc/mha_master/manager.log &

//Stop MHA command: masterha_stop -conf=/etc/mha_master/mha.cnf

11. View the master node status

masterha_check_status -conf=/etc/mha_master/mha.cnf

Output log:

perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
	LANGUAGE = (unset),
	LC_ALL = (unset),
	LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
mha (pid:631) is running(0:PING_OK), master:
//Indicates that the MHA service is running OK, otherwise, it will be displayed as "mha is stopped(1:NOT_RUNNING)

5. Test the master-slave node switching

  • Turn off the master server and simulate the master node crash. View MHA log tail -f /etc/mha_master/manager.log

Indicates that the manager detects the node failure, and then automatically performs the failover to promote to the primary node.

Note that when the failover is complete, the manager will automatically stop using the master ha_ check_ Status command detection will encounter an error message as follows:

13. Add records to the new master database to view the synchronization of the slave database

Record from library:

slave status:

Problem sorting

1. An error is reported when installing MySQL server:
	error: Failed dependencies:
	/usr/bin/perl is needed by mysql-community-server-5.7.30-1.el7.x86_64

Install dependency package net-tools.x86_64,libaio.x86_64,perl.x86_64 ,numactl .

yum -y install perl.x86_64
yum -y install libaio.x86_64
yum -y install net-tools.x86_64	
yum -y install numactl

Reference website:

2. The slave I/O thread stops because master and slave have equal MySQL server UUIDs;

Problem analysis

The problem prompts that the master and slave use the same server UUID, and check one by one:

Check master and slave servers_ ID

show variables like 'server_id';

Check master-slave status:

show master status;

The final inspection found that their auto.cnf The server UUID in is the same because it is a container started from the same msyql image.

Problem solving

Stop mysqld service from the library and delete it auto.cnf File, and then start the database service:

 systemctl stop mysqld.service
 mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak
 systemctl start mysqld.service

Check the status of the slave database again. The synchronization of the master database has been enabled.

show slave status\G;

Reference website:

3. Installation of MHA manager reports an error
yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
Error: Package: mha4mysql-manager-0.58-0.el7.centos.noarch (/mha4mysql-manager-0.58-0.el7.centos.noarch)
           Requires: perl(Log::Dispatch)
Error: Package: mha4mysql-manager-0.58-0.el7.centos.noarch (/mha4mysql-manager-0.58-0.el7.centos.noarch)
           Requires: perl(Log::Dispatch::File)
Error: Package: mha4mysql-manager-0.58-0.el7.centos.noarch (/mha4mysql-manager-0.58-0.el7.centos.noarch)
           Requires: perl(Log::Dispatch::Screen)
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest


Update epel 
  yum install

This method uses all perl dependency packages to report errors. :
1. Installation
yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

Reference website:



4. After selecting a new master, the slave database cannot connect to the new master. Run show slave status \G; to report an error
​	Last_IO_Error: error connecting to master 'root@' - retry-time: 60  retries: 3

Authorize the root user on the new master to access other ip addresses.

	grant all privileges on *.* to 'root'@'%' identified by 'root';
5. After the original offline master is online again, the MHA detects and reports an error:

[external link picture transfer failed. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-rtjxeypd-1592279018087) (IMG / image-2020061535321614. PNG))

According to the principle, only one host can write data at the same time, otherwise it may cause catastrophic failure of inconsistent data!

set global read_only=1

Tags: CentOS Database ssh yum

Posted on Tue, 16 Jun 2020 00:35:54 -0400 by madox