Mysql高可用方案-MHA

最新推荐文章于 2024-03-25 08:31:47 发布
最新推荐文章于 2024-03-25 08:31:47 发布
阅读量516 收藏 2
点赞数
分类专栏： Mysql 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u012965203/article/details/119970697
版权

1.1 简介

MHA（Master HA）是一款开源的 MySQL 的高可用程序，它为 MySQL 主从复制架构提供了 automating master failover 功能。MHA 在监控到 master 节点故障时，会提升其中拥有最新数据的 slave 节点成为新的master 节点，在此期间，MHA 会通过于其它从节点获取额外信息来避免一致性方面的问题。MHA 还提供了 master 节点的在线切换功能，即按需切换 master/slave 节点。
MHA 是由日本人 yoshinorim（原就职于DeNA现就职于FaceBook）开发的比较成熟的 MySQL 高可用方案。MHA 能够在30秒内实现故障切换，并能在故障切换中，最大可能的保证数据一致性。目前淘宝也正在开发相似产品 TMHA， 目前已支持一主一从

1.2 组件

MHA 服务有两种角色， MHA Manager(管理节点)和 MHA Node(数据节点)：
MHA Manager：
　　通常单独部署在一台独立机器上管理多个 master/slave 集群(组)，每个 master/slave 集群称作一个 application，用来管理统筹整个集群。
MHA node：
　　运行在每台 MySQL 服务器上(master/slave/manager)，它通过监控具备解析和清理 logs 功能的脚本来加快故障转移。
　　主要是接收管理节点所发出指令的代理，代理需要运行在每一个 mysql 节点上

1.3 MHA集群架构图

1.4 服务器

Master

192.168.42.141

主机

Slave

192.168.42.142

从机

Slave

192.168.42.143

从机

Manager

192.168.42.143

管理节点

1.5 Mysql安装

1.5.1 给每一台机器设置主机名

hostnamectl set-hostname mysql01

 hostnamectl set-hostname mysql02

hostnamectl set-hostname mysql03

 重启机器

 reboot

1.5.2 每一台机器关闭防火墙

systemctl stop firewalld

/etc/host新增配置

vim /etc/hosts

192.168.42.141 mysql01
192.168.42.142 mysql02
192.168.42.143 mysql03

1.5.3 每一台机器安装工具

yum install -y conntrack ntpdate ntp ipvsadm ipset jq iptables curl sysstat libseccomp wget vim net-tools git iproute lrzsz bash-completion tree bridge-utils unzip bind-utils gcc

 1.5.4 安装mysql

1.5.4.1 每一台机器下载以及安装依赖

wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

yum localinstall mysql57-community-release-el7-8.noarch.rpm

yum repolist enabled | grep "mysql.*-community.*"

 1.5.4.2每一台机器安装mysql-community-server

yum install -y  mysql-community-server

1.5.4.3 启动

systemctl start mysqld

1.5.4.4查看状态

systemctl status mysqld

1.5.4.5 设置开机启动

systemctl enable mysqld

1.5.4.6 重新加载配置

systemctl daemon-reload

1.5.4.7 查看初始化密码

grep 'temporary password' /var/log/mysqld.log

1.5.4.8 修改密码

全部机器修改root账号密码

登录

mysql -uroot -p

设置密码修改策略(校验级别最低，只校验密码长度)

set global validate_password_policy=0;

修改密码长度
set global validate_password_length=6;

设置root密码
set password for 'root'@'localhost'=password('123456'); 

刷新配置

flush privileges;

1.5.5 创建用户

全部机器都需要创建replication、mha账号

create user replication identified by '123456';

create user mha identified by '123456';

create user slave identified by '123456';

给用户replication，mha授权

grant all privileges on *.* to replication@'%' identified by '123456';

grant all privileges on *.* to mha @'%' identified by '123456';

grant all privileges on *.* to slave @'%' identified by '123456';

刷新配置

flush privileges;

检查用户创建是否成功

use mysql

select user,host from user;

验证replication能够相互登录

如果验证不成功需要关闭防火墙

192.168.42.141执行

mysql -ureplication -p123456 -h192.168.42.142

mysql -ureplication -p123456 -h192.168.42.143

192.168.42.142执行

mysql -ureplication -p123456 -h192.168.42.141

mysql -ureplication -p123456 -h192.168.42.143

192.168.42.143执行

mysql -ureplication -p123456 -h192.168.42.141

mysql -ureplication -p123456 -h192.168.42.142

1.5.6 192.168.1.141 配置my.cnf

vim /etc/my.cnf

log-bin

server-id=1

gtid_mode=on

enforce_gtid_consistency=on

1.5.7 192.168.1.142 配置my.cnf

vim  /etc/my.cnf


log-bin
server-id=2
gtid_mode=on
enforce_gtid_consistency=on

 1.5.8 192.168.1.143 配置my.cnf

vim  /etc/my.cnf

log-bin
server-id=3
gtid_mode=on
enforce_gtid_consistency=on

1.5.9 重启mysql

systemctl restart mysqld;

 1.6 主从配置

1.6.1 查看192.168.41.141主复制的指针位

mysql -uroot -p

show master status \G;

1.6.1 查看192.168.41.142主从配置

mysql -uroot -p

change master to master_host='192.168.42.141',
master_user='replication',
master_password='123456',
MASTER_LOG_FILE='mysql01-bin.000004', MASTER_LOG_POS=154;

start slave;

show slave status \G;

1.6.1 查看192.168.41.143主从配置

mysql -uroot -p

change master to master_host='192.168.42.141',
master_user='replication',
master_password='123456',
MASTER_LOG_FILE='mysql01-bin.000001', MASTER_LOG_POS=154;

start slave;

show slave status \G;

1.6.4 主从配置验证是否配置成功

192.168.42.141

     

 192.168.42.142

 192.168.42.143

1.7 ssh免密登录配置

每台机器配置

ssh-keygen -t rsa
cat /root/.ssh/id_rsa.pub |ssh root@192.168.42.141 'cat>>.ssh/authorized_keys'
cat /root/.ssh/id_rsa.pub |ssh root@192.168.42.142 'cat>>.ssh/authorized_keys'
cat /root/.ssh/id_rsa.pub |ssh root@192.168.42.143 'cat>>.ssh/authorized_keys'

1.8 MHA 免密配置

1.8.1所有机器安装epl依赖

yum -y install epel-release
wget http://rpms.remirepo.net/enterprise/remi-release-7.rpm
rpm -Uvh remi-release-7.rpm

1.8.2 所有机器下载与安装node

安装依赖

yum install -y perl-DBD-MySQL ncftp perl-DBI.x86

 下载node

wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm

 安装node

rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

 

1.8.3 所有机器下载与安装manager

只在192.168.42.143机器安装

安装依赖

yum install -y perl-DBD-MySQL  perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager

wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

rpm -Uvh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

 1.8.4 创建配置mha.conf

 所有机器在/etc/mha目录下创建mha.conf

创建目录

mkdir -p /etc/mha

vim mha.conf

##MHA配置
[server default]
user=mha
password=123456
ssh_user=root
repl_user=replication
repl_password=123456
manager_workdir=/var/log/masterha/db1
manager_log=/var/log/masterha/app1/db1.log
master_binlog_dir=/var/lib/mysql
remote_workdir=/data/log/masterha
secondary_check_script=masterha_secondary_check -s 192.168.42.141 -s 192.168.42.142
ping_interval=3
master_ip_failover_script=/script/masterha/master_ip_failover
report_script=/script/masterha/send_master_failover_mail
[server1]
hostname=192.168.42.141
candidate_master=1

[server2]
hostname=192.168.42.142
candidate_master=1

[server3]
hostname=192.168.42.143
no_master=1

 1.8.5 创建master_ip_failover

在19.168.42.143机器操作

创建目录

mkdir -p /script/masterha

vim 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.42.88';
my $brdc = '192.168.42.255';
my $ifdev = 'ens33';
my $key = '1';
my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;";
my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key";
##################################################################################
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 ($@) {
warn "Got Error: $@\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 ($@) {
warn $@;
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 \"`;
}
# 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";
}

修改文件权限

chmod 777 /script/masterha/master_ip_failover

1.8.6 创建send_master_failover_mail

vim /script/masterha/send_master_failover_mail

#!/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='xx@qq.com';
my $mail_user='xx@qq.com';
my $mail_pass='xx';
my $mail_to=['xx@36.cn'];
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, $user, $passwd, $mail_to, $subject, $msg ) = @_;
    open my $DEBUG, "> /etc/mha/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;
}

说明

my $smtp='smtp.qq.com';
my $mail_from='xx@qq.com';
my $mail_user='xx@qq.com';
my $mail_pass='xx';
my $mail_to=['xx@36.cn'];

需要修改成自己的配置

创建文件monitormail.log

touch  /etc/mha/monitormail.log

修改文件权限

chmod 777 /script/masterha/send_master_failover_mail

1.8.7 配置VIP

在192.168.42.141

ifconfig ens33:1 192.168.42.88/24

1.8.8 验证配置

MYSQL 复制环境检测工具

masterha_check_repl --conf=/etc/mha/mha.conf

 

 

MHA 依赖的 ssh 环境监测工具

masterha_check_ssh --conf=/etc/mha/mha.conf

 

1.8.9 创建db1.log

mkdir -p  /var/log/masterha/app1

touch /var/log/masterha/app1/db1.log

1.8.10 启动

nohup masterha_manager -conf=/etc/mha/mha.conf&> /etc/mha/manager.log &

 

1.8.11 查看状态

masterha_check_status --conf=/etc/mha/mha.conf

1.8.12 停止命令

masterha_stop -conf=/etc/mha/mha.conf

1.8.13 模拟宕机

162.168.42.141没有宕机时

162.168.42.141

 

162.168.42.143日志如下 

 162.168.42.141宕机时

162.168.42.142

vip到了162.168.42.142机器

 

 收到邮件如下

 

 162.168.42.143日志如下

IN SCRIPT TEST====/usr/sbin/ip addr del 192.168.42.88/24 dev ens33 label ens33:1==/usr/sbin/ip addr add 192.168.42.88/24 brd 192.168.42.255 dev ens33 label ens33:1;/usr/sbin/arping -q -A -c 1 -I ens33 192.168.42.88;iptables -F;===

Checking the Status of the script.. OK 
Fri Aug 27 19:09:26 2021 - [info]  OK.
Fri Aug 27 19:09:26 2021 - [warning] shutdown_script is not defined.
Fri Aug 27 19:09:26 2021 - [info] Set master ping interval 3 seconds.
Fri Aug 27 19:09:26 2021 - [info] Set secondary check script: masterha_secondary_check -s 192.168.42.141 -s 192.168.42.142
Fri Aug 27 19:09:26 2021 - [info] Starting ping health check on 192.168.42.141(192.168.42.141:3306)..
Fri Aug 27 19:09:26 2021 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..


--------------------------------------------------------------------------------------

Fri Aug 27 19:16:50 2021 - [warning] Got error on MySQL select ping: 2013 (Lost connection to MySQL server during query)
Fri Aug 27 19:16:50 2021 - [info] Executing secondary network check script: masterha_secondary_check -s 192.168.42.141 -s 192.168.42.142  --user=root  --master_host=192.168.42.141  --master_ip=192.168.42.141  --master_port=3306 --master_user=mha --master_password=123456 --ping_type=SELECT
Fri Aug 27 19:16:50 2021 - [info] Executing SSH check script: exit 0
Fri Aug 27 19:16:50 2021 - [info] HealthCheck: SSH to 192.168.42.141 is reachable.
Monitoring server 192.168.42.141 is reachable, Master is not reachable from 192.168.42.141. OK.
Monitoring server 192.168.42.142 is reachable, Master is not reachable from 192.168.42.142. OK.
Fri Aug 27 19:16:51 2021 - [info] Master is not reachable from all other monitoring servers. Failover should start.
Fri Aug 27 19:16:53 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.42.141' (111))
Fri Aug 27 19:16:53 2021 - [warning] Connection failed 2 time(s)..
Fri Aug 27 19:16:56 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.42.141' (111))
Fri Aug 27 19:16:56 2021 - [warning] Connection failed 3 time(s)..
Fri Aug 27 19:16:59 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.42.141' (111))
Fri Aug 27 19:16:59 2021 - [warning] Connection failed 4 time(s)..
Fri Aug 27 19:16:59 2021 - [warning] Master is not reachable from health checker!
Fri Aug 27 19:16:59 2021 - [warning] Master 192.168.42.141(192.168.42.141:3306) is not reachable!
Fri Aug 27 19:16:59 2021 - [warning] SSH is reachable.
Fri Aug 27 19:16:59 2021 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/mha.conf again, and trying to connect to all servers to check server status..
Fri Aug 27 19:16:59 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Aug 27 19:16:59 2021 - [info] Reading application default configuration from /etc/mha/mha.conf..
Fri Aug 27 19:16:59 2021 - [info] Reading server configuration from /etc/mha/mha.conf..
Fri Aug 27 19:17:00 2021 - [info] GTID failover mode = 1
Fri Aug 27 19:17:00 2021 - [info] Dead Servers:
Fri Aug 27 19:17:00 2021 - [info]   192.168.42.141(192.168.42.141:3306)
Fri Aug 27 19:17:00 2021 - [info] Alive Servers:
Fri Aug 27 19:17:00 2021 - [info]   192.168.42.142(192.168.42.142:3306)
Fri Aug 27 19:17:00 2021 - [info]   192.168.42.143(192.168.42.143:3306)
Fri Aug 27 19:17:00 2021 - [info] Alive Slaves:
Fri Aug 27 19:17:00 2021 - [info]   192.168.42.142(192.168.42.142:3306)  Version=5.7.35-log (oldest major version between slaves) log-bin:enabled
Fri Aug 27 19:17:00 2021 - [info]     GTID ON
Fri Aug 27 19:17:00 2021 - [info]     Replicating from 192.168.42.141(192.168.42.141:3306)
Fri Aug 27 19:17:00 2021 - [info]     Primary candidate for the new Master (candidate_master is set)
Fri Aug 27 19:17:00 2021 - [info]   192.168.42.143(192.168.42.143:3306)  Version=5.7.35-log (oldest major version between slaves) log-bin:enabled
Fri Aug 27 19:17:00 2021 - [info]     GTID ON
Fri Aug 27 19:17:00 2021 - [info]     Replicating from 192.168.42.141(192.168.42.141:3306)
Fri Aug 27 19:17:00 2021 - [info]     Not candidate for the new Master (no_master is set)
Fri Aug 27 19:17:00 2021 - [info] Checking slave configurations..
Fri Aug 27 19:17:00 2021 - [info]  read_only=1 is not set on slave 192.168.42.142(192.168.42.142:3306).
Fri Aug 27 19:17:00 2021 - [info]  read_only=1 is not set on slave 192.168.42.143(192.168.42.143:3306).
Fri Aug 27 19:17:00 2021 - [info] Checking replication filtering settings..
Fri Aug 27 19:17:00 2021 - [info]  Replication filtering check ok.
Fri Aug 27 19:17:00 2021 - [info] Master is down!
Fri Aug 27 19:17:00 2021 - [info] Terminating monitoring script.
Fri Aug 27 19:17:00 2021 - [info] Got exit code 20 (Master dead).
Fri Aug 27 19:17:00 2021 - [info] MHA::MasterFailover version 0.58.
Fri Aug 27 19:17:00 2021 - [info] Starting master failover.
Fri Aug 27 19:17:00 2021 - [info] 
Fri Aug 27 19:17:00 2021 - [info] * Phase 1: Configuration Check Phase..
Fri Aug 27 19:17:00 2021 - [info] 
Fri Aug 27 19:17:01 2021 - [info] GTID failover mode = 1
Fri Aug 27 19:17:01 2021 - [info] Dead Servers:
Fri Aug 27 19:17:01 2021 - [info]   192.168.42.141(192.168.42.141:3306)
Fri Aug 27 19:17:01 2021 - [info] Checking master reachability via MySQL(double check)...
Fri Aug 27 19:17:01 2021 - [info]  ok.
Fri Aug 27 19:17:01 2021 - [info] Alive Servers:
Fri Aug 27 19:17:01 2021 - [info]   192.168.42.142(192.168.42.142:3306)
Fri Aug 27 19:17:01 2021 - [info]   192.168.42.143(192.168.42.143:3306)
Fri Aug 27 19:17:01 2021 - [info] Alive Slaves:
Fri Aug 27 19:17:01 2021 - [info]   192.168.42.142(192.168.42.142:3306)  Version=5.7.35-log (oldest major version between slaves) log-bin:enabled
Fri Aug 27 19:17:01 2021 - [info]     GTID ON
Fri Aug 27 19:17:01 2021 - [info]     Replicating from 192.168.42.141(192.168.42.141:3306)
Fri Aug 27 19:17:01 2021 - [info]     Primary candidate for the new Master (candidate_master is set)
Fri Aug 27 19:17:01 2021 - [info]   192.168.42.143(192.168.42.143:3306)  Version=5.7.35-log (oldest major version between slaves) log-bin:enabled
Fri Aug 27 19:17:01 2021 - [info]     GTID ON
Fri Aug 27 19:17:01 2021 - [info]     Replicating from 192.168.42.141(192.168.42.141:3306)
Fri Aug 27 19:17:01 2021 - [info]     Not candidate for the new Master (no_master is set)
Fri Aug 27 19:17:01 2021 - [info] Starting GTID based failover.
Fri Aug 27 19:17:01 2021 - [info] 
Fri Aug 27 19:17:01 2021 - [info] ** Phase 1: Configuration Check Phase completed.
Fri Aug 27 19:17:01 2021 - [info] 
Fri Aug 27 19:17:01 2021 - [info] * Phase 2: Dead Master Shutdown Phase..
Fri Aug 27 19:17:01 2021 - [info] 
Fri Aug 27 19:17:01 2021 - [info] Forcing shutdown so that applications never connect to the current master..
Fri Aug 27 19:17:01 2021 - [info] Executing master IP deactivation script:
Fri Aug 27 19:17:01 2021 - [info]   /script/masterha/master_ip_failover --orig_master_host=192.168.42.141 --orig_master_ip=192.168.42.141 --orig_master_port=3306 --command=stopssh --ssh_user=root  


IN SCRIPT TEST====/usr/sbin/ip addr del 192.168.42.88/24 dev ens33 label ens33:1==/usr/sbin/ip addr add 192.168.42.88/24 brd 192.168.42.255 dev ens33 label ens33:1;/usr/sbin/arping -q -A -c 1 -I ens33 192.168.42.88;iptables -F;===

Disabling the VIP on old master: 192.168.42.141 
Fri Aug 27 19:17:02 2021 - [info]  done.
Fri Aug 27 19:17:02 2021 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Fri Aug 27 19:17:02 2021 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Fri Aug 27 19:17:02 2021 - [info] 
Fri Aug 27 19:17:02 2021 - [info] * Phase 3: Master Recovery Phase..
Fri Aug 27 19:17:02 2021 - [info] 
Fri Aug 27 19:17:02 2021 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Fri Aug 27 19:17:02 2021 - [info] 
Fri Aug 27 19:17:02 2021 - [info] The latest binary log file/position on all slaves is mysql01-bin.000007:318
Fri Aug 27 19:17:02 2021 - [info] Latest slaves (Slaves that received relay log files to the latest):
Fri Aug 27 19:17:02 2021 - [info]   192.168.42.142(192.168.42.142:3306)  Version=5.7.35-log (oldest major version between slaves) log-bin:enabled
Fri Aug 27 19:17:02 2021 - [info]     GTID ON
Fri Aug 27 19:17:02 2021 - [info]     Replicating from 192.168.42.141(192.168.42.141:3306)
Fri Aug 27 19:17:02 2021 - [info]     Primary candidate for the new Master (candidate_master is set)
Fri Aug 27 19:17:02 2021 - [info]   192.168.42.143(192.168.42.143:3306)  Version=5.7.35-log (oldest major version between slaves) log-bin:enabled
Fri Aug 27 19:17:02 2021 - [info]     GTID ON
Fri Aug 27 19:17:02 2021 - [info]     Replicating from 192.168.42.141(192.168.42.141:3306)
Fri Aug 27 19:17:02 2021 - [info]     Not candidate for the new Master (no_master is set)
Fri Aug 27 19:17:02 2021 - [info] The oldest binary log file/position on all slaves is mysql01-bin.000007:318
Fri Aug 27 19:17:02 2021 - [info] Oldest slaves:
Fri Aug 27 19:17:02 2021 - [info]   192.168.42.142(192.168.42.142:3306)  Version=5.7.35-log (oldest major version between slaves) log-bin:enabled
Fri Aug 27 19:17:02 2021 - [info]     GTID ON
Fri Aug 27 19:17:02 2021 - [info]     Replicating from 192.168.42.141(192.168.42.141:3306)
Fri Aug 27 19:17:02 2021 - [info]     Primary candidate for the new Master (candidate_master is set)
Fri Aug 27 19:17:02 2021 - [info]   192.168.42.143(192.168.42.143:3306)  Version=5.7.35-log (oldest major version between slaves) log-bin:enabled
Fri Aug 27 19:17:02 2021 - [info]     GTID ON
Fri Aug 27 19:17:02 2021 - [info]     Replicating from 192.168.42.141(192.168.42.141:3306)
Fri Aug 27 19:17:02 2021 - [info]     Not candidate for the new Master (no_master is set)
Fri Aug 27 19:17:02 2021 - [info] 
Fri Aug 27 19:17:02 2021 - [info] * Phase 3.3: Determining New Master Phase..
Fri Aug 27 19:17:02 2021 - [info] 
Fri Aug 27 19:17:02 2021 - [info] Searching new master from slaves..
Fri Aug 27 19:17:02 2021 - [info]  Candidate masters from the configuration file:
Fri Aug 27 19:17:02 2021 - [info]   192.168.42.142(192.168.42.142:3306)  Version=5.7.35-log (oldest major version between slaves) log-bin:enabled
Fri Aug 27 19:17:02 2021 - [info]     GTID ON
Fri Aug 27 19:17:02 2021 - [info]     Replicating from 192.168.42.141(192.168.42.141:3306)
Fri Aug 27 19:17:02 2021 - [info]     Primary candidate for the new Master (candidate_master is set)
Fri Aug 27 19:17:02 2021 - [info]  Non-candidate masters:
Fri Aug 27 19:17:02 2021 - [info]   192.168.42.143(192.168.42.143:3306)  Version=5.7.35-log (oldest major version between slaves) log-bin:enabled
Fri Aug 27 19:17:02 2021 - [info]     GTID ON
Fri Aug 27 19:17:02 2021 - [info]     Replicating from 192.168.42.141(192.168.42.141:3306)
Fri Aug 27 19:17:02 2021 - [info]     Not candidate for the new Master (no_master is set)
Fri Aug 27 19:17:02 2021 - [info]  Searching from candidate_master slaves which have received the latest relay log events..
Fri Aug 27 19:17:02 2021 - [info] New master is 192.168.42.142(192.168.42.142:3306)
Fri Aug 27 19:17:02 2021 - [info] Starting master failover..
Fri Aug 27 19:17:02 2021 - [info] 
From:
192.168.42.141(192.168.42.141:3306) (current master)
 +--192.168.42.142(192.168.42.142:3306)
 +--192.168.42.143(192.168.42.143:3306)

To:
192.168.42.142(192.168.42.142:3306) (new master)
 +--192.168.42.143(192.168.42.143:3306)
Fri Aug 27 19:17:02 2021 - [info] 
Fri Aug 27 19:17:02 2021 - [info] * Phase 3.3: New Master Recovery Phase..
Fri Aug 27 19:17:02 2021 - [info] 
Fri Aug 27 19:17:02 2021 - [info]  Waiting all logs to be applied.. 
Fri Aug 27 19:17:02 2021 - [info]   done.
Fri Aug 27 19:17:02 2021 - [info] Getting new master's binlog name and position..
Fri Aug 27 19:17:02 2021 - [info]  mysql02-bin.000003:194
Fri Aug 27 19:17:02 2021 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.42.142', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='replication', MASTER_PASSWORD='xxx';
Fri Aug 27 19:17:02 2021 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql02-bin.000003, 194, 527601db-06fa-11ec-8eb1-000c29a03380:1-6
Fri Aug 27 19:17:02 2021 - [info] Executing master IP activate script:
Fri Aug 27 19:17:02 2021 - [info]   /script/masterha/master_ip_failover --command=start --ssh_user=root --orig_master_host=192.168.42.141 --orig_master_ip=192.168.42.141 --orig_master_port=3306 --new_master_host=192.168.42.142 --new_master_ip=192.168.42.142 --new_master_port=3306 --new_master_user='mha'   --new_master_password=xxx
Unknown option: new_master_user
Unknown option: new_master_password


IN SCRIPT TEST====/usr/sbin/ip addr del 192.168.42.88/24 dev ens33 label ens33:1==/usr/sbin/ip addr add 192.168.42.88/24 brd 192.168.42.255 dev ens33 label ens33:1;/usr/sbin/arping -q -A -c 1 -I ens33 192.168.42.88;iptables -F;===

Enabling the VIP - 192.168.42.88 on the new master - 192.168.42.142 
Fri Aug 27 19:17:02 2021 - [info]  OK.
Fri Aug 27 19:17:02 2021 - [info] ** Finished master recovery successfully.
Fri Aug 27 19:17:02 2021 - [info] * Phase 3: Master Recovery Phase completed.
Fri Aug 27 19:17:02 2021 - [info] 
Fri Aug 27 19:17:02 2021 - [info] * Phase 4: Slaves Recovery Phase..
Fri Aug 27 19:17:02 2021 - [info] 
Fri Aug 27 19:17:02 2021 - [info] 
Fri Aug 27 19:17:02 2021 - [info] * Phase 4.1: Starting Slaves in parallel..
Fri Aug 27 19:17:02 2021 - [info] 
Fri Aug 27 19:17:02 2021 - [info] -- Slave recovery on host 192.168.42.143(192.168.42.143:3306) started, pid: 12892. Check tmp log /var/log/masterha/db1/192.168.42.143_3306_20210827191700.log if it takes time..
Fri Aug 27 19:17:04 2021 - [info] 
Fri Aug 27 19:17:04 2021 - [info] Log messages from 192.168.42.143 ...
Fri Aug 27 19:17:04 2021 - [info] 
Fri Aug 27 19:17:02 2021 - [info]  Resetting slave 192.168.42.143(192.168.42.143:3306) and starting replication from the new master 192.168.42.142(192.168.42.142:3306)..
Fri Aug 27 19:17:02 2021 - [info]  Executed CHANGE MASTER.
Fri Aug 27 19:17:03 2021 - [info]  Slave started.
Fri Aug 27 19:17:03 2021 - [info]  gtid_wait(527601db-06fa-11ec-8eb1-000c29a03380:1-6) completed on 192.168.42.143(192.168.42.143:3306). Executed 0 events.
Fri Aug 27 19:17:04 2021 - [info] End of log messages from 192.168.42.143.
Fri Aug 27 19:17:04 2021 - [info] -- Slave on host 192.168.42.143(192.168.42.143:3306) started.
Fri Aug 27 19:17:04 2021 - [info] All new slave servers recovered successfully.
Fri Aug 27 19:17:04 2021 - [info] 
Fri Aug 27 19:17:04 2021 - [info] * Phase 5: New master cleanup phase..
Fri Aug 27 19:17:04 2021 - [info] 
Fri Aug 27 19:17:04 2021 - [info] Resetting slave info on the new master..
Fri Aug 27 19:17:04 2021 - [info]  192.168.42.142: Resetting slave info succeeded.
Fri Aug 27 19:17:04 2021 - [info] Master failover to 192.168.42.142(192.168.42.142:3306) completed successfully.
Fri Aug 27 19:17:04 2021 - [info] Deleted server1 entry from /etc/mha/mha.conf .
Fri Aug 27 19:17:04 2021 - [info] 

----- Failover Report -----

mha: MySQL Master failover 192.168.42.141(192.168.42.141:3306) to 192.168.42.142(192.168.42.142:3306) succeeded

Master 192.168.42.141(192.168.42.141:3306) is down!

Check MHA Manager logs at mysql03:/var/log/masterha/app1/db1.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.42.141(192.168.42.141:3306)
Selected 192.168.42.142(192.168.42.142:3306) as a new master.
192.168.42.142(192.168.42.142:3306): OK: Applying all logs succeeded.
192.168.42.142(192.168.42.142:3306): OK: Activated master IP address.
192.168.42.143(192.168.42.143:3306): OK: Slave started, replicating from 192.168.42.142(192.168.42.142:3306)
192.168.42.142(192.168.42.142:3306): Resetting slave info succeeded.
Master failover to 192.168.42.142(192.168.42.142:3306) completed successfully.

1.8.14  模拟宕机修复

删除文件

先把192.168.42.143的这个文件删除（如果存在的话）

rm -rf  /var/log/masterha/db1/mha.failover.complete 

备份192.168.42.142主库以及查看指针位

 备份主库

mysqldump --single-transaction --master-data=2 --all-databases -uroot -p123456>all.sql

 把备份主库复制到192.168.42.141

scp all.sql root@192.168.42.141:/root

 

 

宕机需要复制主机的位置以及文件

查看192.168.42.142

mysql -uroot -p

show master status \G;

192.168.42.141主从配置

启动mysql

mysql start mysqld;

 执行all.sql

mysql -uroot -p<all.sql

 

因为192.168.42.141原来是主机，所以登录重置下master

登入

mysql -uroot -p

重置主机

reset master;

stop slave;

 由于

 

所以

change master to master_host='192.168.42.142',

master_user='replication',

master_password='123456',

MASTER_LOG_FILE='mysql02-bin.000001', MASTER_LOG_POS=154;

start slave;

show slave status \G;

 192.168.42.143重启以及查看状态

重启

nohup masterha_manager -conf=/etc/mha/mha.conf&> /etc/mha/manager.log &

查看状态

masterha_check_status --conf=/etc/mha/mha.conf

查看日志

tail -f /var/log/masterha/app1/db1.log

2014Team
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
阿里mysql 高可用方案
06-21
阿里mysql 高可用方案， 内部包含了阿里TMHA的实现细节。
MySQL高可用方案 MHA
07-26
MySQL高可用方案 MHA MySQL高可用方案 MHA MySQL高可用方案 MHA
【MHA】命系列介绍--[masterha_master_switch]
weixin_34236869的博客
09-25 293
介绍: masterha_master_switch 不监控主库，但是可以用于主库宕机后failover，可以用于在线切换主库 手动failover: 有时候需要手动执行failover机制，masterha_master_switch 命令可以用于 手动执行failover. eg： masterha_master_switch --master_st...
MHA之masterha_master_switch
weixin_34376562的博客
05-25 170
2019独角兽企业重金招聘Python工程师标准>>> ...
MySQL高可用解决方案――从主从复制到InnoDB Cluster架构
最新发布
学Java，找哪吒
03-25 4455
文末送5本《MySQL高可用解决方案――从主从复制到InnoDB Cluster架构》
Mysql之MHA搭建部署
月生的静心苑
02-28 1387
MHA 是由日本人 yoshinorim使用perl语言开发的比较成熟的 MySQL 高可用方案。MHA 能够在30秒内实现故障切换，并能在故障切换中，最大可能的保证数据一致性。MHA（Master HA）是一款开源的 MySQL 的高可用程序，它为 MySQL 主从复制架构提供了 automating master failover 功能。MHA 在监控到 master 节点故障时，会提升其中拥有最新数据的 slave 节点成为新的master 节点，在此期间，MHA 会通过于其它从节点获取额外信息来避免
用MHA实现mysql自动故障转移(配置vip模式，非keepalive）
雅冰石的专栏
08-07 5136
一：MHA介绍 什么是mha，有什么特性 1.主服务器的自动监控和故障转移 MHA监控复制架构的主服务器，一旦检测到主服务器故障，就会自动进行故障转移。即使有些从服务器没有收到最新的relaylog，MHA自动从最新的从服务器上识别差异的relaylog并把这些日志应用到其他从服务器上，因此所有的从服务器保持一致性了。MHA通常在几秒内完成故障转移，9-12秒可以检测出主服
MySQL MHA 管理维护总结
sinat_41780498的博客
12-16 844
第一部分：mha日常管理 1.查看ssh登陆是否成功 masterha_check_ssh --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf 2.查看复制是否建立好 masterha_check_repl --global_conf=/etc/masterha/master...
MHA 手动故障转移
乐沙弥的世界
04-23 4592
MHA提供了3种方式用于实现故障转移，分别自动故障转移，需要启用MHA监控；在无监控的情况下的手动故障转移以及基于在线手动切换。三种方式可以应对MySQL主从故障的任意场景。本文主要描述在无监控的情形是手动实现故障转移。供大家参考......
mha检测mysql报错_MySQL MHA 报错处理
weixin_39786155的博客
01-27 163
安装环境：CentOS 6.5 MySQL 5.7.22 MHA 0.561、找不到mysql 命令Sat Mar 23 07:17:50 2019 - [info] Connecting to [email protected](server2:22)..Checking slave recovery environment settings..Opening /data/mysql/rel...
MHA高可用配置与故障切换
qq_41582883的博客
04-03 191
主从复制 节点间的免密 server4： ssh-keygen server4和123三个节点免密 ssh-copy-id server1 ssh-copy-id server2 ssh-copy-id server3 server1、server2、server3之间互相免密 scp -r .ssh/ server1: scp -r .ssh/ server2: scp -r .ssh/ server3: 为保证实验环境的纯净，server1、server2、server 3三个节点：
MySQL高可用方案大全
05-17
Lvs+Keepalived+MySQL 单点写入主主同步高可用方案 Lvs+Keepalived+MySQL 单点写入读负载均衡主主同步高可用方案 Heartbeat高可用MySQL主主同步方案 Heartbeat+DRBD+MySQL高可用方案 MMM高可用MySQL方案
MySQL高可用架构MHA
01-09
MHA工作原理 部署MHA 1.配置四台主机ssh登录不用密码 ssh-keygen -t rsa ssh-copy-id 192.168.100.10（20.30.102） 2.上传安装包，解压编译安装 配置yum仓库 vim /etc/yum.repos.d/mhapath.repo ##进入yum仓库，...
金融行业MySQL高可用实践-明溪源
12-17
1)金融行业MySQL高可用方案选型 a)MySQL Replication – MHA (MySQL5.7不支持) b)MGR + 轻量级中间件MySQL Router统一访问 2)爱可生金融级MySQL高可用方案 3)某大行核心系统MySQL应用实践
mysql-mha-docker20190807.zip
12-18
基于Mysql_MHA实现的数据库主从高可用方案相关配置文件及脚本文件。其中包含docker-compose文件，自动主从脚本，检查ssh脚本，检查repl脚本等。
MySQL下高可用故障转移方案MHA的超级部署教程
09-10
主要介绍了MySQL下高可用故障切换方案MHA的超级部署教程,文中队MHA方案的一些特点做了介绍,示例基于Linux系统的服务器环境,需要的朋友可以参考下
MySQL 常用高可用方案
AlbenXie的博客
11-14 7084
在使用 MGR 时，如果要实现完整的高可用方案，就需要用到 InnoDB Cluster。MHA（Master High Avaliable） 是一款 MySQL 开源高可用程序，MHA 在监测到主实例无响应后，可以自动将同步最靠前的 Slave 提升为 Master，然后将其他所有的 Slave 重新指向新的 Master。其大致原理是：在主实例的 Keepalived 中，增加监测本机 MySQL 是否存活的脚本，如果监测 MySQL 挂了，就会重启 Keepalived，从而使 VIP 飘到从实例。
Mysql之高可用方案浅析
ldcaws的专栏
05-27 2934
在工程项目中，系统应用的高可用性越来越重要，业主越来越重视。其实高可用可以分为应用层高可用和数据层高可用，数据层高可用中常见的有关系型数据库mysql的高可用、非关系型NoSQl数据库redis的高可用等，下面聊聊典型的关系型数据库mysql的高可用方案。
mha mysql github_MHA+ProxySQL，mysql的高可用集群+读写分离（二）MHA搭建与VIP策略
weixin_31119221的博客
01-27 195
一 MHA介绍MHA是一个日本人yoshinorim创建的高可用架构，其实主要就是基于主从复制的一段perl程序，一个shell脚本。二 安装2.1 下载MHA1、安装perl环境sudo yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiR...
mysql高可用架构mha
08-30
MHA（Master High Availability）是一种用于MySQL数据库的高可用架构解决方案。它由日本DeNA公司开发，旨在实现MySQL主从复制的自动故障转移和故障恢复。 MHA架构通常由以下几个组件组成： 1. MHA Manager：负责监控MySQL主节点的状态，并在主节点故障时自动切换到备节点。MHA Manager使用SSH连接到主节点，并通过观察二进制日志来检测主节点是否正常工作。 2. Master Agent：安装在所有MySQL主节点和备节点上的代理程序。它负责与MHA Manager通信，并在主节点故障时执行故障转移操作。Master Agent会自动将备节点提升为新的主节点。 3. Slave Agent：安装在备节点上的代理程序。它负责监控备节点的状态，并将备节点的状态信息发送给Master Agent。 MHA的工作流程如下： 1. MHA Manager定期检查主节点的状态。如果主节点无法正常工作（如网络故障、MySQL进程崩溃等），MHA Manager会发起故障转移操作。 2. 在故障转移过程中，MHA Manager会将一个备节点提升为新的主节点，并更新其他备节点的配置，使它们成为新主节点的从节点。 3. MHA Manager会使用SSH连接到新主节点，并在新主节点上启动MySQL进程，实现自动的故障恢复。 总结来说，MHA是一种基于MySQL主从复制的高可用架构解决方案，能够自动监控和管理MySQL主节点和备节点，实现故障转移和故障恢复的自动化。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

2014Team

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值