天天看点
返回

MHA搭建MySQL高可用集群

MHA部署mysql数据库的高可用

1、前期准备

1、架构图

MHA搭建MySQL高可用集群

2、IP规划

Manager： 192.168.226.123

MySQL主： 192.168.226.111

MySQL从1： 192.168.226.112

MySQL从2： 192.168.226.113

3、服务器配置

查看系统

cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)

1核1G

2、安装前检查

1.网络情况

ping www.baidu.com 检测是否有网络

2.IP地址

Ifconfig 查看IP地址，与IP规划地址保持一致

3.防火墙

检测防火墙是否关闭

systemctl status firewalld

没关闭就先关闭防火墙

Systemctl stop firewalld

4.Selinux

enforcing 执行SELinux安全策略

permissive SELinux打印警告而不是强制执行(警告模式)

disabled 没有加载SELinux策略

检测selinux

getenforce

没关闭的话关闭

关闭selinux

vim /etc/selinux/config

SELINUX=disabled

5.系统保持一致

uname -r

6.时间同步

时间必须要同步，否则后面做的时候会报错

date #查看系统时间

时间不同步

yum -y install ntpdate #安装时间同步工具

ntpdate pool.ntp.org #同步时间

安装epel源：

先安装wget用来从网络上下载软件包

yum -y install wget
wget -O /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo

MHA manager源码包：

mha4mysql-manager-0.57.tar.gz

MHA node源码包：

mha4mysql-node-0.57.tar.gz

3、安装部署

1、配置免密

互相配置免密

ssh-keygen 生成秘钥对

ssh-copy-id [email protected] 发送公钥

2、配置mysql主从

1、安装mysql 

yum -y install mariadb-server mariadb

2、修改配置文件

mysql主：（192.168.226.111）

vim /etc/my.cnf

#必须写在[mysqld]模块下

server-id=1

log-bin=mysql-bin

Mysql从1：（192.168.226.112）

vim /etc/my.cnf

#id不能一样

server-id=2

log-bin=slave-bin

relay-log=slave-log

mysql从2：（192.168.226.113）

vim /etc/my.cnf

#id不能一样

server-id=3

log-bin=slave2-bin

relay-log=slave2-log

3、开启并进入MySQL 

systemctl start mariadb
mysql

4、授权从的复制权限（192.168.226.111） 

grant replication slave on *.* to [email protected]'%' identified by '123';
flush privileges;
show master status;

5、从（192.168.226.112/192.168.226.113） 

stop salve;
change master to  master_host='192.168.226.111', master_user='slave', master_password='123', master_log_file='mysql-bin.000003', master_log_pos=533;
start slave;
show slave status\G;

双yes即为成功

6、从设置为只读模式（192.168.226.112/192.168.226.113） 

set global read_only=1;

7、授权manager检测mysql权限（三台mysql都做） 

grant all on *.* to [email protected]'192.168.226.%' identified by '123';

8、关闭数据库relaylog自动删除 

set global relay_log_purge = 0;

3、安装node节点（所有主机都安装）

1、安装依赖 

yum -y install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-DBD-MySQL perl-devel perl-CPAN

2、上传node源码包

3、/etc下创建mha目录 

mkdir /etc/mha

4、解压源码包并编译安装 

tar xf mha4mysql-node-0.57.tar.gz
mv mha4mysql-node-0.57 /etc/mha/node
cd /etc/mha/node/
perl Makefile.PL  && make && make install

4、安装manager节点（192.168.226.123）

1、安装依赖 

yum -y install epel-release --nogpgcheck
yum -y install perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes

2、上传manager源码包

3、解压并编译安装 

tar xf mha4mysql-manager-0.57.tar.gz
mv mha4mysql-manager-0.57 /etc/mha/manager
cd /etc/mha/manager/
perl Makefile.PL && make && make install

4、修改配置文件 

[server default]
#设置manager的工作目录
manager_workdir=/etc/mha/app1

#设置manager的日志
manager_log=/etc/mha/app1/manager.log

#设置master 保存binlog的位置，以便MHA可以找到master的日志，我这里的也就是mysql的数据目录
master_binlog_dir="/var/lib/mysql"

#设置远端mysql在发生切换时binlog的保存位置
remote_workdir=/etc/mha/app1

#设置自动failover时候的切换脚本
master_ip_failover_script=/etc/mha/master_ip_failover

#设置手动切换时候的切换脚本
master_ip_online_change_script=/etc/mha/master_ip_online_change

#设置发生切换后发送的报警的脚本
report_script=/etc/mha/send_report

#设置监控用户manager
user=manager

#设置mysql中root用户的密码，这个密码是前文中创建监控用户的那个密码
password=123

#设置主从复制环境中用户名
repl_user=manager

#设置主从复制用户的密码
repl_password=123

#设置监控主库，发送ping包的时间间隔，尝试四次没有回应的时候自动进行failover
ping_interval=1

#一旦MHA到server02的监控之间出现问题，MHA Manager将会尝试从server03登录到server02
secondary_check_script= masterha_secondary_check -s 192.168.226.112 -s 192.168.226.113

[server1]
hostname=192.168.226.111
port=3306
ssh_port=22

[server2]
hostname=192.168.226.112
port=3306
ssh_port=22

#设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave
candidate_master=1

#默认情况下如果一个slave落后master 100M的relay logs的话，MHA将不会选择该slave作为一个新的master， 因为对于这个slave的恢复需要花费很长时间，通过设置check_repl_delay=0,MHA触发切换 在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1 的主机非常有用，因为这个候选主在切换的过程中一定是新的master
check_repl_delay=0

[server3]
hostname=192.168.226.113
port=3306
no_master=1
ssh_port=22

5、脚本（所有数据库）

1、定时删除relaylog文件

1、给relay-log新建目录并做硬连接 
mkdir -p /var/lib/mysql/logs1
ln /var/lib/mysql/relay-log* /var/lib/mysql/logs1/
2、编写脚本

vim /etc/mha/purge_relay_log.sh

#!/bin/bash
user=root
#passwd=123		数据库密码，默认是没有密码的，所以注释掉
port=3306
log_dir=’/var/lib/mysql/’
work_dir=’/var/lib/mysql/logs1’
purge=’/usr/local/bin/purge_relay_logs’
if [ ! -d $log_dir ]
then
mkdir $log_dir -p
fi
 $purge --user=$user --password=$passwd --disable_relay_log_purge --port= $port --host=localhost --workdir=$work_dir >> $log_dir/purge_relay_logs.log 2>&1
3、测试 
purge_relay_logs --user=root --host=localhost --port=3306 -disable_relay_log_purge --workdir=/var/lib/mysql/

最后结果出现 succeeded 即为成功

4、做定时任务定时删除

crontab -e

0 0 */3 * * sh purge_relay_log.sh

2、自动切换脚本

vim /etc/mha/master_ip_failover

#!/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 = '192.168.181.111/24';
  my $key = '0';
  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 {
    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 ([email protected]) {
      warn "Got Error: [email protected]\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 ([email protected]) {
      warn [email protected];
      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";
  }

3、手动切换脚本

vim /etc/mha/master_ip_online_change

#!/usr/bin/env perl
#手动切换
  use strict;
  use warnings FATAL =>'all';
  use Getopt::Long;

  my $vip = '192.168.181.111/24'; # Virtual IP
  my $key = "0";
  my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
  my $ssh_stop_vip = "/sbin/ifconfig ens33:$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 "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

    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 "\n\n\n***************************************************************\n";
        print "Disabling the VIP - $vip on old master: $orig_master_host\n";
        print "***************************************************************\n\n\n\n";
        &stop_vip();
        $exit_code = 0;
      };

      if ([email protected]) {
        warn "Got Error: [email protected]\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 "\n\n\n***************************************************************\n";
        print "Enabling the VIP - $vip on new master: $new_master_host \n";
        print "***************************************************************\n\n\n\n";
        &start_vip();
        $exit_code = 0;
      };

      if ([email protected]) {
        warn [email protected];
        exit $exit_code;
      }

      exit $exit_code;
    }

    elsif ( $command eq "status" ) {
      print "Checking the Status of the script.. OK \n";
      `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=port -new_master_host=host -new_master_ip=ip -new_master_port=port\n";
  }

4、邮件报警脚本

vim /etc/mha/send_report

#!/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='[email protected]';	 	#发送的邮箱地址
  my $mail_user='[email protected]';		#发送的用户（邮箱地址）
  my $mail_pass='xxxxxxxxxx';					#邮箱的秘钥
  my $mail_to=['[email protected]']; 			#要发送的的邮箱地址
  
  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,
  );

  mailToContacts($smtp,$mail_from,$mail_user,$mail_pass,$mail_to,$subject,$body);
  
  sub mailToContacts {
    my ( $smtp, $mail_from, $user, $passwd, $mail_to, $subject, $msg ) = @_;
    open my $DEBUG, "> /tmp/monitormail.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      => $user,
      authpwd     => $passwd,
      to          => $mail_to,
      subject     => $subject,
      debug       => $DEBUG
    };
  
    $sender->MailMsg(
    {   
      msg   => $msg,
      debug => $DEBUG
    }
    ) 
    or print $Mail::Sender::Error;
    return 1;
  }

  # Do whatever you want here
  exit 0;

5、给脚本添加执行权限 

chmod +x /etc/mha/master_ip_* send_report purge_relay_log.sh

6、manager检测、启动（192.168.226.123）

1、ssh免密检测 

/etc/mha/manager/bin/masterha_check_ssh --conf=/etc/mha/app1.cnf

出现 successfully 为成功

2、mysql主从状态检测 

/etc/mha/manager/bin/masterha_check_repl --conf=/etc/mha/app1.cnf

下面这样为检测正常

MHA搭建MySQL高可用集群

3、给主数据库添加VIP 

ifconfig ens33:0 192.168.226.200 netmask 255.255.255.0

查看是否生成VIP

4、启动manager 

nohup /etc/mha/manager/bin/masterha_manager --conf=/etc/mha/app1.cnf --ignore_last_failover >/tmp/mha_manager.log < /dev/null 2>&1 &
MHA搭建MySQL高可用集群

5、查看主的状态 

/etc/mha/manager/bin/masterha_check_status --conf=/etc/mha/app1.cnf
MHA搭建MySQL高可用集群

4、测试

1、关闭主上的mariadb 

systemctl stop mariadb

2、查看主上vip是否消失

3、查看从1（192.168.226.112）上出现IP，即 vip 漂移成功

192.168.226.112成为新主，然后启动旧主192.168.226.111，然后为111和112做主从（112为主，111为从）

4、修改管理机（192.168.226.123）的manager配置文件

vim /etc/mha/app1.cnf

MHA搭建MySQL高可用集群

5、启动manager 

nohup /etc/mha/manager/bin/masterha_manager --conf=/etc/mha/app1.cnf --ignore_last_failover >/tmp/mha_manager.log < /dev/null 2>&1 &

6、查看状态，此时192.168.226.112为主 

/etc/mha/manager/bin/masterha_check_status --conf=/etc/mha/app1.cnf
MHA搭建MySQL高可用集群
mysql MHA 运维
上一篇: 龙山湖小学六年级女生的读书笔记：精美雅致！读《鲁滨逊漂流记》的随记，每一章节的优美词汇、精彩句段摘抄，还有阅读感想。孩子
下一篇: Java项目开发命名规范

继续阅读