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

Related package links:

Links: https://pan.baidu.com/s/1VNdEIYvT1g_xKbrhzYNI-A
Extraction Code: wmyg

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

  1. 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
  2. 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 root@192.168.68.129(192.168.68.129:22) to root@192.168.68.132(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 root@192.168.68.129(192.168.68.129:22) to root@192.168.68.133(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 root@192.168.68.133(192.168.68.133:22) to root@192.168.68.129(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 root@192.168.68.133(192.168.68.133:22) to root@192.168.68.132(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 root@192.168.68.132(192.168.68.132:22) to root@192.168.68.129(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 root@192.168.68.132(192.168.68.132:22) to root@192.168.68.133(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 root@192.168.68.129(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 root@192.168.68.132(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 root@192.168.68.133(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.

Tags: MySQL ssh Database vim

Posted on Tue, 14 Jan 2020 12:43:37 -0500 by nor0101