mysql MHA搭建

架构

ip  一主两从

192.168.6.36  主

192.168.6.128 从1

192.168.7.93 从2

在3台上搭建mysql

在mysql官网 下载 mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz 下载通用的

主1配置文件

[mysqld]
basedir=/data/mysql/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
#autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
# 这个比较重要，直接影响同步的性能 延时等问题.mysql5.7多源复制参数，5.7之前版本可不加
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

default-time_zone='+8:00'

[mysql]
prompt=db01 [d]>

 从1配置文件

[mysqld]
basedir=/data/mysql/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
#autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
default-time_zone = '+8:00'
# 这个比较重要，直接影响同步的性能 延时等问题.mysql5.7多源复制参数，5.7之前版本可不加
#sync_binlog=1
#auto_increment_increment=2
#auto_increment_offset=1
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysql]
prompt=db02 [d]>

从2 配置文件

[mysqld]
basedir=/data/mysql/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
#autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

#master_info_repository=TABLE
#relay_log_info_repository=TABLE

default-time_zone = '+8:00'

#复制并发数设置
# 多源复制设置
#slave_parallel_workers = 16
[mysql]
prompt=db03 [d]>

mkdir /data/mysql -p

mkdir /data/mysql/data -p 

cd /data/mysql

tar -xvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz

ln -s mysql-5.7.35-linux-glibc2.12-x86_64 mysql

cd /data/mysql/mysql/bin

chown -R  mysql:mysql /data/mysql/

# 初始化，# --initialize-insecure 没有密码 #--initialize 密码在日志中

./mysqld  --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql  --basedir=/data/mysql/mysql  --datadir=/data/mysql/data

cp /data/mysql/mysql/support-files/mysql.server  /etc/init.d/mysqld
service  mysqld start

做主从 

在主上操作

grant replication slave  on *.* to repl@'%' identified by '123';

在从上操作

CHANGE MASTER TO MASTER_HOST='192.168.6.36', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';
start slave;
# 查看从的状态
show slave statusG;
#看到

  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes 

  主从成功

做MHA

mha官网：https://code.google.com/archive/p/mysql-master-ha/ github

下载地址：https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads

在3台mysql 中安装  mha4mysql-node

yum install perl-DBD-MySQL -y   perl-Log-Dispatch perl-Parallel-ForkManager
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

Manager软件安装 

使用从2 当做 Manager  也可以重新选一台

 

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

rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

在从2 上操作

创建配置文件目录 mkdir -p /etc/mha

创建日志目录 mkdir -p /var/log/mha/app1

编辑mha配置文件

vim /etc/mha/app1.cnf

[root@mysql03 mha]# cat app1.cnf.bak
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/binlog
password=mha

# 探测心跳的间隔时间，默认3次
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root
user=mha

# vip的配置

master_ip_failover_script=/usr/local/bin/master_ip_failover

# 发送邮件的脚本
report_script=/usr/local/bin/send_report

[server1]
hostname=192.168.6.36
port=3306
[server2]
hostname=192.168.6.128
port=3306
[server3]
hostname=192.168.7.93
port=3306

 

 master_ip_failover文件

需要改动的地方

my $vip = '192.168.168.100/24';  # vip
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";  #ens33 网卡名

my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";  #ens33 网卡名

#!/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,  $new_master_user,
  $new_master_password
);
my $vip = '192.168.168.100/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,
  'new_master_user=s'     => $new_master_user,
  'new_master_password=s' => $new_master_password,
);

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 {

      # updating global catalog, etc
      $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();
            &stop_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@$orig_master_host " $ssh_start_vip "`;
        exit 0;
    }
    else {
        &usage();
        exit 1;
    }
}


sub start_vip() {
    `ssh $ssh_user@$new_master_host " $ssh_start_vip "`;
}
# A simple system call that disable the VIP on the old_master 
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
";
}

 send_report 文件

需要改动的地方

my $smtp='smtp.126.com';  
my $mail_from='from@126.com';  #发送的邮件 
my $mail_user='from@126.com';  #发送的邮件
my $mail_pass='password';  #smtp的密码  不是账号的密码

#my $mail_to=['to1@qq.com','to2@qq.com']; 
my $mail_to='to@126.com'; # 发送的邮箱

#!/usr/bin/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 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.126.com';  
my $mail_from='from@126.com';  
my $mail_user='from@126.com';  
my $mail_pass='password';  
#my $mail_to=['to1@qq.com','to2@qq.com'];  
my $mail_to='to@126.com';  
  
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,  
);  
  
# Do whatever you want here  
mailToContacts($smtp,$mail_from,$mail_user,$mail_pass,$mail_to,$subject,$body);  
  
sub mailToContacts {  
    my ($smtp, $mail_from, $mail_user, $mail_pass, $mail_to, $subject, $msg ) = @_;  
    open my $DEBUG, ">/var/log/masterha/app1/mail.log"  
        or die "Can't open the debug    file:$!
";  
    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      => $mail_user,  
        authpwd     => $mail_pass,  
        to      => $mail_to,  
        subject     => $subject,  
        debug       => $DEBUG  
    };  
    $sender->MailMsg(  
        {  
            msg => $msg,  
            debug => $DEBUG  
        }  
    ) or print $Mail::Sender::Error;  
    return 1;  
}  
  
exit 0;