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工作原理

MHA数据补偿

节点

ip

主节点

10.243.95.3

从节点1

10.243.95.4

从节点2

10.243.95.5

数据库复制一主两从架构，MHAnode部署在每个节点上，MHAmanager部署在从节点2上

发送密钥至三台服务器实现互信ssh-copy-idroot@10.243.95.3ssh-copy-idroot@10.243.95.4ssh-copy-idroot@10.243.95.5

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

2.所有节点安装mha-node包rpm-ivh?mha4mysql-node-0.57-0.el6.noarch.rpm#3.管理节点安装mha-manage包rpm-ivh?mha4mysql-manager-0.57-0.el6.noarch.rpm

2.安装依赖rpm-ivhperl-DBI-1.609-4.el6.x86_64.rpmrpm-ivhperl-DBD-MySQL-4.013-3.el6.x86_64.rpmrpm-ivhmha4mysql-node-0.54-0.el6.noarch.rpmrpm-ivhperl-DBI-1.609-4.el6.x86_64.rpmrpm-ivhperl-DBD-MySQL-4.013-3.el6.x86_64.rpmrpm-ivhmha4mysql-node-0.54-0.el6.noarch.rpmrpm-ivhperl-Config-Tiny-2.12-7.1.el6.noarch.rpmrpm-ivhperl-TimeDate-1.16-11.1.el6.noarch.rpmrpm-ivhperl-MailTools-2.04-4.el6.noarch.rpmrpm-ivhperl-Email-Date-Format-1.002-5.el6.noarch.rpmrpm-ivhperl-MIME-Types-1.28-2.el6.noarch.rpmrpm-ivhperl-MIME-Lite-3.027-2.el6.noarch.rpmrpm-ivhperl-Mail-Sender-0.8.16-3.el6.noarch.rpmrpm-ivhperl-Mail-Sendmail-0.79-12.el6.noarch.rpmrpm-ivhperl-Params-Validate-0.92-3.el6.x86_64.rpmrpm-ivhperl-Log-Dispatch-2.27-1.el6.noarch.rpmrpm-ivhperl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpmrpm-ivhperl-Time-HiRes-1.9721-127.el6.x86_64.rpmrpm-ivhmha4mysql-manager-0.55-0.el6.noarch.rpm

[serverdefault]#mysqlrootuserandpassworduser=rootpassword=123456

master_binlog_dir=/mysql/data

#usertouseSSHaroundtheclusterssh_user=root

#repluserandpasswordrepl_user=replrepl_password=123456

remote_workdir=/mysql/mha/log

#MHApingintervalping_interval=5

scriptstosupportMHAfunctionssecondary_check_script=/usr/bin/masterha_secondary_check-sremote_host1-sremote_host2master_ip_failover_script=/root/mha/scripts/master_ip_failovershutdown_script=/usr/local/masterha/scripts/power_managerreport_script=/usr/local/masterha/scripts/send_reportmaster_ip_online_change_script=/root/mha/scripts/master_ip_online_change

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

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

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

#建立mha的文件夹mkdir-p/root/mha/confmkdir-p/root/mha/log?mkdir-p/root/mha/scriptschmod-R777/root/mha/scripts

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';?networkinterfacemy$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);

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??2.Setread_only=1onthecurrentmaster??4.Deegisteroriginalmaster'siptothecatalogdatabase??#*Anydatabaseaccessfailurewillresultinscriptdie.??my$exit_code=1;??eval{???Settingread_only=1onthenewmaster(toavoidaccident)???my$new_master_handler=newMHA::DBHelper();

";???}???else{die"Failed!

";???}???$new_master_handler->disconnect();

",??current_time_us(),$threads<5){??printData::Dumper->new([$_])->Indent(0)->Terse(1)->Dump."

"???foreach(@threads);?}}sleep_until();$_tstart=[gettimeofday];$time_until_read_only--;@threads=get_threads_util($orig_master_handler->{dbh},?$orig_master_handler->{connection_id});???}

";???}???else{die"Failed!

",??current_time_us(),$threads<5){??printData::Dumper->new([$_])->Indent(0)->Terse(1)->Dump."

"???foreach(@threads);?}}sleep_until();$_tstart=[gettimeofday];$time_until_kill_threads--;@threads=get_threads_util($orig_master_handler->{dbh},?$orig_master_handler->{connection_id});???}

";???$orig_master_handler->kill_threads(@threads)if($#threads>=0);???printcurrent_time_us()."done.

";???$orig_master_handler->enable_log_bin_local();???$orig_master_handler->disconnect();

";???&stop_vip();???$exit_code=10;?Afterfinishingthescript,MHAexecutesFLUSHTABLESWITHREADLOCK???$exit_code=0;??};??if($@){???warn"GotError:$@

";???exit$exit_code;??}??exit$exit_code;?}?elsif($commandeq"start"){??#1.Registernewmaster'siptothecatalogdatabase

Ifexitcodeis0or10,MHAdoesnotabort??my$exit_code=10;??eval{???Updatemasteriponthecatalogdatabase,etc???print"EnablingtheVIP-$viponthenewmaster-$new_master_host

";???&start_vip();?$exit_code=0;??};??if($@){???warn"GotError:$@

";???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_mastersubstop_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

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

1、开启三个数据库节点实例，确保主从复制状态正常(执行2.6.2操作)

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