A Free Trial That Lets You Build Big!
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
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
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)
(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
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–IVHhttp://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:
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
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
[Root@mysql-manager ~]# Yum install-y perl-dbd-mysql*
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
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
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.confapp01 (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!
Start building with 50+ products and up to 12 months usage for Elastic Compute Service