MHA High Availability Architecture Deployment Configuration Instance

1. Preface

1.1What's MHA?--An introduction to the principles

MHA - Master High Availability, currently a relatively mature solution for MySQL high availability, is a set of excellent MySQL failover and master-slave enhanced high availability software.

Here we mention two key points:'High availability','Failover'.Let's give you a brief overview of what each means.

Why is 1.1.1 highly available?

High availability means high availability. Under certain conditions (when a server goes wrong or down), the server can operate normally without affecting the operation of the business to a certain extent.

1.1.2 Failover

When the master server fails and is monitored by the manager server to stop the mysqld service in the master library, SSH login check (save_binary_logs-command=test) is performed on the master library, then the health check on the mysqld service (PING(SELECT) is performed once every 3 seconds for 3 times, and Master is down! master failover begins to process the error correctly., the specific process can refer to the online blog, here is a link: https://www.cnblogs.com/xiaoboluo768/p/5210820.html You can refer to this friend's article, which is very detailed.

2. MHA High Availability Architecture Deployment Instances

2.1 Deployment Environment and Base Configuration Requirements

In a virtual machine environment, four Centos7 servers are required (here I'm using Centos7, so the mha version used is 0.57)

One serves as the mha server (manager) to monitor and manage the MySQL server below;

The remaining three master-slaves, one of which serves as the reserve master server, are promoted to the master server when the master server is down or in error.(This is Master-Slave Promotion)

Master-Slave Replication has introduced the configuration example of MySQL version 5.7.17 in the previous article. The configuration principle and idea of MySQL 5.6.36 are the same, but there are some differences in details.

First, assign an ip and define it yourself for experimental validation on your private network

MHA Server - manager: 192.168.68.136

MySQL Master Server - master:192.168.68.129

MySQL Reserve Master Server (originally from server) - slave1:192.168.68.132

MySQL slave server - slave2:192.168.68.133

2.2 Configuration process

2.2.1 Overview of the configuration process

Install Compile Environment - Install MySQL 5.6.36 Database - Configure Time Synchronization (refer to the previous blog experiment) - Configure Master-Slave Replication and Reserve MySQL Server - Install node Tool - Configure mha Server - Test Validation

2.2.2 Detailed configuration steps 2.2.2.1 First let's configure MySQL server - install MySQL 5.6.36

The MySQL server configuration is as follows:

#1. Install the compilation environment yum install -y ncurses-devel gcc-c++ perl-Module-Install #2. Install the gmake compilation environment tar zxf cmake-2.8.6.tar.gz -C /opt/ cd /opt/cmake-2.8.6/ ./configure gmake && gmake install

Install MySQL 5.6.36 database

tar zxf mysql-5.6.36.tar.gz -C /opt/ cd /opt/mysql-5.6.36/ cmake \ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_EXTRA_CHARSETS=all \ -DSYSCONFDIR=/etc make make install

Related Optimized Configuration

cp support-files/my-default.cnf /etc/my.cnf #Main Profile cp support-files/mysql.server /etc/rc.d/init.d/mysqld chmod +x /etc/rc.d/init.d/mysqld chkconfig --add mysqld echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile source /etc/profile useradd -M -s /sbin/nologin mysql chown -R mysql.mysql /usr/local/mysql /usr/local/mysql/scripts/mysql_install_db \ --basedir=/usr/local/mysql \ --datadir=/usr/local/mysql/data \ --user=mysql

We won't do firewall rules here, just turn it off

systemctl stop firewalld.service setenforce 0

Next, we configure the main profile for each of the three MySQL databases

1.MySQL Host Server Configuration - vim/etc/my.cnf

[mysqld] server-id = 1 log_bin = master-bin log-slave-update = true

2.MySQL Configuration from Server slave1, slave2 - vim/etc/my.cnf

[mysqld] server-id = 2 #Open Binary Log log_bin = master-bin #Synchronize using relay logs relay-log = relay-log-bin relay-log-index = slave-relay-bin.index

[mysqld] server-id = 3 #Open Binary Log log_bin = master-bin #Synchronize using relay logs relay-log = relay-log-bin relay-log-index = slave-relay-bin.index

3.master, slave1, slave2 do two soft links

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

4. Start MySQL Service

/usr/local/mysql/bin/mysqld_safe --user=mysql &

5. View Authentication Ports

[root@master mysql-5.6.36]# netstat -natp | grep 3306 tcp6 0 0 :::3306 :::* LISTEN 103231/mysqld

2.2.2.2 Configure master-slave replication

Configuration on MySQL Server - based on your own segment settings

Authorize two users on all database nodes, one using user myslave synchronously from the library and the other using user mha by manager

#Access all server databases for authorization settings mysql> grant replication slave on *.* to 'myslave'@'192.168.68.%' identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> grant all privileges on *.* to 'mha'@'192.168.68.%' identified by 'manager'; Query OK, 0 rows affected (0.00 sec) #Supplement configurations that avoid problems due to mha-related features mysql>grant all privileges on *.* to 'mha'@'master' identified by 'manager'; Query OK, 0 rows affected (0.00 sec) mysql>grant all privileges on *.* to 'mha'@'slave1' identified by 'manager'; Query OK, 0 rows affected (0.00 sec) mysql>grant all privileges on *.* to 'mha'@'slave2' identified by 'manager'; Query OK, 0 rows affected (0.00 sec) mysql>flush privileges; Query OK, 0 rows affected (0.00 sec)

Note: This step needs to be configured on all MySQL servers

View and record the synchronization location of the MySQL master server

show master status; mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 120 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

MySQL Set Synchronization From Server

#Set Synchronization Configuration Command mysql> change master to master_host='192.168.68.129',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=120; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) #See if two threads are yes mysql> show slave status\G *************************** 1. row *************************** ...//Omit some content Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 120 Relay_Log_File: relay-log-bin.000002 Relay_Log_Pos: 284 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes #Indicates successful synchronization Slave_SQL_Running: Yes Replicate_Do_DB: Master_SSL_CA_File:

Create a database on the primary server and verify that the same database exists from the server.

Be careful!!!Two slave libraries must be set to read-only mode

mysql> set global read_only=1; Query OK, 0 rows affected (0.00 sec)

This is the process of manual compilation, installation and master-slave replication of MySQL 5.6.36 database. Let's start configuring mha

2.2.2.3 Configuration mha

1. Install MHA-dependent environments on all servers, starting with the epel source.

yum -y install epel-release --nogpgcheck yum -y install perl-DBD-MySQL \ perl-Config-Tiny \ perl-Log-Dispatch \ perl-Parallel-ForkManager \ perl-ExtUtils-CBuilder \ perl-ExtUtils-MakeMaker \ perl-CPAN

2.The MHA package is different for each operating system version. Here is centos7.4 which must select version 0.57.
You must install the node component on all servers and finally the manager component on the HA-manager node.
Because manager relies on node components, the following are demonstrations of how to install node components on master

tar zxf mha4mysql-node-0.57.tar.gz cd mha4mysql-node-0.57 perl Makefile.PL make && make install

3. Install the manager component on the HA-manager

tar -zxvf mha4mysql-manager-0.57.tar.gz cd mha4mysql-manager-0.57 perl Makefile.PL make && make install

After the manager is installed, several tools are generated under / usr/local/bin, mainly including the following:
Masha_check_ssh Checks MHA's SSH configuration
masterha_check_repl Check MySQL Replication Status
masterha_manager Start manager script
masterha_check_status checks the current MHA running state
masterha_master_monitor detects whether master is down
masterha_master_switch controls failover (automatic or manual)
Master ha_conf_host adds or deletes configured server information
Master ha_stop closes manager

[root@manager mha4mysql-manager-0.57]# cd /usr/local/bin/ [root@manager bin]# ls apply_diff_relay_logs masterha_check_status mysql mysql_embedded mysql_upgrade filter_mysqlbinlog masterha_conf_host mysqladmin mysqlimport mysqlxtest innochecksum masterha_manager mysqlbinlog mysql_install_db perror libmysqlclient.a masterha_master_monitor mysqlcheck mysql_plugin pkgconfig libmysqlclient.so masterha_master_switch mysql_client_test mysqlpump plugin libmysqlclient.so.20 masterha_secondary_check mysql_client_test_embedded mysql_secure_installation purge_relay_logs libmysqlclient.so.20.3.4 masterha_stop mysql_config mysqlshow replace libmysqld.a myisamchk mysql_config_editor mysqlslap resolveip libmysqlservices.a myisam_ftdump mysqld mysql_ssl_rsa_setup resolve_stack_dump lz4_decompress myisamlog mysqld_pre_systemd mysqltest save_binary_logs masterha_check_repl myisampack mysqldump mysqltest_embedded zlib_decompress masterha_check_ssh my_print_defaults mysqldumpslow mysql_tzinfo_to_sql [root@manager bin]#

After the node is installed, several scripts are also generated under / usr/local/bin (these tools are usually run by the MHA manager)
The script triggers without human action) mainly as follows:
save_binary_logs Saves and copies master's binary logs
apply_diff_relay_logs identifies differences in relay log events and applies their differences to other slave s
filter_mysqlbinlog removes unnecessary ROLLBACK events (MHA no longer uses this tool)
purge_relay_logs clears relay logs (does not block SQL threads)

[root@slave2 mha4mysql-node-0.57]# cd /usr/local/bin/ [root@slave2 bin]# ls apply_diff_relay_logs ccmake cmake cpack ctest filter_mysqlbinlog purge_relay_logs save_binary_logs

4. Configure Password-Free Authentication

4.1 Password-free authentication configured on manager to all database nodes

[root@manager ~]# Ssh-keygen-t RSA //Press Enter all the way [root@manager ~]# ssh-copy-id 192.168.68.129 [root@manager ~]# ssh-copy-id 192.168.68.132 [root@manager ~]# ssh-copy-id 192.168.68.133

4.2 Password-free authentication configured on Mysql1 to database nodes Mysql 2 and Mysql3

[root@master ~]# ssh-keygen -t rsa [root@master ~]# ssh-copy-id 192.168.68.132 [root@master ~]# ssh-copy-id 192.168.68.133

4.3 Password-free authentication configured on Mysql2 to database nodes Mysql1 and Mysql 3

[root@slave1 ~]# ssh-keygen -t rsa [root@slave1 ~]# ssh-copy-id 192.168.68.129 [root@slave1 ~]# ssh-copy-id 192.168.68.133

4.4 Password-free authentication configured on Mysql3 to database nodes Mysql1 and Mysql2

[root@slave2 ~]# ssh-keygen -t rsa [root@slave2 ~]# ssh-copy-id 192.168.68.129 [root@slave2 ~]# ssh-copy-id 192.168.68.132

5. Configure MHA scripts

Copy the script on the manager node to the / usr/local/bin directory.

[root@manager ~]# cp -ra /root/mha/mha4mysql-manager-0.57/samples/scripts /usr/local/bin //Four execution files are copied [root@managers ~]# ll /usr/local/bin/scripts/ //Total usage 32 -rwxr-xr-x 1 mysql mysql 3648 5 Month 31, 2015 master_ip_failover #Script for VIP management on automatic switch -rwxr-xr-x 1 mysql mysql 9872 5 February 2509:07 master_ip_online_change #vip management during online switching -rwxr-xr-x 1 mysql mysql 11867 5 Month 31, 2015 power_manager #Script to shut down the host after a failure occurs -rwxr-xr-x 1 mysql mysql 1360 5 Month 31, 2015 send_report #Script to send an alert after failover
Copy the VIP-managed scripts from the above auto-switch to the / usr/local/bin directory, where the VIP is managed using scripts.

[root@manager bin]# cd scripts/ [root@managerscripts]# ls master_ip_failover master_ip_online_change power_manager send_report [root@manager scripts]# cp master_ip_failover /usr/local/bin/

Modify the configuration file as follows:The main part is to add content

[root@manager ~]#vim /usr/local/bin/master_ip_failover #!/usr/bin/env perl use strict; use warnings FATAL => 'all'; use Getopt::Long; my ( $command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port ); #############################Add Content Section######################################### my $vip = '192.168.68.200'; my $brdc = '192.168.68.255'; my $ifdev = 'ens33'; my $key = '1'; my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip"; my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down"; my $exit_code = 0; #my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;"; #my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key"; ################################################################################## GetOptions( 'command=s' => \$command, 'ssh_user=s' => \$ssh_user, 'orig_master_host=s' => \$orig_master_host, 'orig_master_ip=s' => \$orig_master_ip, 'orig_master_port=i' => \$orig_master_port, 'new_master_host=s' => \$new_master_host, 'new_master_ip=s' => \$new_master_ip, 'new_master_port=i' => \$new_master_port, ); exit &main(); sub main { print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n"; if ( $command eq "stop" || $command eq "stopssh" ) { my $exit_code = 1; eval { print "Disabling the VIP on old master: $orig_master_host \n"; &stop_vip(); $exit_code = 0; }; if ($@) { warn "Got Error: $@\n"; exit $exit_code; } exit $exit_code; } elsif ( $command eq "start" ) { my $exit_code = 10; eval { print "Enabling the VIP - $vip on the new master - $new_master_host \n"; &start_vip(); $exit_code = 0; }; if ($@) { warn $@; exit $exit_code; } exit $exit_code; } elsif ( $command eq "status" ) { print "Checking the Status of the script.. OK \n"; exit 0; } else { &usage(); exit 1; } } sub start_vip() { `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`; } # A simple system call that disable the VIP on the old_master sub stop_vip() { `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`; } sub usage { print "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n"; }

6. Create the MHA software catalog and copy the configuration file.

[root@manager scripts]# mkdir /etc/masterha [root@manager scripts]# cd ~/mha/mha4mysql-manager-0.57/ [root@manager mha4mysql-manager-0.57]# ls AUTHORS blib debian lib Makefile.PL META.yml README samples tests bin COPYING inc Makefile MANIFEST pm_to_blib rpm t [root@manager mha4mysql-manager-0.57]# cd samples/ [root@manager samples]# cd conf/ [root@manager conf]# ls [root@manager conf]# cp app1.cnf /etc/masterha/ [root@manager conf]# vim /etc/masterha/app1.cnf

Edit and modify app1.cnf

[server default] manager_log=/var/log/masterha/app1/manager.log##manager working directory manager_workdir=/var/log/masterha/app1#manager log master_binlog_dir=/usr/local/mysql/data#Master holds the binlog, where the path matches that of the binlog configured in master so that mha can find it master_ip_failover_script=/usr/local/bin/master_ip_failover#Set the switch script for automatic failover, which script is above master_ip_online_change_script=/usr/local/bin/master_ip_online_change#Set switch script for manual switch password=manager#Set the password for the root user in mysql, which is the password you created earlier to monitor the user ping_interval=1#Set the time interval between monitoring libraries and sending ping packages, defaulting to 3 seconds, to automatically railover when three attempts fail to respond remote_workdir=/tmp#Set the location where binlog will be saved for remote mysql when a switch occurs repl_password=123#Set the password for the replication user repl_user=myslave#Set up users for replication users secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.68.132 -s 192.168.68.133 shutdown_script=""#Set up the fail host script to shut down after a failure occurs (the main purpose of this script is to shut down the host in case of a brain fissure, which is not used here) ssh_user=root#Set ssh login user name user=mha#Set monitoring user root [server1] hostname=192.168.68.129 port=3306 [server2] candidate_master=1#Set as candidate master check_repl_delay=0 hostname=192.168.68.132 port=3306 [server3] hostname=192.168.68.133 port=3306

2.2.2.4 Test Validation

Testing ssh for no password authentication will eventually output success if normal, as shown below.

[root@manager conf]# masterha_check_ssh -conf=/etc/masterha/app1.cnf Tue Jan 14 16:44:58 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue Jan 14 16:44:58 2020 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Tue Jan 14 16:44:58 2020 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Tue Jan 14 16:44:58 2020 - [info] Starting SSH connection tests.. Tue Jan 14 16:45:00 2020 - [debug] Tue Jan 14 16:44:58 2020 - [debug] Connecting via SSH from [email protected](192.168.68.129:22) to [email protected](192.168.68.132:22).. Tue Jan 14 16:44:59 2020 - [debug] ok. Tue Jan 14 16:44:59 2020 - [debug] Connecting via SSH from [email protected](192.168.68.129:22) to [email protected](192.168.68.133:22).. Tue Jan 14 16:45:00 2020 - [debug] ok. Tue Jan 14 16:45:01 2020 - [debug] Tue Jan 14 16:44:59 2020 - [debug] Connecting via SSH from [email protected](192.168.68.133:22) to [email protected](192.168.68.129:22).. Tue Jan 14 16:45:00 2020 - [debug] ok. Tue Jan 14 16:45:00 2020 - [debug] Connecting via SSH from [email protected](192.168.68.133:22) to [email protected](192.168.68.132:22).. Tue Jan 14 16:45:01 2020 - [debug] ok. Tue Jan 14 16:45:01 2020 - [debug] Tue Jan 14 16:44:59 2020 - [debug] Connecting via SSH from [email protected](192.168.68.132:22) to [email protected](192.168.68.129:22).. Tue Jan 14 16:45:00 2020 - [debug] ok. Tue Jan 14 16:45:00 2020 - [debug] Connecting via SSH from [email protected](192.168.68.132:22) to [email protected](192.168.68.133:22).. Tue Jan 14 16:45:00 2020 - [debug] ok. Tue Jan 14 16:45:01 2020 - [info] All SSH connection tests passed successfully.

Health checks are as follows:

[root@manager conf]# masterha_check_repl -conf=/etc/masterha/app1.cnf Tue Jan 14 16:45:21 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue Jan 14 16:45:21 2020 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Tue Jan 14 16:45:21 2020 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Tue Jan 14 16:45:21 2020 - [info] MHA::MasterMonitor version 0.57. Creating directory /var/log/masterha/app1.. done. Tue Jan 14 16:45:22 2020 - [info] GTID failover mode = 0 Tue Jan 14 16:45:22 2020 - [info] Dead Servers: Tue Jan 14 16:45:22 2020 - [info] Alive Servers: Tue Jan 14 16:45:22 2020 - [info] 192.168.68.129(192.168.68.129:3306) Tue Jan 14 16:45:22 2020 - [info] 192.168.68.132(192.168.68.132:3306) Tue Jan 14 16:45:22 2020 - [info] 192.168.68.133(192.168.68.133:3306) Tue Jan 14 16:45:22 2020 - [info] Alive Slaves: Tue Jan 14 16:45:22 2020 - [info] 192.168.68.132(192.168.68.132:3306) Version=5.6.36-log (oldest major version between slaves) log-bin:enabled Tue Jan 14 16:45:22 2020 - [info] Replicating from 192.168.68.129(192.168.68.129:3306) Tue Jan 14 16:45:22 2020 - [info] Primary candidate for the new Master (candidate_master is set) Tue Jan 14 16:45:22 2020 - [info] 192.168.68.133(192.168.68.133:3306) Version=5.6.36-log (oldest major version between slaves) log-bin:enabled Tue Jan 14 16:45:22 2020 - [info] Replicating from 192.168.68.129(192.168.68.129:3306) Tue Jan 14 16:45:22 2020 - [info] Current Alive Master: 192.168.68.129(192.168.68.129:3306) Tue Jan 14 16:45:22 2020 - [info] Checking slave configurations.. Tue Jan 14 16:45:22 2020 - [warning] relay_log_purge=0 is not set on slave 192.168.68.132(192.168.68.132:3306). Tue Jan 14 16:45:22 2020 - [warning] relay_log_purge=0 is not set on slave 192.168.68.133(192.168.68.133:3306). Tue Jan 14 16:45:22 2020 - [info] Checking replication filtering settings.. Tue Jan 14 16:45:22 2020 - [info] binlog_do_db= , binlog_ignore_db= Tue Jan 14 16:45:22 2020 - [info] Replication filtering check ok. Tue Jan 14 16:45:22 2020 - [info] GTID (with auto-pos) is not supported Tue Jan 14 16:45:22 2020 - [info] Starting SSH connection tests.. Tue Jan 14 16:45:25 2020 - [info] All SSH connection tests passed successfully. Tue Jan 14 16:45:25 2020 - [info] Checking MHA Node version.. Tue Jan 14 16:45:26 2020 - [info] Version check ok. Tue Jan 14 16:45:26 2020 - [info] Checking SSH publickey authentication settings on the current master.. Tue Jan 14 16:45:26 2020 - [info] HealthCheck: SSH to 192.168.68.129 is reachable. Tue Jan 14 16:45:26 2020 - [info] Master MHA Node version is 0.57. Tue Jan 14 16:45:26 2020 - [info] Checking recovery script configurations on 192.168.68.129(192.168.68.129:3306).. Tue Jan 14 16:45:26 2020 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/usr/local/mysql/data --output_file=/tmp/save_binary_logs_test --manager_version=0.57 --start_file=master-bin.000001 Tue Jan 14 16:45:26 2020 - [info] Connecting to [email protected](192.168.68.129:22).. Creating /tmp if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /usr/local/mysql/data, up to master-bin.000001 Tue Jan 14 16:45:26 2020 - [info] Binlog setting check done. Tue Jan 14 16:45:26 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Tue Jan 14 16:45:26 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.68.132 --slave_ip=192.168.68.132 --slave_port=3306 --workdir=/tmp --target_version=5.6.36-log --manager_version=0.57 --relay_log_info=/usr/local/mysql/data/relay-log.info --relay_dir=/usr/local/mysql/data/ --slave_pass=xxx Tue Jan 14 16:45:26 2020 - [info] Connecting to [email protected](192.168.68.132:22).. Checking slave recovery environment settings.. Opening /usr/local/mysql/data/relay-log.info ... ok. Relay log found at /usr/local/mysql/data, up to relay-log-bin.000002 Temporary relay log file is /usr/local/mysql/data/relay-log-bin.000002 Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Tue Jan 14 16:45:44 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.68.133 --slave_ip=192.168.68.133 --slave_port=3306 --workdir=/tmp --target_version=5.6.36-log --manager_version=0.57 --relay_log_info=/usr/local/mysql/data/relay-log.info --relay_dir=/usr/local/mysql/data/ --slave_pass=xxx Tue Jan 14 16:45:44 2020 - [info] Connecting to [email protected](192.168.68.133:22).. Checking slave recovery environment settings.. Opening /usr/local/mysql/data/relay-log.info ... ok. Relay log found at /usr/local/mysql/data, up to relay-log-bin.000002 Temporary relay log file is /usr/local/mysql/data/relay-log-bin.000002 Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Tue Jan 14 16:46:08 2020 - [info] Slaves settings check done. Tue Jan 14 16:46:08 2020 - [info] 192.168.68.129(192.168.68.129:3306) (current master) +--192.168.68.132(192.168.68.132:3306) +--192.168.68.133(192.168.68.133:3306) Tue Jan 14 16:46:08 2020 - [info] Checking replication health on 192.168.68.132.. Tue Jan 14 16:46:08 2020 - [info] ok. Tue Jan 14 16:46:08 2020 - [info] Checking replication health on 192.168.68.133.. Tue Jan 14 16:46:08 2020 - [info] ok. Tue Jan 14 16:46:08 2020 - [info] Checking master_ip_failover_script status: Tue Jan 14 16:46:08 2020 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.68.129 --orig_master_ip=192.168.68.129 --orig_master_port=3306 IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 192.168.68.200=== Checking the Status of the script.. OK Tue Jan 14 16:46:08 2020 - [info] OK. Tue Jan 14 16:46:08 2020 - [warning] shutdown_script is not defined. Tue Jan 14 16:46:08 2020 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK.

2.2.3 Configuration steps summary

This experiment involves a lot of content, the main problems in the configuration process are as follows:

Master-slave replication: time synchronization of the server is required first;
Key Push: Pay attention to the accuracy of the IP address;
Whether the two configuration files for MHA are correct or not;

These are the places where errors are most likely, and you need to pay special attention to them.

2.2.4 Start MHA command

[root@manager conf]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 & [1] 104076

--remove_dead_master_conf This parameter indicates that the ip of the old master library will be removed from the configuration file when a master-slave switch occurs.

--manger_log log log storage location.

--ignore_last_failover By default, Failover will not occur if MHA detects a continuous downtime with less than eight hours between downtimes, and the limit is to avoid the ping-pong effect.This parameter represents the file generated by ignoring the last MHA trigger switch. By default, when MHA switches, the log directory, which is the log app1.failover.complete file set above, will not be allowed to trigger the switch if it is found to exist in that directory the next time it switches, unless the file is deleted after the first switch, which is convenientInside is set to -ignore_last_failover.

2.2.5 View MHA status

You can see that the current master is the primary service node and the ip address is 192.168.68.129

[root@manager conf]# masterha_check_status --conf=/etc/masterha/app1.cnf app1 (pid:104076) is running(0:PING_OK), master:192.168.68.129 #Or you can look at the log and discover the ip address of the primary server node [root@manager conf]# tail -2 /var/log/masterha/app1/manager.log Tue Jan 14 17:12:48 2020 - [info] Starting ping health check on 192.168.68.129(192.168.68.129:3306).. Tue Jan 14 17:12:48 2020 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

2.2.6 Verify primary server downtime

Of course, the first configuration requires manual configuration of VIP (virtual ip)

[root@master bin]# /sbin/ifconfig ens33:1 192.168.68.200/24 [root@master bin]# ifconfig ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 192.168.68.129 netmask 255.255.255.0 broadcast 192.168.68.255 inet6 fe80::bdab:b59b:d041:d8b0 prefixlen 64 scopeid 0x20<link> ether 00:0c:29:e6:6d:eb txqueuelen 1000 (Ethernet) RX packets 775098 bytes 1083035145 (1.0 GiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 413307 bytes 31715433 (30.2 MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 192.168.68.200 netmask 255.255.255.0 broadcast 192.168.68.255 ether 00:0c:29:e6:6d:eb txqueuelen 1000 (Ethernet) lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536 inet 127.0.0.1 netmask 255.0.0.0 inet6 ::1 prefixlen 128 scopeid 0x10<host> loop txqueuelen 1 (Local Loopback) RX packets 20 bytes 1568 (1.5 KiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 20 bytes 1568 (1.5 KiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 virbr0: flags=4099<UP,BROADCAST,MULTICAST> mtu 1500 inet 192.168.122.1 netmask 255.255.255.0 broadcast 192.168.122.255 ether 52:54:00:43:58:6e txqueuelen 1000 (Ethernet) RX packets 0 bytes 0 (0.0 B) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 0 bytes 0 (0.0 B) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

Use pkill-9 mysql to simulate primary server downtime to see if mysql gets vip address from server slave1

[root@slave1 bin]# ifconfig ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 192.168.68.132 netmask 255.255.255.0 broadcast 192.168.68.255 inet6 fe80::4d95:1de7:d0a5:25c4 prefixlen 64 scopeid 0x20<link> ether 00:0c:29:0d:06:80 txqueuelen 1000 (Ethernet) RX packets 766652 bytes 1074024643 (1.0 GiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 379004 bytes 29740586 (28.3 MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 192.168.68.200 netmask 255.255.255.0 broadcast 192.168.68.255 ether 00:0c:29:0d:06:80 txqueuelen 1000 (Ethernet) lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536 inet 127.0.0.1 netmask 255.0.0.0 inet6 ::1 prefixlen 128 scopeid 0x10<host> loop txqueuelen 1 (Local Loopback) RX packets 146 bytes 12570 (12.2 KiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 146 bytes 12570 (12.2 KiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 virbr0: flags=4099<UP,BROADCAST,MULTICAST> mtu 1500 inet 192.168.122.1 netmask 255.255.255.0 broadcast 192.168.122.255 ether 52:54:00:95:86:1a txqueuelen 1000 (Ethernet) RX packets 0 bytes 0 (0.0 B) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 0 bytes 0 (0.0 B) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

We can go into slave1's database to see its status

mysql> show slave status\G Empty set (0.00 sec) mysql> show master status -> ; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 1213 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

Indicates that the switch from database to primary service has been successful.

3. Summary

This article is mainly about the principles and configuration examples of MySQLMHA highly available clustering. The concepts and types of clustering will be introduced in the next article.