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

_ 2026-01-21 09:49
返回列表
香港服务器的MySQL之高可用组件MHA
云服务器 2026-01-21 09:49 42
香港服务器的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读写分离

九九云计算版权所有