MySQL MHA high availability technology

Time:2022-5-9

The specific construction environment of MHA is as follows:

Role IP address host name

VIP                                192.168.1.169   

manager                        192.168.1.170                                170

master                            192.168.1.171                                171

slave01                          192.168.1.172                                172

slave02                          192.168.1.173                                173

#Once the Master goes down, the alternative master will be promoted to a new master, and the slave will point to the new master

1. Install and configure MHA

1.1 to prepare the environment, first install the EPEL source and install all dependencies

yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager –skip-broken

1.2 modify host resolution

[[email protected] ~]# hostnamectl set-hostname 170    #Modify all host names

[[email protected] ~]# vim /etc/hosts 

192.168.1.170 170

192.168.1.171 171

192.168.1.172 172

192.168.1.173 173

1.3 configure all hosts to mutually SSH login without password authentication

[[email protected] ~]# ssh-keygen -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (/ root /. SSH / id_rsa): Enter

Enter passphrase (empty for no passphrase): Enter

Enter same passphrase again: Enter

[[email protected] ~]# ssh-copy-id 192.168.1.170

[[email protected] ~]# ssh-copy-id 192.168.1.171

[[email protected] ~]# ssh-copy-id 192.168.1.172

[[email protected] ~]# ssh-copy-id 192.168.1.173

//Repeat the above operation for 170, 171, 172, 173 and 174 hosts

1.4 verify mutual trust among nodes

[[email protected] ~]# ssh 192.168.1.171 date && ssh 192.168.1.172 date && ssh 192.168.1.173 date && ssh 192.168.1.170 date

1.5 upload MHA related packages and install MHA node on all nodes

[[email protected] ~]# mkdir /soft

[[email protected] soft]# scp  /soft/MHA-2019-6.28.zip [email protected]:/soft/

[[email protected] soft]# scp  /soft/MHA-2019-6.28.zip [email protected]:/soft/ 

[[email protected] soft]# scp  /soft/MHA-2019-6.28.zip [email protected]:/soft/                                         

[[email protected] soft]# unzip MHA-2019-6.28.zip

[[email protected] soft]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

//After installation, the following script files will be generated in the / usr / bin / directory

[[email protected] soft]# cd /usr/bin

[[email protected] bin]# ll app* filter* purge* save*

-r-xr-xr-x 1 root root 15498 Apr 20 10:05 apply_diff_relay_logs

-r-xr-xr-x 1 root root  4807 Apr 20 10:05 filter_mysqlbinlog

-r-xr-xr-x 1 root root  7401 Apr 20 10:05 purge_relay_logs

-r-xr-xr-x 1 root root  7263 Apr 20 10:05 save_binary_logs

2. Configure MySQL master-slave synchronization (one master and multiple slaves)

2.1 install MySQL on both master and slave nodes

[[email protected] ~]# rpm -ivh mysql57-community-release-el7-10.noarch.rpm

[[email protected] ~]# yum install mysql-community-server -y

[[email protected] ~]# systemctl start mysqld

[[email protected] ~]#systemctl enable mysqld

//If you need a password for MySQL login, please check this file

[[email protected] ~]# grep ‘temporary password’ /var/log/mysqld.log

//Log in to MYSQL to reconfigure the password

[[email protected] ~]# mysql -uroot -p’password’

mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘MyNewPass4!’;

2.2 create a database to be synchronized:

mysql> create database ha;

mysql> use ha;

mysql> create table test(id int,name varchar(20));

2.3 configure the master database server:

vim /etc/my.cnf

log-bin=mysql-bin-master171

server-id=171#Local database ID

binlog-do-db=ha   #Libraries that can be copied from the server. Binary database name to be synchronized

binlog-ignore-db=mysql  #Libraries that cannot be copied from the server

validate-password=off    #Turn off password policy

//Authorization:

mysql> grant replication slave on *.* to [email protected]’192.168.1.%’ identified by ‘password’;

mysql> flush privileges;

//Restart MySQL:

systemctl restart mysqld

//View status information:

mysql> show master status;

//Export database to slave server

[[email protected] ~]# mysqldump -uroot -p –databases ha > ha.sql

[[email protected] ~]# scp ha.sql [email protected]:/root

[[email protected] ~]# scp ha.sql [email protected]:/root

2.4 configure the slave service and import the database in the master data server

[[email protected] ~]# mysql -uroot -p < ha.sql

vim /etc/my.cnf          #Configure my cnf

log-bin=mysql-slave172 #Enable binary logging

server-id=172    #Local database ID

binlog-do-db=ha  #Libraries that can be copied from the server. Binary database name to be synchronized

binlog-ignore-db=mysql  #Libraries that cannot be copied from the server

log_slave_updates=1 #Only open log_ slave_ Updates, the slave database binlog will record the operation log of master database synchronization

validate-password=off

//Authorization

mysql> grant replication slave on *.* to ‘repl’@’192.168.1.%’ identified by ‘password’;

mysql> flush privileges;

[ [email protected] ~]#Systemctl restart MySQL D restart MySQL

//Establish master-slave relationship

mysql> stop slave;

mysql> change master to master_host=’192.168.1.171′,master_user=’repl’,master_password=’password’;

mysql> start slave;

mysql> show slave status \G; # view slave status

Slave_ IO_ Running: one is responsible for IO communication with the host

Slave_ SQL_ Running: responsible for your own slave MySQL process

Two yes is successful!

//173 configuration is the same as above

2.5 set read for two slave servers_ Only (read service is provided from the library, so it is not written into the configuration file because slave will be promoted to master at any time)

[[email protected] ~]# mysql -uroot -ppassword -e  ‘set global read_only=1’

Or MySQL > set global read_ only=1;

[[email protected] ~]# mysql -uroot -pTalent*19871988 -e ‘set global read_only=1’

2.6 create monitoring user (after the master database is created, the slave database will be automatically created)

mysql> grant all privileges on *.* to ‘mha’@’192.168.1.%’ identified  by ‘pwassword’;

mysql> flush  privileges;

3. Manager Configuration

3.1 manager software installation

[[email protected] ~]# yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes #Installation dependency

[[email protected] ~]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm    #MHA manager package

//After installation, the following script files will be generated under the / usr / bin directory

[[email protected] ~]# ll /usr/bin/mast*

-Rwxr-xr-x 1 root 13 December 1995 2012 / usr / bin / masterha_ check_ repl

-Rwxr-xr-x 1 root 1779 December 13, 2012 / usr / bin / masterha_ check_ ssh

-Rwxr-xr-x 1 root 1865 December 13, 2012 / usr / bin / masterha_ check_ status

-Rwxr-xr-x 1 root 3201 December 13, 2012 / usr / bin / masterha_ conf_ host

-Rwxr-xr-x 1 root 2517 December 13, 2012 / usr / bin / masterha_ manager

-Rwxr-xr-x 1 root 2165 December 13, 2012 / usr / bin / masterha_ master_ monitor

-Rwxr-xr-x 1 root 2373 December 13, 2012 / usr / bin / masterha_ master_ switch

-Rwxr-xr-x 1 root 3879 December 13, 2012 / usr / bin / masterha_ secondary_ check

-Rwxr-xr-x 1 root 1739 December 13, 2012 / usr / bin / masterha_ stop

Manger configuration file preparation

[[email protected] ~]# mkdir -p /etc/mha

[[email protected] ~]# mkdir -p /var/log/mha/app1

vim/etc/mha/app1.cnf

[server default]

manager_log=/var/log/mha/app1/manager

manager_workdir=/var/log/mha/app1

master_binlog_dir=/var/lib/mysql

user=mha

password=”password”

ping_interval=2

repl_password=”password”

repl_user=repl

ssh_user=root

[server1]

hostname=192.168.1.171

port=3306

[server2]

hostname=192.168.1.172

port=3306

[server3]

hostname=192.168.1.173

port=3306

3.3 verifying mutual trust

[[email protected] ~]# masterha_check_ssh –conf=/etc/mha/app1.cnf         #Mutual trust check

Thu Jul  8 01:42:00 2021 – [info] All SSH connection tests passed successfully.

3.4 master slave status inspection

[[email protected] ~]# masterha_check_repl –conf=/etc/mha/app1.cnf          #Master slave status check

MySQL Replication Health is OK.

3.5 start Manager

[[email protected] ~]# nohup masterha_manager –conf=/etc/mha/app1.cnf –remove_dead_master_conf –ignore_last_failover </dev/null> /var/log/mha/app1/manager.log 2>&1 &

3.6 viewing MHA status

[[email protected] ~]# masterha_check_status –conf=/etc/mha/app1.cnf

app1 (pid:7350) is running(0:PING_OK), master:192.168.1.171


4. Simulated fault

4.1 fault simulation and treatment

[[email protected] ~]# pkill mysqld    #Stop main library

[[email protected] ~]# tail -f /var/log/mha/app1/manager          #The normal switching is successful only when successful is displayed at the end.

Started automated(non-interactive) failover.

The latest slave 192.168.1.172(192.168.1.172:3306) has all relay logs for recovery.

Selected 192.168.1.172(192.168.1.172:3306) as a new master.

192.168.1.172(192.168.1.172:3306): OK: Applying all logs succeeded.

192.168.1.173(192.168.1.173:3306): This host has the latest relay log events.

Generating relay diff files from the latest slave succeeded.

192.168.1.173(192.168.1.173:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.1.172(192.168.1.172:3306)

192.168.1.172(192.168.1.172:3306): Resetting slave info succeeded.

Master failover to 192.168.1.172(192.168.1.172:3306) completed successfully.

4.2 viewing the status of main library

[[email protected] ~]# masterha_check_status –conf=/etc/mha/app1.cnf

app1 (pid:7676) is running(0:PING_OK), master:192.168.1.172

//Viewing the status of 173 slave Libraries

mysql> show slave status \G;

Master_Host: 192.168.1.172      #Slave points to the new master library

4.3 repair the main warehouse

[[email protected] ~]# systemctl restart mysqld

[[email protected] ~]# vim /var/log/mha/app1/manager    #Check the log records

[[email protected] ~]# mysql -uroot -p

mysql> CHANGE MASTER TO MASTER_HOST=’192.168.1.171′, MASTER_PORT=3306, MASTER_LOG_FILE=’mysql-slave171.000002′, MASTER_LOG_POS=154, MASTER_USER=’repl’, MASTER_PASSWORD=’xxx’;

mysql> start slave;

mysql> show slave status \G;   #A new master library has been specified

4.4 modify manger configuration file

vim /etc/mha/app1.cnf

[server1]

hostname=192.168.1.171

port=3306

4.5 start Manager

nohup masterha_manager –conf=/etc/mha/app1.cnf –remove_dead_master_conf –ignore_last_failover </dev/null> /var/log/mha/app1/manager.log 2>&1 &


5. VIP function of MHA

5.1 upload script and modify content (/ usr / local / bin)

[[email protected] ~]# ls

anaconda-ks. cfg            email_ 2019 – latest zip    MHA-2019-6.28. zip                        mha4mysql-node-0.56-0. el6. noarch. rpm

Atlas-2.2.1.el6.x86_64.rpm  master_ip_failover.txt  mha4mysql-manager-0.56-0.el6.noarch.rpm  mysql57-community-release-el7-10.noarch.rpm

[[email protected] ~]# cp ./master_ip_failover.txt  /usr/local/bin/master_ip_failover

//Modify file

[[email protected] ~]# vim /usr/local/bin/master_ip_failover

my $vip = ‘192.168.1.169/24’;

my $key = ‘1’;

my $ssh_start_vip = “/sbin/ifconfig ens192:$key $vip”;

my $ssh_stop_vip = “/sbin/ifconfig eth192:$key down”;

5.2 modification authority and format

[[email protected] ~]# dos2unix /usr/local/bin/master_ip_failover

dos2unix: converting file /usr/local/bin/master_ip_failover to Unix format …

[[email protected] ~]# chmod +x /usr/local/bin/master_ip_failover

5.3 change the manager configuration file:

[[email protected] ~]# vim /etc/mha/app1.cnf

Add: Master_ ip_ failover_ script=/usr/local/bin/master_ ip_ failover

5.4 when configuring VIP for the first time, you need to manually generate VIP in the main library

[[email protected] ~]# ifconfig ens192:1 192.168.1.169 #Open Vip

[[email protected] ~]# ifconfig ens192:1 down  #Close VIP

5. Restart the manager

[[email protected] ~]# masterha_stop –conf=/etc/mha/app1.cnf

[[email protected] ~]# nohup masterha_manager –conf=/etc/mha/app1.cnf –remove_dead_master_conf –ignore_last_failover </dev/null> /var/log/mha/app1/manager.log 2>&1 &

[[email protected] ~]# mysql -uroot -p -h 192.168.1.169   #Test VIP login MySQL


6、MHA Send_ Report fault email reminder

6.1 add mail script

[[email protected] ~]# cd /usr/local/bin/       

[[email protected] bin]# vim /usr/local/bin/send_report

#!/usr/bin/env perl

use strict;

use warnings FATAL => ‘all’;

use Mail::Sender;

use Getopt::Long;

#new_master_host and new_slave_hosts are set only when recovering master succeeded

my ( $dead_master_host, $new_master_host, $new_slave_hosts, $subject, $body );

my $smtp=’smtp.qq.com’;

my $mail_from=’[email protected]’;

my $mail_user=’[email protected]’;

my $mail_ pass=’*******’; # authorization code

#my $mail_to=[‘[email protected]’,’[email protected]’];

my $mail_to=’[email protected]’;

GetOptions(

  ‘orig_master_host=s’ => \$dead_master_host,

  ‘new_master_host=s’  => \$new_master_host,

  ‘new_slave_hosts=s’  => \$new_slave_hosts,

  ‘subject=s’          => \$subject,

  ‘body=s’            => \$body,

);

# Do whatever you want here

mailToContacts($smtp,$mail_from,$mail_user,$mail_pass,$mail_to,$subject,$body);

sub mailToContacts {

    my ($smtp, $mail_from, $mail_user, $mail_pass, $mail_to, $subject, $msg ) = @_;

    open my $DEBUG, “>/tmp/mail.log”

        or die “Can’t open the debug    file:$!\n”;

    my $sender = new Mail::Sender {

        ctype      => ‘text/plain;charset=utf-8’,

        encoding    => ‘utf-8’,

        smtp        => $smtp,

        from        => $mail_from,

        auth        => ‘LOGIN’,

        TLS_allowed => ‘0’,

        authid      => $mail_user,

        authpwd    => $mail_pass,

        to      => $mail_to,

        subject    => $subject,

        debug      => $DEBUG

    };

    $sender->MailMsg(

        {

            msg => $msg,

            debug => $DEBUG

        }

    ) or print $Mail::Sender::Error;

    return 1;

}

exit 0;

[[email protected] bin]# chmod +x *

6.2 modify the manager configuration file and call the mail script

vim /etc/mha/app1.cnf

report_script=/usr/local/bin/send     #Add a new row

6.3 Restart Manager

[[email protected] ~]# masterha_stop –conf=/etc/mha/app1.cnf

[[email protected] ~]# nohup masterha_manager –conf=/etc/mha/app1.cnf –remove_dead_master_conf –ignore_last_failover </dev/null> /var/log/mha/app1/manager.log 2>&1 &

6.4 simulate the main database failure and verify the email prompt

MySQL MHA high availability technology


7、MHA binlog server

7.1 introduction to additional parameters of manager

Note: who will take over when the main library goes down?

1. All slave node logs are consistent. By default, a new master will be selected in the order of configuration files.

2. If the slave node logs are inconsistent, automatically select the slave database closest to the master database

3. If a weight is set for a node (candidate_master = 1), the weight node will be selected first. However, if the log volume of this node lags behind that of the main database by 100m, it will not be selected. Can cooperate with check_ repl_ Delay = 0, turn off the check of log volume and force the selection of candidate nodes.

(1)  ping_ Interval = 1# set the time interval for monitoring the main database and sending Ping packets. When there is no response for three attempts, it will automatically fail over

(2) candidate_ Master = 1# set as the candidate master. If this parameter is set, the slave database will be promoted to the master database after the master-slave switch occurs, even if the master database is not the latest slave in the cluster

(3)check_ repl_ Delay = 0# by default, if a slave lags behind the relay logs of the master by 100m, MHA will not select the slave as a new master, because the recovery of the slave takes a long time. Set check_ repl_ Delay = 0, MHA triggers the switch. When selecting a new master, the replication delay will be ignored. This parameter is set to candidate_ The host with master = 1 is very useful because the candidate master must be a new master in the process of switching

7.2 configuring MHA binlog server

Binlogserver configuration:If we want to use the same host ID as the source library, we must find an additional version of gt174 and support it directly

[[email protected] ~]vim /etc/mha/app1.cnf

[binlog1]

no_master=1

hostname=192.168.1.174

master_binlog_dir=/data/mysql/binlog

//Create the necessary directory, which cannot be consistent with the source binlog directory

[[email protected] ~]# mkdir -p /data/mysql/binlog

[[email protected] ~]# chown -R mysql.mysql /data/mysql/binlog/

After the modification is completed, pull the binlog from the main database (starting from 00000 1, the subsequent binlogs will be automatically pulled in order)

Pull binlog log of main database

[[email protected] ~]cd /data/mysql/binlog     #You must enter the directory you created

[[email protected] ~]mysqlbinlog -R –host=192.168.1.172 –user=mha –password=mha –raw –stop-never mysql-slave172.000001 &   #Note: the starting point of pulling logs should be mysql-slave172, which is the binary log point obtained from the library 00000 1, passed[ [email protected] ~]#Ll / var / lib / MySQL #master binary log path

7.3 restart MHA

[[email protected] ~]# masterha_stop –conf=/etc/mha/app1.cnf

[[email protected] ~]#nohup masterha_manager –conf=/etc/mha/app1.cnf –remove_dead_master_conf –ignore_last_failover </dev/null> /var/log/mha/app1/manager.log 2>&1 &

7.4 main database flush logs verification binlog server synchronization logs

flush logs;

show master status;

[[email protected] ~]ll /data/mysql/binlog   #Verify whether binlog is synchronized

7.5 troubleshooting

When the main database goes down, binlogserver will stop automatically and the manager will stop automatically. Treatment idea:

1. Retrieve the binlog of the new master database to binlogserver

2. Reconfigure file binlog server information

3. Finally, start MHA