配合binlog-server和VIP和郵件實(shí)現(xiàn)MHA高可用

MHA各個(gè)節(jié)點(diǎn)規(guī)劃如下


節(jié)點(diǎn)

考慮點(diǎn)

各個(gè)節(jié)點(diǎn)做相互的免密登錄

安裝基于GTID復(fù)制的主庫(kù)和從庫(kù) 注意server_id不一致 從庫(kù)只讀，切換成功后新主要把read-only從配置文件中注釋掉
安裝MHA

主節(jié)點(diǎn)設(shè)置VIP 虛擬IP 漂移

建立binlogserver作為數(shù)據(jù)補(bǔ)償方案 這個(gè)也需要安裝mysql客戶端工具mysqlbinlog

主節(jié)點(diǎn)mysql宕機(jī)兩種情況：1.mysql進(jìn)程斷 binlog能正常被獲取 2.linux服務(wù)器掛掉

所以此時(shí)應(yīng)該考慮日志補(bǔ)償 即 linux主機(jī)斷 也不會(huì)影響從節(jié)點(diǎn)和新主正常獲取舊主的二進(jìn)制日志

建立郵件告警

斷掉主庫(kù) 驗(yàn)證

1.所有節(jié)點(diǎn)做相互的免密登錄

cat >~/for_public_key.sh <<END
#!/bin/bash
HOSTLIST='
10.0.0.17
10.0.0.27
10.0.0.37
10.0.0.7
'
PASS=123456
rpm -q sshpass &> /dev/null || yum -y install sshpass
[ -f ~/.ssh/id_rsa ] || ssh-keygen -P "" -f /root/.ssh/id_rsa 

for IP in \$HOSTLIST ;do
{ 
sshpass -p \${PASS} ssh-copy-id -o  StrictHostKeyChecking=no -i /root/.ssh/id_rsa.pub root@\${IP} &>/dev/null
}&
done
wait
END

2.安裝基于GTID復(fù)制 的主從復(fù)制

2.1 安裝

slave1的IP是10.0.0.27 所以我們命名server_id為27
slave2的IP是10.0.0.37 所以我們命名server_id為37
兩個(gè)slave都開啟GTID和二進(jìn)制功能 都設(shè)置read-only
主節(jié)點(diǎn)的IP是10.0.0.17 所以我們命名server_id為17

2.2 建立主從關(guān)系

master建立復(fù)制用戶

grant replication slave  on *.* to repl@'10.0.0.%' identified by '123';

slave1和slave2
編輯change master to寫入到master.info的信息

change master to 
master_host='10.0.0.17',    
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;

start slave;
show slave status\G;

3.安裝MHA

3.1 安裝MHA依賴(所有節(jié)點(diǎn)操作)

10.0.0.17master用node
10.0.0.27slave1用node
10.0.0.37slave2用manager和node
10.0.0.7 binlog_server用node
準(zhǔn)備mha-node和mha-manager放在10.0.0.37:/root下

#perl語(yǔ)言開發(fā)的連接mysql的驅(qū)動(dòng)
yum install perl-DBD-MySQL -y 

#所有節(jié)點(diǎn)安裝node
cd /root
#將node包傳送到各個(gè)節(jié)點(diǎn)
scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm 10.0.0.27:/root
scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm 10.0.0.17:/root
scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm 10.0.0.7:/root
yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm

3.2 master節(jié)點(diǎn)操作

準(zhǔn)備mha用戶

mysql -e 'grant all privileges on *.* to mha@'10.0.0.%' identified by 'mha';'
mysql -e 'select user,host from mysql.user'

3.3 manager節(jié)點(diǎn)操作

a. 準(zhǔn)備依賴 安裝manager

yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
cd /root/
yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

b. 準(zhǔn)備MHA的目錄和相關(guān)文件

#配置目錄
mkdir -p /etc/mha   
#日志目錄
mkdir -p /var/log/mha/app1  

cat > /etc/mha/app1.cnf <<EOF
[server default]
# report_script=/usr/local/bin/send_report
# master_ip_failover_script=/usr/local/bin/master_ip_failover
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1
master_binlog_dir=/mysql/3306/binlog
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root

[server1]
hostname=10.0.0.17
port=3306
[server2]
hostname=10.0.0.27
port=3306
[server3]
hostname=10.0.0.37
port=3306

[binlog1]
# no_master=1
# hostname=10.0.0.7
# master_binlog_dir=/data/mysql/binlog
EOF

c. MHA啟動(dòng)前狀態(tài)檢測(cè)

masterha_check_ssh  --conf=/etc/mha/app1.cnf    #SSH免密鑰檢查
masterha_check_repl  --conf=/etc/mha/app1.cnf   #mysql主從檢查

d. 啟動(dòng)MHA

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 &

e. 檢測(cè)MHA運(yùn)行狀態(tài)

masterha_check_status --conf=/etc/mha/app1.cnf

4.VIP+binlogserver+mail

a. 準(zhǔn)備master_ip_failover文件(manager操作)

準(zhǔn)備官網(wǎng)的VIP文件 修改#標(biāo)注的地方 并把該文件復(fù)制到/usr/local/bin 下改名master_ip_failover 方便MHA的manager調(diào)用

#!/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 = '10.0.0.55/24';
my $vip = '10.0.0.100/24';  #此處為VIP地址
my $key = '1';                          #此處為VIP的標(biāo)簽 比如eth0:1的1就是label
# my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip"; #三節(jié)點(diǎn)網(wǎng)卡名需要同名
# my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip"; #三節(jié)點(diǎn)網(wǎng)卡名需要同名,開啟VIP
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";     #關(guān)閉VIP
# my $ssh_Bcast_arp= "/sbin/arping -I ens33 -c 3 -A 10.0.0.55";
my $ssh_Bcast_arp= "/sbin/arping -I eth0 -c 3 -A 10.0.0.100";
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";
}

cp master_ip_failover /usr/local/bin/master_ip_failover
chmod +x /usr/local/bin/master_ip_failover

b.手工添加vip(master操作)

ip addr add 10.0.0.100/24 dev eth0 label eth0:1
ip a |grep eth0

c.測(cè)試VIP是否可用

另外找個(gè)機(jī)器測(cè)試

 mysql -umha -pmha -h10.0.0.100 -e 'select @@server_id;'

d.準(zhǔn)備郵件腳本

下面的內(nèi)容更改為自己的郵箱設(shè)置

#!/usr/bin/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='發(fā)件箱';
my $mail_user='發(fā)件箱名稱';
my $mail_pass='發(fā)件箱授權(quán)嗎';
#my $mail_to=['to1@qq.com','to2@qq.com'];
my $mail_to='收件人郵箱';

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;

cp -a send_report /usr/local/bin/
chmod +x /usr/local/bin/send_report

e.數(shù)據(jù)補(bǔ)償辦法：建立主庫(kù)的binlogserver

找一臺(tái)額外的機(jī)器绢陌，必須要有5.6以上的版本蝉衣，支持gtid并開啟腋粥，我們用10.0.0.7

binlog_server創(chuàng)建必要的目錄(binlog_server操作)

mkdir -p /data/mysql/binlog
chown -R mysql.mysql /data/*

拉取主庫(kù)日志到binlogserver(binlog_server操作)

cd /data/mysql/binlog #必須進(jìn)入到自己創(chuàng)建好的目錄
nohup mysqlbinlog  -R --host=10.0.0.17 --user=mha --password=mha --raw  --stop-never mysql-bin.000001 &>/dev/null &

# --host是主庫(kù)的IP --user --password是主庫(kù)設(shè)置的mha的密碼和用戶 
# 此處從mysql-bin.000001拉取所有二進(jìn)制日志跪呈，生產(chǎn)中從master的最新二進(jìn)制日志處拉取二進(jìn)制日志
# --raw: 以 binlog 格式存儲(chǔ)日志妇汗，方便后期使用;

f.更新MHA配置文件 加入調(diào)用VIP腳本與郵件腳本與binlogserver參數(shù)

cat > /etc/mha/app1.cnf <<EOF
[server default]
report_script=/usr/local/bin/send_report
master_ip_failover_script=/usr/local/bin/master_ip_failover
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1
master_binlog_dir=/mysql/3306/binlog
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root

[server1]
hostname=10.0.0.17
port=3306
[server2]
hostname=10.0.0.27
port=3306
[server3]
hostname=10.0.0.37
port=3306

[binlog1]
no_master=1
hostname=10.0.0.7
master_binlog_dir=/data/mysql/binlog
EOF

g.重啟MHA

masterha_stop --conf=/etc/mha/app1.cnf
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 &
masterha_check_status --conf=/etc/mha/app1.cnf

5.測(cè)試

a. 追蹤manager日志(manager操作)

tail -f /var/log/app1/manager.log

b. 斷開主庫(kù)

pkill mysqld

c. 查看日志(manager)

下面就是MHA在主庫(kù)10.0.0.17斷開后的切換新主的日志

root@37  ~]# tail -f /var/log/mha/app1/manager.log -n0
Sat Jan 23 21:06:41 2021 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Sat Jan 23 21:06:41 2021 - [info] Executing SSH check script: exit 0
Sat Jan 23 21:06:41 2021 - [info] HealthCheck: SSH to 10.0.0.17 is reachable.
Sat Jan 23 21:06:43 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.0.0.17' (111))
Sat Jan 23 21:06:43 2021 - [warning] Connection failed 2 time(s)..
Sat Jan 23 21:06:45 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.0.0.17' (111))
Sat Jan 23 21:06:45 2021 - [warning] Connection failed 3 time(s)..
Sat Jan 23 21:06:47 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.0.0.17' (111))
Sat Jan 23 21:06:47 2021 - [warning] Connection failed 4 time(s)..
Sat Jan 23 21:06:47 2021 - [warning] Master is not reachable from health checker!
Sat Jan 23 21:06:47 2021 - [warning] Master 10.0.0.17(10.0.0.17:3306) is not reachable!
Sat Jan 23 21:06:47 2021 - [warning] SSH is reachable.
Sat Jan 23 21:06:47 2021 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/app1.cnf again, and trying to connect to all servers to check server status..
Sat Jan 23 21:06:47 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Jan 23 21:06:47 2021 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Sat Jan 23 21:06:47 2021 - [info] Reading server configuration from /etc/mha/app1.cnf..
Sat Jan 23 21:06:48 2021 - [info] GTID failover mode = 1
Sat Jan 23 21:06:48 2021 - [info] Dead Servers:
Sat Jan 23 21:06:48 2021 - [info]   10.0.0.17(10.0.0.17:3306)
Sat Jan 23 21:06:48 2021 - [info] Alive Servers:
Sat Jan 23 21:06:48 2021 - [info]   10.0.0.27(10.0.0.27:3306)
Sat Jan 23 21:06:48 2021 - [info]   10.0.0.37(10.0.0.37:3306)
Sat Jan 23 21:06:48 2021 - [info] Alive Slaves:
Sat Jan 23 21:06:48 2021 - [info]   10.0.0.27(10.0.0.27:3306)  Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sat Jan 23 21:06:48 2021 - [info]     GTID ON
Sat Jan 23 21:06:48 2021 - [info]     Replicating from 10.0.0.17(10.0.0.17:3306)
Sat Jan 23 21:06:48 2021 - [info]   10.0.0.37(10.0.0.37:3306)  Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sat Jan 23 21:06:48 2021 - [info]     GTID ON
Sat Jan 23 21:06:48 2021 - [info]     Replicating from 10.0.0.17(10.0.0.17:3306)
Sat Jan 23 21:06:48 2021 - [info] Checking slave configurations..
Sat Jan 23 21:06:48 2021 - [info]  read_only=1 is not set on slave 10.0.0.27(10.0.0.27:3306).
Sat Jan 23 21:06:48 2021 - [info] Checking replication filtering settings..
Sat Jan 23 21:06:48 2021 - [info]  Replication filtering check ok.
Sat Jan 23 21:06:48 2021 - [info] Master is down!
Sat Jan 23 21:06:48 2021 - [info] Terminating monitoring script.
Sat Jan 23 21:06:48 2021 - [info] Got exit code 20 (Master dead).
Sat Jan 23 21:06:48 2021 - [info] MHA::MasterFailover version 0.58.
Sat Jan 23 21:06:48 2021 - [info] Starting master failover.
Sat Jan 23 21:06:48 2021 - [info]
Sat Jan 23 21:06:48 2021 - [info] * Phase 1: Configuration Check Phase..
Sat Jan 23 21:06:48 2021 - [info]
Sat Jan 23 21:06:48 2021 - [info] HealthCheck: SSH to 10.0.0.7 is reachable.
Sat Jan 23 21:06:49 2021 - [info] Binlog server 10.0.0.7 is reachable.
Sat Jan 23 21:06:50 2021 - [info] GTID failover mode = 1
Sat Jan 23 21:06:50 2021 - [info] Dead Servers:
Sat Jan 23 21:06:50 2021 - [info]   10.0.0.17(10.0.0.17:3306)
Sat Jan 23 21:06:50 2021 - [info] Checking master reachability via MySQL(double check)...
Sat Jan 23 21:06:50 2021 - [info]  ok.
Sat Jan 23 21:06:50 2021 - [info] Alive Servers:
Sat Jan 23 21:06:50 2021 - [info]   10.0.0.27(10.0.0.27:3306)
Sat Jan 23 21:06:50 2021 - [info]   10.0.0.37(10.0.0.37:3306)
Sat Jan 23 21:06:50 2021 - [info] Alive Slaves:
Sat Jan 23 21:06:50 2021 - [info]   10.0.0.27(10.0.0.27:3306)  Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sat Jan 23 21:06:50 2021 - [info]     GTID ON
Sat Jan 23 21:06:50 2021 - [info]     Replicating from 10.0.0.17(10.0.0.17:3306)
Sat Jan 23 21:06:50 2021 - [info]   10.0.0.37(10.0.0.37:3306)  Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sat Jan 23 21:06:50 2021 - [info]     GTID ON
Sat Jan 23 21:06:50 2021 - [info]     Replicating from 10.0.0.17(10.0.0.17:3306)
Sat Jan 23 21:06:50 2021 - [info] Starting GTID based failover.
Sat Jan 23 21:06:50 2021 - [info]
Sat Jan 23 21:06:50 2021 - [info] ** Phase 1: Configuration Check Phase completed.
Sat Jan 23 21:06:50 2021 - [info]
Sat Jan 23 21:06:50 2021 - [info] * Phase 2: Dead Master Shutdown Phase..
Sat Jan 23 21:06:50 2021 - [info]
Sat Jan 23 21:06:50 2021 - [info] Forcing shutdown so that applications never connect to the current master..
Sat Jan 23 21:06:50 2021 - [info] Executing master IP deactivation script:
Sat Jan 23 21:06:50 2021 - [info]   /usr/local/bin/master_ip_failover --orig_master_host=10.0.0.17 --orig_master_ip=10.0.0.17 --orig_master_port=3306 --command=stopssh --ssh_user=root


IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 10.0.0.100/24===

Disabling the VIP on old master: 10.0.0.17
Sat Jan 23 21:06:50 2021 - [info]  done.
Sat Jan 23 21:06:50 2021 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Sat Jan 23 21:06:50 2021 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Sat Jan 23 21:06:50 2021 - [info]
Sat Jan 23 21:06:50 2021 - [info] * Phase 3: Master Recovery Phase..
Sat Jan 23 21:06:50 2021 - [info]
Sat Jan 23 21:06:50 2021 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Sat Jan 23 21:06:50 2021 - [info]
Sat Jan 23 21:06:50 2021 - [info] The latest binary log file/position on all slaves is mysql-bin.000002:310
Sat Jan 23 21:06:50 2021 - [info] Retrieved Gtid Set: fdce59f6-5bde-11eb-a39b-000c29b493d9:1
Sat Jan 23 21:06:50 2021 - [info] Latest slaves (Slaves that received relay log files to the latest):
Sat Jan 23 21:06:50 2021 - [info]   10.0.0.27(10.0.0.27:3306)  Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sat Jan 23 21:06:50 2021 - [info]     GTID ON
Sat Jan 23 21:06:50 2021 - [info]     Replicating from 10.0.0.17(10.0.0.17:3306)
Sat Jan 23 21:06:50 2021 - [info]   10.0.0.37(10.0.0.37:3306)  Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sat Jan 23 21:06:50 2021 - [info]     GTID ON
Sat Jan 23 21:06:50 2021 - [info]     Replicating from 10.0.0.17(10.0.0.17:3306)
Sat Jan 23 21:06:50 2021 - [info] The oldest binary log file/position on all slaves is mysql-bin.000002:310
Sat Jan 23 21:06:50 2021 - [info] Retrieved Gtid Set: fdce59f6-5bde-11eb-a39b-000c29b493d9:1
Sat Jan 23 21:06:50 2021 - [info] Oldest slaves:
Sat Jan 23 21:06:50 2021 - [info]   10.0.0.27(10.0.0.27:3306)  Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sat Jan 23 21:06:50 2021 - [info]     GTID ON
Sat Jan 23 21:06:50 2021 - [info]     Replicating from 10.0.0.17(10.0.0.17:3306)
Sat Jan 23 21:06:50 2021 - [info]   10.0.0.37(10.0.0.37:3306)  Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sat Jan 23 21:06:50 2021 - [info]     GTID ON
Sat Jan 23 21:06:50 2021 - [info]     Replicating from 10.0.0.17(10.0.0.17:3306)
Sat Jan 23 21:06:50 2021 - [info]
Sat Jan 23 21:06:50 2021 - [info] * Phase 3.3: Determining New Master Phase..
Sat Jan 23 21:06:50 2021 - [info]
Sat Jan 23 21:06:50 2021 - [info] Searching new master from slaves..
Sat Jan 23 21:06:50 2021 - [info]  Candidate masters from the configuration file:
Sat Jan 23 21:06:50 2021 - [info]  Non-candidate masters:
Sat Jan 23 21:06:50 2021 - [info] New master is 10.0.0.27(10.0.0.27:3306)
Sat Jan 23 21:06:50 2021 - [info] Starting master failover..
Sat Jan 23 21:06:50 2021 - [info]
From:
10.0.0.17(10.0.0.17:3306) (current master)
 +--10.0.0.27(10.0.0.27:3306)
 +--10.0.0.37(10.0.0.37:3306)

To:
10.0.0.27(10.0.0.27:3306) (new master)
 +--10.0.0.37(10.0.0.37:3306)
Sat Jan 23 21:06:50 2021 - [info]
Sat Jan 23 21:06:50 2021 - [info] * Phase 3.3: New Master Recovery Phase..
Sat Jan 23 21:06:50 2021 - [info]
Sat Jan 23 21:06:50 2021 - [info]  Waiting all logs to be applied..
Sat Jan 23 21:06:50 2021 - [info]   done.
Sat Jan 23 21:06:50 2021 - [info] -- Saving binlog from host 10.0.0.7 started, pid: 26690
Sat Jan 23 21:06:51 2021 - [info]
Sat Jan 23 21:06:51 2021 - [info] Log messages from 10.0.0.7 ...
Sat Jan 23 21:06:51 2021 - [info]
Sat Jan 23 21:06:50 2021 - [info] Fetching binary logs from binlog server 10.0.0.7..
Sat Jan 23 21:06:50 2021 - [info] Executing binlog save command: save_binary_logs --command=save --start_file=mysql-bin.000002  --start_pos=310 --output_file=/var/tmp/saved_binlog_binlog1_20210123210648.binlog --handle_raw_binlog=0 --skip_filter=1 --disable_log_bin=0 --manager_version=0.58 --oldest_version=5.7.31-log  --binlog_dir=/data/mysql/binlog
  Creating /var/tmp if not exists..    ok.
 Concat binary/relay logs from mysql-bin.000002 pos 310 to mysql-bin.000002 EOF into /var/tmp/saved_binlog_binlog1_20210123210648.binlog ..
No additional binlog events found.
Event not exists.
Sat Jan 23 21:06:50 2021 - [info] Additional events were not found from the binlog server. No need to save.
Sat Jan 23 21:06:51 2021 - [info] End of log messages from 10.0.0.7.
Sat Jan 23 21:06:51 2021 - [info] No binlog events found from 10.0.0.7. Skipping
Sat Jan 23 21:06:51 2021 - [info] Getting new master's binlog name and position..
Sat Jan 23 21:06:51 2021 - [info]  mysql-bin.000001:154
Sat Jan 23 21:06:51 2021 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.27', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Sat Jan 23 21:06:51 2021 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000001, 154, fdce59f6-5bde-11eb-a39b-000c29b493d9:1
Sat Jan 23 21:06:51 2021 - [info] Executing master IP activate script:
Sat Jan 23 21:06:51 2021 - [info]   /usr/local/bin/master_ip_failover --command=start --ssh_user=root --orig_master_host=10.0.0.17 --orig_master_ip=10.0.0.17 --orig_master_port=3306 --new_master_host=10.0.0.27 --new_master_ip=10.0.0.27 --new_master_port=3306 --new_master_user='mha'   --new_master_password=xxx
Unknown option: new_master_user
Unknown option: new_master_password


IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 10.0.0.100/24===

Enabling the VIP - 10.0.0.100/24 on the new master - 10.0.0.27
Sat Jan 23 21:06:51 2021 - [info]  OK.
Sat Jan 23 21:06:51 2021 - [info] ** Finished master recovery successfully.
Sat Jan 23 21:06:51 2021 - [info] * Phase 3: Master Recovery Phase completed.
Sat Jan 23 21:06:51 2021 - [info]
Sat Jan 23 21:06:51 2021 - [info] * Phase 4: Slaves Recovery Phase..
Sat Jan 23 21:06:51 2021 - [info]
Sat Jan 23 21:06:51 2021 - [info]
Sat Jan 23 21:06:51 2021 - [info] * Phase 4.1: Starting Slaves in parallel..
Sat Jan 23 21:06:51 2021 - [info]
Sat Jan 23 21:06:51 2021 - [info] -- Slave recovery on host 10.0.0.37(10.0.0.37:3306) started, pid: 26698. Check tmp log /var/log/mha/app1/10.0.0.37_3306_20210123210648.log if it takes time..
Sat Jan 23 21:06:53 2021 - [info]
Sat Jan 23 21:06:53 2021 - [info] Log messages from 10.0.0.37 ...
Sat Jan 23 21:06:53 2021 - [info]
Sat Jan 23 21:06:51 2021 - [info]  Resetting slave 10.0.0.37(10.0.0.37:3306) and starting replication from the new master 10.0.0.27(10.0.0.27:3306)..
Sat Jan 23 21:06:51 2021 - [info]  Executed CHANGE MASTER.
Sat Jan 23 21:06:52 2021 - [info]  Slave started.
Sat Jan 23 21:06:52 2021 - [info]  gtid_wait(fdce59f6-5bde-11eb-a39b-000c29b493d9:1) completed on 10.0.0.37(10.0.0.37:3306). Executed 0 events.
Sat Jan 23 21:06:53 2021 - [info] End of log messages from 10.0.0.37.
Sat Jan 23 21:06:53 2021 - [info] -- Slave on host 10.0.0.37(10.0.0.37:3306) started.
Sat Jan 23 21:06:53 2021 - [info] All new slave servers recovered successfully.
Sat Jan 23 21:06:53 2021 - [info]
Sat Jan 23 21:06:53 2021 - [info] * Phase 5: New master cleanup phase..
Sat Jan 23 21:06:53 2021 - [info]
Sat Jan 23 21:06:53 2021 - [info] Resetting slave info on the new master..
Sat Jan 23 21:06:53 2021 - [info]  10.0.0.27: Resetting slave info succeeded.
Sat Jan 23 21:06:53 2021 - [info] Master failover to 10.0.0.27(10.0.0.27:3306) completed successfully.
Sat Jan 23 21:06:53 2021 - [info] Deleted server1 entry from /etc/mha/app1.cnf .
Sat Jan 23 21:06:53 2021 - [info]

----- Failover Report -----

app1: MySQL Master failover 10.0.0.17(10.0.0.17:3306) to 10.0.0.27(10.0.0.27:3306) succeeded

Master 10.0.0.17(10.0.0.17:3306) is down!

Check MHA Manager logs at 37:/var/log/mha/app1/manager.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 10.0.0.17(10.0.0.17:3306)
Selected 10.0.0.27(10.0.0.27:3306) as a new master.
10.0.0.27(10.0.0.27:3306): OK: Applying all logs succeeded.
10.0.0.27(10.0.0.27:3306): OK: Activated master IP address.
10.0.0.37(10.0.0.37:3306): OK: Slave started, replicating from 10.0.0.27(10.0.0.27:3306)
10.0.0.27(10.0.0.27:3306): Resetting slave info succeeded.
Master failover to 10.0.0.27(10.0.0.27:3306) completed successfully.
Sat Jan 23 21:06:53 2021 - [info] Sending mail..
Unknown option: conf

d. 查看主庫(kù)是否切換(slave操作)

10.0.0.27變成新主

mysql -e 'show slave status\G'
#上面這個(gè)查不到結(jié)果

mysql -e 'show slave hosts'
#這個(gè)可以看到slave2 10.0.0.27變成了新主 它的從庫(kù)是10.0.0.37

e. 查看VIP是否漂移(新主操作)

root@27  ~]# ifconfig eth0:1
eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.0.0.100  netmask 255.255.255.0  broadcast 10.0.0.255
        ether 00:0c:29:2a:ed:d8  txqueuelen 1000  (Ethernet)

f. 查看郵件是否收到告警

郵件

6.新主操作

如果不需要舊的服務(wù)器 新主10.0.0.27原來(lái)是從庫(kù) 配置文件的read-only需要注釋掉孩革。
恢復(fù)的話從新操作

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 人面猴
    序言：七十年代末际歼，一起剝皮案震驚了整個(gè)濱河市惶翻，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌鹅心，老刑警劉巖吕粗，帶你破解...
    沈念sama閱讀 206,214評(píng)論 6贊 481
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場(chǎng)離奇詭異巴帮，居然都是意外死亡溯泣，警方通過(guò)查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 88,307評(píng)論 2贊 382
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門榕茧，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)垃沦，“玉大人，你說(shuō)我怎么就攤上這事用押≈荆” “怎么了？”我有些...
    開封第一講書人閱讀 152,543評(píng)論 0贊 341
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵蜻拨，是天一觀的道長(zhǎng)池充。 經(jīng)常有香客問(wèn)我，道長(zhǎng)缎讼，這世上最難降的妖魔是什么收夸？ 我笑而不...
    開封第一講書人閱讀 55,221評(píng)論 1贊 279
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任，我火速辦了婚禮血崭，結(jié)果婚禮上卧惜，老公的妹妹穿的比我還像新娘厘灼。我一直安慰自己，他們只是感情好咽瓷，可當(dāng)我...
    茶點(diǎn)故事閱讀 64,224評(píng)論 5贊 371
  • 惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
    文/花漫 我一把揭開白布设凹。 她就那樣靜靜地躺著，像睡著了一般茅姜。 火紅的嫁衣襯著肌膚如雪闪朱。 梳的紋絲不亂的頭發(fā)上，一...
    開封第一講書人閱讀 49,007評(píng)論 1贊 284
  • 城市分裂傳說(shuō)
    那天钻洒，我揣著相機(jī)與錄音奋姿，去河邊找鬼。 笑死航唆，一個(gè)胖子當(dāng)著我的面吹牛胀蛮，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播糯钙，決...
    沈念sama閱讀 38,313評(píng)論 3贊 399
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼粪狼，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼！你這毒婦竟也來(lái)了任岸？” 一聲冷哼從身側(cè)響起再榄，我...
    開封第一講書人閱讀 36,956評(píng)論 0贊 259
  • 萬(wàn)榮殺人案實(shí)錄
    序言：老撾萬(wàn)榮一對(duì)情侶失蹤，失蹤者是張志新（化名）和其女友劉穎享潜，沒想到半個(gè)月后百揭，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體匈织，經(jīng)...
    沈念sama閱讀 43,441評(píng)論 1贊 300
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡济蝉，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 35,925評(píng)論 2贊 323
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年澜驮，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片艺蝴。...
    茶點(diǎn)故事閱讀 38,018評(píng)論 1贊 333
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡猬腰，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出猜敢，到底是詐尸還是另有隱情姑荷，我是刑警寧澤，帶...
    沈念sama閱讀 33,685評(píng)論 4贊 322
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布缩擂，位于F島的核電站鼠冕，受9級(jí)特大地震影響，放射性物質(zhì)發(fā)生泄漏胯盯。R本人自食惡果不足惜懈费，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,234評(píng)論 3贊 307
  • 男人毒藥：我在死后第九天來(lái)索命
    文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望博脑。 院中可真熱鬧憎乙，春花似錦薄坏、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,240評(píng)論 0贊 19
  • 一樁弒父案，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)君账。三九已至繁堡，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間乡数，已是汗流浹背椭蹄。 一陣腳步聲響...
    開封第一講書人閱讀 31,464評(píng)論 1贊 261
  • 情欲美人皮
    我被黑心中介騙來(lái)泰國(guó)打工， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留净赴，地道東北人绳矩。 一個(gè)月前我還...
    沈念sama閱讀 45,467評(píng)論 2贊 352
  • 代替公主和親
    正文 我出身青樓，卻偏偏與公主長(zhǎng)得像玖翅，于是被迫代替她去往敵國(guó)和親翼馆。 傳聞我的和親對(duì)象是個(gè)殘疾皇子，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 42,762評(píng)論 2贊 345

推薦閱讀更多精彩內(nèi)容

  • [TOC] MHA部署安裝 環(huán)境 描述IPhostnameDescMaster192.168.20.1initno...
    醉紅塵丶閱讀 429評(píng)論 0贊 1
  • 1金度、MHA高可用 1.1 高級(jí)應(yīng)用架構(gòu)演變 1应媚、基本結(jié)構(gòu)（1）一主一從（2）一主多從（3）多級(jí)主從（4）雙主（5）...
    noodlesbook閱讀 172評(píng)論 0贊 0
  • 一.MHA簡(jiǎn)介 MHA（Master High Availability）目前在MySQL高可用方面是一個(gè)相對(duì)成熟...
    只是甲閱讀 366評(píng)論 0贊 0
  • 一.軟件簡(jiǎn)介 MHA是用prel編寫的一套非常流行和使用的MySQL高可用解決方案軟件，它的作用是保證MySQL主...
    唯愛熊閱讀 910評(píng)論 0贊 7
  • 推薦指數(shù)： 6.0 書籍主旨關(guān)鍵詞：特權(quán)猜极、焦點(diǎn)中姜、注意力、語(yǔ)言聯(lián)想跟伏、情景聯(lián)想 觀點(diǎn)： 1.統(tǒng)計(jì)學(xué)現(xiàn)在叫數(shù)據(jù)分析丢胚，社會(huì)...
    Jenaral閱讀 5,701評(píng)論 0贊 5