MySQL High availability operation based on MySQL database

This article will cover another high-availability implementation of MySQL, namely MHA (Masterhigh avaliable). Master HA. is an open source high-availability program that provides MySQL's master-slave architecture with the ability to automatically recover from failures.

I. Introduction of BASIC knowledge

1. MHA definition

Master HA. is an open source high-availability program that provides MySQL's master-slave architecture with the ability to automatically recover from failures. During master-slave replication, when MHA detects a master node failure in MySQL, it automatically promotes a slave node in the replication cluster to become the new master node. During the switchover, data consistency issues are also avoided by obtaining additional information from other nodes. In addition to this, MHA also provides an on-line failover function for the master node, which can be switched master/slave nodes as needed.

2. MHA structure

All the nodes in MHA have two main roles: the management node and the data node.

Management node (MHA Manager): typically deployed separately on a single machine to manage multiple master and slave clusters, each master and slave cluster is called a application.
The data node (MHA node): Runs on each MySQL server and accelerates node failover by monitoring scripts that have the ability to parse and clean logs.

3. HMA Components

(1) Manager component

Masterha_check_sh: MHA-dependent ssh environment monitoring tool

Masterha_check_repl: MySQL replication environment monitoring tool

Masterha_manager: MHA service main program

Masterha_check_status: MHA running status detection tool

Masterha_master_monitor: MySQL master node availability monitoring tool

Masterha_master_switch: master node switching tool

Masterha_conf_host: Add or remove configured nodes

Masterha_stop: a tool to turn off MHA services

(2) Node component

Save_binary_logs: save and assign the master's binary log

Apply_diff_relay_logs: identify the relay log of the difference and apply it to other slaves

Filter_mysqlbinlog: remove unnecessary ROLLBACK events (this tool is no longer used by MHA)

Purge_relay_logs: clears the relay log (does not block the SQL thread)

(3) Custom extensions

Secondary_check_script: Monitors the availability of the master through multiple network routes

Master_ip_failover_script: update the masterip used by the application

Shutdown_script: Force the master node to be shut down

Report_script: send a report

Init_conf_load_script: Load initial configuration parameters

Master_ip_online_change_script: update the master node ip address

Second, MHA construction

1. Environment preparation
Operating system: centos6.9_x86_64
MySQL version: MySQL5.6.39 Universal binary
Server Planning:
Master node master:192.168.199.104 (mysql-master,master)

From Node 1:192.168.199.105 (MYSQL-SLAVE01,SLAVE01)
From Node 2:192.168.199.106 (MYSQL-SLAVE02,SLAVE02)
Management node: 192.168.199.107 (Mysql-manager,manager)

2. Build
(1) Configure the host of each node, in the later use will be more convenient, not every time the handwriting IP

[root@mysql-master ~]# vim /etc/hosts

#Add the following content:

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.199.104 mysql-master master

192.168.199.105 mysql-slave01 slave01

192.168.199.106 mysql-slave02 slave02

192.168.199.107 mysql-manager manager

(2) Copy the configured host file to the other three nodes

[root@mysql-master ~]# scp /etc/hosts slave01:/etc/

[root@mysql-master ~]# scp /etc/hosts slave02:/etc/

[root@mysql-master ~]# scp /etc/hosts manager:/etc/

(3) Configure Master-slave synchronization, SLAVE01 and Slave02 as master from the library

Turn on the binary log of master

[root@mysql-master ~]# vim /etc/my.cnf

#Add the following content under [mysqld]:

Server-id = 104

Skip-name-resolve

Log_bin=/mysql_data/mysql-bin

Log_bin_index=/mysql_data/mysql-bin.index

Binlog_format = mixed

View the current binary log point location on master and create a copy account

[root@mysql-master ~]# mysql -uroot –proot

#View the location of the current binary log point

Mysql> SHOW MASTER STATUS \G

*************************** 1. row ******************** *******

              File: mysql-bin.000090

          Position: 120

      Binlog_Do_DB:

  Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

#Create a copy account from the library

Mysql> GRANT REPLICATION SLAVE , REPLICATION CLIENT ON *.* TO 'repl'@'192.168.199.%' IDENTIFIED BY 'repl';

# Refresh permissions

Mysql> FLUSH PRIVILEGES;

Turn on binary and relay logs on SLAVE01, and configure the From library as master

[root@mysql-slave01 ~]# vim /etc/my.cnf

#Add the following under [mysqld]:

server-id = 105

log_bin = /mysql_data/mysql-bin

log_bin_index = /mysql_data/mysql-bin.index

relay_log = /mysql_data/mysql-relay

relay_log_index = /mysql_data/mysql-relay.index

read_only

relay_log_purge = 0

Parameter explanation:

Relay_log_purge: This parameter indicates that the relay log is not automatically cleared, because the MHA needs to judge the slave to synchronize the master's binlog according to the slave's relay log.

Read_only: indicates read-only, MHA needs to identify the master-slave library based on this parameter.

Bin_log: Opens the binary log from the library, because when the primary node fails, you need to promote one of the secondary libraries to the primary library, so you need to open the binary log from the library.

Start the SLAVE01 node and point the master node information that needs to be synchronized to the master node:

[root@mysql-slave01 ~]# service mysqld restart

[root@mysql-slave01 ~]# mysql -uroot –proot

#Use the change master command to point the main library to the master node.

Mysql> CHANGE MASTER TO MASTER_HOST = '192.168.199.104', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql-bin.000090', MASTER_LOG_POS=120;

Mysql> START SLAVE;

#View whether the synchronization is successful

Mysql> SHOW SLAVE STATUS \G

See the following message indicating that the synchronization was successful:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Configure the second slave library in the same way, you can directly copy the configuration file on the SLAVE01 directly to the second slave, and then modify it.

[root@mysql-slave01 ~]# scp /etc/my.cnf slave02:/etc/

[root@mysql-slave02 ~]# vim /etc/my.cnf

# Modify server-id to:

Server-id = 106

After the modification is complete, save the exit, restart MySQL, and then configure the SLAVE02 as master from the library

[root@mysql-slave02 ~]# mysql -uroot –proot

Mysql> CHANGE MASTER TO MASTER_HOST = '192.168.199.104', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql-bin.000090', MASTER_LOG_POS=120;

Mysql> START SLAVE;

#View whether the synchronization is successful

Mysql> SHOW SLAVE STATUS \G

See the following message indicating that the synchronization was successful:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

(4) Create a user account with administrative privileges

#Create an administrative account on the master node so that it can be synchronized to other connected slaves. Note: This management account needs to be able to connect to MySQL remotely.

Mysql> GRANT ALL ON *.* TO 'mha'@'192.168.199.%' IDENTIFIED BY 'mha';

Mysql> FLUSH PRIVILEGES;

(5) Since the MHA architecture requires mutual trust communication between each node, it is necessary to configure each node to have no secret key login, because the node is more, a single generation and replication is more troublesome, you can generate a key pair on the master node, and then let each host have the same private key

#Generate a key pair on the master

[root@mysql-master ~]# ssh-keygen -t rsa -P ''

#First save to the secret key file of the machine, so that the machine can log in to the machine using ssh without the key.

[root@mysql-master ~]# cat .ssh/id_rsa.pub >> .ssh/authorized_keys

#Use ssh to log in to the machine and find that you can log in without password.

[root@mysql-master ~]# ssh 192.168.199.104

# Modify the permissions of the authorized_keys file, which can only be viewed by the owner. The same group of users and other users cannot view it.

[root@mysql-master ~]# chmod go= .ssh/authorized_keys

Copy the key pair and the private key file to the other three nodes, note: Ensure that their original permissions

[root@mysql-master ~]# scp -p .ssh/id_rsa .ssh/authorized_keys slave01:/root/.ssh/

[root@mysql-master ~]# scp -p .ssh/id_rsa .ssh/authorized_keys slave02:/root/.ssh/

[root@mysql-master ~]# scp -p .ssh/id_rsa .ssh/authorized_keys manager:/root/.ssh/

#Test No password login, execute the following command on the master node to view the ip address of the slave01 node.

[root@mysql-master ~]# ssh slave01 'ifconfig eth0'

# can view the ip address of slave01 to indicate that no key login configuration is successful, the other two nodes can verify by themselves

(6) Download Mha4mysql-manager and Mha4mysql-node installation packages

The package versions used here are as follows:

Management node: mha4mysql-manager-0.56.tar.gz

Data node: mha4mysql-node-0.56.tar.gz

(7) Configure Epel, because mha4mysql some packages are from the base, some packages are from the Epel

[Root@mysql-master ~]# RPM–IVH  
http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

(8) Unzip and execute the makefile.pl script using the perl command

[Root@mysql-master ~]# tar XF mha4mysql-manager-0.56.tar.gz
[root@mysql-master ~]# CD mha4mysql-manager-0.56
[ Root@mysql-master mha4mysql-manager-0.56]# Perl makefile.pl

Note: During the makefile.pl process, if an error similar to the following occurs, you will need to install the library file that the Perl program depends on:

Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Makefile.pm line 4.

BEGIN failed--compilation aborted at inc/Module/Install/Makefile.pm line 4

If the above error occurs, you will need to install dependencies:

[root@mysql-master ~]# yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker cpan perl-Time-HiRes perl-DBI perl-DBD-MySQL

(9) Make sure that Perl makefile.pl is executed correctly and use the make command to complete the installation. Note: You can use the "echo $?" Review the execution results on this command to determine if the previous command completed correctly. Usually 0 represents the correct completion.

[Root@mysql-master mha4mysql-manager-0.56]# make
[root@mysql-master mha4mysql-manager-0.56]# make install

After the manager installation on Master is complete, compile the installation in the same way Mha4mysql-node

[Root@mysql-manager ~]# tar XF mha4mysql-node-0.56.tar.gz[root@mysql-manager ~]# CD mha4mysql-node-0.56[ Root@mysql-manager mha4mysql-node-0.56]# perl makefile.pl# determine if makefile.pl is performing properly [Root@mysql-manager mha4mysql-node-0.56]# echo $?0[root@mysql-manager mha4mysql-node-0.56]# make && make install

(11) Install the Mha4mysql-node on the other three servers (MASTER,SLAVE01,SLAVE02)

[Root@mysql-slave01 ~]# tar mha4mysql-node-0.56.tar.gz
[root@mysql-slave01 ~]# cd mha4mysql-node-0.56
[ Root@mysql-slave01 mha4mysql-node-0.56]# perl makefile.pl
[root@mysql-slave01 mha4mysql-node-0.56]# make && Make install

The other two nodes are installed the same way, here slightly.

(12) Installation completed, you can see there are some scripts under/usr/local/bin, that is, Mha4mysql generated script files

(13) Initialize MHA
Edit the MHA configuration file, mainly in the following two categories:
Global configuration: Provides default configuration for individual application
Application configuration: Used to specify which servers are

#Create a directory and edit the configuration file

[root@mysql-manager ~]# mkdir -pv /etc/masterha/

[root@mysql-manager ~]# vim /etc/masterha/app01.conf

Write the following content:

[server default]

User=mha #admin user name

Password=mha #admin user password

Manager_workdir=/data/masterha/app01 The working directory of the #manager node is used to store some binary logs. If it does not exist, it will be created automatically.

Manager_log=/data/masterha/app01/manager.log #log file location

Remote_workdir=/data/masterha/app01 #The remote working directory of each node is not automatically generated.

Ssh_user=root # Need to use ssh to complete some management operations

Repl_user=repl #username with copy permission

Repl_password=repl #password with copy permission

Ping_interval=1 #How often does the primary node monitor online? Heartbeat information monitoring

#Other host

[server1]

Hostname=192.168.199.104

#ssh_port=3306 #If MySQL does not use the default port number, you need to specify it here. You do not need to specify the default port.

Candidate_master=1 # indicates whether the node participates in becoming the master node after the primary node fails

Master_binlog_dir=/mysql_data/ #Specify the binlog log path of mysql

[server2]

Hostname=192.168.199.105

Candidate_master=1

Master_binlog_dir=/mysql_data/

[server3]

Hostname=192.168.199.106

#no_master=1 # indicates that the node does not participate in becoming the master node after the master node fails.

Master_binlog_dir=/mysql_data/

(14) Check the communication between each node is normal

[Root@mysql-manager ~]# masterha_check_ssh--conf=/etc/masterha/app01.conf

Error 1: Can ' t locate config/tiny.pm in @INC (@INC contains:xxx
Cause: This error is due to a lack of dependent packages
WORKAROUND: Install the hint's dependency package
The following RPM package if the operating system itself is not self-bringing, you need to download from the Internet, you can from
http://rpmfind.net/Download the required RPM package. The following RPM packages are for CentOS6.9 x86_64:

	perl-Mail-Sender-0.8.16-3.el6.noarch.rpm



	perl-Log-Dispatch-2.26-1.el6.rf.noarch.rpm



	perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm



	perl-Config-Tiny-2.12-7.1.el6.noarch.rpm

After the download is complete, install it using the Yum command, as it may also depend on other packages.

Error 2: Error in Master_check_ssh execution:
Permission denied (Publickey,gssapi-keyex,gssapi-with-mic,password).
Cause: The contents of the file under/root/.ssh/or the permissions of the file, I encountered a problem with the file content.
Workaround: It is normal to re-copy the key pair and the private key on the other host.

(15) Check whether the master-slave replication environment is normal

[Root@mysql-manager ~]# Masterha_check_repl--conf=/etc/masterha/app01.conf

Error 1:

	Sat Jun  2 03:07:26 2018 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln301] install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at (eval 27) line 3.



	Perhaps the DBD::mysql perl module hasn't been fully installed,



	orperhaps the capitalisationof'mysql'isn'tright.

Reason: missing Perl-dbd-mysql dependent library

Workaround:

[Root@mysql-manager ~]# Yum install-y perl-dbd-mysql*

Error 2:

Sat June  2 03:11:06 2018-[ERROR][/USR/LOCAL/SHARE/PERL5/MHA/SERVERMANAGER.PM, ln188] There is no alive server. We can ' t do failover

Error 3:

Failed to save binary log:binlog not found from/var/lib/mysql,/var/log/mysql! If you got the MHA Manager, please set 
"Master_binlog_dir=/path/to/binlog_directory_of_the_master

Cause: The MySQL binlog log directory is not in the default/var/log/mysql, but elsewhere, causing the directory to not be found.

Workaround: Make a directory path to the binary log under each node in the Manager node's configuration file.

[server1]hostname=192.168.199.105candidate_master=1master_binlog_dir=/mysql_data/  #Specify binary log file directory

Error 4:

	Can'texec"mysqlbinlog":Nosuch fileordirectoryat/usr/local/share/perl5/MHA/BinlogManager.pm line 106.



	mysqlbinlog version command failedwithrc 1:0, please verify PATH, LD_LIBRARY_PATH,andclient options

Cause: The Mysqlbinlog command was not found after using SSH connection

Workaround: Connect the/usr/local/mysql-5.6.39/directory to the/usr/bin

[Root@mysql-master ~]# ln–s/usr/local/mysql-5.6.39/bin/usr/bin
[root@mysql-slave01 ~]# ln–s/usr/local/mysql-5.6.39/ BIN/USR/BIN
[ROOT@MYSQL-SLAVE02 ~]# Ln–s/usr/local/mysql-5.6.39/bin/usr/bin

(16) Start MHA

#Front desk start, the log will print directly on the console 
[Root@mysql-manager app01]# masterha_manager--conf=/etc/masterha/app01.conf
# background boot 
[ Root@mysql-manager app01]# nohup masterha_manager--conf=/etc/masterha/app01.conf &

(17) Verify high-availability fault auto-transfer

# Stop the mysqld service of the master node

[root@mysql-master ~]# killall mysqld mysqld_safe

#View the original two slave node information and find that slave01 has been promoted to master.

Mysql> SHOW SLAVE STATUS \G

*************************** 1. row ******************** *******

                Slave_IO_State: Waiting for master to send event

                   Master_Host: 192.168.199.105

                   Master_User: repl

                   ...

#View the read_only attribute of slave01 and found that it has been modified to OFF.

Mysql> SHOW VARIABLES LIKE 'read_only';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| read_only | OFF |

+---------------+-------+

Note: When a master fails, the manager service on the original manager node exits automatically when the library is promoted to the primary library. Manual restart is required.
(18) When the failed server is restored again, it needs to be manually added to the slave node using the "Change MASTER" command
After the manager node is started, you can use the Masterha_check_status command to view the current master node status information

[Root@mysql-manager ~]# masterha_check_status--conf=/etc/masterha/app01.conf
     app01 (pid:20582) is running (0: PING_OK), master:192.168.199.105

Other uses of MHA, such as online master-slave switching, can refer to the official documentation, which is not covered here. In addition, there are MHA Manager node automatic restart function, alarm function, fault master removal function can be implemented through the shell script, there will be time to continue to introduce.

At this point, MySQL's MHA high-availability basic functionality is built. Throughout the process there are countless pits, the reader may be in the construction process, but also encounter other deep pits, it is best to bite the bullet one to solve! Length, all the content is verified a word a word knocked up, but also inevitably have a clerical error, if there are errors, welcome in the comments below to point out!