MHA MySQL High-availability cluster setup

software version
mysql:5.7
mha:0.56
centos:7.4

MySQL server
master:192.168.1.224 mysql01
slave1:192.168.1.225 mysql02
slave2:192.168.1.226 MYSQL03

MHA
manager:192.168.1.224 mysql01
node1:192.168.1.225 mysql02
node2:192.168.1.226 MYSQL03

Vip
192.168.1.221

Configuring host Resolution

cat /etc/hosts192.168.1.224   mysql01192.168.1.225   mysql02192.168.1.226   mysql03

Build MySQL master-slave replication cluster on three servers
1. Configuring the MySQL Yum installation source

cat /etc/yum.repos.d/MySQL.repo [mysql57-community]name=MySQL 5.7 Community Serverbaseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/enabled=1gpgcheck=0gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

2. Install MySQL

yum install mysql-community-{server,client,common,libs}-*

3. Modify the configuration file

#Masterserver-id=1gtid_mode=onenforce_gtid_consistency=onlog_binbinlog_format=row#Slave1server-id=2log_bingtid_mode=onenforce_gtid_consistency=onlog_slave_updates=1binlog_format=row#Slave2server-id=3log_bingtid_mode=onenforce_gtid_consistency=onlog_slave_updates=1binlog_format=row

4. Start MySQL and get the initial password

5. Modify the root password and create a copy account on three servers

mysql -u root -pset sql_log_bin=0;alter user [email protected]‘localhost‘ identified by ‘MySQL5.7‘;create user [email protected]‘192.168.1.%‘ identified by ‘MySQL5.7‘grant all on *.* to [email protected]‘192.168.1.%‘;flush privileges; set sql_log_bin=1;

6. Specify slave master, start slave, view status

change master to master_host=‘192.168.1.224‘,master_user=‘root‘,master_password=‘MySQL5.7‘,master_auto_position=1;start slave;show slave status\G

Password-free login on three servers

ssh-keygen -t rsassh-copy-id mysql01ssh-copy-id mysql02ssh-copy-id mysql03

Installing MHA
1, configure the Epel source, install the dependency package

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repoyum install -y perl-CPAN* perl-DBD-MySQL* perl-Params-Validate perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes

2. Install node on three servers

tar -zxvf mha4mysql-node-0.56.tar.gz cd mha4mysql-node-0.56perl Makefile.PLmake && make install

3. Install manager in Mater

tar -zxvf mha4mysql-manager-0.57.tar.gz perl Makefile.PLmake make install

4. Creating directories and configuration Files

mkdir -p /usr/local/mhamkdir -p /etc/mhacd /etc/mha/cat /etc/mha/mha.conf ----------------------------------------------------------------------------------------------------[server default]user=rootpassword=MySQL5.7manager_workdir=/usr/local/mhamanager_log=/usr/local/mha/manager.logremote_workdir=/usr/local/mhassh_user=rootrepl_user=rootrepl_password=MySQL5.7ping_interval=1master_ip_failover_script=/usr/local/scripts/master_ip_failovermaster_ip_online_change_script=/usr/local/scripts/master_ip_online_change[server1]hostname=192.168.1.224ssh_port=22master_binlog_dir=/var/lib/mysqlcandidate_master=1port=3306[server2]hostname=192.168.1.225ssh_port=22master_binlog_dir=/var/lib/mysqlcandidate_master=1port=3306[server3]hostname=192.168.1.226ssh_port=22master_binlog_dir=/var/lib/mysqlno_master=1port=3306----------------------------------------------------------------------------------------------------

5, create directories and scripts, pay attention to the specific situation to fix IP and network card name

Mkdir-p/usr/local/scriptscat/usr/local/scripts/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_hos  T, $new _master_ip, $new _master_port);  My $vip = ' 192.168.1.221/24 ';  My $key = ' 0 ';  My $ssh _start_vip = "/sbin/ifconfig ens160: $key $vip";  My $ssh _stop_vip = "/sbin/ifconfig ens160: $key down"; 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 _mast Er_port, ' new_master_host=s ' = \ $new _master_host, ' new_master_ip=s ' + \ $new _master_ip, ' New_mas  Ter_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 ([email protected]) {warn "Got Error: [email protected]\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 ([email protected]) {warn [email protected];          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 \";      } Sub Stop_vip () {return 0 unless ($ssh _user);  ' 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--or   Ig_master_ip=ip--orig_master_port=port--new_master_host=host--new_master_ip=ip--new_master_port=port\n "; }----------------------------------------------------------------------------------------------------cat/usr/ Local/scripts/master_ip_online_change-------------------------------------------------------------------------  ---------------------------#!/usr/bin/env perl use strict;  Use warnings FATAL = ' all ';  Use Getopt::long;  My $vip = ' 192.168.1.221/24 ';  # Virtual IP My $key = "0";  My $ssh _start_vip = "/sbin/ifconfig ens160: $key $vip"; My $ssh _STOP_VIP ="/sbin/ifconfig ens160: $key down";  My $exit _code = 0;         My ($command, $orig _master_is_new_slave, $orig _master_host, $orig _master_ip, $orig _master_port,        $orig _master_user, $orig _master_password, $orig _master_ssh_user, $new _master_host, $new _master_ip,  $new _master_port, $new _master_user, $new _master_password, $new _master_ssh_user,);     GetOptions (' command=s ' + \ $command, ' orig_master_is_new_slave ' + \ $orig _master_is_new_slave, ' orig_master_host=s ' + \ $orig _master_host, ' orig_master_ip=s ' + \ $orig _master_ip, ' Orig_ma Ster_port=i ' + \ $orig _master_port, ' orig_master_user=s ' + \ $orig _master_user, ' ORIG_MASTER_PASSW        Ord=s ' + \ $orig _master_password, ' orig_master_ssh_user=s ' + \ $orig _master_ssh_user, ' new_master_host=s '     = \ $new _master_host, ' new_master_ip=s ' + \ $new _master_ip, ' new_master_port=i '   + \ $new _master_port, ' new_master_user=s ' + \ $new _master_user, ' new_master_password=s ' and ' = $n  Ew_master_password, ' new_master_ssh_user=s ' = \ $new _master_ssh_user,);  Exit &main (); Sub Main {#print "\n\nin SCRIPT test==== $ssh _stop_vip== $ssh _start_vip===\n\n"; if ($command eq "Stop" | | $command eq "STOPSSH") {# $orig _master_host, $orig _master_ip, $orig _master_port is          Passed.          # If you manage the master IP address at global Catalog database, # invalidate orig_master_ip here.          My $exit _code = 1;              eval {print "\n\n\n***************************************************************\n";              Print "Disabling the vip-$vip on Old master: $orig _master_host\n";  print "***************************************************************\n\n\n\n";              &STOP_VIP ();          $exit _code = 0;          }; if ([email protected]) {warn "Got Error: [EMAIL&NBSp;protected]\n ";          Exit $exit _code;  } exit $exit _code;          } elsif ($command eq "Start") {# All arguments is passed.          # If you manage the master IP address at global Catalog database, # Activate NEW_MASTER_IP here.  # can also grant Write access (create user, set read_only=0, etc) here.          My $exit _code = 10;              eval {print "\n\n\n***************************************************************\n";              print "Enabling the vip-$vip on new master: $new _master_host \ n";  print "***************************************************************\n\n\n\n";              &START_VIP ();          $exit _code = 0;          };              if ([email protected]) {warn [email protected];          Exit $exit _code;  } exit $exit _code; }elsif ($command eq "status") {print "Checking the status of the script.          OK \ n "; ' SSH $orig _master_ssh_user\@$orig _master_host \ "$ssh _start_vip \";  Exit 0;          } else {&usage ();  Exit 1; }} # A simple system call, enable the VIP on the new master sub Start_vip () {' ssh $new _master_ssh_user\@ $new _mas  Ter_host \ "$ssh _start_vip \"; # A Simple System "call" Disable the VIP on the Old_master sub Stop_vip () {' ssh $orig _master_ssh_user\@ $orig _mast  Er_host \ "$ssh _stop_vip \"; } Sub Usage {print "Usage:master_ip_failover–command=start|stop|stopssh|status–orig_master_host=host–orig_master_i  P=ip–orig_master_port=po rt–new_master_host=host–new_master_ip=ip–new_master_port=port\n "; }----------------------------------------------------------------------------------------------------chmod +x/ usr/local/scripts/*

6. Check the configuration

/usr/local/bin/masterha_check_ssh --conf=/etc/mha/mha.conf

7, add VIP on Master, start MHA, check status

ip addr add 192.168.1.221 dev ens160nohup masterha_manager --conf=/etc/mha/mha.conf > /tmp/mha_manager.log  < /dev/null 2>&1 &masterha_check_status --conf=/etc/mha/mha.conf

Note: The Mha.failover.complete file needs to be deleted after switching, otherwise the switchover will fail

Reference document: MySQL mainstream high-availability architecture in the enterprise the MHA of the actual battle trilogy

MHA MySQL High-availability cluster setup