一、环境准备
1、主机：
| 主机 | 用途 |
|---|---|
| 192.168.10.11(linux1) | mysql(master) |
| 192.168.10.12(linux2) | mysql(slave1) |
| 192.168.10.13(linux3) | mysql(slave2) |
| 192.168.10.14(linux4) | MHA_node |
2、软件：
MySQL-5.7安装包(官网下载)
MHA_0.58 点击此处下载，提取码：2btf
其他相关依赖包如下：
下载链接：
点击此处可下载相关依赖包，提取码： 92wd
二、搭建步骤
1、搭建mysql主从环境
1.1 修改主机名，每台主机都要修改
[root@linux1 ~]# vi /etc/hosts --添加如下内容：
192.168.10.11 linux1
192.168.10.12 linux2
192.168.10.13 linux3
192.168.10.14 linux4
[root@linux1 ~]# hostnamectl set-hostname linux1
[root@linux2 ~]# hostnamectl set-hostname linux2
[root@linux3 ~]# hostnamectl set-hostname linux3
[root@linux4 ~]# hostnamectl set-hostname linux4
1.2 关闭防火墙
关闭防火墙
[root@linux1 ~]# systemctl stop firewalld
[root@linux1 ~]# systemctl disable firewalld
[root@linux1 ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:firewalld(1)
关闭selinux(或者直接使用vi修改也可以)
[root@linux4 selinux]# cd /etc/selinux/
[root@linux4 selinux]# sed -i 's/^SELINUX=.*/SELINUX=disabled/g' config
[root@linux4 selinux]# cat config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
1.3 安装MySQL(此步骤省略)
1.4 开启GTID模式
相关参数如下：
server-id=11 # 根据自己的主机来定，每个mysql-server的id务必不一样
log_bin=on
binlog_format=row
gtid_mode=ON
log_slave_updates=1 #5.6要打开，5.7可以不打开
enforce-gtid-consistency=1
1.5 最终版的参数文件内容如下
[root@linux1 ~]# vi /etc/my.cnf
[mysql]
user=root
password=123456
prompt=linux1 [\\d]>
[mysqld]
user=mysql
basedir=/usr/local/mysql/
datadir=/data/mysql57
log_error=error.log
socket=/tmp/mysql.sock
server_id=11
port=3306
secure-file-priv=/tmp
autocommit=1
transaction_isolation=read-committed
log_timestamps=system
#bin_log
log_bin=/data/mysql57/mysql-bin
binlog_format=row
expire_logs_days=7
#replication
sync_binlog=1
innodb_flush_log_at_trx_commit=1
slave_parallel_type=logical_clock
slave_parallel_workers=8
plugin-dir=/usr/local/mysql/lib/plugin
#rpl_semi_sync_master_enabled=1 #这个参数需要先注释掉，等到配置完半同步再打开
#rpl_semi_sync_slave_enabled=1 #这个参数需要先注释掉，等到配置完半同步再打开
master_info_repository=table
#relay_log
relay_log=mysql-relay
relay_log_info_repository=TABLE
relay_log_recovery=1
#gtid
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysqldump]
single-transaction
master-data=1
1.6 重启数据库(三个都重启)
[root@linux1 tmp]# /etc/init.d/mysql.server restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
1.7 配置主从
GTID模式，初始化完成后，直接指定即可。
# linux1创建rpl用户并授权用于复制
linux1 [(none)]>create user rpl identified by '123456';
Query OK, 0 rows affected (0.01 sec)
linux1 [(none)]>grant all on *.* to rpl;
Query OK, 0 rows affected (0.00 sec)
linux1 [(none)]>flush privileges;
Query OK, 0 rows affected (0.00 sec)
从库做change master(两个从库都要做)：
linux2 [(none)]>change master to master_host='192.168.10.11',master_port=3306,master_user='rpl',master_password='123456',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
linux2 [(none)]>
linux2 [(none)]>start slave;
Query OK, 0 rows affected (0.01 sec)
linux2 [(none)]>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.11
Master_User: rpl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 782
Relay_Log_File: mysql-relay.000003
Relay_Log_Pos: 995
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 782
Relay_Log_Space: 1655
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: 8d0a9377-1267-11ed-9186-000c2997d67e
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 8d0a9377-1267-11ed-9186-000c2997d67e:1-4
Executed_Gtid_Set: 8d0a9377-1267-11ed-9186-000c2997d67e:1-4,
8fe7f20a-1267-11ed-a7a4-000c29e9f399:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
1.8 开启半同步复制(每个mysql都要执行)
安装半同步插件：
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
启用半同步参数：
mysql> set global rpl_semi_sync_master_enabled=1; --开启半同步复制的主
Query OK, 0 rows affected (0.00 sec)
mysql> set global rpl_semi_sync_slave_enabled=1; --开启半同步复制的从
Query OK, 0 rows affected (0.00 sec)
**注意：两个从库安装完插件，开启半同步参数后，还要重启slave**
linux2 [(none)]>stop slave;
Query OK, 0 rows affected (0.00 sec)
linux2 [(none)]>start slave;
Query OK, 0 rows affected (0.01 sec)
请注意：启用完半同步参数后，将参数文件中注释掉的如下2个参数打开
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
1.9 验证主从
主库建库建表，插入数据
linux1 [(none)]>create database test;
Query OK, 1 row affected (0.00 sec)
linux1 [(none)]>use test;
Database changed
linux1 [test]>create table a(id int);
Query OK, 0 rows affected (0.01 sec)
linux1 [test]>insert into a values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
linux1 [test]>select * from a;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
从库slave1查询：
linux2 [(none)]>use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
linux2 [test]>select * from a;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
从库slave2查询：
linux3 [(none)]>use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
linux3 [test]>select * from a;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
主库清空表：
linux1 [test]>truncate table a;
Query OK, 0 rows affected (0.00 sec)
linux1 [test]>select * from a;
Empty set (0.00 sec)
从库slave1查询：
linux2 [test]>select * from a;
Empty set (0.01 sec)
从库slave2查询：
linux3 [test]>select * from a;
Empty set (0.00 sec)
2、打通ssh互信(ssh无密码认证)
[root@linux1 ~]# ssh-keygen -t rsa
[root@linux2 ~]# ssh-keygen -t rsa
[root@linux3 ~]# ssh-keygen -t rsa
[root@linux4 ~]# ssh-keygen -t rsa
执行完上述命令后，会在用户家目录下.ssh/目录下生成秘钥文件，将公钥文件拷贝到同一台主机上
[root@linux1 .ssh]# scp id_rsa.pub 192.168.10.14:/root/.ssh/linux1.pub
[root@linux2 .ssh]# scp id_rsa.pub 192.168.10.14:/root/.ssh/linux2.pub
[root@linux3 .ssh]# scp id_rsa.pub 192.168.10.14:/root/.ssh/linux3.pub
在linux4上生成新的公钥文件
[root@linux4 .ssh]# cat *.pub >> authorized_keys
生成新的公钥文件之后，将新的公钥文件拷贝到其他几个主机的.ssh目录下即可
[root@linux4 .ssh]# scp authorized_keys linux1:/root/.ssh/
[root@linux4 .ssh]# scp authorized_keys linux2:/root/.ssh/
[root@linux4 .ssh]# scp authorized_keys linux3:/root/.ssh/
配置完之后ssh验证一下：
linux1
[root@linux1 .ssh]# ssh linux2 date
[root@linux1 .ssh]# ssh linux3 date
[root@linux1 .ssh]# ssh linux4 date
linux2
[root@linux2 .ssh]# ssh linux1 date
[root@linux2 .ssh]# ssh linux3 date
[root@linux2 .ssh]# ssh linux4 date
linux3
[root@linux3 .ssh]# ssh linux1 date
[root@linux3 .ssh]# ssh linux2 date
[root@linux3 .ssh]# ssh linux4 date
linux4
[root@linux4 .ssh]# ssh linux1 date
[root@linux4 .ssh]# ssh linux2 date
[root@linux4 .ssh]# ssh linux3 date
ssh连接不需要输入密码就说明配置成功
3、安装MHA软件
linux1/linux2/linux3 上只安装mha_node就可以，如果提示需要安装依赖包，自行安装即可，此处不在赘述。
先做软链接(3个mysql服务器都做)：
[root@linux3 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@linux3 ~]# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
安装MHA软件
[root@linux1 mha]# rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@linux2 mha]# rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@linux3 mha]# rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
linux4安装mha_manger和mha_node
[root@linux4 mha]# rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@linux4 mha]# rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
**请注意此处会报错，缺失相关的依赖包，对应的依赖包已经放在前面的链接中，自行下载后使用rpm安装即可(请注意包的依赖顺序，报错的话可以先尝试安装其他包)。**
安装完成后会在/usr/bin下生成下面这些可执行文件
[root@linux4 bin]# pwd
/usr/bin
[root@linux4 bin]# ll masterha_*
-rwxr-xr-x. 1 root root 1995 Mar 23 2018 masterha_check_repl
-rwxr-xr-x. 1 root root 1779 Mar 23 2018 masterha_check_ssh
-rwxr-xr-x. 1 root root 1865 Mar 23 2018 masterha_check_status
-rwxr-xr-x. 1 root root 3201 Mar 23 2018 masterha_conf_host
-rwxr-xr-x. 1 root root 2517 Mar 23 2018 masterha_manager
-rwxr-xr-x. 1 root root 2165 Mar 23 2018 masterha_master_monitor
-rwxr-xr-x. 1 root root 2373 Mar 23 2018 masterha_master_switch
-rwxr-xr-x. 1 root root 5172 Mar 23 2018 masterha_secondary_check
-rwxr-xr-x. 1 root root 1739 Mar 23 2018 masterha_stop
可执行文件说明：
masterha_check_repl: 检查mysql复制情况
masterha_check_ssh: 检查MHA的ssh配置情况
masterha_check_status: 检查当前MHA运行状态
masterha_conf_host: 添加或者删除配置的server信息
masterha_manager: 启动MHA
masterha_master_monitor: 检查master是否宕机
masterha_master_switch: 控制故障转移(自动或者手动)
masterha_secondary_check: master状态二次检查
masterha_stop: 停止MHA
4、修改相关配置
**在MySQL的主上创建MHA使用的用户**
linux1 [(none)]>create user mha identified by '123456';
Query OK, 0 rows affected (0.00 sec)
linux1 [(none)]>grant all on *.* to mha;
Query OK, 0 rows affected (0.00 sec)
linux1 [(none)]>flush privileges;
Query OK, 0 rows affected (0.00 sec)
**修改MHA主配置文件**
[root@linux4 mha]# cd /etc/mha
[root@linux4 mha]# vi app1.conf #这是MHA的主配置文件，定义了工作目录，主机，切换脚本等内容
[server default]
manager_log=/usr/local/mha/manager.log #mha_manager日志位置
manager_workdir=/usr/local/mha #mha_manager工作目录
master_binlog_dir=/data/mysql57 #主库binlog的位置
master_ip_failover_script=/usr/local/mha/scripts/master_ip_failover #failover脚本位置
master_ip_online_change_script=/usr/local/mha/scripts/master_ip_online_change #online_change脚本位置
user=mha #设置监控用户的用户名mha
password=123456 #监控用户mha的密码123456
ping_interval=1 #监控主库发送ping包的时间间隔
ssh_user=root #ssh_user的用户名
remote_workdir=/tmp
repl_user=rpl #复制用户的用户名
repl_password=123456 #复制用户的密码
secondary_check_script=/usr/bin/masterha_secondary_check -s 192.168.10.12 -s 192.168.10.13
shutdown_script=""
[server1] #master的host和port
hostname=192.168.10.11
port=3306
[server2] #slave1的host和port
candidate_master=1 #表示可以作为备选的master，当主出现问题时，将主切换到该server
check_repl_delay=0 #主从切换时，忽略被选择的slave上的同步延时
hostname=192.168.10.12
port=3306
[server3] #slave2的host和port
hostname=192.168.10.13
port=3306
[root@linux4 mha]#
**修改master_ip_failover脚本**
[root@linux4 scripts]# vi /usr/local/mha/scripts/master_ip_failover
#!/usr/bin/env perl
# Copyright (C) 2011 DeNA Co.,Ltd.
# 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
);
# 请注意需要修改的内容只有下面这四行(根据自己的地址添加下面4行内容即可)
my $vip = '192.168.10.20/24'; #mha的虚拟ip地址
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip"; #启动虚拟ip的命令，ens33是自己的网卡，根据自己的设备来填写即可
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down"; #关闭虚拟ip的命令，ens33是自己的网卡，根据自己的设备来填写即可
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 {
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 \"`;
}
sub stop_vip() {
return 0 unless ($ssh_user);
`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@linux4 scripts]#
**修改master_ip_online_change脚本**
[root@linux4 scripts]# cat master_ip_online_change
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
use MHA::DBHelper;
use MHA::NodeUtil;
use Time::HiRes qw( sleep gettimeofday tv_interval );
use Data::Dumper;
my $_tstart;
my $_running_interval = 0.1;
#注意：需要修改的只有下面两行，如果没有直接添加即可
my $vip = "192.168.10.20"; #mha的vip地址
my $if = "ens33:1"; #绑定vip地址的设备名称
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 drop_vip {
my $output = `ssh -o ConnectTimeout=15 -o ConnectionAttempts=3 $orig_master_host /sbin/ip addr del $vip/24 dev $if`;
}
sub add_vip {
my $output = `ssh -o ConnectTimeout=15 -o ConnectionAttempts=3 $new_master_host /sbin/ip addr add $vip/24 dev $if`;
}
sub current_time_us {
my ( $sec, $microsec ) = gettimeofday();
my $curdate = localtime($sec);
return $curdate . " " . sprintf( "%06d", $microsec );
}
sub sleep_until {
my $elapsed = tv_interval($_tstart);
if ( $_running_interval > $elapsed ) {
sleep( $_running_interval - $elapsed );
}
}
sub get_threads_util {
my $dbh = shift;
my $my_connection_id = shift;
my $running_time_threshold = shift;
my $type = shift;
$running_time_threshold = 0 unless ($running_time_threshold);
$type = 0 unless ($type);
my @threads;
my $sth = $dbh->prepare("SHOW PROCESSLIST");
$sth->execute();
while ( my $ref = $sth->fetchrow_hashref() ) {
my $id = $ref->{Id};
my $user = $ref->{User};
my $host = $ref->{Host};
my $command = $ref->{Command};
my $state = $ref->{State};
my $query_time = $ref->{Time};
my $info = $ref->{Info};
$info =~ s/^\s*(.*?)\s*$/$1/ if defined($info);
next if ( $my_connection_id == $id );
next if ( defined($query_time) && $query_time < $running_time_threshold );
next if ( defined($command) && $command eq "Binlog Dump" );
next if ( defined($user) && $user eq "system user" );
next
if ( defined($command)
&& $command eq "Sleep"
&& defined($query_time)
&& $query_time >= 1 );
if ( $type >= 1 ) {
next if ( defined($command) && $command eq "Sleep" );
next if ( defined($command) && $command eq "Connect" );
}
if ( $type >= 2 ) {
next if ( defined($info) && $info =~ m/^select/i );
next if ( defined($info) && $info =~ m/^show/i );
}
push @threads, $ref;
}
return @threads;
}
sub main {
if ( $command eq "stop" ) {
## Gracefully killing connections on the current master
# 1. Set read_only= 1 on the new master
# 2. DROP USER so that no app user can establish new connections
# 3. Set read_only= 1 on the current master
# 4. Kill current queries
# * Any database access failure will result in script die.
my $exit_code = 1;
eval {
## Setting read_only=1 on the new master (to avoid accident)
my $new_master_handler = new MHA::DBHelper();
# args: hostname, port, user, password, raise_error(die_on_error)_ or_not
$new_master_handler->connect( $new_master_ip, $new_master_port,
$new_master_user, $new_master_password, 1 );
print current_time_us() . " Set read_only on the new master.. ";
$new_master_handler->enable_read_only();
if ( $new_master_handler->is_read_only() ) {
print "ok.\n";
}
else {
die "Failed!\n";
}
$new_master_handler->disconnect();
# Connecting to the orig master, die if any database error happens
my $orig_master_handler = new MHA::DBHelper();
$orig_master_handler->connect( $orig_master_ip, $orig_master_port,
$orig_master_user, $orig_master_password, 1 );
## Drop application user so that nobody can connect. Disabling per-session binlog beforehand
$orig_master_handler->disable_log_bin_local();
# print current_time_us() . " Drpping app user on the orig master..\n";
print current_time_us() . " drop vip $vip..\n";
#drop_app_user($orig_master_handler);
&drop_vip();
## Waiting for N * 100 milliseconds so that current connections can exit
my $time_until_read_only = 15;
$_tstart = [gettimeofday];
my @threads = get_threads_util( $orig_master_handler->{dbh},
$orig_master_handler->{connection_id} );
while ( $time_until_read_only > 0 && $#threads >= 0 ) {
if ( $time_until_read_only % 5 == 0 ) {
printf
"%s Waiting all running %d threads are disconnected.. (max %d milliseconds)\n",
current_time_us(), $#threads + 1, $time_until_read_only * 100;
if ( $#threads < 5 ) {
print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n"
foreach (@threads);
}
}
sleep_until();
$_tstart = [gettimeofday];
$time_until_read_only--;
@threads = get_threads_util( $orig_master_handler->{dbh},
$orig_master_handler->{connection_id} );
}
## Setting read_only=1 on the current master so that nobody(except SUPER) can write
print current_time_us() . " Set read_only=1 on the orig master.. ";
$orig_master_handler->enable_read_only();
if ( $orig_master_handler->is_read_only() ) {
print "ok.\n";
}
else {
die "Failed!\n";
}
## Waiting for M * 100 milliseconds so that current update queries can complete
my $time_until_kill_threads = 5;
@threads = get_threads_util( $orig_master_handler->{dbh},
$orig_master_handler->{connection_id} );
while ( $time_until_kill_threads > 0 && $#threads >= 0 ) {
if ( $time_until_kill_threads % 5 == 0 ) {
printf
"%s Waiting all running %d queries are disconnected.. (max %d milliseconds)\n",
current_time_us(), $#threads + 1, $time_until_kill_threads * 100;
if ( $#threads < 5 ) {
print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n"
foreach (@threads);
}
}
sleep_until();
$_tstart = [gettimeofday];
$time_until_kill_threads--;
@threads = get_threads_util( $orig_master_handler->{dbh},
$orig_master_handler->{connection_id} );
}
## Terminating all threads
print current_time_us() . " Killing all application threads..\n";
$orig_master_handler->kill_threads(@threads) if ( $#threads >= 0 );
print current_time_us() . " done.\n";
$orig_master_handler->enable_log_bin_local();
$orig_master_handler->disconnect();
## After finishing the script, MHA executes FLUSH TABLES WITH READ LOCK
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
## Activating master ip on the new master
# 1. Create app user with write privileges
# 2. Moving backup script if needed
# 3. Register new master's ip to the catalog database
# We don't return error even though activating updatable accounts/ip failed so that we don't interrupt slaves' recovery.
# If exit code is 0 or 10, MHA does not abort
my $exit_code = 10;
eval {
my $new_master_handler = new MHA::DBHelper();
# args: hostname, port, user, password, raise_error_or_not
$new_master_handler->connect( $new_master_ip, $new_master_port,
$new_master_user, $new_master_password, 1 );
## Set read_only=0 on the new master
$new_master_handler->disable_log_bin_local();
print current_time_us() . " Set read_only=0 on the new master.\n";
$new_master_handler->disable_read_only();
## Creating an app user on the new master
#print current_time_us() . " Creating app user on the new master..\n";
print current_time_us() . "Add vip $vip on $if..\n";
# create_app_user($new_master_handler);
&add_vip();
$new_master_handler->enable_log_bin_local();
$new_master_handler->disconnect();
## Update master ip on the catalog database, etc
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
# do nothing
exit 0;
}
else {
&usage();
exit 1;
}
}
sub usage {
print
"Usage: master_ip_online_change --command=start|stop|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";
die;
}
[root@linux4 scripts]#
**在MySQL的master上手动启用虚拟ip**
[root@linux1 ~]# ifconfig ens33:1 192.168.10.20/24
[root@linux1 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:97:d6:7e brd ff:ff:ff:ff:ff:ff
inet 192.168.10.11/24 brd 192.168.10.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.10.20/24 brd 192.168.10.255 scope global secondary ens33:1
valid_lft forever preferred_lft forever
inet6 fe80::774d:d3f0:9f79:2054/64 scope link noprefixroute
valid_lft forever preferred_lft forever
**MHA相关的状态检查**
#ssh状态检查
[root@linux4 ~]# masterha_check_ssh --conf=/etc/mha/app1.conf
Tue Aug 2 21:57:56 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Aug 2 21:57:56 2022 - [info] Reading application default configuration from /etc/mha/app1.conf..
Tue Aug 2 21:57:56 2022 - [info] Reading server configuration from /etc/mha/app1.conf..
Tue Aug 2 21:57:56 2022 - [info] Starting SSH connection tests..
Tue Aug 2 21:57:57 2022 - [debug]
Tue Aug 2 21:57:56 2022 - [debug] Connecting via SSH from root@192.168.10.11(192.168.10.11:22) to root@192.168.10.12(192.168.10.12:22)..
Tue Aug 2 21:57:56 2022 - [debug] ok.
Tue Aug 2 21:57:56 2022 - [debug] Connecting via SSH from root@192.168.10.11(192.168.10.11:22) to root@192.168.10.13(192.168.10.13:22)..
Tue Aug 2 21:57:56 2022 - [debug] ok.
Tue Aug 2 21:57:57 2022 - [debug]
Tue Aug 2 21:57:56 2022 - [debug] Connecting via SSH from root@192.168.10.12(192.168.10.12:22) to root@192.168.10.11(192.168.10.11:22)..
Tue Aug 2 21:57:56 2022 - [debug] ok.
Tue Aug 2 21:57:56 2022 - [debug] Connecting via SSH from root@192.168.10.12(192.168.10.12:22) to root@192.168.10.13(192.168.10.13:22)..
Tue Aug 2 21:57:57 2022 - [debug] ok.
Tue Aug 2 21:57:58 2022 - [debug]
Tue Aug 2 21:57:57 2022 - [debug] Connecting via SSH from root@192.168.10.13(192.168.10.13:22) to root@192.168.10.11(192.168.10.11:22)..
Tue Aug 2 21:57:57 2022 - [debug] ok.
Tue Aug 2 21:57:57 2022 - [debug] Connecting via SSH from root@192.168.10.13(192.168.10.13:22) to root@192.168.10.12(192.168.10.12:22)..
Tue Aug 2 21:57:57 2022 - [debug] ok.
Tue Aug 2 21:57:58 2022 - [info] All SSH connection tests passed successfully.
#MySQL主从同步检查
[root@linux4 ~]# masterha_check_repl --conf=/etc/mha/app1.conf
Tue Aug 2 21:58:05 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Aug 2 21:58:05 2022 - [info] Reading application default configuration from /etc/mha/app1.conf..
Tue Aug 2 21:58:05 2022 - [info] Reading server configuration from /etc/mha/app1.conf..
Tue Aug 2 21:58:05 2022 - [info] MHA::MasterMonitor version 0.58.
Tue Aug 2 21:58:06 2022 - [info] GTID failover mode = 1
Tue Aug 2 21:58:06 2022 - [info] Dead Servers:
Tue Aug 2 21:58:06 2022 - [info] Alive Servers:
Tue Aug 2 21:58:06 2022 - [info] 192.168.10.11(192.168.10.11:3306)
Tue Aug 2 21:58:06 2022 - [info] 192.168.10.12(192.168.10.12:3306)
Tue Aug 2 21:58:06 2022 - [info] 192.168.10.13(192.168.10.13:3306)
Tue Aug 2 21:58:06 2022 - [info] Alive Slaves:
Tue Aug 2 21:58:06 2022 - [info] 192.168.10.12(192.168.10.12:3306) Version=5.7.37-log (oldest major version between slaves) log-bin:enabled
Tue Aug 2 21:58:06 2022 - [info] GTID ON
Tue Aug 2 21:58:06 2022 - [info] Replicating from 192.168.10.11(192.168.10.11:3306)
Tue Aug 2 21:58:06 2022 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Aug 2 21:58:06 2022 - [info] 192.168.10.13(192.168.10.13:3306) Version=5.7.37-log (oldest major version between slaves) log-bin:enabled
Tue Aug 2 21:58:06 2022 - [info] GTID ON
Tue Aug 2 21:58:06 2022 - [info] Replicating from 192.168.10.11(192.168.10.11:3306)
Tue Aug 2 21:58:06 2022 - [info] Current Alive Master: 192.168.10.11(192.168.10.11:3306)
Tue Aug 2 21:58:06 2022 - [info] Checking slave configurations..
Tue Aug 2 21:58:06 2022 - [info] read_only=1 is not set on slave 192.168.10.12(192.168.10.12:3306).
Tue Aug 2 21:58:06 2022 - [info] read_only=1 is not set on slave 192.168.10.13(192.168.10.13:3306).
Tue Aug 2 21:58:06 2022 - [info] Checking replication filtering settings..
Tue Aug 2 21:58:06 2022 - [info] binlog_do_db= , binlog_ignore_db=
Tue Aug 2 21:58:06 2022 - [info] Replication filtering check ok.
Tue Aug 2 21:58:06 2022 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Tue Aug 2 21:58:06 2022 - [info] Checking SSH publickey authentication settings on the current master..
Tue Aug 2 21:58:06 2022 - [info] HealthCheck: SSH to 192.168.10.11 is reachable.
Tue Aug 2 21:58:06 2022 - [info]
192.168.10.11(192.168.10.11:3306) (current master)
+--192.168.10.12(192.168.10.12:3306)
+--192.168.10.13(192.168.10.13:3306)
Tue Aug 2 21:58:06 2022 - [info] Checking replication health on 192.168.10.12..
Tue Aug 2 21:58:06 2022 - [info] ok.
Tue Aug 2 21:58:06 2022 - [info] Checking replication health on 192.168.10.13..
Tue Aug 2 21:58:06 2022 - [info] ok.
Tue Aug 2 21:58:06 2022 - [info] Checking master_ip_failover_script status:
Tue Aug 2 21:58:06 2022 - [info] /usr/local/mha/scripts/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.10.11 --orig_master_ip=192.168.10.11 --orig_master_port=3306
IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 192.168.10.20/24===
Checking the Status of the script.. OK
Tue Aug 2 21:58:06 2022 - [info] OK.
Tue Aug 2 21:58:06 2022 - [warning] shutdown_script is not defined.
Tue Aug 2 21:58:06 2022 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
5、启动与关闭MHA
**启动MHA**
[root@linux4 mha]# nohup masterha_manager --conf=/etc/mha/app1.conf --ignore_last_failover > /usr/local/mha/mha_manager.log < /dev/null 2>&1 &
[1] 3111
[root@linux4 ~]#
**检查MHA运行状态**
[root@linux4 mha]# masterha_check_status --conf=/etc/mha/app1.conf
app1 (pid:3111) is running(0:PING_OK), master:192.168.10.11
[root@linux4 mha]#
**关闭MHA**
[root@linux4 mha]# masterha_stop --conf=/etc/mha/app1.conf
Stopped app1 successfully.
[1]+ Exit 1 nohup masterha_manager --conf=/etc/mha/app1.conf --ignore_last_failover > /usr/local/mha/mha_manager.log < /dev/null 2>&1
[root@linux4 mha]#
**查看MHA运行状态**
[root@linux4 mha]# masterha_check_status --conf=/etc/mha/app1.conf
app1 is stopped(2:NOT_RUNNING).
[root@linux4 mha]#
三、故障演练
1、online_change
#请注意：在线切换前务必停掉MHA
**执行在线切换命令**
[root@linux4 mha]# masterha_master_switch --conf=/etc/mha/app1.conf --master_state=alive --new_master_host=192.168.10.12 --orig_master_is_new_slave
Tue Aug 2 22:03:16 2022 - [info] MHA::MasterRotate version 0.58.
Tue Aug 2 22:03:16 2022 - [info] Starting online master switch..
Tue Aug 2 22:03:16 2022 - [info]
Tue Aug 2 22:03:16 2022 - [info] * Phase 1: Configuration Check Phase..
Tue Aug 2 22:03:16 2022 - [info]
Tue Aug 2 22:03:16 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Aug 2 22:03:16 2022 - [info] Reading application default configuration from /etc/mha/app1.conf..
Tue Aug 2 22:03:16 2022 - [info] Reading server configuration from /etc/mha/app1.conf..
Tue Aug 2 22:03:17 2022 - [info] GTID failover mode = 1
Tue Aug 2 22:03:17 2022 - [info] Current Alive Master: 192.168.10.11(192.168.10.11:3306)
Tue Aug 2 22:03:17 2022 - [info] Alive Slaves:
Tue Aug 2 22:03:17 2022 - [info] 192.168.10.12(192.168.10.12:3306) Version=5.7.37-log (oldest major version between slaves) log-bin:enabled
Tue Aug 2 22:03:17 2022 - [info] GTID ON
Tue Aug 2 22:03:17 2022 - [info] Replicating from 192.168.10.11(192.168.10.11:3306)
Tue Aug 2 22:03:17 2022 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Aug 2 22:03:17 2022 - [info] 192.168.10.13(192.168.10.13:3306) Version=5.7.37-log (oldest major version between slaves) log-bin:enabled
Tue Aug 2 22:03:17 2022 - [info] GTID ON
Tue Aug 2 22:03:17 2022 - [info] Replicating from 192.168.10.11(192.168.10.11:3306)
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.10.11(192.168.10.11:3306)? (YES/no): YES
Tue Aug 2 22:03:18 2022 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Tue Aug 2 22:03:18 2022 - [info] ok.
Tue Aug 2 22:03:18 2022 - [info] Checking MHA is not monitoring or doing failover..
Tue Aug 2 22:03:18 2022 - [info] Checking replication health on 192.168.10.12..
Tue Aug 2 22:03:18 2022 - [info] ok.
Tue Aug 2 22:03:18 2022 - [info] Checking replication health on 192.168.10.13..
Tue Aug 2 22:03:18 2022 - [info] ok.
Tue Aug 2 22:03:18 2022 - [info] 192.168.10.12 can be new master.
Tue Aug 2 22:03:18 2022 - [info]
From:
192.168.10.11(192.168.10.11:3306) (current master)
+--192.168.10.12(192.168.10.12:3306)
+--192.168.10.13(192.168.10.13:3306)
To:
192.168.10.12(192.168.10.12:3306) (new master)
+--192.168.10.13(192.168.10.13:3306)
+--192.168.10.11(192.168.10.11:3306)
Starting master switch from 192.168.10.11(192.168.10.11:3306) to 192.168.10.12(192.168.10.12:3306)? (yes/NO): YES
Tue Aug 2 22:03:20 2022 - [info] Checking whether 192.168.10.12(192.168.10.12:3306) is ok for the new master..
Tue Aug 2 22:03:20 2022 - [info] ok.
Tue Aug 2 22:03:20 2022 - [info] 192.168.10.11(192.168.10.11:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Tue Aug 2 22:03:20 2022 - [info] 192.168.10.11(192.168.10.11:3306): Resetting slave pointing to the dummy host.
Tue Aug 2 22:03:20 2022 - [info] ** Phase 1: Configuration Check Phase completed.
Tue Aug 2 22:03:20 2022 - [info]
Tue Aug 2 22:03:20 2022 - [info] * Phase 2: Rejecting updates Phase..
Tue Aug 2 22:03:20 2022 - [info]
Tue Aug 2 22:03:20 2022 - [info] Executing master ip online change script to disable write on the current master:
Tue Aug 2 22:03:20 2022 - [info] /usr/local/mha/scripts/master_ip_online_change --command=stop --orig_master_host=192.168.10.11 --orig_master_ip=192.168.10.11 --orig_master_port=3306 --orig_master_user='mha' --new_master_host=192.168.10.12 --new_master_ip=192.168.10.12 --new_master_port=3306 --new_master_user='mha' --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx
Tue Aug 2 22:03:20 2022 454739 Set read_only on the new master.. ok.
Tue Aug 2 22:03:20 2022 458415 drop vip 192.168.10.20..
Tue Aug 2 22:03:20 2022 600629 Waiting all running 2 threads are disconnected.. (max 1500 milliseconds)
{'Time' => '1341','db' => undef,'Id' => '5','User' => 'rpl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'linux2:59856'}
{'Time' => '1252','db' => undef,'Id' => '6','User' => 'rpl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'linux3:47776'}
Tue Aug 2 22:03:21 2022 104048 Waiting all running 2 threads are disconnected.. (max 1000 milliseconds)
{'Time' => '1342','db' => undef,'Id' => '5','User' => 'rpl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'linux2:59856'}
{'Time' => '1253','db' => undef,'Id' => '6','User' => 'rpl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'linux3:47776'}
Tue Aug 2 22:03:21 2022 608431 Waiting all running 2 threads are disconnected.. (max 500 milliseconds)
{'Time' => '1342','db' => undef,'Id' => '5','User' => 'rpl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'linux2:59856'}
{'Time' => '1253','db' => undef,'Id' => '6','User' => 'rpl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'linux3:47776'}
Tue Aug 2 22:03:22 2022 111343 Set read_only=1 on the orig master.. ok.
Tue Aug 2 22:03:22 2022 112773 Waiting all running 2 queries are disconnected.. (max 500 milliseconds)
{'Time' => '1343','db' => undef,'Id' => '5','User' => 'rpl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'linux2:59856'}
{'Time' => '1254','db' => undef,'Id' => '6','User' => 'rpl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'linux3:47776'}
Tue Aug 2 22:03:22 2022 615615 Killing all application threads..
Tue Aug 2 22:03:22 2022 616550 done.
Tue Aug 2 22:03:22 2022 - [info] ok.
Tue Aug 2 22:03:22 2022 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Tue Aug 2 22:03:22 2022 - [info] Executing FLUSH TABLES WITH READ LOCK..
Tue Aug 2 22:03:22 2022 - [info] ok.
Tue Aug 2 22:03:22 2022 - [info] Orig master binlog:pos is mysql-bin.000003:2409.
Tue Aug 2 22:03:22 2022 - [info] Waiting to execute all relay logs on 192.168.10.12(192.168.10.12:3306)..
Tue Aug 2 22:03:22 2022 - [info] master_pos_wait(mysql-bin.000003:2409) completed on 192.168.10.12(192.168.10.12:3306). Executed 0 events.
Tue Aug 2 22:03:22 2022 - [info] done.
Tue Aug 2 22:03:22 2022 - [info] Getting new master's binlog name and position..
Tue Aug 2 22:03:22 2022 - [info] mysql-bin.000003:2644
Tue Aug 2 22:03:22 2022 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.10.12', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rpl', MASTER_PASSWORD='xxx';
Tue Aug 2 22:03:22 2022 - [info] Executing master ip online change script to allow write on the new master:
Tue Aug 2 22:03:22 2022 - [info] /usr/local/mha/scripts/master_ip_online_change --command=start --orig_master_host=192.168.10.11 --orig_master_ip=192.168.10.11 --orig_master_port=3306 --orig_master_user='mha' --new_master_host=192.168.10.12 --new_master_ip=192.168.10.12 --new_master_port=3306 --new_master_user='mha' --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx
Tue Aug 2 22:03:22 2022 698540 Set read_only=0 on the new master.
Tue Aug 2 22:03:22 2022 699342Add vip 192.168.10.20 on ens33:1..
Tue Aug 2 22:03:22 2022 - [info] ok.
Tue Aug 2 22:03:22 2022 - [info]
Tue Aug 2 22:03:22 2022 - [info] * Switching slaves in parallel..
Tue Aug 2 22:03:22 2022 - [info]
Tue Aug 2 22:03:22 2022 - [info] -- Slave switch on host 192.168.10.13(192.168.10.13:3306) started, pid: 3264
Tue Aug 2 22:03:22 2022 - [info]
Tue Aug 2 22:03:23 2022 - [info] Log messages from 192.168.10.13 ...
Tue Aug 2 22:03:23 2022 - [info]
Tue Aug 2 22:03:22 2022 - [info] Waiting to execute all relay logs on 192.168.10.13(192.168.10.13:3306)..
Tue Aug 2 22:03:22 2022 - [info] master_pos_wait(mysql-bin.000003:2409) completed on 192.168.10.13(192.168.10.13:3306). Executed 0 events.
Tue Aug 2 22:03:22 2022 - [info] done.
Tue Aug 2 22:03:22 2022 - [info] Resetting slave 192.168.10.13(192.168.10.13:3306) and starting replication from the new master 192.168.10.12(192.168.10.12:3306)..
Tue Aug 2 22:03:22 2022 - [info] Executed CHANGE MASTER.
Tue Aug 2 22:03:22 2022 - [info] Slave started.
Tue Aug 2 22:03:23 2022 - [info] End of log messages from 192.168.10.13 ...
Tue Aug 2 22:03:23 2022 - [info]
Tue Aug 2 22:03:23 2022 - [info] -- Slave switch on host 192.168.10.13(192.168.10.13:3306) succeeded.
Tue Aug 2 22:03:23 2022 - [info] Unlocking all tables on the orig master:
Tue Aug 2 22:03:23 2022 - [info] Executing UNLOCK TABLES..
Tue Aug 2 22:03:23 2022 - [info] ok.
Tue Aug 2 22:03:23 2022 - [info] Starting orig master as a new slave..
Tue Aug 2 22:03:23 2022 - [info] Resetting slave 192.168.10.11(192.168.10.11:3306) and starting replication from the new master 192.168.10.12(192.168.10.12:3306)..
Tue Aug 2 22:03:23 2022 - [info] Executed CHANGE MASTER.
Tue Aug 2 22:03:23 2022 - [info] Slave started.
Tue Aug 2 22:03:23 2022 - [info] All new slave servers switched successfully.
Tue Aug 2 22:03:23 2022 - [info]
Tue Aug 2 22:03:23 2022 - [info] * Phase 5: New master cleanup phase..
Tue Aug 2 22:03:23 2022 - [info]
Tue Aug 2 22:03:23 2022 - [info] 192.168.10.12: Resetting slave info succeeded.
Tue Aug 2 22:03:23 2022 - [info] Switching master to 192.168.10.12(192.168.10.12:3306) completed successfully.
[root@linux4 mha]#
**显示 completed successfully. 表示切换成功**
**在192.168.10.12上查看主从状态**
linux2 [(none)]>show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 11 | | 3306 | 12 | 8d0a9377-1267-11ed-9186-000c2997d67e |
| 13 | | 3306 | 12 | 9bd87bfb-1267-11ed-9893-000c29464474 |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)
**查看vip是否切换，可以看到192.168.10.20已经切换到linux2上**
[root@linux2 tmp]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:e9:f3:99 brd ff:ff:ff:ff:ff:ff
inet 192.168.10.12/24 brd 192.168.10.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.10.20/24 scope global secondary ens33
valid_lft forever preferred_lft forever
inet6 fe80::12d6:7d61:cbe8:a26/64 scope link noprefixroute
valid_lft forever preferred_lft forever
[root@linux2 tmp]#
**数据验证省略，自行验证**
2、failover
**启动MHA manager**
[root@linux4 mha]# nohup masterha_manager --conf=/etc/mha/app1.conf --ignore_last_failover > /usr/local/mha/mha_manager.log < /dev/null 2>&1 &
[1] 3321
**停掉主库，查看是否切换**
[root@linux2 tmp]# /etc/init.d/mysql.server stop
Shutting down MySQL............ SUCCESS!
**MHA manager日志如下**
Tue Aug 2 22:09:13 2022 - [info] OK.
Tue Aug 2 22:09:13 2022 - [warning] shutdown_script is not defined.
Tue Aug 2 22:09:13 2022 - [info] Set master ping interval 1 seconds.
Tue Aug 2 22:09:13 2022 - [info] Set secondary check script: /usr/bin/masterha_secondary_check -s 192.168.10.12 -s 192.168.10.13
Tue Aug 2 22:09:13 2022 - [info] Starting ping health check on 192.168.10.12(192.168.10.12:3306)..
Tue Aug 2 22:09:13 2022 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
Tue Aug 2 22:10:29 2022 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Tue Aug 2 22:10:29 2022 - [info] Executing secondary network check script: /usr/bin/masterha_secondary_check -s 192.168.10.12 -s 192.168.10.13 --user=root --master_host=192.168.10.12 --master_ip=192.168.10.12 --master_port=3306 --master_user=mha --master_password=123456 --ping_type=SELECT
Tue Aug 2 22:10:29 2022 - [info] Executing SSH check script: exit 0
Tue Aug 2 22:10:29 2022 - [info] HealthCheck: SSH to 192.168.10.12 is reachable.
Monitoring server 192.168.10.12 is reachable, Master is not reachable from 192.168.10.12. OK.
Monitoring server 192.168.10.13 is reachable, Master is not reachable from 192.168.10.13. OK.
Tue Aug 2 22:10:30 2022 - [info] Master is not reachable from all other monitoring servers. Failover should start.
Tue Aug 2 22:10:30 2022 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.10.12' (111))
Tue Aug 2 22:10:30 2022 - [warning] Connection failed 2 time(s)..
Tue Aug 2 22:10:31 2022 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.10.12' (111))
Tue Aug 2 22:10:31 2022 - [warning] Connection failed 3 time(s)..
Tue Aug 2 22:10:32 2022 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.10.12' (111))
Tue Aug 2 22:10:32 2022 - [warning] Connection failed 4 time(s)..
Tue Aug 2 22:10:32 2022 - [warning] Master is not reachable from health checker!
Tue Aug 2 22:10:32 2022 - [warning] Master 192.168.10.12(192.168.10.12:3306) is not reachable!
Tue Aug 2 22:10:32 2022 - [warning] SSH is reachable.
Tue Aug 2 22:10:32 2022 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/app1.conf again, and trying to connect to all servers to check server status..
Tue Aug 2 22:10:32 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Aug 2 22:10:32 2022 - [info] Reading application default configuration from /etc/mha/app1.conf..
Tue Aug 2 22:10:32 2022 - [info] Reading server configuration from /etc/mha/app1.conf..
Tue Aug 2 22:10:33 2022 - [info] GTID failover mode = 1
Tue Aug 2 22:10:33 2022 - [info] Dead Servers:
Tue Aug 2 22:10:33 2022 - [info] 192.168.10.12(192.168.10.12:3306)
Tue Aug 2 22:10:33 2022 - [info] Alive Servers:
Tue Aug 2 22:10:33 2022 - [info] 192.168.10.11(192.168.10.11:3306)
Tue Aug 2 22:10:33 2022 - [info] 192.168.10.13(192.168.10.13:3306)
Tue Aug 2 22:10:33 2022 - [info] Alive Slaves:
Tue Aug 2 22:10:33 2022 - [info] 192.168.10.11(192.168.10.11:3306) Version=5.7.37-log (oldest major version between slaves) log-bin:enabled
Tue Aug 2 22:10:33 2022 - [info] GTID ON
Tue Aug 2 22:10:33 2022 - [info] Replicating from 192.168.10.12(192.168.10.12:3306)
Tue Aug 2 22:10:33 2022 - [info] 192.168.10.13(192.168.10.13:3306) Version=5.7.37-log (oldest major version between slaves) log-bin:enabled
Tue Aug 2 22:10:33 2022 - [info] GTID ON
Tue Aug 2 22:10:33 2022 - [info] Replicating from 192.168.10.12(192.168.10.12:3306)
Tue Aug 2 22:10:33 2022 - [info] Checking slave configurations..
Tue Aug 2 22:10:33 2022 - [info] read_only=1 is not set on slave 192.168.10.13(192.168.10.13:3306).
Tue Aug 2 22:10:33 2022 - [info] Checking replication filtering settings..
Tue Aug 2 22:10:33 2022 - [info] Replication filtering check ok.
Tue Aug 2 22:10:33 2022 - [info] Master is down!
Tue Aug 2 22:10:33 2022 - [info] Terminating monitoring script.
Tue Aug 2 22:10:33 2022 - [info] Got exit code 20 (Master dead).
Tue Aug 2 22:10:33 2022 - [info] MHA::MasterFailover version 0.58.
Tue Aug 2 22:10:33 2022 - [info] Starting master failover.
Tue Aug 2 22:10:33 2022 - [info]
Tue Aug 2 22:10:33 2022 - [info] * Phase 1: Configuration Check Phase..
Tue Aug 2 22:10:33 2022 - [info]
Tue Aug 2 22:10:34 2022 - [info] GTID failover mode = 1
Tue Aug 2 22:10:34 2022 - [info] Dead Servers:
Tue Aug 2 22:10:34 2022 - [info] 192.168.10.12(192.168.10.12:3306)
Tue Aug 2 22:10:34 2022 - [info] Checking master reachability via MySQL(double check)...
Tue Aug 2 22:10:34 2022 - [info] ok.
Tue Aug 2 22:10:34 2022 - [info] Alive Servers:
Tue Aug 2 22:10:34 2022 - [info] 192.168.10.11(192.168.10.11:3306)
Tue Aug 2 22:10:34 2022 - [info] 192.168.10.13(192.168.10.13:3306)
Tue Aug 2 22:10:34 2022 - [info] Alive Slaves:
Tue Aug 2 22:10:34 2022 - [info] 192.168.10.11(192.168.10.11:3306) Version=5.7.37-log (oldest major version between slaves) log-bin:enabled
Tue Aug 2 22:10:34 2022 - [info] GTID ON
Tue Aug 2 22:10:34 2022 - [info] Replicating from 192.168.10.12(192.168.10.12:3306)
Tue Aug 2 22:10:34 2022 - [info] 192.168.10.13(192.168.10.13:3306) Version=5.7.37-log (oldest major version between slaves) log-bin:enabled
Tue Aug 2 22:10:34 2022 - [info] GTID ON
Tue Aug 2 22:10:34 2022 - [info] Replicating from 192.168.10.12(192.168.10.12:3306)
Tue Aug 2 22:10:34 2022 - [info] Starting GTID based failover.
Tue Aug 2 22:10:34 2022 - [info]
Tue Aug 2 22:10:34 2022 - [info] ** Phase 1: Configuration Check Phase completed.
Tue Aug 2 22:10:34 2022 - [info]
Tue Aug 2 22:10:34 2022 - [info] * Phase 2: Dead Master Shutdown Phase..
Tue Aug 2 22:10:34 2022 - [info]
Tue Aug 2 22:10:34 2022 - [info] Forcing shutdown so that applications never connect to the current master..
Tue Aug 2 22:10:34 2022 - [info] Executing master IP deactivation script:
Tue Aug 2 22:10:34 2022 - [info] /usr/local/mha/scripts/master_ip_failover --orig_master_host=192.168.10.12 --orig_master_ip=192.168.10.12 --orig_master_port=3306 --command=stopssh --ssh_user=root
IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 192.168.10.20/24===
Disabling the VIP on old master: 192.168.10.12
SIOCSIFFLAGS: Cannot assign requested address
Tue Aug 2 22:10:34 2022 - [info] done.
Tue Aug 2 22:10:34 2022 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Tue Aug 2 22:10:34 2022 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Tue Aug 2 22:10:34 2022 - [info]
Tue Aug 2 22:10:34 2022 - [info] * Phase 3: Master Recovery Phase..
Tue Aug 2 22:10:34 2022 - [info]
Tue Aug 2 22:10:34 2022 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Tue Aug 2 22:10:34 2022 - [info]
Tue Aug 2 22:10:35 2022 - [info] The latest binary log file/position on all slaves is mysql-bin.000003:2896
Tue Aug 2 22:10:35 2022 - [info] Retrieved Gtid Set: 8fe7f20a-1267-11ed-a7a4-000c29e9f399:1-2
Tue Aug 2 22:10:35 2022 - [info] Latest slaves (Slaves that received relay log files to the latest):
Tue Aug 2 22:10:35 2022 - [info] 192.168.10.11(192.168.10.11:3306) Version=5.7.37-log (oldest major version between slaves) log-bin:enabled
Tue Aug 2 22:10:35 2022 - [info] GTID ON
Tue Aug 2 22:10:35 2022 - [info] Replicating from 192.168.10.12(192.168.10.12:3306)
Tue Aug 2 22:10:35 2022 - [info] 192.168.10.13(192.168.10.13:3306) Version=5.7.37-log (oldest major version between slaves) log-bin:enabled
Tue Aug 2 22:10:35 2022 - [info] GTID ON
Tue Aug 2 22:10:35 2022 - [info] Replicating from 192.168.10.12(192.168.10.12:3306)
Tue Aug 2 22:10:35 2022 - [info] The oldest binary log file/position on all slaves is mysql-bin.000003:2896
Tue Aug 2 22:10:35 2022 - [info] Retrieved Gtid Set: 8fe7f20a-1267-11ed-a7a4-000c29e9f399:1-2
Tue Aug 2 22:10:35 2022 - [info] Oldest slaves:
Tue Aug 2 22:10:35 2022 - [info] 192.168.10.11(192.168.10.11:3306) Version=5.7.37-log (oldest major version between slaves) log-bin:enabled
Tue Aug 2 22:10:35 2022 - [info] GTID ON
Tue Aug 2 22:10:35 2022 - [info] Replicating from 192.168.10.12(192.168.10.12:3306)
Tue Aug 2 22:10:35 2022 - [info] 192.168.10.13(192.168.10.13:3306) Version=5.7.37-log (oldest major version between slaves) log-bin:enabled
Tue Aug 2 22:10:35 2022 - [info] GTID ON
Tue Aug 2 22:10:35 2022 - [info] Replicating from 192.168.10.12(192.168.10.12:3306)
Tue Aug 2 22:10:35 2022 - [info]
Tue Aug 2 22:10:35 2022 - [info] * Phase 3.3: Determining New Master Phase..
Tue Aug 2 22:10:35 2022 - [info]
Tue Aug 2 22:10:35 2022 - [info] Searching new master from slaves..
Tue Aug 2 22:10:35 2022 - [info] Candidate masters from the configuration file:
Tue Aug 2 22:10:35 2022 - [info] Non-candidate masters:
Tue Aug 2 22:10:35 2022 - [info] New master is 192.168.10.11(192.168.10.11:3306)
Tue Aug 2 22:10:35 2022 - [info] Starting master failover..
Tue Aug 2 22:10:35 2022 - [info]
From:
192.168.10.12(192.168.10.12:3306) (current master)
+--192.168.10.11(192.168.10.11:3306)
+--192.168.10.13(192.168.10.13:3306)
To:
192.168.10.11(192.168.10.11:3306) (new master)
+--192.168.10.13(192.168.10.13:3306)
Tue Aug 2 22:10:35 2022 - [info]
Tue Aug 2 22:10:35 2022 - [info] * Phase 3.3: New Master Recovery Phase..
Tue Aug 2 22:10:35 2022 - [info]
Tue Aug 2 22:10:35 2022 - [info] Waiting all logs to be applied..
Tue Aug 2 22:10:35 2022 - [info] done.
Tue Aug 2 22:10:35 2022 - [info] Getting new master's binlog name and position..
Tue Aug 2 22:10:35 2022 - [info] mysql-bin.000003:2896
Tue Aug 2 22:10:35 2022 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.10.11', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rpl', MASTER_PASSWORD='xxx';
Tue Aug 2 22:10:35 2022 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000003, 2896, 8d0a9377-1267-11ed-9186-000c2997d67e:1-13,
8fe7f20a-1267-11ed-a7a4-000c29e9f399:1-2
Tue Aug 2 22:10:35 2022 - [info] Executing master IP activate script:
Tue Aug 2 22:10:35 2022 - [info] /usr/local/mha/scripts/master_ip_failover --command=start --ssh_user=root --orig_master_host=192.168.10.12 --orig_master_ip=192.168.10.12 --orig_master_port=3306 --new_master_host=192.168.10.11 --new_master_ip=192.168.10.11 --new_master_port=3306 --new_master_user='mha' --new_master_password=xxx
IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 192.168.10.20/24===
Enabling the VIP - 192.168.10.20/24 on the new master - 192.168.10.11
Tue Aug 2 22:10:35 2022 - [info] OK.
Tue Aug 2 22:10:35 2022 - [info] Setting read_only=0 on 192.168.10.11(192.168.10.11:3306)..
Tue Aug 2 22:10:35 2022 - [info] ok.
Tue Aug 2 22:10:35 2022 - [info] ** Finished master recovery successfully.
Tue Aug 2 22:10:35 2022 - [info] * Phase 3: Master Recovery Phase completed.
Tue Aug 2 22:10:35 2022 - [info]
Tue Aug 2 22:10:35 2022 - [info] * Phase 4: Slaves Recovery Phase..
Tue Aug 2 22:10:35 2022 - [info]
Tue Aug 2 22:10:35 2022 - [info]
Tue Aug 2 22:10:35 2022 - [info] * Phase 4.1: Starting Slaves in parallel..
Tue Aug 2 22:10:35 2022 - [info]
Tue Aug 2 22:10:35 2022 - [info] -- Slave recovery on host 192.168.10.13(192.168.10.13:3306) started, pid: 3448. Check tmp log /usr/local/mha/192.168.10.13_3306_20220802221033.log if it takes time..
Tue Aug 2 22:10:36 2022 - [info]
Tue Aug 2 22:10:36 2022 - [info] Log messages from 192.168.10.13 ...
Tue Aug 2 22:10:36 2022 - [info]
Tue Aug 2 22:10:35 2022 - [info] Resetting slave 192.168.10.13(192.168.10.13:3306) and starting replication from the new master 192.168.10.11(192.168.10.11:3306)..
Tue Aug 2 22:10:35 2022 - [info] Executed CHANGE MASTER.
Tue Aug 2 22:10:35 2022 - [info] Slave started.
Tue Aug 2 22:10:35 2022 - [info] gtid_wait(8d0a9377-1267-11ed-9186-000c2997d67e:1-13,
8fe7f20a-1267-11ed-a7a4-000c29e9f399:1-2) completed on 192.168.10.13(192.168.10.13:3306). Executed 0 events.
Tue Aug 2 22:10:36 2022 - [info] End of log messages from 192.168.10.13.
Tue Aug 2 22:10:36 2022 - [info] -- Slave on host 192.168.10.13(192.168.10.13:3306) started.
Tue Aug 2 22:10:36 2022 - [info] All new slave servers recovered successfully.
Tue Aug 2 22:10:36 2022 - [info]
Tue Aug 2 22:10:36 2022 - [info] * Phase 5: New master cleanup phase..
Tue Aug 2 22:10:36 2022 - [info]
Tue Aug 2 22:10:36 2022 - [info] Resetting slave info on the new master..
Tue Aug 2 22:10:36 2022 - [info] 192.168.10.11: Resetting slave info succeeded.
Tue Aug 2 22:10:36 2022 - [info] Master failover to 192.168.10.11(192.168.10.11:3306) completed successfully.
Tue Aug 2 22:10:36 2022 - [info]
----- Failover Report -----
app1: MySQL Master failover 192.168.10.12(192.168.10.12:3306) to 192.168.10.11(192.168.10.11:3306) succeeded
Master 192.168.10.12(192.168.10.12:3306) is down!
Check MHA Manager logs at linux4:/usr/local/mha/manager.log for details.
Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.10.12(192.168.10.12:3306)
Selected 192.168.10.11(192.168.10.11:3306) as a new master.
192.168.10.11(192.168.10.11:3306): OK: Applying all logs succeeded.
192.168.10.11(192.168.10.11:3306): OK: Activated master IP address.
192.168.10.13(192.168.10.13:3306): OK: Slave started, replicating from 192.168.10.11(192.168.10.11:3306)
192.168.10.11(192.168.10.11:3306): Resetting slave info succeeded.
Master failover to 192.168.10.11(192.168.10.11:3306) completed successfully.
**出现completed successfully.表示failover成功**
验证：
linux1 [(none)]>show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 13 | | 3306 | 11 | 9bd87bfb-1267-11ed-9893-000c29464474 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
linux3上查看slave状态
linux3 [(none)]>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.11
Master_User: rpl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 2896
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2896
Relay_Log_Space: 617
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: 8d0a9377-1267-11ed-9186-000c2997d67e
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 8d0a9377-1267-11ed-9186-000c2997d67e:1-13,
8fe7f20a-1267-11ed-a7a4-000c29e9f399:1-2,
9bd87bfb-1267-11ed-9893-000c29464474:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
linux1上查看vip是否存在
[root@linux1 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:97:d6:7e brd ff:ff:ff:ff:ff:ff
inet 192.168.10.11/24 brd 192.168.10.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.10.20/24 brd 192.168.10.255 scope global secondary ens33:1
valid_lft forever preferred_lft forever
inet6 fe80::774d:d3f0:9f79:2054/64 scope link noprefixroute
valid_lft forever preferred_lft forever
[root@linux1 ~]#
数据验证省略，请自行验证
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
