mysql下MHA搭建过程

58次阅读
没有评论

共计 6561 个字符,预计需要花费 17 分钟才能阅读完成。

这篇文章主要介绍“mysql 下 MHA 搭建过程”,在日常操作中,相信很多人在 mysql 下 MHA 搭建过程问题上存在疑惑,丸趣 TV 小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”mysql 下 MHA 搭建过程”的疑惑有所帮助!接下来,请跟着丸趣 TV 小编一起来学习吧!

环境

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

主从搭建

创建用户

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 服务器

[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 服务器

[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 源(所有节点)

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 软件

安装依赖包(所有节点)

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(所有节点)

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 脚本

原脚本中无 VIP 切换

#!/usr/bin/env perl

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

#

#  This program is free software; you can redistribute 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 节点)

[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 启动和关闭

[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

注意项

所有主从数据完全一致,包含业务数据和系统表数据,不然会导致故障切换途中,无法切到新主库中

切换 VIP 时,需要各个服务器都有 net-tools 包,能够执行 ifconfig

到此,关于“mysql 下 MHA 搭建过程”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注丸趣 TV 网站,丸趣 TV 小编会继续努力为大家带来更多实用的文章!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-08-01发表,共计6561字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)