MySQL MHA應(yīng)用實(shí)踐（方案實(shí)戰(zhàn)）

1、環(huán)境與條件
已存在主從架構(gòu)環(huán)境

主從確保主從狀態(tài)正常

mysql>show slave status\G;
IO與SQL進(jìn)程為YES

參數(shù)更改

relay_log_purge = 0                 #不自動(dòng)刪除relay log 以便于宕機(jī)修復(fù)數(shù)據(jù)
log_bin = /data/log/leo-bin      #從庫(kù)開(kāi)啟binlog  以便于宕機(jī)修復(fù)數(shù)據(jù)
expire_logs_days=7
log-slave-updates=1

2、配置SSH
MHA在運(yùn)行是會(huì)通過(guò)SSH服務(wù)連接到其他節(jié)點(diǎn)服務(wù)器進(jìn)行檢測(cè)或復(fù)制數(shù)據(jù)，因此需要集群內(nèi)可免密碼登陸。
在leo1-3內(nèi)分別執(zhí)行如下命令：

    ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub 192.168.3.2
ssh-copy-id -i ~/.ssh/id_rsa.pub 192.168.3.3
ssh-copy-id -i ~/.ssh/id_rsa.pub 192.168.3.4

3、安裝MHA Node
對(duì)所有mysql節(jié)點(diǎn)安裝MHA Node

        1）安裝perl語(yǔ)言
         yum install perl-DBD-MySQL  -y   ###因?yàn)镸HA用perl 開(kāi)發(fā)
         2）安裝MHA Node
         rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
     下載地址
         https://code.google.com/archive/p/mysql-master-ha/downloads
         3）創(chuàng)建命令軟連接方便使用：
         ls -s /app/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
         ls -s /app/mysql/bin/mysql  /usr/bin/mysql
         4）檢查MHA賬戶
         grant all privileges on *.* to mha@'192.168.3%'  identified by 'mha';
         select user,host from mysql.user;

4、MHA-Manager
Manager 可以安裝在任何一臺(tái)機(jī)器上。

    1）使用epel源安裝perl環(huán)境
        wget -O /etc/yum.repo.d/epel.repo  http://mirrors.aliyum.com/repo/eprl-6.repo
    2）安裝管理節(jié)點(diǎn)以來(lái)包
        yum intall -u perl-Config-Tiny* epel-release perl-Log-Dispatch* perl-Parallel* per-Time*
    3）安裝MHA-Node
            rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
    4）安裝MHA-Manger
        rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

5、配置MHA管理節(jié)點(diǎn)

        mkdir -p /etc/mha
        mkdir -p /var/log/mha/app1
        vi /etc/mha/app1.cnf
        [server default]
        manager_log=/var/log/mha/app1/manager.log   
        manager_workdir=/var/log/mha/app1.log
        mater_binlog_dir=/app/mysql/data   ##MHA保存主庫(kù)binlog路徑
        user=mha                                          ##mysql 數(shù)據(jù)庫(kù)授權(quán)yonghu
        password=mha
        ping_interval=2                                ##監(jiān)控ping包時(shí)間間隔
        repl_user=rep                                  ##主從復(fù)制用戶
        repl_password=leo123
        ssh_user=root
        repot_script=/usr/local/send_report      ##故障發(fā)生后觸發(fā)腳本
        secondary_check_script=/usr/local/bin/masterha_secondary_check -s leo3 -s leo2 --user=root --master_host=leo1 --master_ip=192.168.3.2 --master_port=3306
        ########manager 聯(lián)系不上leo1時(shí)會(huì)通過(guò)leo2、leo3 查看leo1的狀態(tài)
        shutdown_script=""
        [server1]
        hostname=192.168.3.2
        port=3306
        [server2]
        hostname=192.168.3.4
        port=3306
        candidate_master=1             ##設(shè)定此參數(shù)后server2 會(huì)將優(yōu)先升為主庫(kù)
        check_repl_delay=0              ##MHA忽律主從復(fù)制延遲
        [server3]
        hostname=192.168.3.3
        port=3306

6、啟動(dòng)及測(cè)試

1）檢測(cè)ssh免密登陸

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

2）檢測(cè)主從復(fù)制狀態(tài)

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

3）啟動(dòng)MHA

    nohup master_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failver < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
    ##remove_dead_master_conf 在配置文件中刪除master信息
    ##ignore_last_failver  忽律上一次故障切換

7、配置VIP漂移

   1）利用keepalived
    2）在配置文件中填入以下參數(shù)
    master_ip_failover_script=/usr/local/bin/master_ip_failover
    3）在腳本中加入以下參數(shù)
    my = $vip= '192.168.3.6';
    my $key=  0
    my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
    my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

    chmod +x /etc/mha/master_ip_failover