目录

Life in Flow

知不知，尚矣；不知知，病矣。
不知不知，殆矣。

X

MHA

   
    0 0 410686931

主从从GTID复制

rm -rf /data/*
rm -rf /data/*
mkdir -p /data/mysql/data
mkdir -p /data/binlog
chown -R mysql.mysql /data

# 主库db1配置文件
cat >/etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/application/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
[mysql]
prompt=db1 [\\d]>
EOF

# 主库db2配置文件
cat >/etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/application/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
[mysql]
prompt=db2 [\\d]>
EOF

# 主库db3配置文件
cat >/etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/application/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
[mysql]
prompt=db3 [\\d]>
EOF

# 主库进行创建用户用于从库们的连接
grant replication slave on *.* to repl@'192.168.31.%' identified by '123';

# 两个从库分别开启主从
[root@db2 software]# mysql -e "CHANGE MASTER TO MASTER_HOST='192.168.31.180',MASTER_USER='repl',MASTER_PASSWORD='123',MASTER_AUTO_POSITION=1;start slave;"
[root@db2 software]# mysql -e "show slave status \G"|grep Yes
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

准备环境

# 配置关键程序软连接
ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /application/mysql/bin/mysql /usr/bin/mysql

# 配置各节点互信
rm -rf /root/.ssh
ssh-keygen
cd /root/.ssh
mv id_rsa.pub authorized_keys
scp -r /root/.ssh 192.168.31.181:/root
scp -r /root/.ssh 192.168.31.182:/root

# 各节点验证
db1：
ssh 192.168.31.180 date
ssh 192.168.31.181 date
ssh 192.168.31.182 date

db2：
ssh 192.168.31.180 date
ssh 192.168.31.181 date
ssh 192.168.31.182 date

db3：
ssh 192.168.31.180 date
ssh 192.168.31.181 date
ssh 192.168.31.182 date

MHA搭建

# 所有节点安装Node软件和依赖包
yum install -y perl-DBD-MySQL
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

# db1主库中创建mha需要的用户
grant all privileges on *.* to mha@'192.168.31.%' identified by 'mha';

# 在db3安装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

# db3配置文件准备
创建配置文件目录
mkdir -p /etc/mha
创建日志目录
mkdir -p /var/log/mha/app1
编辑mha配置文件
cat > /etc/mha/app1.cnf<<EOF
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/binlog
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root
[server1]
hostname=192.168.31.180
port=3306
[server2]
hostname=192.168.31.181
port=3306
[server3]
hostname=192.168.31.182
port=3306
EOF

# 互信检查(db3)
masterha_check_ssh --conf=/etc/mha/app1.cnf
   All SSH connection tests passed successfully.
masterha_check_repl --conf=/etc/mha/app1.cnf
   MySQL Replication Health is OK.

# 启动MHA(db3)
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &

# 查看MHA状态
masterha_check_status --conf=/etc/mha/app1.cnf
   app1 (pid:12539) is running(0:PING_OK), master:192.168.31.180

额外的功能

1.提供Binlog Server

2.应用透明（VIP）

3.实时通知管理员（send_report）

4.自愈（待开发。。。）

模拟故障和故障修复

# 模拟故障：181变为主库
/etc/init.d/mysqld stop

# 观察 manager 日志 tail -f /var/log/mha/app1/manager,末尾必须显示uccessfully,才算正常切换成功。
Master failover to 192.168.31.181(192.168.31.181:3306) completed successfully.

# 在182上查看，db3从库指向181主库
db3 [(none)]>show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.31.181

#　修复故障主库
/etc/init.d/mysqld start

# 恢复主从结构(指向新主库)
[root@db3 ~]# grep -i 'change master to' /var/log/mha/app1/manager
Tue May 11 08:43:13 2021 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.31.181', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';

CHANGE MASTER TO MASTER_HOST='192.168.31.181', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';

start slave;

show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.31.181

# db3 manager节点修改配置文件,并且添加如下内容
vim /etc/mha/app1.cnf
[server1]
hostname=192.168.31.180
port=3306

# db3 manager 节点启动MHA
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &

VIP

脚本如下

[root@db3 ~]# vim /usr/local/bin/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.31.190/24';  # 修改这里的ip, 找个空闲的ip, 不能是在用的 
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig enp0s3:$key $vip";　　# 这里和下一行需要注意下网卡信息,我是用的虚拟机网卡是ens32, 需修改成机器的网卡信息
my $ssh_stop_vip = "/sbin/ifconfig enp0s3:$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" ) {

       # $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 $@;
           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;
    }
}

# A simple system call that enable the VIP on the new master
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";
}

脚本中有中文字符

[root@db3 ~]# dos2unix /usr/local/bin/master_ip_failover
dos2unix: converting file /usr/local/bin/master_ip_failover to Unix format ...

# 添加执行权限
[root@db3 ~]# chmod +x /usr/local/bin/master_ip_failover

添加参数 master_ip_failover_script=/usr/local/bin/master_ip_failover

[root@db3 ~]# vim /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/binlog
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root
user=mha
master_ip_failover_script=/usr/local/bin/master_ip_failover
[server1]
hostname=192.168.31.180
port=3306

[server2]
hostname=192.168.31.181
port=3306

[server3]
hostname=192.168.31.182
port=3306

主库手工绑定VIP (确认主节点)

ifconfig enp0s3:1 192.168.31.190/24

重启mha生效

# 停止mha
[root@db3 ~]# masterha_stop --conf=/etc/mha/app1.cnf

# 启动mha
[root@db3 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &

邮件通知

安装sendEmail

#下载安装包
wget http://caspian.dotconf.net/menu/Software/SendEmail/sendEmail-v1.56.tar.gz
#创建目录
mkdir -p /usr/local/bin
#解压
tar zxf sendEmail-v1.56.tar.gz -C /usr/src/
#进入解压目录
cd /usr/src/sendEmail-v1.56/
#复制程序到指定目录
cp -a sendEmail /usr/local/bin/
#给执行权限
chmod +x /usr/local/bin/sendEmail

脚本如下

# 编写脚本
[root@db3 perl-5.10.0]# vim /usr/local/bin/send
#!/bin/bash
/usr/local/bin/sendEmail -f wcsb19900116@126.com -t 410686931@qq.com -s smtp.126.com:25 -xu wcsb19900116 -xp PUTFDYGLEKBPEQDM -u "MHA Waring" -m "Your MHA MAY BE FAILOVER" &>/tmp/sendmail.log

# 添加执行权限
[root@db3 perl-5.10.0]# chmod +x /usr/local/bin/send

# 将脚本添加到db3 manager配置文件中
[root@db3 perl-5.10.0]# vim /etc/mha/app1.cnf
report_script=/usr/local/bin/send

重启MHA

# 停止mha
[root@db3 ~]# masterha_stop --conf=/etc/mha/app1.cnf

# 启动mha
[root@db3 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &

MHA-binlog-server

# 创建必要目录
mkdir -p /data/mysql/binlog
chown -R mysql.mysql /data/*

# db3 manager配置文件  (hostname=192.168.31.185 找一台专门的服务器，这里节省机器写生了db3)
[root@db3 ~]# /etc/mha/app1.cnf
[binlog1]
no_master=1
hostname=192.168.31.182
master_binlog_dir=/data/mysql/binlog

# cd /data/mysql/binlog  必须进入到自己创建好的目录，host写主库的地址
# mysql-bin.000005 是根据从库执行 show slave status 查询到的Master_Log_File的值写的
mysqlbinlog -R --host=192.168.31.180 --user=mha --raw --stop-never mysql-bin.000005 &

# 重启MHA
[root@db3 ~]# masterha_stop --conf=/etc/mha/app1.cnf
[root@db3 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &

最终架构

MHA + VIP + SendReport + BinlogServer

作者：Soulboy