mysql mha高可用集群

master： 192.168.1.51
slave01: 192.168.1.47
slave02: 192.168.1.48
manager: 192.168.1.23
VIP 192.168.1.80

1、关闭防火墙、selinux

2、配置服务器相互密钥登陆（远程端口10022）

3、三台服务器上都创建mha管理数据库的账号 (我是直接用的root账户，密码全设置的一样）

4、三台服务器上都创建用于主从复制的MySQL用户repl

一、数据库配置

master 添加

server_id=1
log_bin=mysql_bin
relay_log=relay_bin
log_slave_updates=on
gtid_mode=ON
enforce_gtid_consistency=1

binlog-ignore-db = sys
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema

plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled=1
loose_rpl_semi_sync_slave_enabled=1
loose_rpl_semi_sync_master_timeout=1000

slave001和slave002（server _id不一样）

server_id=2
log_bin=mysql_bin
relay_log=relay_bin
log_slave_updates=on
gtid_mode=ON
enforce_gtid_consistency=1
read_only=1
binlog-ignore-db = sys
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema

plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled=1
loose_rpl_semi_sync_slave_enabled=1
loose_rpl_semi_sync_master_timeout=1000

完成以上配置文件的修改后，分别重启这三个节点上的MySQL服务

查询master节点binlog日志及位点

show master status;

show slave hosts;

进入slave-01节点的MySQL命令行终端，分别执行如下语句来配置主从复制链路：
stop slave;
change master to master_host='192.168.1.51',master_port=3306,master_user='repl',master_password='xxxxxxxxx',master_log_file='mysql_bin.000011',master_log_pos=412;
start slave;
show slave status\G;

导出导入数据库
mysqldump -u root -p --master-data=1 --single-transaction -all-databases >/all.sql
source /all.sql

二、所有服务器都安装mha node节点

1、安装依赖环境

yum install -y epel-release
yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

2、下载mha node 软件

wget https://raw.githubusercontent.com/linyue515/mysql-master-ha/master/mha4mysql-node-0.58-0.el7.noarch.rpm

3、安装node

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

三、manager服务器安装mha manage

1、安装依赖环境

yum install -y epel-release
yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

2、下载mha manage 软件

wget https://raw.githubusercontent.com/linyue515/mysql-master-ha/master/mha4mysql-manager-0.58-0.el7.noarch.rpm

3、安装manage软件

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

4、创建mha配置文件mysql_mha.cnf(ssh端口10022)

vim /etc/mha/mysql_mha.cnf

[server default]
# mha用于访问数据库的账户和密码
user=root
password=xxxxx
# 指定mha的工作目录
manager_workdir=/home/mysql_mha
# mha日志文件的存放路径
manager_log=/home/mysql_mha/manager.log
# 指定mha在远程节点上的工作目录
remote_workdir=/home/mysql_mha/tmp
# 可以使用ssh登录的用户
ssh_user=root
ssh_port=10022
# 用于主从复制的MySQL用户和密码
repl_user=repl
repl_password=xxxxx
# 指定间隔多少秒检测一次
ping_interval=1
# 指定master节点存放binlog日志文件的目录
master_binlog_dir=/var/lib/mysql
# 指定一个脚本，该脚本实现了在主从切换之后，将虚拟IP漂移到新的Master上
master_ip_failover_script = /usr/bin/master_ip_failover
master_ip_online_change_script = /usr/bin/master_ip_online_change
# 指定用于二次检查节点状态的脚本
secondary_check_script=/usr/bin/masterha_secondary_check -s 192.168.1.51 -s 192.168.1.47 -s 192.168.1.48 --port=10022

# 配置集群中的节点信息
[server1]
hostname=192.168.1.51
port=3306
#master_binlog_dir=/usr/local/mysql/logs
# 指定该节点可以参与Master选举
candidate_master=1
check_repl_delay=0

[server2]
hostname=192.168.1.47
port=3306
#master_binlog_dir=/usr/local/mysql/logs
candidate_master=1
check_repl_delay=0

[server3]
hostname=192.168.1.48
port=3306
#master_binlog_dir=/usr/local/mysql/logs
# 指定该节点不能参与Master选举
no_master=1
ignore_fail=1

创建配置文件路径、日志文件路径

mkdir -p /etc/mha
mkdir -p /home/mysql_mha

5、配置VIP切换脚本 master_ip_failover(ssh端口10022)

vim -b /usr/bin/master_ip_failover (vim -b 查看是否有中文符号，后面带M的就是有中文字符，要删掉，不然在测试的时候会提示 /usr/bin/master_ip_failover没有那个文件或目录，找不到文件)

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

my (
    $command, $orig_master_host, $orig_master_ip,$ssh_user,
    $orig_master_port, $new_master_host, $new_master_ip,$new_master_port,
    $orig_master_ssh_port,$new_master_ssh_port,$new_master_user,$new_master_password
);

# 这里定义的虚拟IP可以根据实际情况进行修改
my $vip = '192.168.1.80/24';
my $key = '1';
# 这里的网卡名称 “ens224” 需要根据你机器的网卡名称进行修改
my $ssh_start_vip = "sudo /usr/sbin/ifconfig ens224:$key $vip";
my $ssh_stop_vip = "sudo /usr/sbin/ifconfig ens224:$key down";
#my $ssh_Bcast_arp= "sudo /sbin/arping -I bond0 -c 3 -A $vip";

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,
    'orig_master_ssh_port=i' => \$orig_master_ssh_port,
    'new_master_host=s'  => \$new_master_host,
    'new_master_ip=s'    => \$new_master_ip,
    'new_master_port=i'  => \$new_master_port,
    'new_master_ssh_port' => \$new_master_ssh_port,
    'new_master_user' => \$new_master_user,
    'new_master_password' => \$new_master_password

);

exit &main();

sub main {
    $ssh_user = defined $ssh_user ? $ssh_user : 'root';
    print "\n\nIN SCRIPT TEST====$ssh_user|$ssh_stop_vip==$ssh_user|$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();
        &start_arp();
            $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 -p 10022 $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip() {
    `ssh -p 10022 $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}


sub usage {
    print
    "Usage: master_ip_failover --command=start|stop|stopssh|status --ssh_user=user --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_h
ost=host --new_master_ip=ip --new_master_port=port\n";}

6、创建在线手动切换vip脚本master_ip_online_change(ssh端口10022)

vim -b /usr/bin/master_ip_online_change

#!/usr/bin/env perl
use strict; 
use warnings FATAL =>'all';
 
use Getopt::Long; 
 
my $vip = '192.168.1.80/24'; 
my $key = "1";  
my $ssh_start_vip = "/sbin/ifconfig ens224:$key $vip";  
my $ssh_stop_vip = "/sbin/ifconfig ens224:$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 ($@) {  
            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 "\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 ($@) {  
            warn $@;  
            exit $exit_code;  
        }  
        exit $exit_code;  
}  
elsif ( $command eq "status" ) {  
        print "Checking the Status of the script.. OK \n";  
        `ssh -p 10022 $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 -p 10022 $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 -p 10022 $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_maste
r_ip=ip --new_master_port=port\n";

将脚本赋予可执行权限

chmod +x /usr/bin/master_ip_failover

chmod +x /usr/bin/master_ip_online_change

四、在master服务器创建vip

ifconfig ens224:1 192.168.1.80/24

ifconfig ens224:1 del 192.168.1.80 #删除vip

ifconfig ens224:1 down #关闭vip

五、测试MHA配置信息是否正常

检查ssh配置

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

检查主从复制情况

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

六、启动mha(不能直接关闭窗口，要使用exit退出)

nohup masterha_manager --conf=/etc/mha/mysql_mha.cnf --ignore_last_failover /home/mysql_mha/manager.log 2>&1 &

查看mha状态
masterha_check_status --conf=/etc/mha/mysql_mha.cnf
关闭mha
masterha_stop --conf=/etc/mha/mysql_mha.cnf

查看mha日志

tail -30f /home/mysql_mha/manager.log

日志中大致的流程是检测到主库（192.168.1.51:3306）不可用-->连续试探3次（次数可自定义）-->检测进群中剩余存活的节点-->从备选主节点中选择一个节点为主节点-->漂移VIP至新的主节点（如果原主节点系统正常则将VIP在原主机上删除）-->拷贝原主节点的binlog日志-->新主节点判断是否需要补充日志-->其他节点全部改为从新主节点复制数据（组成新的集群）

七、手动进行主备切换(在进行手动切换前要先停值manager进程)

masterha_master_switch --conf=/etc/mha/mysql_mha.cnf --master_state=alive --new_master_host=192.168.1.47 --orig_master_is_new_slave --running_updates_limit=10000 --interactive=0

八、旧主启动之后，把它重新加入mha并把它重新设定为master的流程

set global read_only=1;设置为从
stop slave；
然后根据manage日志查切换到新的master时的master_log_file、master_log_pos

change master to master_host='192.168.1.47',master_port=3306,master_user='repl',master_password='xxxxxxxx',master_log_file='mysql_bin.000023',master_log_pos=779;

加入mha集群重新检测repl复制状态

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

没问题的话手动进行主备切换(在进行手动切换前要先停值manager进程)

masterha_master_switch --conf=/etc/mha/mysql_mha.cnf --master_state=alive --new_master_host=192.168.1.51 --orig_master_is_new_slave --running_updates_limit=10000 --interactive=0

没问题的话启动mha进程

报错：

主节点Slave IO未运行

[/usr/share/perl5/vendor_perl/MHA/Server.pm, ln490] Slave IO thread is not running on 192.168.1.51(192.168.1.51:3306)

因为主库自己设成自己从库了，你主库show slave status，就知道咋回事了；可以stop slave——reset slave——service mysqld restart。然后测试实施

ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS can

change master to master_auto_position=0;

Multi-master configuration is detected, but two or more masters are eith er writable (read-only is not set) or dead!

在现在为slave角色的服务器上

set global read_only=1;

参考：

https://blog.51cto.com/zero01/2468767
https://www.cnblogs.com/qixing/p/13205542.html
https://blog.csdn.net/fw19940314/article/details/107118776
https://www.lagou.com/lgeduarticle/129881.html
https://mp.weixin.qq.com/s?__biz=MzIwMjcwMTQzMA==&mid=2247483742&idx=1&sn=cd43e876326c815680424278368a8c38&chksm=96dbee47a1ac6751980bbe3b4ca2b91289f3eca48c5b339480369e95958600d2426e2b80cd74&mpshare=1&scene=24&srcid=1017OBKmvZ5iwnVgwBD8veOs&sharer_sharetime=1602901600026&sharer_shareid=914430d88182d4290318605488d02a01&key=221452a4d6b5ef37177a4e3daae922a8ec075c34fdc30f66a619e6d1291b5f042d03303e6d7e85f7c362ee7bd242bd7baa474d24bba076a4ce20087440481ecda973025eaae46bf98632fd75d1d9d325af4e3ccdc1d2bc95fb65b98f239d32510b58af3d8c2eb16130a68fc9cb010ba575d278e2d66df3b653b9a8b041bba2ef&ascene=14&uin=MjA2MzczMTEyMQ%3D%3D&devicetype=Windows+7+x64&version=62090529&lang=zh_CN&exportkey=Acyzr1VmssT6jrN8EdFdPK4%3D&pass_ticket=oyQy3uk3wXjNBom4WfmxkmHzUAQ%2FQmi7OdJgaxmrxurE54MfCPaaqy%2B5mv7QAYPe&wx_header=0

posted on 2020-10-23 15:58 小油2018 阅读(227) 评论(0) 编辑收藏举报