MHA搭建步驟

狗福發表於2018-04-04

1.主機及例項IP：          

Manager      : 192.168.100.110          
Master       : 10.200.3.2:3306          
Slave1       : 10.200.3.3:3306          
Slave2       : 10.200.3.4:3306          
VIP          : 10.200.3.5

2.Slave配置引數：         

log_bin = /home/my3306/log/mysql-bin         
read_only=1           
relay_log_purge=0          #（一主一叢不需要此項，兩從及以上建議開次引數，防止切換為成主庫的從庫自動刪除中繼日誌後，無法給其他 從庫應用這部分日誌）

3.配置主從同步：

msyql>grant replication slave on *.* to `repl_17zuoye`@`%` identified by `office.repl.17zuoye`;
mysql>flush privileges;          #（三個節點都需要配置，以用於當某個slave升為主後其他的從進行同步）           
mysql>change master to master_host=`10.200.3.2`,master_user=`dtstack`,master_port=3306,master_password=`abc123`,master_log_file=`logbin.000014`,master_log_pos=70980879;
mysql>start slave;

4.配置SSH互信，四臺伺服器之間互通：

# ssh-keygen -t rsa         
# ssh-copy-id -i .ssh/id_rsa.pub root@10.200.3.2

5.四個節點安裝EPEL源以及相關yum包：          

# rpm -ivh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm          
# yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perlTime-HiRes

6.下載安裝mha包

# wget https://code.google.com/p/mysql-master-ha/wiki/Downloads?tm=2          
manager節點：                    
# rpm –ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
# rpm –ivh mha4mysql-node-0.56-0.el6.noarch.rpm          
node 節點：                    
# rpm –ivh mha4mysql-node-0.56-0.el6.noarch.rpm

7. mysql建立與授權mha使用者

GRANT ALL PRIVILEGES ON *.* TO `mha`@`%` IDENTIFIED BY `mhamha`;         
flush privileges;

8.manager節點建立相關目錄和配置檔案：

# tree /mha          
/mha         
├── app1         
│   ├── app1.conf         
│   └── manager.log         
└── conf              
├── master_ip_failover_3306              
├── master_ip_online_change              
└── send_report         
2 directories, 5 files

9.相關配置檔案內容：

# cat app1.conf
[server default]
manager_workdir = /mha/app1
manager_log = /mha/app1/manager.log
remote_workdir = /mha/app1
master_ip_failover_script=/mha/conf/master_ip_failover_3306 #master failover時執行
report_script=/mha/conf/send_report            #master failover時執行,傳送郵件使用
master_ip_online_change_script=/mha/conf/master_ip_online_change
#master_switchover時執行（手動切換）
user=mha
password=mhamha
ping_interval=1
ping_type=CONNECT
repl_password=office.repl.17zuoye
repl_user=repl_17zuoye
ssh_port=22
ssh_user=root
[server1]
hostname = 10.200.3.2
port=3306
master_binlog_dir = /database1/data_5.6.17_3306/binlog
candidate_master = 1
#這個伺服器有較高的優先順序提升為新的master(還要具備：開啟binlog,複製沒有延遲)
[server2]
hostname = 10.200.3.3
port=3306
master_binlog_dir = /database1/data_5.6.17_3306/binlog
candidate_master =1
ignore_fail=1
[server3]
hostname = 10.200.3.4
port=3306
master_binlog_dir = /database1/data_5.6.17_3306/binlog
candidate_master =1
ignore_fail=1
#如果slave存在故障，在主庫出現問題時預設情況下mha不會進行故障切換，該引數即設定MHA會在所有的機器有問題的時間也會進行故障切換。
no_master=1
#從不將這臺主機轉換為master

10.主庫啟動一個虛擬IP

# /sbin/ifconfig em1:0 10.200.3.5/23 up

刪除vip：                   

# ip addr del 10.200.3.5/23 dev em1;

11.失敗切換指令碼

# cat master_ip_failover_3306
#!/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.200.3.5/23`;  # Virtual IP
my $key = "0";
my $ssh_start_vip = "/sbin/ifconfig em1:$key $vip";
my $start_new_master_vip = "/sbin/ifconfig em1:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig em1:$key down";
my $arp = "/usr/sbin/arping -A -q -c 2 -I em1:$key 10.200.3.5";

#虛擬IP配置，在哪一個網路卡上，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 "

IN SCRIPT TEST====$ssh_stop_vip==$start_new_master_vip===

";
    if ( $command eq "stop" || $command eq "stopssh" ) {
        # $orig_master_host, $orig_master_ip, $orig_master_port are passed.
        # If you manage master ip address at global catalog database,
        # invalidate orig_master_ip here.
        my $exit_code = 1;
        eval {
            print "Disabling the VIP on old master: $orig_master_host 
";
            &stop_vip();
            $exit_code = 0;
        };
        if ($@) {
            warn "Got Error: $@
";
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "start" ) {
        # all arguments are passed.
        # If you manage master ip address at global catalog database,
        # activate new_master_ip here.
        # You can also grant write access (create user, set read_only=0, etc) here.
        my $exit_code = 10;
        eval {
            print "Enabling the VIP - $vip on the new master - $new_master_host 
";
            &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 
";
       # `ssh $ssh_user@tm01.okooo.cn " $ssh_start_vip "`;
        exit 0;
    }
    else {
        &usage();
        exit 1;
    }
}
# A simple system call that enable the VIP on the new master
sub start_vip() {
    `ssh root@$new_master_host " $ssh_start_vip "`;
    `ssh root@$new_master_host " $arp "`;
}
# A simple system call that disable the VIP on the old_master
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
";
}

12.手動線上switch指令碼：

# cat master_ip_online_change
#!/usr/bin/env perl 
use strict; 
use warnings FATAL =>`all`; 
 
use Getopt::Long; 
 
my $vip = `10.200.3.5/23`;  # Virtual IP 
my $key = "0"; 
my $ssh_start_vip = "/sbin/ifconfig em1:$key $vip"; 
my $ssh_stop_vip = "/sbin/ifconfig em1:$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_master_port=i`       => $orig_master_port, 
  `orig_master_user=s`       => $orig_master_user, 
  `orig_master_password=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`    => $new_master_password, 
  `new_master_ssh_user=s`    => $new_master_ssh_user, 
); 
 
exit &main(); 
 
sub main { 
#print "

IN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===

"; 
 
if ( $command eq "stop" || $command eq "stopssh" ) { 
 
        # $orig_master_host, $orig_master_ip, $orig_master_port are passed. 
        # If you manage master ip address at global catalog database, 
        # invalidate orig_master_ip here. 
        my $exit_code = 1; 
        eval { 
            print "


***************************************************************
"; 
            print "Disabling the VIP - $vip on old master: $orig_master_host
"; 
            print "***************************************************************



"; 
&stop_vip(); 
            $exit_code = 0; 
        }; 
        if ($@) { 
            warn "Got Error: $@
"; 
            exit $exit_code; 
        } 
        exit $exit_code; 
} 
elsif ( $command eq "start" ) { 
 
        # all arguments are passed. 
        # If you manage master ip address at global catalog database, 
        # activate new_master_ip here. 
        # You can also grant write access (create user, set read_only=0, etc) here. 
my $exit_code = 10; 
        eval { 
            print "


***************************************************************
"; 
            print "Enabling the VIP - $vip on new master: $new_master_host 
"; 
            print "***************************************************************



"; 
&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 
"; 
        `ssh $orig_master_ssh_user@$orig_master_host " $ssh_start_vip "`; 
        exit 0; 
}  
else { 
&usage(); 
        exit 1; 
} 
} 
 
# A simple system call that enable the VIP on the new master 
sub start_vip() { 
`ssh $new_master_ssh_user@$new_master_host " $ssh_start_vip "`; 
} 
# A simple system call that disable the VIP on the old_master 
sub stop_vip() { 
`ssh $orig_master_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=po 
rt –new_master_host=host –new_master_ip=ip –new_master_port=port
"; 
}

13.failover後的傳送郵件指令碼：

# cat send_report
#!/bin/bash 
source /root/.bash_profile  
orig_master_host=`echo "$1" | awk -F = `{print $2}``  
new_master_host=`echo "$2" | awk -F = `{print $2}``  
new_slave_hosts=`echo "$3" | awk -F = `{print $2}``  
subject=`echo "$4" | awk -F = `{print $2}``  
body=`echo "$5" | awk -F = `{print $2}``  #判斷日誌結尾是否有successfully，有則表示切換成功，成功與否都發郵件。  
tac /mha/app1/manager.log | sed -n 2p | grep `successfully` > /dev/null  
if [ $? -eq 0 ]    
  then   
  echo -e "MHA $subject 主從切換成功
 master:$orig_master_host --> $new_master_host 
 $body 
 當前從庫:$new_slave_hosts" | mailx -s "MySQL例項宕掉,MHA $subject 切換成功" yujie.zhang@17zuoye.com  
else    
  echo -e "MHA $subject 主從切換失敗
 master:$orig_master_host --> $new_master_host 
 $body" | mailx -s "MySQL例項宕掉,MHA $subject 切換失敗" yujie.zhang@17zuoye.com  
fi

14.將指令碼修改屬主陣列，並且增加執行許可權。

# chown mysql.mysql ./*         
# chmod +x ./*

15.檢查SSH配置         

# masterha_check_ssh --conf=/mha/app1/app1.conf         
Tue Jan  5 17:16:41 2016 - [info] All SSH connectiontests passed successfully.

16. 檢查MHA配置

[root@VM-TEST-110 conf]# masterha_check_repl --conf=/mha/app1/app1.conf          MySQL Replication Health is OK.

17.啟動MHA：         

# masterha_manager --conf=/mha/app1/app1.conf

18. 發生failover主從切換後，MHAmanager服務會自動停掉       

且在manager_workdir目錄下面生成檔案app1.failover.complete，若要啟動MHA，必須先確保無此檔案)         

# ll         
total 80         
-rw-r--r-- 1 mysql mysql   556 Aug 29 11:23 app1.conf         
-rw-r--r-- 1 root  root      0 Aug 29 15:33 app1.failover.complete         
-rw-r--r-- 1 root  root  69838 Aug 29 15:33 manager.log         
-rw-r--r-- 1 root  root    143 Aug 29 15:33 saved_master_binlog_from_192.168.100.111_3306_20160829153340.binlog

19.線上手動切換主從：

如果MHA在執行，需先停止MHA      

然後再檢查MHA當前置      

# masterha_check_repl --conf=/mha/app1/app1.conf

20.手動切換 

如果不指定new_master_host，則根據配置檔案app1.cnf選出new_master_host，但new_master_port預設是3306 

masterha_master_switch --master_state=alive --conf=/mha/app1/app1.conf  --orig_master_is_new_slave -running_updates_limit=3600 --interactive=0

以下為切換時指定了new_master_host和new_master_port 

masterha_master_switch --master_state=alive --conf=/mha/app1/app1.conf  --orig_master_is_new_slave -running_updates_limit=3600 --interactive=0 --new_master_host=10.200.3.2 --new_master_port=3306

引數 –running_updates_limit 如果現在的master執行寫操作的執行時間大於這個引數，或者任何一臺slave的 Seconds_Behind_Master大於這個引數，那麼master switch將自動放棄，預設引數為1s 

引數 –interactive=0 非互動切換，建議加上，可大大加快切換速度，加上後庫不忙時大概3秒內切換完成。     

注意：

    1、如果需要將現有的從庫修改為從，再啟動mha的時候可能會報錯，     

Wed Sep  7 12:18:56 2016 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln671] Master 192.168.100.111:3306 from which slave 10.200.3.2(10.200.3.2:3306) replicates is not defined in the configuration file!

需要在主庫上reset slave，stop slave即可

    2、切換指令碼可在MHA官網檢視。


相關文章