Database Architecture: One Primary and Two Subordinate

master: 192.168.0.11
slave1: 192.168.0.201
slave2: 192.168.0.202

MHA software: Download address

mha4mysql-manager-0.57.tar.gz
mha4mysql-node-0.57.tar.gz

The Manager Toolkit mainly includes the following tools:

Masha_check_ssh Checks the SSH configuration of MHA
 Masha_check_repl checks MySQL replication status
 Masha_manger starts MHA
 Masha_check_status detects the current MHA status
 Masha_master_monitor detects whether the master is down or not
 Master ha_master_switch control failover (automatic or manual)
Masha_conf_host adds or deletes configuration server information

Node toolkits (which are usually triggered by MHA Manager scripts without human manipulation) mainly include the following tools:

save_binary_logs saves and replicates master's binary logs
 apply_diff_relay_logs identifies different relay log events and applies their different events to other slave s
 filter_mysqlbinlog removes unnecessary ROLLBACK events (MHA no longer uses this tool)
purge_relay_logs clears relay logs (no blocking of SQL threads)

Configuration of msyql5.7
1. In addition to the configuration file, like the three servers, you only need to modify the server-id and log-bin.
Note: The binlog-do-db and replicate-ignore-db settings must be the same. MHA detects filtering rules at startup. If the filtering rules are different, MHA does not start monitoring and failover. There are no settings here.

server-id=266
log-bin=mysql-bin
gtid_mode = on
#To open gtid, master and slave must be fully open
enforce_gtid_consistency = 1
log_slave_updates = 1
#Turn on semi-synchronous replication or the primary key error will be reported when the master and slave are automatically switched on
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

2. Start the server and configure the master and slave.

mysql 5.7 loads the validate_password module by default to control password length and rules. It can be closed in the configuration file with validate_password = off, or set global validate_password_policy=0 is executed on the mysql command line; password rules are cancelled when it comes.

Configure replication users and monitoring users on three servers, all three servers need to be added.

Add replicate users

Monitoring Users

grant all privileges on . to 'root'@'192.168.0.%' identified by '123456';

Execute on master

show master status;

Keep in mind the numbers of File and Position columns

Execute on salve1 and salve2

change master to master_host='192.168.0.11',master_user='repl',master_password='123456',master_log_file='mysql-master.000001',master_log_pos=194;
#Set read-only from the server, do not write in the configuration file, focus!!! Otherwise, accidentally write data from the server, you cry.
set global read_only=1

Configuration of master and slave is completed

II. Installation of MHA
1. Install dependency packages

yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN

2. Slve2 is used as the monitoring server here. Because the virtual machine is turned on, the three computers can hardly be carried. The manager can be made a server by himself if he has the resources.

Install manager (on slave2)

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

Install node (all three servers are installed)

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

When the installation is complete, scripts will be generated under the / usr/local/bin directory, as mentioned earlier.

Copy the script to the / usr/local/bin directory (executed by the manager server)

cp -ra /root/mha4mysql-manager-0.57/samples/scripts /usr/local/bin

master_ip_failover     #If we use keepalived, we can write our own scripts to manage vip, such as monitoring mysql. If MySQL is abnormal, we can stop keeping alived, so VIP will drift automatically.
master_ip_online_change    #The management of vip in online switching is not necessary. Similarly, it can be accomplished by writing a simple shell on its own.
power_manager     #The script to shut down the host after a failure occurs is not required
send_report       #It is not necessary to send an alarm script after failover. It can be completed by writing a simple shell by itself.

3. Configure SSH login without password authentication

On master

ssh-keygen -t rsa
sh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.0.201
sh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.0.202

On slave1

ssh-keygen -t rsa
sh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.0.11
sh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.0.202

On slave2

ssh-keygen -t rsa
sh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.0.11
sh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.0.201

3. configure MHA
Modify / usr/local/bin/master_ip_failover, where vip is managed using scripts
Copy all the following code and modify it according to your actual situation.

#!/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
);

my $vip = '192.168.0.88/24';  #The virtual ip you want to set up here
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig enp0s3:$key $vip"; #Change the name of your network card here
my $ssh_stop_vip = "/sbin/ifconfig enp0s3:$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_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 \"`;
}
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 --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}

4. Add and modify MHA configuration file

mkdir -p /etc/masterha
cp mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/masterha/
cat /etc/masterha/app1.cnf 
[server default]
manager_workdir=/var/log/masterha/app1.log              //Setting up the manager's working directory
manager_log=/var/log/masterha/app1/manager.log          //Setting up the manager's log
master_binlog_dir=/data/mysql                         //Set up the location where the master saves the binlog so that MHA can find the master's log, which is the data directory of mysql.
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 the handover script for manual handover
password=123456         //Set the password of the root user in mysql, which is the password created previously to monitor the user
user=root               Setting up monitoring users root
ping_interval=1         //Set up monitor main library, send ping package time interval, default is 3 seconds, try three times without response automatically railover
remote_workdir=/tmp     //Set the location of binlog for remote mysql when handover occurs
repl_password=123456    //Setting the password of the replicated user
repl_user=repl          //Setting the replication user name in the replication environment
report_script=/usr/local/send_report    //Set the script for alarm sent after handover
secondary_check_script= /usr/local/bin/masterha_secondary_check -s 192.168.0.201 -s 192.168.0.202  
shutdown_script=""      //Set up the failure host script to close after the failure occurs (the main function of the script is to close the host and put it in the case of brain fissure, which is not used here)
ssh_user=root           //Setting ssh login username

[server1]
hostname=192.168.0.11
port=3306

[server2]
hostname=192.168.0.201
port=3306
candidate_master=1   //Set it as a candidate master, and if this parameter is set, the slave library will be elevated to the master library after master-slave switching occurs, even if the master library is not the latest slave of events in the cluster.
check_repl_delay=0   //By default, if a slave lags behind relay logs of master 100M, MHA will not choose the slave as a new master because it takes a long time to recover the slave. By setting check_repl_delay=0, the MHA trigger switch will ignore the replication delay when selecting a new master. This parameter is not applicable to the host with candidate_master=1. This is often useful because the candidate master must be a new master in the process of handover.

[server3]
hostname=192.168.0.202
port=3306

5. Testing Connections
Testing ssh connections

masterha_check_ssh –conf=/etc/masterha/app1.cnf

Test the connection of mysq cluster

masterha_check_repl –conf=/etc/masterha/app1.cnf

Check the status of MHA

masterha_check_status –conf=/etc/masterha/app1.cnf

Start MHA

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 &

remove_dead_master_conf This parameter represents that when a master-slave switch occurs, the ip of the old master library will be removed from the configuration file.
manger_log log log storage location
By default, failure_last_Failover will not occur if MHA detects a continuous downtime with less than eight hours between downtimes. The reason for this limitation is to avoid the ping-pong effect. This parameter means ignoring the file generated by the last MHA trigger switch. By default, MHA will generate app1.failover.complete file in the log directory after the switch occurs, that is, the / data I set up above. If this file exists in the directory next time, it will not be allowed to trigger the switch unless it is deleted after the first switch, for convenience. Set it to ignore_last_failover.

Stop MHA

masterha_stop –conf=/etc/masterha/app1.cnf

6. Check the start-up status of MHA

7. Use the ip addr command to see if there is a virtual ip 192.168.0.88 on the master
ip addr

ip addr
enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 08:00:27:9a:ef:e5 brd ff:ff:ff:ff:ff:ff
    inet 192.168.0.11/24 brd 192.168.0.255 scope global dynamic enp0s3
       valid_lft 66813sec preferred_lft 66813sec
    inet 192.168.0.88/24 brd 192.168.0.255 scope global secondary enp0s3:1
       valid_lft forever preferred_lft forever
    inet6 fe80::a00:27ff:fe9a:efe5/64 scope link 
       valid_lft forever preferred_lft forever

8. Kill master, slave 1 and salve 2 become master and slave 2, vip is transplanted to slave 1 successfully.

9. Repair master and let the suspended master continue to use as slave.

grep -i "All other slaves should start" manager.log 


[info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.0.11', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';

Execute on the suspended master (192.168.0.11)

CHANGE MASTER TO MASTER_HOST='192.168.0.11', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123456';

Stop to the current master slave process (192.168.0.201)

mysql -e -u root -p123456 'stop slave'
mysql -e -u root -p123456 'reset slave'

Then modify the MHA configuration file to configure the new architecture into the new MHA configuration file, check the status of the cluster, and finally start MHA, according to the previous instructions to modify, no longer posted here.