mysql下MHA搭建過(guò)程

這篇文章主要介紹“下MHA搭建過(guò)程”，在日常操作中，相信很多人在mysql下MHA搭建過(guò)程問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對大家解答”mysql下MHA搭建過(guò)程”的疑惑有所幫助！接下來(lái)，請跟著(zhù)小編一起來(lái)學(xué)習吧！

環(huán)境

1主2從，manager放在從庫

主庫：192.168.137.201

從庫（manager ）：192.168.137.202

從庫：192.168.137.203

需要的軟件包：

epel-release-7-10.noarch.rpm

mha4mysql-node-0.56.tar.gz

mha4mysql-manager-0.56.tar.gz

mysql-5.7.14-linux-glibc2.5-x86_64.tar.gz

主從搭建

• 創(chuàng )建用戶(hù)

create user 'repl'@'%' identified by 'repl';

grant all on *.* to 'repl'@'%';

create user 'root'@'%' identified by 'root';

grant all on *.* to 'root'@'%';

• 建立主從

CHANGE MASTER TO

  MASTER_HOST='192.168.137.201',

  MASTER_USER='repl',

  MASTER_PASSWORD='repl',

  MASTER_PORT=3306,

  master_auto_position=1;

配置ssh互相信任

c1

[root@c1 ~]# ssh-keygen -t rsa

[root@c1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.201

[root@c1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.202

[root@c1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.203

c2服務(wù)器

[root@c2 ~]# ssh-keygen -t rsa

[root@c2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.201

[root@c2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.202

[root@c2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.203

c3服務(wù)器

[root@c3 ~]# ssh-keygen -t rsa

[root@c3 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.201

[root@c3 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.202

[root@c3 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.203

安裝MHA軟件

安裝epel源（所有節點(diǎn)）

centos6

 rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

centos7

 rpm -ivh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-10.noarch.rpm

安裝MHA軟件

• 安裝依賴(lài)包（所有節點(diǎn)）

yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN net-tools -y

epel源的所有epel，epel-debuginfo，epel-source，都啟用

• 軟件下載

MHA軟件下載

https://downloads.mariadb.com/MHA/

https://github.com/yotoobo/linux/tree/master/mha

• 安裝MHA-node（所有節點(diǎn)）

tar zxf mha4mysql-node-0.56.tar.gz

cd mha4mysql-node-0.56

perl Makefile.PL

make && make install

• 安裝MHA-manager（202）

tar zxf mha4mysql-manager-0.56.tar.gz

cd mha4mysql-manager-0.56

perl Makefile.PL

make && make install

• 配置MHA-manager-conf文件（202）

vi /data/mha/3306/log/mha.cnf

[server default]

client_bindir=/usr/local/mysql/bin/

manager_log=/data/mha/3306/log/manager.log

manager_workdir=/data/mha/3306/log

master_binlog_dir=/data/mysql/mysql3306/logs

master_ip_failover_script=/usr/local/bin/master_ip_failover

master_ip_online_change_script=/usr/local/bin/master_ip_online_change

report_script=/usr/local/bin/send_report

#init_conf_load_script=/usr/local/bin/load_cnf

remote_workdir=/data/mysql/mysql3306/tmp

#secondary_check_script= /usr/local/bin/masterha_secondary_check -s 192.168.137.201 -s 192.168.137.202

user=root

password=root

ping_interval=3

repl_user=repl

repl_password=repl

ssh_port=22

ssh_user=root

#max_ping_errors=40

[server1]

hostname=192.168.137.201

port=3306

[server2]

#check_repl_delay=0

hostname=192.168.137.202

port=3306

[server3]

candidate_master=1

check_repl_delay=0

hostname=192.168.137.203

port=3306

• 設置從庫readonly（所有從庫）

mysql> show global variables like "%read_only%";

mysql> flush tables with read lock;

mysql> set global read_only=1;

mysql> show global variables like "%read_only%";

• 拷貝腳本

cp /usr/local/mha_manager/samples/scripts/master_ip_failover /usr/local/bin/

cp /usr/local/mha_manager/samples/scripts/master_ip_online_change /usr/local/bin/

cp /usr/local/mha_manager/samples/scripts/power_manager /usr/local/bin/

cp /usr/local/mha_manager/samples/scripts/send_report /usr/local/bin/

• 修改master_ip_failover腳本

原腳本中無(wú)VIP切換

#!/usr/bin/env perl

#  Copyright (C) 2011 DeNA Co.,Ltd.

#

#  This program is free software; you can tribute it and/or modify

#  it under the terms of the GNU General Public License as published by

#  the Free Software Foundation; either version 2 of the License, or

#  (at your option) any later version.

#

#  This program is distributed in the hope that it will be useful,

#  but WITHOUT ANY WARRANTY; without even the implied warranty of

#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

#  GNU General Public License for more details.

#

#  You should have received a copy of the GNU General Public License

#   along with this program; if not, write to the Free Software

#  Foundation, Inc.,

#  51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA

## Note: This is a sample script and is not complete. Modify the script based on your environment.

use strict;

use warnings FATAL => 'all';

use Getopt::Long;

use MHA::DBHelper;

my (

  $command,          $ssh_user,        $orig_master_host, $orig_master_ip,

  $orig_master_port, $new_master_host, $new_master_ip,    $new_master_port

);

my $vip='192.168.137.88/24';

my $key="1";

my $ssh_start_vip ="/sbin/ifconfig ens33:$key $vip";

my $ssh_stop_vip="/sbin/ifconfig ens33:$key down";

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 {

  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 \n";

         &stop_vip();

      $exit_code = 0;

    };

    if ($@) {

      warn "Got Error: $@\n";

      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 \n";

         &start_vip();

      $exit_code = 0;

    };

    if ($@) {

      warn $@;

      # If you want to continue failover, exit 10.

      exit $exit_code;

    }

    exit $exit_code;

  }

  elsif ( $command eq "status" ) {

    print "Checking the Status of the script.. ok \n";

    # do nothing

    exit 0;

  }

  else {

    &usage();

    exit 1;

  }

}

sub start_vip(){

         `ssh $ssh_user\@$new_master_host \ " $ssh_start_vip \"`;

}

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";

}

• 檢測配置文件（202節點(diǎn)）

[root@c2 ~]# masterha_check_repl --conf=/data/mha/3306/log/mha.cnf

• 主庫添加VIP

[root@c1 ~]# ifconfig ens33:1 192.168.137.88/16

或者/sbin/ip addr add 192.168.137.88/32 dev ens33

刪除VIP  ifconfig ens33:1 down

/sbin/ip addr del 192.168.137.88/32 dev ens33

• MHA manager啟動(dòng)和關(guān)閉

[root@c2 ~]#  nohup masterha_manager --conf=/data/mha/3306/log/mha.cnf < /dev/null > /data/mha/3306/log/manager.log 2>&1 &

[root@c2 ~]#  masterha_stop --conf=/data/mha/3306/log/mha.cnf

注意項

• 所有主從數據完全一致，包含業(yè)務(wù)數據和系統表數據，不然會(huì )導致故障切換途中，無(wú)法切到新主庫中

• 切換VIP時(shí)，需要各個(gè)服務(wù)器都有net-tools包，能夠執行ifconfig