MHA高可用配置与故障切换
1 MHA的概述
支持故障切换，在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用
MHA还提供在线主库切换的功能，能够安全地切换当前运行的主库到一个新的主库中(通过将从库提升为主库),大概0.5-2秒内即可完成
部署MHA的原因
- 传统mysql主从架构存在单点故障的问题
- 传统架构中，只有一个mysql主服务器，所以当出现单点故障的时候，整个服务器群集就会瘫痪掉
为了解决这种情况，我们需要在主服务器宕机的时候，重新建立一台主服务器，负责监控等工作
MHA的组成
- MHA Manager（管理节点）：用来接收外部信号，监控下方数据节点的工作状态
- MHA Node（数据节点）：工作的单位，负责具体的工作
MHA特点
- 自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失
- 使用 MySQL55的半同步复制，可以大大降低数据丢失的风险
2 实验环境
2.1 配置无密码认证
（1）节点间的免密
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:
2.2 数据库的主从复制
主从复制环境中的mysql同步的用户名为：root，密码：westos
（1）为保证实验环境的纯净，server1、server2、server3三个节点清除mysql的数据目录：
cd /data/mysql/
rm -rf *
mysqld --initialize --user=mysql
/etc/init.d/mysqld start
mysql -p
ALTER USER root@localhost identified by 'westos';
（2）所有数据库授权marry用户对数据库的操作权限，每一个mysql数据库都要设置
- 授权：
grant replication slave on *.* to marry@'%' identified by 'westos'; - 查看master的状态信息：
show master status\G;
server2和server3
cd /data/mysql/
rm -rf *
mysqld --initialize --user=mysql ## 初始化数据库
/etc/init.d/mysqld start ## 启动mysql
mysql -p ## 登录数据库
ALTER USER root@localhost identified by 'westos'; ## 修改本地root用户的密码
change master to master_host='172.25.12.1', master_user='marry',master_password='westos',master_auto_position=1;
start slave; ## 启动slave
server1:远程登录
- 在server1上更改，因为server1为master，server2、server3自动生效
grant all on *.* to root@'%' identified by 'westos'; ## 授予远程用户root所有的权限
select * from mysql.user where user='root'; ## 查看root用户的信息
3 MHA的部署
3.1 安装Node组件和Manager组件
（1）安装mysql的高可用节点（server1、server2、server3）：
yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
（2）server4安装mysql高可用管理软件:yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
3.2 配置MHA
（1）创建MHA软件目录并拷贝配置文件
mkdir /etc/masterha
cp /root/MHA-7/mha4mysql-manager-0.58/samples/conf/app1.cnf /etc/masterha
- 编辑配置文件：
cd /root/MHA-7/mha4mysql-manager-0.58/samples/conf/
vim masterha_default.cnf
- 编辑配置文件：
vim /etc/masterha/app1.cnf
[server default]
user=root
password=westos
ssh_user=root
master_binlog_dir= /data/mysql
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 172.25.12.1 -s 172.25.12.2
ping_interval=3
# master_ip_failover_script= /script/masterha/master_ip_failover
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
# master_ip_online_change_script= /script/masterha/master_ip_online_change
manager_workdir=/etc/masterha/app1
manager_log=/etc/masterha/app1/manager.log
repl_user=marry
repl_password=westos
[server1]
hostname=172.25.12.1
[server2]
hostname=172.25.12.2
candidate_master=1
check_repl_delay=0
[server3]
hostname=172.25.12.3
no_master=1
（3）查看ssh登陆是否成功：masterha_check_ssh --conf=/etc/masterha/app1.cnf
- 查看复制是否建立好 ：
masterha_check_repl --conf=/etc/masterha/app1.cnf
[root@server4 masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnf
Thu Jan 14 05:29:00 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jan 14 05:29:00 2021 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Thu Jan 14 05:29:00 2021 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Thu Jan 14 05:29:00 2021 - [info] MHA::MasterMonitor version 0.58.
Creating directory /etc/masterha/app1.. done.
Thu Jan 14 05:29:02 2021 - [info] GTID failover mode = 1
Thu Jan 14 05:29:02 2021 - [info] Dead Servers:
Thu Jan 14 05:29:02 2021 - [info] Alive Servers:
Thu Jan 14 05:29:02 2021 - [info] 172.25.12.1(172.25.12.1:3306)
Thu Jan 14 05:29:02 2021 - [info] 172.25.12.2(172.25.12.2:3306)
Thu Jan 14 05:29:02 2021 - [info] 172.25.12.3(172.25.12.3:3306)
Thu Jan 14 05:29:02 2021 - [info] Alive Slaves:
Thu Jan 14 05:29:02 2021 - [info] 172.25.12.2(172.25.12.2:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Thu Jan 14 05:29:02 2021 - [info] GTID ON
Thu Jan 14 05:29:02 2021 - [info] Replicating from 172.25.12.1(172.25.12.1:3306)
Thu Jan 14 05:29:02 2021 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Jan 14 05:29:02 2021 - [info] 172.25.12.3(172.25.12.3:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Thu Jan 14 05:29:02 2021 - [info] GTID ON
Thu Jan 14 05:29:02 2021 - [info] Replicating from 172.25.12.1(172.25.12.1:3306)
Thu Jan 14 05:29:02 2021 - [info] Not candidate for the new Master (no_master is set)
Thu Jan 14 05:29:02 2021 - [info] Current Alive Master: 172.25.12.1(172.25.12.1:3306)
Thu Jan 14 05:29:02 2021 - [info] Checking slave configurations..
Thu Jan 14 05:29:02 2021 - [info] read_only=1 is not set on slave 172.25.12.2(172.25.12.2:3306).
Thu Jan 14 05:29:02 2021 - [info] read_only=1 is not set on slave 172.25.12.3(172.25.12.3:3306).
Thu Jan 14 05:29:02 2021 - [info] Checking replication filtering settings..
Thu Jan 14 05:29:02 2021 - [info] binlog_do_db= , binlog_ignore_db=
Thu Jan 14 05:29:02 2021 - [info] Replication filtering check ok.
Thu Jan 14 05:29:02 2021 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Thu Jan 14 05:29:02 2021 - [info] Checking SSH publickey authentication settings on the current master..
Thu Jan 14 05:29:02 2021 - [info] HealthCheck: SSH to 172.25.12.1 is reachable.
Thu Jan 14 05:29:02 2021 - [info]
172.25.12.1(172.25.12.1:3306) (current master)
+--172.25.12.2(172.25.12.2:3306)
+--172.25.12.3(172.25.12.3:3306)
Thu Jan 14 05:29:02 2021 - [info] Checking replication health on 172.25.12.2..
Thu Jan 14 05:29:02 2021 - [info] ok.
Thu Jan 14 05:29:02 2021 - [info] Checking replication health on 172.25.12.3..
Thu Jan 14 05:29:02 2021 - [info] ok.
Thu Jan 14 05:29:02 2021 - [warning] master_ip_failover_script is not defined.
Thu Jan 14 05:29:02 2021 - [warning] shutdown_script is not defined.
Thu Jan 14 05:29:02 2021 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
4 MHA的故障切换
4.1 手动切换master
（1）手动执行failover机制（server4），切换master
masterha_master_switch --master_state=dead \ ## --master_state=dead 强制的参数，参数值为"dead" 或者 "alive" . 如果 设置为 alive 模式，masterha_master_switch 开始在线主库切换操作。
--conf=/etc/masterha/app1.cnf \
--master_state=alive \
--new_master_host=172.25.12.2 \ ## 新主机地址，可选参数，明确新的主库的主机
--new_master_port=3306 \ ##
--orig_master_is_new_slave
- 查看slave端的状态（server3）：
show slave status\G;
- server2：
show slave status\G;
- server1：
show slave status\G;
（2）server2端的mysql停止服务： /etc/init.d/mysqld stop
- 手动执行failover机制（server4），切换master
masterha_master_switch --master_state=dead \
--conf=/etc/masterha/app1.cnf \
--master_state=dead \
--dead_master_host=172.25.12.2 \
--dead_master_port=3306 \
--new_master_host=172.25.12.1 \
--new_master_port=3306
- master节点切换到server1
- server3：
show slave status\G;
- 查看slave的状态（server1）：
show slave status\G;
server2:
- 同步server1的mysql信息
CHANGE MASTER TO MASTER_HOST='172.25.12.1', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='marry', MASTER_PASSWORD='westos';
- 启动serve2的slave：
start slave; - 查看slave的状态：
show slave status\G;
- 将mha的manager打入后台运行：
masterha_manager --conf=/etc/masterha/app1.cnf &
- 查看进程：
ps ax
- 每次failover切换后会在管理目录生成文件app1.failover.complete，下次在切换的时候会发现有这个文件导致切换不成功，需要手动清理掉
rm -rf app1.failover.complete
server1停止mysql服务：/etc/init.d/mysqld stop
server4:
- 查看manager的日志信息：
cat /etc/masterha/app1/manager.log，mysql的master节点server1已经宕机
server3为slave，master为server2：show slave status\G;
将server1的master切换为server2
CHANGE MASTER TO MASTER_HOST='172.25.12.2', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='marry', MASTER_PASSWORD='westos';
start slave; ## 启动slave
show slave status\G; ## 查看slave的状态
4.2 自动切换master
（1）给脚本添加执行的权限
chmod +x master_ip_failover
chmod +x master_ip_online_change
（2） MHA故障切换脚本： 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 = '172.25.12.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
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 \"`;
}
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";
}
（3）编辑主库在线切换脚本：vim master_ip_online_change
#!/usr/bin/env perl
use strict;
use warnings FATAL =>'all';
use Getopt::Long;
my $vip = '172.25.12.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
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 $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 $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 $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_master_ip=ip --new_master_port=port\n";
}
（4）编辑mha的配置文件：vim /etc/masterha/app1.cnf
[server default]
user=root
password=westos
ssh_user=root
master_binlog_dir= /data/mysql
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 172.25.12.1 -s 172.25.12.2
ping_interval=3
master_ip_failover_script= /etc/masterha/master_ip_failover ## MHA的故障切换脚本
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
master_ip_online_change_script= /etc/masterha/master_ip_online_change ## MHA的master在线切换脚本
manager_workdir=/etc/masterha/app1
manager_log=/etc/masterha/app1/manager.log
repl_user=marry
repl_password=westos
[server1]
hostname=172.25.12.1
[server2]
hostname=172.25.12.2
candidate_master=1
check_repl_delay=0
[server3]
hostname=172.25.12.3
no_master = 1
（5）每次failover切换后会在管理目录生成文件app1.failover.complete，下次在切换的时候会发现有这个文件导致切换不成功，需要手动清理掉
rm -rf app1.failover.complete
（6） 启动MHA：
masterha_manager --conf=/etc/masterha/app1.cnf &
- 查看进程：
ps ax
（7）测试
- server2添加vip：
ip addr add 172.25.12.100/24 dev eth0
- 停止server2的mysql服务：
/etc/init.d/mysqld stop
（8）实验结果
- server4查看manager的日志信息：
cat /etc/masterha/app1/manager.log
- master自动迁移到server1上
- server3查看slave的状态：
show slave status\G;
- master节点自动切换后，vip也随之迁移到server1：
ip addr
-
server2启动mysql服务：
/etc/init.d/mysqld start -
server2同步server1的数据库
CHANGE MASTER TO MASTER_HOST='172.25.12.1', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='marry', MASTER_PASSWORD='westos';
- server2启动slave：
start slave;
（1）手动执行failover机制
-
server4删除文件：
rm -rf app1.failover.complete -
手动执行failover机制（server4），切换master
masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.12.2 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
（2）实验结果
- 查看server4的日志信息：
cat /etc/masterha/app1/manager.log
- master切换到server2，vip也随之漂移，查看slave的状态（server3）：
show slave status\G;
- 查看slave的状态（server2）：
show slave status\G;
- 查看ip（server2）：
ip addr
- 查看server1的slave的状态：
show slave status\G;
- 查看server1的ip地址：
ip addr
扫一扫
556
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
