各种报错，搭建Mysql MHA高可用集群时踩的各种坑
mha下载地址，需要××× https://code.google.com/p/mysql-master-ha/ 管理软件 mha4mysql-manager-0.52-0.noarch.rpm 节点软件 mha4mysql-node-0.52-0.noarch.rpm 环境介绍Centos6.7X64 192.168.30.210monitor 192.168.30.211db1(master) 192.168.30.212db2(备master) 192.168.30.213db3 192.168.30.214db4 版本Mysql5.5.45 一、准备工作 db1-3需要先安装好Mysql，不会装的不用看下去了 三台机器都添加hosts表 192.168.30.211db1 192.168.30.212db2 192.168.30.213db3 192.168.30.214db4 实现4台机器间免密码登陆 在db1上执行shell ssh-keygen-trsa ssh-copy-id192.168.30.210 ssh-copy-id192.168.30.212 ssh-copy-id192.168.30.213 ssh-copy-id192.168.30.214 在db2上执行shell ssh-keygen-trsa ssh-copy-id192.168.30.211 ssh-copy-id192.168.30.210 ssh-copy-id192.168.30.213 ssh-copy-id192.168.30.214 在db3上执行shell ssh-keygen-trsa ssh-copy-id192.168.30.211 ssh-copy-id192.168.30.212 ssh-copy-id192.168.30.210 ssh-copy-id192.168.30.214 在db4上执行shell ssh-keygen-trsa ssh-copy-id192.168.30.211 ssh-copy-id192.168.30.212 ssh-copy-id192.168.30.210 ssh-copy-id192.168.30.213 在monitor上执行shell ssh-keygen-trsa ssh-copy-id192.168.30.211 ssh-copy-id192.168.30.212 ssh-copy-id192.168.30.213 ssh-copy-id192.168.30.214 建立Mysql直接主从同步 特别注意：每台DB的server id必须唯一 在DB1 上面建立同步账户 mysql>grantreplicationslaveon*.*toslave@'192.168.30.%'identifiedby"123"; mysql>showmasterstatus; +------------------+----------+--------------+------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB| +------------------+----------+--------------+------------------+ |mysql-bin.000001|5001||| +------------------+----------+--------------+------------------+ 1rowinset(0.00sec) 在DB2 上面建立同步账户,因为是备用master mysql>grantreplicationslaveon*.*toslave@'192.168.30.%'identifiedby"123"; 打开防火墙 iptables-IINPUT-ptcp--dport3306-jACCEPT&&serviceiptablessave 在db2上做主从，开防火墙 mysql>CHANGEMASTERTOMASTER_HOST='192.168.30.211',MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=5001,MASTER_USER='slave', MASTER_PASSWORD='123'; mysql>slavestart; QueryOK,0rowsaffected(0.00sec) 查看同步状态 mysql>showslavestatus\G ***************************1.row*************************** Slave_IO_State:Waitingformastertosendevent Master_Host:192.168.30.212 Master_User:slave Master_Port:3306 Connect_Retry:60 Master_Log_File:mysql-bin.000003 Read_Master_Log_Pos:107 Relay_Log_File:mysql-relay-bin.000005 Relay_Log_Pos:253 Relay_Master_Log_File:mysql-bin.000003 Slave_IO_Running:Yes Slave_SQL_Running:Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno:0 Last_Error: Skip_Counter:0 Exec_Master_Log_Pos:107 Relay_Log_Space:555 Until_Condition:None Until_Log_File: Until_Log_Pos:0 Master_SSL_Allowed:No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master:0 Master_SSL_Verify_Server_Cert:No Last_IO_Errno:0 Last_IO_Error: Last_SQL_Errno:0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id:2 1rowinset(0.00sec) iptables-IINPUT-ptcp--dport3306-jACCEPT&&serviceiptablessave 在db3上做主从，开防火墙 CHANGEMASTERTOMASTER_HOST='192.168.30.211',MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=5001,MASTER_USER='slave', MASTER_PASSWORD='123'; iptables-IINPUT-ptcp--dport3306-jACCEPT&&serviceiptablessave 基础环境搭建好了 二、安装配置MHA 在monitir上安装 如果安装0.56版本的，则需要添加epel源来提供需要的依赖 yumlocalinstall-ymha4mysql-node-0.52-0.noarch yumlocalinstall-ymha4mysql-manager-0.52-0.noarch.rpm 在db1-4上安装 yumlocalinstall-ymha4mysql-node-0.52-0.noarch 在所有DB上面授权MHA管理账号 mysql>grantallon*.*tomha@'192.168.30.%'identifiedby'123456'; 在monitor上面 先新建一个工作目录 mkdir/mha 编辑配置文件 vim/etc/masterha_default.cnf [serverdefault] #刚才授权的mysql管理用戶名 user=mha password=123456 manager_workdir=/mha manager_log=/mha/manager.log remote_workdir=/mha #ssh免密钥登录的帐号名 ssh_user=root #mysql复制帐号，用来在主从机之间同步二进制日志等 repl_user=slave repl_password=123 #ping间隔，用来检测master是否正常 ping_interval=1 [server1] hostname=db1 master_binlog_dir=/data/mysql #候选人，master挂掉时候优先让它顶 candidate_master=1 [server2] hostname=db2 master_binlog_dir=/data/mysql candidate_master=1 [server3] hostname=db3 master_binlog_dir=/data/mysql #不能成为master no_master=1 [server4] hostname=db4 master_binlog_dir=/data/mysql #不能成为master no_master=1 验证SSH互认是否成功 [root@monitor ~]# masterha_check_ssh --conf=/etc/masterha_default.cnf [root@monitor~]#masterha_check_ssh--conf=/etc/masterha_default.cnf FriAug2617:59:442016-[info]Readingdefaultconfiguratoinsfrom/etc/masterha_default.cnf.. FriAug2617:59:442016-[info]Readingapplicationdefaultconfigurationsfrom/etc/masterha_default.cnf.. FriAug2617:59:442016-[info]Readingserverconfigurationsfrom/etc/masterha_default.cnf.. FriAug2617:59:442016-[info]StartingSSHconnectiontests.. FriAug2617:59:452016-[error][/usr/lib64/perl5/vendor_perl/MHA/SSHCheck.pm,ln63] FriAug2617:59:442016-[debug]ConnectingviaSSHfromroot@db2(192.168.30.212)toroot@db1(192.168.30.211).. FriAug2617:59:442016-[debug]ok. FriAug2617:59:442016-[debug]ConnectingviaSSHfromroot@db2(192.168.30.212)toroot@db3(192.168.30.213).. FriAug2617:59:452016-[debug]ok. FriAug2617:59:452016-[debug]ConnectingviaSSHfromroot@db2(192.168.30.212)toroot@db4(192.168.30.214).. Permissiondenied(publickey,gssapi-keyex,gssapi-with-mic,password). FriAug2617:59:452016-[error][/usr/lib64/perl5/vendor_perl/MHA/SSHCheck.pm,ln106]SSHconnectionfromroot@db2(192.168.30.212)toroot@db4(192.168.30.214)failed! FriAug2617:59:462016-[debug] FriAug2617:59:442016-[debug]ConnectingviaSSHfromroot@db1(192.168.30.211)toroot@db2(192.168.30.212).. FriAug2617:59:452016-[debug]ok. FriAug2617:59:452016-[debug]ConnectingviaSSHfromroot@db1(192.168.30.211)toroot@db3(192.168.30.213).. FriAug2617:59:452016-[debug]ok. FriAug2617:59:452016-[debug]ConnectingviaSSHfromroot@db1(192.168.30.211)toroot@db4(192.168.30.214).. FriAug2617:59:452016-[debug]ok. FriAug2617:59:462016-[error][/usr/lib64/perl5/vendor_perl/MHA/SSHCheck.pm,ln63] FriAug2617:59:452016-[debug]ConnectingviaSSHfromroot@db4(192.168.30.214)toroot@db2(192.168.30.212).. Permissiondenied(publickey,gssapi-keyex,gssapi-with-mic,password). FriAug2617:59:452016-[error][/usr/lib64/perl5/vendor_perl/MHA/SSHCheck.pm,ln106]SSHconnectionfromroot@db4(192.168.30.214)toroot@db2(192.168.30.212)failed! FriAug2617:59:462016-[error][/usr/lib64/perl5/vendor_perl/MHA/SSHCheck.pm,ln63] FriAug2617:59:452016-[debug]ConnectingviaSSHfromroot@db3(192.168.30.213)toroot@db2(192.168.30.212).. FriAug2617:59:452016-[debug]ok. FriAug2617:59:452016-[debug]ConnectingviaSSHfromroot@db3(192.168.30.213)toroot@db1(192.168.30.211).. FriAug2617:59:462016-[debug]ok. FriAug2617:59:462016-[debug]ConnectingviaSSHfromroot@db3(192.168.30.213)toroot@db4(192.168.30.214).. Permissiondenied(publickey,gssapi-keyex,gssapi-with-mic,password). FriAug2617:59:462016-[error][/usr/lib64/perl5/vendor_perl/MHA/SSHCheck.pm,ln106]SSHconnectionfromroot@db3(192.168.30.213)toroot@db4(192.168.30.214)failed! SSHConfigurationCheckFailed! at/usr/bin/masterha_check_sshline44 报错：这个错就是root@db2(192.168.30.212) to root@db4(192.168.30.214)之间互认还没完成，添加ssh认证即可 再来 [root@monitor~]#masterha_check_ssh--conf=/etc/masterha_default.cnf FriAug2618:03:002016-[info]Readingdefaultconfiguratoinsfrom/etc/masterha_default.cnf.. FriAug2618:03:002016-[info]Readingapplicationdefaultconfigurationsfrom/etc/masterha_default.cnf.. FriAug2618:03:002016-[info]Readingserverconfigurationsfrom/etc/masterha_default.cnf.. FriAug2618:03:002016-[info]StartingSSHconnectiontests.. FriAug2618:03:022016-[debug] FriAug2618:03:002016-[debug]ConnectingviaSSHfromroot@db2(192.168.30.212)toroot@db1(192.168.30.211).. FriAug2618:03:012016-[debug]ok. FriAug2618:03:012016-[debug]ConnectingviaSSHfromroot@db2(192.168.30.212)toroot@db3(192.168.30.213).. FriAug2618:03:012016-[debug]ok. FriAug2618:03:012016-[debug]ConnectingviaSSHfromroot@db2(192.168.30.212)toroot@db4(192.168.30.214).. FriAug2618:03:022016-[debug]ok. FriAug2618:03:022016-[debug] FriAug2618:03:012016-[debug]ConnectingviaSSHfromroot@db1(192.168.30.211)toroot@db2(192.168.30.212).. FriAug2618:03:012016-[debug]ok. FriAug2618:03:012016-[debug]ConnectingviaSSHfromroot@db1(192.168.30.211)toroot@db3(192.168.30.213).. FriAug2618:03:022016-[debug]ok. FriAug2618:03:022016-[debug]ConnectingviaSSHfromroot@db1(192.168.30.211)toroot@db4(192.168.30.214).. FriAug2618:03:022016-[debug]ok. FriAug2618:03:032016-[debug] FriAug2618:03:022016-[debug]ConnectingviaSSHfromroot@db4(192.168.30.214)toroot@db2(192.168.30.212).. FriAug2618:03:022016-[debug]ok. FriAug2618:03:022016-[debug]ConnectingviaSSHfromroot@db4(192.168.30.214)toroot@db1(192.168.30.211).. FriAug2618:03:022016-[debug]ok. FriAug2618:03:022016-[debug]ConnectingviaSSHfromroot@db4(192.168.30.214)toroot@db3(192.168.30.213).. FriAug2618:03:032016-[debug]ok. FriAug2618:03:032016-[debug] FriAug2618:03:012016-[debug]ConnectingviaSSHfromroot@db3(192.168.30.213)toroot@db2(192.168.30.212).. FriAug2618:03:022016-[debug]ok. FriAug2618:03:022016-[debug]ConnectingviaSSHfromroot@db3(192.168.30.213)toroot@db1(192.168.30.211).. FriAug2618:03:022016-[debug]ok. FriAug2618:03:022016-[debug]ConnectingviaSSHfromroot@db3(192.168.30.213)toroot@db4(192.168.30.214).. FriAug2618:03:032016-[debug]ok. FriAug2618:03:032016-[info]AllSSHconnectiontestspassedsuccessfully. 通过检查 下一步 检查mysql主从复制 [root@monitor~]#masterha_check_repl--conf=/etc/masterha_default.cnf ------------------------------省略号------------------------------------------------------ Can'tlocateMHA/BinlogManager.pmin@INC(@INCcontains:/usr/local/lib64/perl5/usr/local/share/perl5/usr/lib64/perl5/vendor_perl/usr/share/perl5/vendor_perl/usr/lib64/perl5/usr/share/perl5.)at/usr/bin/apply_diff_relay_logsline24. BEGINfailed--compilationabortedat/usr/bin/apply_diff_relay_logsline24. FriAug2618:11:552016-[error][/usr/lib64/perl5/vendor_perl/MHA/ManagerUtil.pm,ln132]nodeversionondb4notfound!MaybeMHANodepackageisnotinstalled? at/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pmline278 FriAug2618:11:552016-[error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm,ln315]Errorhappendoncheckingconfigurations.Diedat/usr/lib64/perl5/vendor_perl/MHA/ManagerUtil.pmline133. FriAug2618:11:552016-[error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm,ln396]Errorhappenedonmonitoringservers. FriAug2618:11:552016-[info]Gotexitcode1(Notmasterdead). MySQLReplicationHealthisNOTOK! 报错： 那是不是主从检查没通过呢，其实不是得，这是个坑，关键报错在这句 Can'tlocateMHA/BinlogManager.pmin@INC(@INCcontains:/usr/local/lib64/perl5/usr/local/share/perl5/usr/lib64/perl5/vendor_perl/usr/share/perl5/vendor_perl/usr/lib64/perl5/usr/share/perl5.)at/usr/bin/apply_diff_relay_logsline24 百度一下结果是这样的 http://ronaldbradford.com/blog/mysql-mha-and-perl-pathing-2013-08-26/ 解决办法是在5台机器上面做软连接，把这个32位的依赖链接到64位的支持库里面去 ln-s/usr/lib/perl5/vendor_perl/MHA/usr/lib64/perl5/vendor_perl/ 解决完之后，再执行检查，又报错 [root@monitor~]#masterha_check_repl--conf=/etc/masterha_default.cnf ------------------------------省略号------------------------------------------------------ Checkingoutputdirectoryisaccessibleornot.. ok. Binlogfoundat/data/mysql,uptomysql-bin.000003 FriAug2618:21:232016-[info]Mastersettingcheckdone. FriAug2618:21:232016-[info]CheckingSSHpublickeyauthenticationandcheckingrecoveryscriptconfigurationsonallaliveslaveservers.. FriAug2618:21:232016-[info]Executingcommand:apply_diff_relay_logs--command=test--slave_user=mha--slave_host=db1--slave_ip=192.168.30.211--slave_port=3306--workdir=/mha--target_version=5.5.45-log--manager_version=0.52--relay_log_info=/data/mysql/relay-log.info--slave_pass=xxx FriAug2618:21:232016-[info]Connectingtoroot@192.168.30.211(db1).. Checkingslaverecoveryenvironmentsettings.. Opening/data/mysql/relay-log.info...ok. Relaylogfoundat/data/mysql,uptomysql-relay-bin.000048 Temporaryrelaylogfileis/data/mysql/mysql-relay-bin.000048 Testingmysqlconnectionandprivileges..done. Testingmysqlbinlogoutput..done. Cleaninguptestfile(s)..done. FriAug2618:21:252016-[info]Executingcommand:apply_diff_relay_logs--command=test--slave_user=mha--slave_host=db3--slave_ip=192.168.30.213--slave_port=3306--workdir=/mha--target_version=5.5.45-log--manager_version=0.52--relay_log_info=/data/mysql/relay-log.info--slave_pass=xxx FriAug2618:21:252016-[info]Connectingtoroot@192.168.30.213(db3).. Checkingslaverecoveryenvironmentsettings.. Opening/data/mysql/relay-log.info...ok. Relaylogfoundat/data/mysql,uptomysql-relay-bin.000051 Temporaryrelaylogfileis/data/mysql/mysql-relay-bin.000051 Testingmysqlconnectionandprivileges..done. Testingmysqlbinlogoutput..done. Cleaninguptestfile(s)..done. FriAug2618:21:272016-[info]Executingcommand:apply_diff_relay_logs--command=test--slave_user=mha--slave_host=db4--slave_ip=192.168.30.214--slave_port=3306--workdir=/mha--target_version=5.5.45-log--manager_version=0.52--relay_log_info=/data/mysql/relay-log.info--slave_pass=xxx FriAug2618:21:272016-[info]Connectingtoroot@192.168.30.214(db4).. Can'texec"mysqlbinlog":Nosuchfileordirectoryat/usr/lib64/perl5/vendor_perl/MHA/BinlogManager.pmline99. mysqlbinlogversionnotfound! at/usr/bin/apply_diff_relay_logsline425 FriAug2618:21:272016-[error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm,ln129]Slavessettingscheckfailed! FriAug2618:21:272016-[error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm,ln304]Slaveconfigurationfailed. FriAug2618:21:272016-[error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm,ln315]Errorhappendoncheckingconfigurations.at/usr/bin/masterha_check_replline48 FriAug2618:21:272016-[error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm,ln396]Errorhappenedonmonitoringservers. FriAug2618:21:272016-[info]Gotexitcode1(Notmasterdead). MySQLReplicationHealthisNOTOK! 这次报错提示找不到mysqlbinlog命令 Can'texec"mysqlbinlog":Nosuchfileordirectoryat/usr/lib64/perl5/vendor_perl/MHA/BinlogManager.pmline99. 我的mysql是编译安装的，添加了mysql bin目录的环境变量的，但是它竟然提示找不到这个命令，可能是没有读取/etc/profile文件吧，那我们就再做软连接到系统目录好了 解决：在所有db执行 [root@db4~]#ln-s/usr/local/mysql/bin/mysqlbinlog/usr/bin/mysqlbinlog 再来检查，又错，再看 [root@monitor~]#masterha_check_repl--conf=/etc/masterha_default.cnf ------------------------------省略号------------------------------------------------------ Checkingoutputdirectoryisaccessibleornot.. ok. Binlogfoundat/data/mysql,uptomysql-bin.000003 FriAug2618:28:122016-[info]Mastersettingcheckdone. FriAug2618:28:122016-[info]CheckingSSHpublickeyauthenticationandcheckingrecoveryscriptconfigurationsonallaliveslaveservers.. FriAug2618:28:122016-[info]Executingcommand:apply_diff_relay_logs--command=test--slave_user=mha--slave_host=db1--slave_ip=192.168.30.211--slave_port=3306--workdir=/mha--target_version=5.5.45-log--manager_version=0.52--relay_log_info=/data/mysql/relay-log.info--slave_pass=xxx FriAug2618:28:122016-[info]Connectingtoroot@192.168.30.211(db1).. Checkingslaverecoveryenvironmentsettings.. Opening/data/mysql/relay-log.info...ok. Relaylogfoundat/data/mysql,uptomysql-relay-bin.000464 Temporaryrelaylogfileis/data/mysql/mysql-relay-bin.000464 Testingmysqlconnectionandprivileges..done. Testingmysqlbinlogoutput..done. Cleaninguptestfile(s)..done. FriAug2618:28:132016-[info]Executingcommand:apply_diff_relay_logs--command=test--slave_user=mha--slave_host=db3--slave_ip=192.168.30.213--slave_port=3306--workdir=/mha--target_version=5.5.45-log--manager_version=0.52--relay_log_info=/data/mysql/relay-log.info--slave_pass=xxx FriAug2618:28:132016-[info]Connectingtoroot@192.168.30.213(db3).. Checkingslaverecoveryenvironmentsettings.. Opening/data/mysql/relay-log.info...ok. Relaylogfoundat/data/mysql,uptomysql-relay-bin.000469 Temporaryrelaylogfileis/data/mysql/mysql-relay-bin.000469 Testingmysqlconnectionandprivileges..done. Testingmysqlbinlogoutput..done. Cleaninguptestfile(s)..done. FriAug2618:28:162016-[info]Executingcommand:apply_diff_relay_logs--command=test--slave_user=mha--slave_host=db4--slave_ip=192.168.30.214--slave_port=3306--workdir=/mha--target_version=5.5.45-log--manager_version=0.52--relay_log_info=/data/mysql/relay-log.info--slave_pass=xxx FriAug2618:28:162016-[info]Connectingtoroot@192.168.30.214(db4).. Checkingslaverecoveryenvironmentsettings.. Opening/data/mysql/relay-log.info...ok. Relaylogfoundat/data/mysql,uptodb4-relay-bin.000002 Temporaryrelaylogfileis/data/mysql/db4-relay-bin.000002 Testingmysqlconnectionandprivileges..sh:mysql:commandnotfound mysqlcommandfailedwithrc127:0! at/usr/bin/apply_diff_relay_logsline315 main::check()calledat/usr/bin/apply_diff_relay_logsline429 eval{...}calledat/usr/bin/apply_diff_relay_logsline409 main::main()calledat/usr/bin/apply_diff_relay_logsline97 FriAug2618:28:162016-[error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm,ln129]Slavessettingscheckfailed! FriAug2618:28:162016-[error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm,ln304]Slaveconfigurationfailed. FriAug2618:28:162016-[error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm,ln315]Errorhappendoncheckingconfigurations.at/usr/bin/masterha_check_replline48 FriAug2618:28:162016-[error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm,ln396]Errorhappenedonmonitoringservers. FriAug2618:28:162016-[info]Gotexitcode1(Notmasterdead). MySQLReplicationHealthisNOTOK! 这次报错提示 Testingmysqlconnectionandprivileges..sh:mysql:commandnotfound 那好吧，跟上面一样，软连接 [root@db4~]#ln-s/usr/local/mysql/bin/mysql/usr/bin/mysql 问题解决 再来检查 [root@monitor~]#masterha_check_repl--conf=/etc/masterha_default.cnf ------------------------------省略号------------------------------------------------------ SatAug2710:27:382016-[info]Executingcommand:apply_diff_relay_logs--command=test--slave_user=mha--slave_host=db4--slave_ip=192.168.30.214--slave_port=3306--workdir=/mha--target_version=5.5.45-log--manager_version=0.52--relay_log_info=/data/mysql/relay-log.info--slave_pass=xxx SatAug2710:27:382016-[info]Connectingtoroot@192.168.30.214(db4).. Checkingslaverecoveryenvironmentsettings.. Opening/data/mysql/relay-log.info...ok. Relaylogfoundat/data/mysql,uptomysql-relay-bin.032494 Temporaryrelaylogfileis/data/mysql/mysql-relay-bin.032494 Testingmysqlconnectionandprivileges..done. Testingmysqlbinlogoutput..done. Cleaninguptestfile(s)..done. SatAug2710:27:392016-[info]Slavessettingscheckdone. SatAug2710:27:392016-[info] db2(currentmaster) +--db1 +--db3 +--db4 SatAug2710:27:392016-[info]Checkingreplicationhealthondb1.. SatAug2710:27:392016-[info]ok. SatAug2710:27:392016-[info]Checkingreplicationhealthondb3.. SatAug2710:27:392016-[info]ok. SatAug2710:27:392016-[info]Checkingreplicationhealthondb4.. SatAug2710:27:392016-[info]ok. SatAug2710:27:392016-[warning]master_ip_failover_scriptisnotdefined. SatAug2710:27:392016-[warning]shutdown_scriptisnotdefined. SatAug2710:27:392016-[info]Gotexitcode0(Notmasterdead). MySQLReplicationHealthisOK. 这次终于正常通过了 启动MHA管理程序 [root@monitor~]#masterha_manager--conf=/etc/masterha_default.cnf& SatAug2710:31:512016-[info]Readingdefaultconfiguratoinsfrom/etc/masterha_default.cnf.. SatAug2710:31:512016-[info]Readingapplicationdefaultconfigurationsfrom/etc/masterha_default.cnf.. SatAug2710:31:512016-[info]Readingserverconfigurationsfrom/etc/masterha_default.cnf.. 一切正常 观察日志 [root@monitor~]#cat/mha/manager.log ------------------------------省略号--------------------------------------------------- SatAug2710:33:042016-[info] db2(currentmaster) +--db1 +--db3 +--db4 SatAug2710:33:042016-[warning]master_ip_failover_scriptisnotdefined. SatAug2710:33:042016-[warning]shutdown_scriptisnotdefined. SatAug2710:33:042016-[info]Setmasterpinginterval1seconds. SatAug2710:33:042016-[warning]secondary_check_scriptisnotdefined.Itishighlyrecommendedsettingittocheckmasterreachabilityfromtwoormoreroutes. SatAug2710:33:042016-[info]Startingpinghealthcheckondb2(192.168.30.212:3306).. SatAug2710:33:042016-[info]Pingsucceeded,sleepinguntilitdoesn'trespond.. 看到目前启动正常 db2是master（不是说好的master是db1么？好吧，我之前做完切过一次了，所以master飘到db2了，大家凑合着看哈） db1 db3 db3是从机 到目前为止 MHA就搭起来了 三、做故障测试，把db2关掉，看下会不会主从自动切换到db1 关掉db2 mysql，我们来tail monitor日志 [root@monitor~]#tail-f/mha/manager.log ------------------------------省略号--------------------------------------------------- SatAug2711:15:402016-[info]Masterfailovertodb1(192.168.30.211:3306)completedsuccessfully. SatAug2711:15:402016-[info] -----FailoverReport----- masterha_default:MySQLMasterfailoverdb2todb1succeeded Masterdb2isdown!#DB2挂了 CheckMHAManagerlogsatmonitor:/mha/manager.logfordetails. Startedautomated(non-interactive)failover. Thelatestslavedb1(192.168.30.211:3306)hasallrelaylogsforrecovery. Selecteddb1asanewmaster. db1:OK:Applyingalllogssucceeded. db4:Thishosthasthelatestrelaylogevents. db3:Thishosthasthelatestrelaylogevents. Generatingrelaydifffilesfromthelatestslavesucceeded. db4:OK:Applyingalllogssucceeded.Slavestarted,replicatingfromdb1.###db4重新设置主从到db1 db3:OK:Applyingalllogssucceeded.Slavestarted,replicatingfromdb1.###db3重新设置主从到db1 db1:Resettingslaveinfosucceeded. Masterfailovertodb1(192.168.30.211:3306)completedsuccessfully.###master飘到db1成功 在刷了一大堆日志后，出现了这个汇总报告，主从切换成功 我们去db3 db4上面看下是不是真的切换成功了 mysql>showslavestatus\G; ***************************1.row*************************** Slave_IO_State:Waitingformastertosendevent Master_Host:192.168.30.211 Master_User:slave Master_Port:3306 Connect_Retry:60 Master_Log_File:mysql-bin.000002 Read_Master_Log_Pos:1869 Relay_Log_File:mysql-relay-bin.000002 Relay_Log_Pos:253 Relay_Master_Log_File:mysql-bin.000002 Slave_IO_Running:Yes Slave_SQL_Running:Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno:0 Last_Error: Skip_Counter:0 Exec_Master_Log_Pos:1869 Relay_Log_Space:409 Until_Condition:None Until_Log_File: Until_Log_Pos:0 Master_SSL_Allowed:No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master:0 Master_SSL_Verify_Server_Cert:No Last_IO_Errno:0 Last_IO_Error: Last_SQL_Errno:0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id:1 1rowinset(0.01sec) ERROR: Noqueryspecified db4主从切到db1了，成功 mysql>showslavestatus\G; ***************************1.row*************************** Slave_IO_State:Waitingformastertosendevent Master_Host:192.168.30.211 Master_User:slave Master_Port:3306 Connect_Retry:60 Master_Log_File:mysql-bin.000002 Read_Master_Log_Pos:1869 Relay_Log_File:mysql-relay-bin.000002 Relay_Log_Pos:253 Relay_Master_Log_File:mysql-bin.000002 Slave_IO_Running:Yes Slave_SQL_Running:Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno:0 Last_Error: Skip_Counter:0 Exec_Master_Log_Pos:1869 Relay_Log_Space:409 Until_Condition:None Until_Log_File: Until_Log_Pos:0 Master_SSL_Allowed:No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master:0 Master_SSL_Verify_Server_Cert:No Last_IO_Errno:0 Last_IO_Error: Last_SQL_Errno:0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id:1 1rowinset(0.00sec) ERROR: Noqueryspecified 再去看看db1，主从已经停止了（废话，都成master了，主从肯定停了） mysql>showslavestatus\G; ***************************1.row*************************** Slave_IO_State: Master_Host:192.168.30.212 Master_User:slave Master_Port:3306 Connect_Retry:60 Master_Log_File: Read_Master_Log_Pos:4 Relay_Log_File:mysql-relay-bin.000001 Relay_Log_Pos:4 Relay_Master_Log_File: Slave_IO_Running:No Slave_SQL_Running:No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno:0 Last_Error: Skip_Counter:0 Exec_Master_Log_Pos:0 Relay_Log_Space:126 Until_Condition:None Until_Log_File: Until_Log_Pos:0 Master_SSL_Allowed:No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master:NULL Master_SSL_Verify_Server_Cert:No Last_IO_Errno:0 Last_IO_Error: Last_SQL_Errno:0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id:2 1rowinset(0.00sec) ERROR: Noqueryspecified 至此，mha测试完成，搭建MHA上面的坑，问题还是挺多的，要多看日志多看报错，才能找出问题的所在，当然，一篇靠谱的教程还是要有的
