MHA High Availability Configuration and Failover for MySQL

Posted by dicamille on Mon, 13 Jan 2020 19:00:43 +0100

Preface:
Servers running the same service are combined to be called clusters
Centralized clusters: master, slave,
MHA is used to resolve cluster master downtime.
MMM Cluster Architecture: There are two master servers
Overview of MHA

  • DeNA youshimaton (now a Facebook company) in Japan
  • A set of excellent high-availability software for failover and master-slave promotion in MySQL high-availability environment
    Composition of MHA
  • MHA Manager (Management Node)
  • MHA Node (Data Node)
    MHA features
  • During automatic failover, MHA tries to save binary logs from the downtime master server to maximize data loss
  • Using MySQL 5.5 semi-synchronous replication can significantly reduce the risk of data loss
    Case Topology

    Experimental environment
    centos7 version
    MHA version 0.57
    mysql version 5.6.36
    cmake version 2.8.6
    MHA: 192.168.191.132
    master:192.168.191.160
    slave1:192.168.191.161
    slave2:192.168.191.162
    Set up MySQL master-slave replication environment
    1. Modify hostname to distinguish servers




    The installation and configuration of the three mysql servers are consistent, so take master for example
    Install compile-dependent environments

    Install the cmake compiler



    Install mysql database


    Next is the make && make install process
    Optimize mysql database path, create mysql user, initialize database



    Under vim/etc/my.cnf:
    Modify master master master profile for master server

    Modify slave1 master profile from server

    Modify slave2 master profile from server

    Two soft connections between master and slave servers for easy operation

    Start MySQL from Master-Slave Server

    Create master-slave synchronization user myslave
    mysql> grant replication slave on *.* to 'myslave'@'192.168.191.%' identified by '123';
    Query OK, 0 rows affected (0.00 sec)

    Grant all databases mha users permission to operate on the database

    mysql> grant all privileges on *.* to 'mha'@'192.168.191.%' identified by 'manager';
    Query OK, 0 rows affected (0.00 sec)
    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)
    #Refresh Database
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

    View on the mysql master server as a forbidden file and synchronization point. Do not add anything to the database at this time, as this will cause

    Perform synchronization from the server

    mysql> change master to master_host='192.168.191.160',master_user='myslave',master_password='123',master_log_file='master-bin.000002',master_log_pos=12215;
    Query OK, 0 rows affected, 2 warnings (0.00 sec)
    mysql> start slave;
    Query OK, 0 rows affected (0.01 sec)
    mysql> show slave status\G;
    ···Omit some content
             Slave_IO_Running: Yes      //If this is Slave_IO_Running: Connecting, check to see if the firewall on the primary server is turned off
            Slave_SQL_Running: Yes
    ···Omit some content

    Set two slave servers to read-only mode

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

    Install Node components (all servers, version 0.57)

    Install perl environment

    MHA is different for each operating system version, centos7.4 must select version 0.57, the node component must be installed on all servers, and the manager component must be installed on the MHA-manager node, because the manager depends on the node component
    Install Node Components

    [root@master ~]# tar zxvf /abc/mha/mha4mysql-node-0.57.tar.gz
    [root@master ~]# cd mha4mysql-node-0.57/
    [root@master mha4mysql-node-0.57]# perl Makefile.PL
    [root@master mha4mysql-node-0.57]# make
    [root@master mha4mysql-node-0.57]# make install

    Generate several script tools under / usr/local/bin/after node installation (these tools are usually triggered by MHA Manager scripts and do not require human action)

    save_binary_logs: Save and copy master's binaries
    apply_diff_relay_logs: Identify differences in relay log events and apply their differences to other slave s
    filter_mysqlbinlog: Remove unnecessary ROLLBACK rollback events (MHA no longer uses this tool)
    purge_relay_logs: Clears relay logs (does not block SQL threads)
    Install the Manager component (version 0.57 on the manager node)

    [root@manager ~]# tar zxvf /abc/mha/mha4mysql-manager-0.57.tar.gz
    [root@manager ~]# cd mha4mysql-manager-0.57/
    [root@manager mha4mysql-manager-0.57]# perl Makefile.PL
    [root@manager mha4mysql-manager-0.57]# make
    [root@manager mha4mysql-manager-0.57]# make install

    Several script tools are generated below/usr/local/bin/after manager is installed

    masterha_check_ssh: Check the SHH configuration of MHA
    masterha_check_repl: Check MySQL replication status
    masterha_manager: Script to start Manager
    masterha_check_status: Detect the current MHA running state
    masterha_master_monitor: Detect if master is down
    masterha_master_switch: Control failover (automatic or manual)
    masterha_conf_host: Add or remove configuration server information
    masterha_stop: Turn off manager
    Configure Password-Free Authentication
    Tools: ssh-keygen, ssh-copy-id
    (1) Configure password-free authentication for all data nodes on manager

    [root@manager ~]# ssh-keygen -t rsa
    #Enter at all times
    [root@manager ~]# ssh-copy-id 192.168.191.160
    [root@manager ~]# ssh-copy-id 192.168.191.161
    [root@manager ~]# ssh-copy-id 192.168.191.162

    (2) Password-free authentication configured on master to database nodes slave1 and slave 2

    #Enter at all times
    [root@master ~]# ssh-copy-id 192.168.191.161
    [root@master ~]# ssh-copy-id 192.168.191.162

    (3) Password-free authentication configured to database nodes master and slave2 on slave1

    [root@slave1 ~]# ssh-keygen -t rsa
    #Enter at all times
    [root@slave1 ~]# ssh-copy-id 192.168.191.160
    [root@slave1 ~]# ssh-copy-id 192.168.191.162

    (4) Password-free authentication configured to database nodes master and slave1 on slave2

    [root@slave2 ~]# ssh-keygen -t rsa
    #Enter at all times
    [root@slave2 ~]# ssh-copy-id 192.168.191.160
    [root@slave2 ~]# ssh-copy-id 192.168.191.161

    Configure MHA (on the manager node)
    Copy the script to the /usr/local/bin directory

    [root@manager ~]# cp -ra /root/mha4mysql-manager-0.57/samples/scripts /usr/local/bin

    Four execution files are copied

    master_ip_failover: script for VIP management when switching automatically
    master_ip_online_change: vip management during online switching
    power_manager: A script to shut down the host after a failure occurs
    send_report: A script that sends an alert after failover
    (2) Copy the above automatic switch VIP management scripts to/usr/local/bin directory and use scripts to manage VIP

    [root@manager ~]# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin

    Modify master_ip_failover script (delete original content, rewrite)

    [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#########################################
    #Set Drift IP
    my $vip = '192.168.191.200';
    my $brdc = '192.168.191.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";
    }

    Note: First configuration requires manual turning on virtual IP on master

    [root@master ~]# /sbin/ifconfig ens33:1 192.168.191.200/24

    (4) Create MHA software catalog and copy configuration file

    [root@manager ~]# mkdir /etc/masterha
    [root@manager ~]# cp /root/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/masterha/
    [root@manager ~]# vim /etc/masterha/app1.cnf 
    [server default]
    #manager Profile
    manager_log=/var/log/masterha/app1/manager.log     
    #manager log
    manager_workdir=/var/log/masterha/app1
    #Master saves the binlog in the same location as the bilog configured in master
    master_binlog_dir=/home/mysql
    #Set the switch script for automatic failover.That's the script above
    master_ip_failover_script=/usr/local/bin/master_ip_failover
    #Set switch script for manual switch
    master_ip_online_change_script=/usr/local/bin/master_ip_online_change
    #This password is the one you created earlier to monitor users
    password=manager
    ping_interval=1
    remote_workdir=/tmp
    #Set Replication User Password
    repl_password=123
    #Set up users for replication users
    repl_user=myslave
    #Set script to alert when a switch occurs
    secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.150.158 -s 192.168.150.244
    #Set Up Failure Shutdown Failure Script Host
    shutdown_script=""
    #Set ssh login user name
    ssh_user=root
    #Set up monitoring users
    user=mha
    [server1]
    hostname=192.168.150.240
    port=3306
    [server2]
    #Set as candidate master, if this parameter is set, the sender master slave will be upgraded from the library to the master library after the sender slave switch
    candidate_master=1
    #By default, MHA will not choose a slave as the new master if it falls behind master's relay logs of 100M
    check_repl_delay=0
    hostname=192.168.150.158
    port=3306
    [server3]
    hostname=192.168.150.244
    port=3306

    test
    SSH Free Interactive Logon (manager node)

    [root@manager ~]# masterha_check_ssh -conf=/etc/masterha/app1.cnf
    ···Omit some content
    #Output success if normal
    Sun Jan 12 19:19:11 2020 - [info] All SSH connection tests passed successfully.

    Start MHA to see MHA status

    #Start MHA and run in the background
    [root@manager ~]# 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] 13525
    #Looking at the MHA status, you can see that the current master is a mysql node
    [root@manager ~]# masterha_check_status --conf=/etc/masterha/app1.cnf
    app1 (pid:13525) is running(0:PING_OK), master:192.168.191.200
  • - remove_dead_master_conf: This parameter indicates that the old ip will be removed from the configuration file when a master-slave switch occurs
  • - ignore_last_failover: By default, failover will not occur if MHA detects a successive downtime with less than 8 hours of downtime interval. This restriction is to avoid ping-pong effect, which means ignoring files generated after the last MHA triggered the switch. By default, MHA switches to the log directory, which is aboveSet the appl.failover.complete file, which will not be allowed to trigger a switch if it is found to exist in the directory next time you switch, unless you receive the deletion after the first switch. For convenience, set this to -ignore_last_failover
    Start monitoring observation logging on manager

    Analog failure

    Execute Stop mysql Service on master of Primary Library
    [root@master ~]# pkill -9 mysqld

    manager detects that the primary server is down and switches the standby primary server to the primary server

    View IP address on slave1

    [root@slave1 ~]# ifconfig
    ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.191.160  netmask 255.255.255.0  broadcast 192.168.191.255
    ···
    #Virtual IP address translation to alternate primary server
    ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.191.200  netmask 255.255.255.0  broadcast 192.168.191.255
    ···

    At this time, the client can log in through the virtual IP address, or log in.
    The MHA high availability configuration and failover experiments are complete.

Topics: MySQL ssh Database vim