香港服务器的MySQL之高可用组件MHA

云服务器
香港服务器的MySQL之高可用组件MHA

2026-01-21 09:49





香港服务器的MySQL之高可用组件MHA
1简介
MHA（MasterHighAvailability）是由日本人yoshinorim开发的一款成熟且开源的MySQL高可用程序，它实现了MySQL主从环境下MASTER宕机后能够自动进行单次故障转移的功能，其本身由perl语言编写，安装方便，使用简单


MHAMaster可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上


MHAManager主要运行一些工具，比如masterha_manager工具实现自动监控MySQLMaster和实现master故障切换，其它工具实现手动实现master故障切换、在线master转移、连接检查等


MHANode部署在所有运行MySQL的服务器上，无论是master还是slave。主要作用有三个


Ⅰ、保存二进制日志如果能够访问故障master，会拷贝master的二进制日志


II、应用差异中继日志从拥有最新数据的slave上生成差异中继日志，然后应用差异日志


III、清除中继日志在不停止SQL线程的情况下删除中继日志


MHA工作原理


从宕机崩溃的Master保存二进制日志事件（binlogevent）；
识别含有最新更新的Slave；
应用差异的中继日志（relaylog）到其他Slave；
应用从Master保存的二进制日志事件；
提升一个Slave为新的Master；
使其他的Slave连接新的Master进行复制；
MHA数据补偿


当SSH能连接,从库对比主库GTID或者position号,立即将二进制日志保存至各个从节点并且应用(save_binary_logs)
当SSH不能连接,对比从库之间的relaylog的差异(apply_diff_relay_logs)
2环境部署
节点ip
主节点10.243.95.3
从节点110.243.95.4
从节点210.243.95.5
数据库复制一主两从架构，MHAnode部署在每个节点上，MHAmanager部署在从节点2上


2.1建立SSH互信
#各节点生成rsa密钥
ssh-keygen-trsa
#发送密钥至三台服务器实现互信
ssh-copy-idroot@10.243.95.3
ssh-copy-idroot@10.243.95.4
ssh-copy-idroot@10.243.95.5


2.2搭建主从复制
具体内容见上篇主从复制，注意mha57以下版本不支持GTID事务，需避雷，按需选择下面的部署方法


2.3安装依赖包
2.3.1MHA-0.57-el6版本
#1.所有节点安装以下依赖包
yum-yinstallperl-DBD-MySQLperl-ExtUtils-MakeMakerperl-CPAN
yuminstall-yperl-DBD-MySQLperl-Config-Tinyperl-Log-Dispatchperl-Parallel-ForkManagerperl-Time-HiRes
#2.所有节点安装mha-node包
rpm-ivhmha4mysql-node-0.57-0.el6.noarch.rpm
#3.管理节点安装mha-manage包
rpm-ivhmha4mysql-manager-0.57-0.el6.noarch.rpm


2.3.2MHA-0.55-el6版本
#1.解压mha包
unzip-umha_pack.zip
cdmha_pack/mha_pack/rhel6/mha2
#2.安装依赖
rpm-ivhperl-DBI-1.609-4.el6.x86_64.rpm
rpm-ivhperl-DBD-MySQL-4.013-3.el6.x86_64.rpm
rpm-ivhmha4mysql-node-0.54-0.el6.noarch.rpm
rpm-ivhperl-DBI-1.609-4.el6.x86_64.rpm
rpm-ivhperl-DBD-MySQL-4.013-3.el6.x86_64.rpm
rpm-ivhmha4mysql-node-0.54-0.el6.noarch.rpm
rpm-ivhperl-Config-Tiny-2.12-7.1.el6.noarch.rpm
rpm-ivhperl-TimeDate-1.16-11.1.el6.noarch.rpm
rpm-ivhperl-MailTools-2.04-4.el6.noarch.rpm
rpm-ivhperl-Email-Date-Format-1.002-5.el6.noarch.rpm
rpm-ivhperl-MIME-Types-1.28-2.el6.noarch.rpm
rpm-ivhperl-MIME-Lite-3.027-2.el6.noarch.rpm
rpm-ivhperl-Mail-Sender-0.8.16-3.el6.noarch.rpm
rpm-ivhperl-Mail-Sendmail-0.79-12.el6.noarch.rpm
rpm-ivhperl-Params-Validate-0.92-3.el6.x86_64.rpm
rpm-ivhperl-Log-Dispatch-2.27-1.el6.noarch.rpm
rpm-ivhperl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
rpm-ivhperl-Time-HiRes-1.9721-127.el6.x86_64.rpm
rpm-ivhmha4mysql-manager-0.55-0.el6.noarch.rpm


2.4配置文件
新建mha配置文件：vi/etc/masterha/app1.cnf


[serverdefault]
#mysqlrootuserandpassword
user=root
password=123456


#mysqlloglocations
#master_binlog_dir=/mysql/data


#usertouseSSHaroundthecluster
ssh_user=root


#repluserandpassword
repl_user=repl
repl_password=123456


#workingdirectoryonthemanager
manager_log=/root/mha/log/manager.log
manager_workdir=/root/mha/log
#remote_workdir=/mysql/mha/log


#MHApinginterval
ping_interval=5


##scriptstosupportMHAfunctions
##secondary_check_script=/usr/bin/masterha_secondary_check-sremote_host1-sremote_host2
master_ip_failover_script=/root/mha/scripts/master_ip_failover
##shutdown_script=/usr/local/masterha/scripts/power_manager
##report_script=/usr/local/masterha/scripts/send_report
master_ip_online_change_script=/root/mha/scripts/master_ip_online_change


#masters
[server1]
hostname=10.243.95.3
port=3306
candidate_master=1
master_binlog_dir=/opt/mysql/binlog
remote_workdir=/root/mha/log


[server2]
hostname=10.243.95.4
port=3306
candidate_master=1
check_repl_delay=0
master_binlog_dir=/opt/mysql/binlog
remote_workdir=/root/mha/log


[server3]
hostname=10.243.95.5
port=3306
candidate_master=1
check_repl_delay=0
master_binlog_dir=/opt/mysql/binlog
remote_workdir=/root/mha/log


2.5切换脚本
1）创建一些目录


#建立mha的文件夹
mkdir-p/root/mha/conf
mkdir-p/root/mha/log
mkdir-p/root/mha/scripts
chmod-R777/root/mha/scripts


2）新建脚本


在线切换脚本
vi/root/mha/scripts/master_ip_online_change
#!/usr/bin/envperl


#Copyright(C)2011DeNACo.,Ltd.
#
#Thisprogramisfreesoftware;youcanredistributeitand/ormodify
#itunderthetermsoftheGNUGeneralPublicLicenseaspublishedby
#theFreeSoftwareFoundation;eitherversion2oftheLicense,or
#(atyouroption)anylaterversion.
#
#Thisprogramisdistributedinthehopethatitwillbeuseful,
#butWITHOUTANYWARRANTY;withouteventheimpliedwarrantyof
#MERCHANTABILITYorFITNESSFORAPARTICULARPURPOSE.Seethe
#GNUGeneralPublicLicenseformoredetails.
#
#YoushouldhavereceivedacopyoftheGNUGeneralPublicLicense
#alongwiththisprogram;ifnot,writetotheFreeSoftware
#Foundation,Inc.,
#51FranklinStreet,FifthFloor,Boston,MA02110-1301USA


##Note:Thisisasamplescriptandisnotcomplete.Modifythescriptbasedonyourenvironment.


usestrict;
usewarningsFATAL=>'all';


useGetopt::Long;
useMHA::DBHelper;
useMHA::NodeUtil;
useTime::HiResqw(sleepgettimeofdaytv_interval);
useData::Dumper;


my$_tstart;
my$_running_interval=0.1;
my(
$command,$ssh_user,$orig_master_host,$orig_master_ip,
$orig_master_port,$orig_master_user,$orig_master_password,
$new_master_host,$new_master_ip,$new_master_port,
$new_master_user,$new_master_password
);


my$vip='192.168.90.30';#writerVirtualIP
my$gw=`route|grepdefault|awk'{print\$2}'`;
chomp($gw);
my$interface='eth0';#networkinterface
my$ssh_start_vip="ipaddradd$vip/32dev$interface";
my$ssh_ping="arping-U-I$interface-s$vip$gw-c5";
my$ssh_stop_vip="ipaddrdel$vip/32dev$interface";


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_user=s'=>\$orig_master_user,
'orig_master_password=s'=>\$orig_master_password,
'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,
);


$ssh_user='root'unless($ssh_user);


exit&main();


subcurrent_time_us{
my($sec,$microsec)=gettimeofday();
my$curdate=localtime($sec);
return$curdate."".sprintf("%06d",$microsec);
}


subsleep_until{
my$elapsed=tv_interval($_tstart);
if($_running_interval>$elapsed){
sleep($_running_interval-$elapsed);
}
}


subget_threads_util{
my$dbh=shift;
my$my_connection_id=shift;
my$running_time_threshold=shift;
my$type=shift;
$running_time_threshold=0unless($running_time_threshold);
$type=0unless($type);
my@threads;


my$sth=$dbh->prepare("SHOWPROCESSLIST");
$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/ifdefined($info);
nextif($my_connection_id==$id);
nextif(defined($query_time)&&$query_time<$running_time_threshold);
nextif(defined($command)&&$commandeq"BinlogDump");
nextif(defined($user)&&$usereq"systemuser");
next
if(defined($command)
&&$commandeq"Sleep"
&&defined($query_time)
&&$query_time>=1);


if($type>=1){
nextif(defined($command)&&$commandeq"Sleep");
nextif(defined($command)&&$commandeq"Connect");
}


if($type>=2){
nextif(defined($info)&&$info=~m/^select/i);
nextif(defined($info)&&$info=~m/^show/i);
}


push@threads,$ref;
}
return@threads;
}


submain{
if($commandeq"stop"){
##Gracefullykillingconnectionsonthecurrentmaster
#1.Setread_only=1onthenewmaster
#2.Setread_only=1onthecurrentmaster
#3.Killcurrentqueries
#4.Deegisteroriginalmaster'siptothecatalogdatabase
#*Anydatabaseaccessfailurewillresultinscriptdie.
my$exit_code=1;
eval{
##Settingread_only=1onthenewmaster(toavoidaccident)
my$new_master_handler=newMHA::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);
printcurrent_time_us()."Setread_onlyonthenewmaster..";
$new_master_handler->enable_read_only();
if($new_master_handler->is_read_only()){
print"ok.\n";
}
else{
die"Failed!\n";
}
$new_master_handler->disconnect();


#Connectingtotheorigmaster,dieifanydatabaseerrorhappens
my$orig_master_handler=newMHA::DBHelper();
$orig_master_handler->connect($orig_master_ip,$orig_master_port,
$orig_master_user,$orig_master_password,1);


##Dropapplicationusersothatnobodycanconnect.Disablingper-sessionbinlogbeforehand
$orig_master_handler->disable_log_bin_local();


##WaitingforN*100millisecondssothatcurrentconnectionscanexit
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
"%sWaitingallrunning%dthreadsaredisconnected..(max%dmilliseconds)\n",
current_time_us(),$#threads+1,$time_until_read_only*100;
if($#threads<5){
printData::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});
}


##Settingread_only=1onthecurrentmastersothatnobody(exceptSUPER)canwrite
printcurrent_time_us()."Setread_only=1ontheorigmaster..";
$orig_master_handler->enable_read_only();
if($orig_master_handler->is_read_only()){
print"ok.\n";
}
else{
die"Failed!\n";
}


##WaitingforM*100millisecondssothatcurrentupdatequeriescancomplete
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
"%sWaitingallrunning%dqueriesaredisconnected..(max%dmilliseconds)\n",
current_time_us(),$#threads+1,$time_until_kill_threads*100;
if($#threads<5){
printData::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});
}


##Terminatingallthreads
printcurrent_time_us()."Killingallapplicationthreads..\n";
$orig_master_handler->kill_threads(@threads)if($#threads>=0);
printcurrent_time_us()."done.\n";
$orig_master_handler->enable_log_bin_local();
$orig_master_handler->disconnect();


##Deregisteroriginalmaster'sVIP
print"DisablingtheVIPonoldmaster:$orig_master_host\n";
&stop_vip();
$exit_code=10;


##Afterfinishingthescript,MHAexecutesFLUSHTABLESWITHREADLOCK
$exit_code=0;
};
if($@){
warn"GotError:$@\n";
exit$exit_code;
}
exit$exit_code;
}
elsif($commandeq"start"){
#1.Registernewmaster'siptothecatalogdatabase


#Wedon'treturnerroreventhoughactivatingupdatableaccounts/ipfailedsothatwedon'tinterruptslaves'recovery.
#Ifexitcodeis0or10,MHAdoesnotabort
my$exit_code=10;
eval{
##Updatemasteriponthecatalogdatabase,etc
print"EnablingtheVIP-$viponthenewmaster-$new_master_host\n";
&start_vip();


$exit_code=0;
};
if($@){
warn"GotError:$@\n";
exit$exit_code;
}
exit$exit_code;
}
elsif($commandeq"status"){


#donothing
exit0;
}
else{
&usage();
exit1;
}
}


#AsimplesystemcallthatenabletheVIPonthenewmaster
substart_vip(){
`ssh$ssh_user\@$new_master_host\"$ssh_start_vip\"`;
`ssh$ssh_user\@$new_master_host\"$ssh_ping\"`;
}


#AsimplesystemcallthatdisabletheVIPontheold_master
substop_vip(){
`ssh$ssh_user\@$orig_master_host\"$ssh_stop_vip\"`;
}


subusage{
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;
}


注：若提示用户无repl权限且确认已添加，检查mysql.user表下的repl用户是否存在其它host存在repl权限为N的行，因为mha检查到任意行存在N即返回失败


2.6.3故障自动切换验证
1、开启三个数据库节点实例，确保主从复制状态正常(执行2.6.2操作)


2、后台打开监控程序，并持续监控日志


4、完成切换


MySQL之高可用组件MHA


故障切换内容：


检查到master不通，尝试继续ping检查，达到阈值5触发自动切换
若配置文件未指定候选节点，根据日志量选举新的master主节点
若主节点未宕机，移除原master节点的vip
其他节点与新的master建立复制关系，主从复制同步中
新master节点启动vip，完成切换
2.6.4手动切换
1）主节点已宕机


2）主节点未宕机


购买使用一诺网络香港服务器，可以极大降低初创企业、中小企业以及个人开发者等用户群体的整体IT使用成本，无需亲自搭建基础设施、简化了运维和管理的日常工作量，使用户能够更专注于自身的业务发展和创新。香港服务器低至29元/月，购买链接：https://www.enuoidc.com/vps.html?typeid=2
美国云服务器的javascript的集合类型有哪些香港vps服务器的MySQL配置Keepalived读写分离