一、MHA概述
二、MHA服务组成
2.1 MHA Manager(管理节点)
2.2 MHA Node(数据节点)
三、MHA工作原理及特点
四、MHA搭建及故障模拟
4.1 搭建环境
主机名 IP地址 角色 所有安装软件包(MHA) manager 192.168.131.14 manager管理节点 manager组件,MHA node 组件 master 192.168.131.9 mysql主数据库 MHA node 组件 slave1 192.168.131.10 mysql从数据库 MHA node 组件 slave2 192.168.131.11 mysql从数据库 MHA node 组件
4.2 部署思路
4.3 安装MySQL数据库与主从同步
[ root@localhost ~ ] # systemctl stop firewalld. service
[ root@localhost ~ ] # systemctl disable firewalld. service
[ root@localhost ~ ] # setenforce 0
[ root@localhost ~ ] # hostnamectl set- hostname master
[ root@localhost ~ ] # su
[ root@master ~ ] #
【将安装包分别拖进三台需装MySQL的服务器(一主两从)】
[ root@master opt] # ls
cmake- 2.8 .6 . tar. gz mysql- 5.6 .36 . tar. gz rh
[ root@slave1 opt] # ls
cmake- 2.8 .6 . tar. gz mysql- 5.6 .36 . tar. gz rh
[ root@slave2 opt] # ls
cmake- 2.8 .6 . tar. gz mysql- 5.6 .36 . tar. gz rh
[ root@localhost ~ ] # yum - y install ncurses- devel. x86_64 gcc- c++ . x86_64 perl- Module- Install
[ root@localhost ~ ] # cd / opt/
[ root@localhost opt] # ls
cmake- 2.8 .6 . tar. gz mysql- 5.6 .36 . tar. gz rh
[ root@localhost opt] # tar zxvf cmake- 2.8 .6 . tar. gz
[ root@localhost opt] # cd cmake- 2.8 .6 /
[ root@localhost cmake- 2.8 .6 ] # . /configure
[ root@localhost cmake- 2.8 .6 ] # gmake && gmake install
[ root@localhost cmake- 2.8 .6 ] # cd / opt/
[ root@localhost opt] # tar zxvf mysql- 5.6 .36 . tar. gz
[ root@localhost opt] # cd mysql- 5.6 .36 /
[ root@localhost mysql- 5.6 .36 ] # cmake \
> - DCMAKE_INSTALL_PREFIX= / usr/ local/ mysql \
> - DDEFAULT_CHARSET= utf8 \
> - DDEFAULT_COLLATION= utf8_general_ci \
> - DEXTRA_CHARSETS= all \
> - DSYSCONFIDIR= / etc
[ root@localhost mysql- 5.6 .36 ] # make - j6 && make install
【/ etc/ init. d/ :各种服务器和程序的二进制文件存放目录】
【/ etc/ rc. d/ :各个启动级别的执行程序连接目录。里面很多文件都是指向init. d/ 的一些软链接】
[ root@localhost mysql- 5.6 .36 ] # cp support- files/ my- default . cnf / etc/ my. cnf
cp:是否覆盖"/etc/my.cnf" ? y
[ root@localhost mysql- 5.6 .36 ] # cp support- files/ mysql. server / etc/ rc. d/ init. d/ mysqld
[ root@localhost mysql- 5.6 .36 ] # chmod + 755 / etc/ rc. d/ init. d/ mysqld
[ root@localhost mysql- 5.6 .36 ] # chkconfig -- add / etc/ init. d/ mysqld
[ root@localhost mysql- 5.6 .36 ] # chkconfig mysqld -- level 35 on
[ root@localhost mysql- 5.6 .36 ] # echo 'PATH=$PATH:/usr/local/mysql/bin' >> / etc/ profile
[ root@localhost mysql- 5.6 .36 ] # source / etc/ profile
[ root@localhost mysql- 5.6 .36 ] # useradd - s / sbin/ nologin mysql
[ root@localhost mysql- 5.6 .36 ] # chown - R mysql. mysql / usr/ local/ mysql/
[ root@localhost mysql- 5.6 .36 ] # / usr/ local/ mysql/ scripts/ mysql_install_db \
> -- basedir= / usr/ local/ mysql \
> -- datadir= / usr/ local/ mysql/ data/ \
> -- user= mysql
【报错的话则安装autoconf库】
[ root@localhost mysql- 5.6 .36 ] # / usr/ local/ mysql/ scripts/ mysql_install_db -- basedir= / usr/ local/ mysql -- datadir= / usr/ local/ mysql/ data -- user= mysql
FATAL ERROR: please install the following Perl modules before executing / usr/ local/ mysql/ scripts/ mysql_install_db:
Data: : Dumper
[ root@localhost mysql- 5.6 .36 ] # yum - y install autoconf
[ root@localhost mysql- 5.6 .36 ] # vim / etc/ my. cnf
30 server- id= 10
31 log_bin= master- bin
32 log- slave- updates= true
[ root@localhost mysql- 5.6 .36 ] # vim / etc/ my. cnf
30 server- id= 20
31 log_bin= master- bin
32 relay- log= relay- log- bin
33 relay- log- index= slave- realy- bin. index
【从服务器2 】
[ root@localhost mysql- 5.6 .36 ] # vim / etc/ my. cnf
30 server- id= 30
31 log_bin= master- bin
32 relay- log= relay- log- bin
33 relay- log- index= slave- realy- bin. index
[ root@localhost mysql- 5.6 .36 ] # ln - s / usr/ local/ mysql/ bin/ mysql / usr/ sbin/
[ root@localhost mysql- 5.6 .36 ] # ln - s / usr/ local/ mysql/ bin/ mysqlbinlog / usr/ sbin/
【第一个是mysql命令文件】
【第二个是mysqlbinlog可用于断点恢复】
[ root@localhost mysql- 5.6 .36 ] # systemctl start mysqld
[ root@localhost mysql- 5.6 .36 ] # netstat - natp | grep 3306
tcp6 0 0 : : : 3306 : : : * LISTEN 40198 / mysqld
[ root@localhost mysql- 5.6 .36 ]
[ root@localhost mysql- 5.6 .36 ]
【在所有数据库节点上授权两个用户,一个是从库同步使用,一个是manager使用】
mysql> grant replication slave on * . * to 'myslave' @'192.168 .131 . % ' identified by ' 111222 '; 【从数据库同步使用】
mysql> grant all privileges on *.* to ' mha'@' 192.168 .131 . % ' identified by ' manager'; 【manager使用】
【下面三条授权是为了实验MHA检查MySQL主从报错才添加,防止从库通过主机名连接不上主库】
mysql> grant all privileges on *.* to ' mha'@' master' identified by ' manager';
mysql> grant all privileges on *.* to ' mha'@' slave1' identified by ' manager';
mysql> grant all privileges on *.* to ' mha'@' slave2' identified by ' manager';
mysql> flush privileges;
【在Master上查看二进制文件和同步点】
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 | 1720 | | | |
+-------------------+----------+--------------+------------------+-------------------+
【在两台slave节点服务器进行同步】
mysql> change master to master_host=' 192.168 .131 .9 ',master_user=' myslave',master_password=' 111222 ',master_log_file=' master- bin. 000002 ', master_log_pos= 1720 ;
mysql> start slave;
mysql> show slave status \G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
【设置两个从库为只读模式(必改项)】
mysql> set global read_only= 1 ;
mysql> flush privileges ;
4.4 配置MHA
【安装epel源,并且不进行gph检查】
[ root@localhost ~ ] # yum - y install epel- release -- nogpgcheck
[ root@localhost / ] # yum install - y perl- DBD- MySQL \ 【perl针对于mysql数据库】
> perl- Config- Tiny \ 【从配置文件中提拿其中的值】
> perl- Log- Dispatch \ 【log- 日志】
> perl- Parallel- ForkManager \ 【多线程管理】
> perl- ExtUtils- CBuilder \ 【扩展工具CBuilder MakeMaker】
> perl- ExtUtils- MakeMaker \
> perl- CPAN 【cpan perl中的数据库】
[ root@localhost opt] # ls
cmake- 2.8 .6 cmake- 2.8 .6 . tar. gz mha4mysql- node- 0.57 . tar. gz mysql- 5.6 .36 mysql- 5.6 .36 . tar. gz rh
[ root@localhost opt] # tar zxvf mha4mysql- node- 0.57 . tar. gz
[ root@localhost opt] # cd mha4mysql- node- 0.57
[ root@localhost mha4mysql- node- 0.57 ] # perl Makefile. PL
[ root@localhost mha4mysql- node- 0.57 ] # make && make install
[ root@localhost opt] # ls
mha4mysql- manager- 0.57 . tar. gz mha4mysql- node- 0.57 mha4mysql- node- 0.57 . tar. gz rh
[ root@localhost opt] # tar zxvf mha4mysql- manager- 0.57 . tar. gz
[ root@localhost opt] # cd mha4mysql- manager- 0.57
[ root@localhost mha4mysql- manager- 0.57 ] # perl Makefile. PL
[ root@localhost mha4mysql- manager- 0.57 ] # make && make install
4.5 manager和node安装后生成的常用脚本和工具
[ root@localhost mha4mysql- manager- 0.57 ] # ls / usr/ local/ bin/
apply_diff_relay_logs json_pp masterha_check_status masterha_master_monitor masterha_stop purge_relay_logs
config_data masterha_check_repl masterha_conf_host masterha_master_switch package - stash- conflicts save_binary_logs
filter_mysqlbinlog masterha_check_ssh masterha_manager masterha_secondary_check prove
工具 说名 masterha_check_ssh 检查MHA的SSH配置状况 masterha_check_repl 检查MySQL复制状况 masterha_manager 启动manager脚本 masterha_check_status 检查当前MHA运行状态 masterha_master_monitor 检查master是否宕机 masterha_master_switch 控制故障转移(手动或自动) masterha_conf_host 添加或删除配置的server信息 masterha_stop 关闭manager
脚本 说明 save_binary_logs 保存和复制master的二进制日志 apply_diff_relay_logs 识别差异的中继日志事件,并将其差异的事件应用于其他的slave filter_mysqlbinlog 去除不必要的rollback回滚事件(MHA已不适用这个工具) purge_relay_logs 清除中继日志(不会阻塞SQL线程)
4.6 配置无密码认证
【全部直接回车确认进入下一步】
[ root@localhost mha4mysql- manager- 0.57 ] # ssh- keygen - t rsa
[ root@localhost mha4mysql- manager- 0.57 ] # ssh- copy- id 192.168 .131 .9
【按完yes后需输入ssh登录密码5514 】
[ root@localhost mha4mysql- manager- 0.57 ] # ssh- copy- id 192.168 .131 .10
[ root@localhost mha4mysql- manager- 0.57 ] # ssh- copy- id 192.168 .131 .11
[ root@localhost mha4mysql- node- 0.57 ] # ssh- keygen - t rsa
[ root@localhost mha4mysql- node- 0.57 ] # ssh- copy- id 192.168 .131 .10
[ root@localhost mha4mysql- node- 0.57 ] # ssh- copy- id 192.168 .131 .11
[ root@localhost mha4mysql- node- 0.57 ] # ssh- keygen - t rsa
[ root@localhost mha4mysql- node- 0.57 ] # ssh- copy- id 192.168 .131 .9
[ root@localhost mha4mysql- node- 0.57 ] # ssh- copy- id 192.168 .131 .11
[ root@localhost mha4mysql- node- 0.57 ] # ssh- keygen - t rsa
[ root@localhost mha4mysql- node- 0.57 ] # ssh- copy- id 192.168 .131 .9
[ root@localhost mha4mysql- node- 0.57 ] # ssh- copy- id 192.168 .131 .10
4.7 修改MHA相关配置
[ root@localhost mha4mysql- manager- 0.57 ] # cp - rp / opt/ mha4mysql- manager- 0.57 / samples/ scripts / usr/ local/ bin
【拷贝后查询会有四个执行文件】
[ root@localhost mha4mysql- manager- 0.57 ] # ll / usr/ local/ bin/ scripts/
总用量 32
- rwxr- xr- x. 1 1001 1001 3648 5 月 31 2015 master_ip_failover 【自动切换时VIP管理的脚本】
- rwxr- xr- x. 1 1001 1001 9870 5 月 31 2015 master_ip_online_change 【在线切换时VIP的管理】
- rwxr- xr- x. 1 1001 1001 11867 5 月 31 2015 power_manager 【故障发生后关闭主机的脚本】
- rwxr- xr- x. 1 1001 1001 1360 5 月 31 2015 send_report 【因故障切换后发送报警的脚本】
【再将master_ip_failover复制到/ usr/ local/ bin/ 目录】
[ root@localhost mha4mysql- manager- 0.57 ] # cp / usr/ local/ bin/ scripts/ master_ip_failover / usr/ local/ bin
[ root@localhost mha4mysql- manager- 0.57 ] # vim / usr/ local/ bin/ master_ip_failover
#! / usr/ bin/ env perl
use strict;
use warnings FATAL = > 'all' ;
use Getopt: : Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
) ;
my $vip = '192.168.131.100' ; 【浮动IP】
my $brdc = '192.168.131.255' ; 【广播地址】
my $ifdev = 'ens33' ; 【使用的网卡为ens33】
my $key = '1' ; 【国际序列号】
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip" ; 【使用ifoconfig命令将其启动,同时设置浮动地址】
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down" ; 【可以使用ifconfig命令将其down掉(关闭)】
my $exit_code = 0 ; 【正常退出(返回状态码)】
#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 ;
}
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" ;
}
[ root@localhost mha4mysql- manager- 0.57 ] # mkdir / etc/ masterha
[ root@localhost mha4mysql- manager- 0.57 ] # cp / opt/ mha4mysql- manager- 0.57 / samples/ conf/ app1. cnf / etc/ masterha/
[ root@localhost mha4mysql- manager- 0.57 ] # vim / etc/ masterha/ app1. cnf
[ server default ]
manager_log= / var/ log/ masterha/ app1/ manager. log 【manager工作目录】
manager_workdir= / var/ log/ masterha/ app1 【manager工作日志】
master_binlog_dir= / usr/ local/ mysql/ data 【master保存binlog的位置】
master_ip_failover_script= / usr/ local/ bin/ master_ip_failover 【设置自动failover时候切换脚本,也就是上一个配置的脚本】
master_ip_online_change_script= / usr/ local/ bin/ master_ip_online_change 【设置手动切换时候的切换脚本】
password= manager 【此处密码为之前创建监控用户的密码】
user= mha 【设置监控用户】
ping_interval= 1 【设置监控主库,发送Ping包的事件间隔,默认为3 秒,尝试3 次没有回应的时候自动进行failover】
remote_workdir= / tmp 【设置远端的MySQL再发送切换时binlog的(临时)保存位置】
repl_password= 111222 【设置复制(slaves)用户的密码】
repl_user= myslave 【设置复制用户的账号】
report_script= / usr/ local/ send_report 【设置发送切换后发送的报警脚本(为了实验这里不进行此项配置)】
secondary_check_script= / usr/ local/ bin/ masterha_secondary_check - s 192.168 .131 .10 - s 192.168 .131 .11 【设置检查从服务器的脚本】
shutdown_script= "" 【设置故障发生后关闭故障主机脚本】
ssh_user= root 【设置ssh的登录用户名】
[ server1]
hostname= 192.168 .131 .9
port= 3306
[ server2]
hostname= 192.168 .131 .10
port= 3306
[ server3]
candidate_master= 1
【设置候选master,如果设置了此参数,则发生主从切换后,会将此库提升为主库】
hostname= 192.168 .131 .11
check_repl_delay= 0
【默认情况下如果一个slave落后master 超过100 M的relay logs的话,MHA将不会选择该slave作为一个新的master】
【因为对于这个slave的恢复需要花费很长时间;通过设置check_repl_delay= 0 ,MHA触发切换在选择一个新的master的时候将会忽略复制延时】
【这个参数对于设置了candidate_master= 1 的主机非常有用,因为这个候选主在切换的过程中一定是新的master】
port= 3306
[ root@master mha4mysql- node- 0.57 ] # / sbin/ ifconfig ens33: 1 192.168 .131 .100 / 24
4.8 查看无密码认证和manager节点上mysql 主从连接情况
4.9 在 manager 节点上启动 MHA
[ root@localhost mha4mysql- manager- 0.57 ] # nohup masterha_manager -- conf= / etc/ masterha/ app1. cnf -- remove_dead_master_conf -- ignore_last_failover < / dev/ null > / var/ log/ masterha/ app1/ manager. log 2 > & 1 &
[ 1 ] 9558
4.10 并查看MHA状态和日志
[ root@localhost mha4mysql- manager- 0.57 ] # masterha_check_status -- conf= / etc/ masterha/ app1. cnf
app1 ( pid: 9558 ) is running ( 0 : PING_OK) , master: 192.168 .131 .9
[ root@localhost mha4mysql- manager- 0.57 ] # cat / var/ log/ masterha/ app1/ manager. log | grep "current master"
Wed Feb 24 12 : 01 : 16 2021 - [ info] Checking SSH publickey authentication settings on the current master. .
192.168 .131 . 9 ( 192.168 .131 .9 : 3306 ) ( current master)
4.11 查看 master 的 VIP 地址是否存在
4.12 故障模拟
[ root@localhost mha4mysql- manager- 0.57 ] # tail - f / var/ log/ masterha/ app1/ manager. log
[ root@localhost mha4mysql- node- 0.57 ] # systemctl stop mysqld. service
4.13 修复模拟故障
[ root@localhost mha4mysql- node- 0.57 ] # systemctl start mysqld. service
【在现主库服务器 slave2 查看二进制文件和同步点】
mysql> show master status ;
+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+
| master- bin. 000001 | 1515 | | | |
+
1 row in set ( 0.00 sec)
【在原主库服务器 master 执行同步操作】
[ root@localhost mha4mysql- node- 0.57 ]
mysql> change master to master_host= '192.168.131.11' , master_user= 'myslave' , master_password= '111222' , master_log_file= 'master-bin.000001' , master_log_pos= 1515 ;
Query OK, 0 rows affected, 2 warnings ( 0.03 sec)
mysql> start slave;
[ root@localhost mha4mysql- manager- 0.57 ] # vim / etc/ masterha/ app1. cnf
28 [ server1]
29 hostname= 192.168 .131 .9
30 port= 3306
[ root@localhost mha4mysql- manager- 0.57 ] # nohup masterha_manager -- conf= / etc/ masterha/ app1. cnf -- remove_dead_master_conf -- ignore_last_failover < / dev/ null > / var/ log/ masterha/ app1/ manager. log 2 > & 1 &
五、总结