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 status\G;
#看到

  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: $@\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();
            &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 \n";
        `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\n";
}

 

 

 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:$!\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      => $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;

 

mysql MHA搭建

上一篇:详述Centos中的ftp命令的使用方法


下一篇:linux系统创建新LV,挂载新分区。