第十五课mysql8.0高可用架构之mha和

MHA（MasterHighAvailability）目前在MySQL高可用方面是一个相对成 MHA还提 MHA能够在一个已经存在的 MySQL，当检测到Master故障后能够实现自动故障转移，通过鉴定出最“新”的Salve的relaylog，并将其应用到所有的Slave，这样MHA就能够保证各个slave之间的数据一致性，即使有些slave在主库 的relaylog 配置它的优先级。由于master能够保证各个slave之间的数据一致性，所以所有的slave节点都有希望成为主节点。在通常的replication环境中由于 数据一致性问题，在MHA中将不会发生。MHA可以手动地实现故障转移，而不必去理会master的状态，即不 确认故障发生后可通过MHA手动切换切换MasterMHA能够在0.5-2秒内实现切换，0.5-2秒的写阻塞通常是可接受的，所以你甚至能在非 性能优秀，可工作在半同步和异步，当mysql状态时，仅需要每隔N向master发送包(默认3秒)，所以对性能无影响。你可以理解为MHA单的主从框架性能一样。 引擎，MHA都支持，不会局限于部署在一台slave节点上。部署在一台slave节点上。MHANode运行在每台MySQL服务器上，MHAManager 数据的slaveMHAManager可以单独部署MHA由Manager节点和Node节点组成升为新的master，然后将所有其他的slave重新指向新的master 的master保存二进制日 （binlog; 应用差异的中继日志（relaylog）到其他的应用从master保存的二进制日 （binlog提升一个slave为新的使其他的slave连接新的master进 在所有节点上安装nodeyuminstall-yperl-DBD-rpm-ivhmha4mysql-node-0.58-在管理节点上安装manager[root@server1data]#rpm-ivhepel-release-latest-yuminstallperl-DBD-MySQLperl-Config-Tinyperl-Log-Dispatchperl-Parallel-ForkManager-yrpm-ivhmha4mysql-manager-0.58-manager_host$cat [serverdefault]#mysqluserandpassword#workingdirectoryonthemanager#workingdirectoryonMySQLservers port=3308server_id=2#socket=#Removeleading#tosetoptionsmainlyusefulforreportingservers.#TheserverdefaultsarefasterfortransactionsandfastSELECTs.#Adjustsizesasneeded,experimenttofindtheoptimal#join_buffer_size=128M#sort_buffer_size=2M#read_rnd_buffer_size=MHAmanager通过 ssh-keygen-t

[root@mycat.ssh]#cat mkdir/var/log/masterha/app1-ln-sln-s/usr/local/mysql/bin/mysqlMHAmanager可通过masterha_check_ssh 检测SSH连接是否配置正常#masterha_check_ssh-- [root@mycatapp1]#masterha_check_ssh- ThuJun2221:08:552017-[warning]Globalconfigurationfile/etc/ma fnot ThuJun2221:08:552017-[info]Readingapplicationdefaultconfigurationsf..ThuJun2221:08:552017-[info]ReadingserverconfigurationsThuJun2221:08:552017-[info]StartingSSHconnectiontests..ThuJun2221:08:562017-[debug]ThuJun2221:08:552017-[debug]ConnectingviaSSHfromWarning:Permanentlyadded'30'(ECDSA)tothelistofknownhosts.ThuJun2221:08:552017-[debug]ok.ThuJun2221:08:552017-[debug]ConnectingviaSSHfromroot@master(28:22)Warning:Permanentlyadded'31'(ECDSA)tothelistofknownThuJun2221:08:562017-[debug]ThuJun2221:08:562017-ThuJun2221:08:552017-[debug]ConnectingviaSSHfrom

Jun2221:08:562017-[debug]ok.ThuJun2221:08:562017-[debug]ConnectingviaSSHfromroot@slave1(30:22)toroot@slave2(31:22)..Warning:Permanentlyadded'31'(ECDSA)tothelistofhosts.ThuJun2221:08:562017ThuJun2221:08:572017-ThuJun2221:08:562017-[debug]

[debug]ConnectingviaSSHfromThuJun2221:08:56 [debug]ConnectingviaSSHThuJun2221:08:56 [debug]ConnectingviaSSHfromThuJun2221:08:572017-[debug]

为了让MHA为了让MHAmaster和slave必须在配置文件中正确配置，MHA •[root@mycat.ssh]#masterha_check_repl- -[info]Alive-[info]-[info]-[info]-[info]Alive-[info]Version=5.7.17-log(oldestmajorversionbetweenslaves)log-bin:enabledThuJun2221:58:242017-[info] ReplicatingfromThuJun2221:58:242017-[info]Version=5.7.17-log(oldestmajorversionMySQLReplicationHealthis

slaves)log- ，正确安装了manager和node节点，SSH配置也正确，那么下一步就是开启manager，可通过masterha_manager命令开启manager_host$nohupmasterha_manager- >/var/log/masterha/app1/mha_manager.log</dev/null检查manager当MHAmanager启 manager_host$masterha_check_status-- app1(pid:3287)is _OK),你可以通过masterha_stopmanagermanager_host$masterha_stop--fStoppedapp1测试master [root@masterbin]#/etc/init.d/mysql.serverstopShuttingdownMySQL. 这时候检查manager的log日志，看看slave1是否成功成为新的master，并且slave2 vi-----FailoverReport----app1:MySQLMasterfailovermastertoslave1succeededMastermasterisdown!CheckMHAManagerlogsatmycatdetails.Startedautomated(non- Thelatestslaveslave1(30:3308)hasallrelaylogsforrecovery.Selectedslave1asanewmaster.slave1:OK:Applyingalllogsslave2:Thishosthasthelatestrelaylogevents.Generatingrelaydifffilesfromthelatestslaveslave2:OK:Applyingalllogssucceeded.Slavestarted,replicatingfromslave1.slave1:Resettingslaveinfosucceeded.Masterfailovertoslave1(30:3308)completed mysql>showslavestatus\GEmptyset(0.00 mysql>showWaitingformastertosendeventMaster_Host:30Master_User:replMaster_Port:3308Connect_Retry:60Master_Log_File:mysql-bin.000015Read_Master_Log_Pos:520bin.000002Relay_Log_Pos:320SlaveIORunning:YesSlaveSQL 英[root@0~]#cat/etc [serverdefault]manager_workdir=/var/log/masterha/app1.log//设置manager的工作manager_log=/var/log/masterha/app1/manager.log//设置manager的日志//设置保存master_ip_failover_script=/usr/local/bin/master_ip_failover//设置自动failover时候的切换本password=12 //设置mysql中root用户的 user=root设 用户_interval=1//设 failoverremote_workdir=/tmp//设置远端mysql在发生切换时binlog的保存位置repl_password=123456//设置 repl_user=repl//设置 report_script=/usr/local/send_report//设置发生切换后发送的 secondary_check_script=/usr/local/bin/masterha_secondary_check-sserver03-s ssh_user=root//设置ssh的登录用户名[server1]port=3306port=3306 slavecheck_repl_delay=0//默认情况下如果一个slave master100M的relaylogs的话，MHA将不会选择该slave作为 port=3306~]#relaylog（slave [root@0~]#mysql-e'setglobalrelay_log_purge=0'[root@0~]#mysql-e'setglobalrelay_log_purge=0'注意：MHArelaylog的相关信息，所以这里要将relaylog的自动清除设置为OFF，采用手动清除relaylog的方式。在默认情况下，从服务器上的中继日志会在SQL线程执行完毕后被自动删除。但是在MHA。mysql>stopmysql>insertintotemp2values(5000,'abc');QueryOK,1rowaffected(0.00sec)[root@master~]#/etc/init.d/mysql.serverstopShuttingdownMySQL. mysql>select*fromtemp2whereid=5000;Emptyset(0.00sec)-----FailoverReport----app1:MySQLMasterfailovermastertosucceededMastermasterisMasterfailovertoslave1(30:3308)completedmysql>select*fromtemp2whereid=5000;+------ |id|name+------ |5000|abc[root@mycat~]#/usr/local/mysql/bin/mysqlbinlog-[root@mycat~]#masterha_stop- [root@master~]#/etc/init.d/mysql.serverShuttingdown [root@mycat~]#masterha_master_switch--master_state=dead- f------FailoverReport----app1:MySQLMasterfailovermasterto

--new_master_ip=31-succeededMastermasterisdown!CheckMHAManagerlogsatmycatfordetails.Started Thelatestslaveslave1(30:3308)hasallrelaylogsforSelectedslave1asanewslave1:OK:Applyingalllogsslave2:Thishosthasthelatestrelaylogevents.Generatingrelaydifffilesfromthelatestslaveslave2:OK:Applyingalllogssucceeded.Slavestarted,replicatingfromMasterfailovertoslave1(30:3308)completedMasterfailovertoslave1(30:3308)completedmysql>showslavestatus\GEmptyset(0.00sec)mysql>showslaveWaitingformastertosendeventMaster_Host:30Master_User:replMaster_Port:3308Connect_Retry:60Master_Log_File:mysql-bin.000022Read_Master_Log_Pos:531bin.000002Relay_Log_Pos:320Slave_IO_Running:YesSlave_SQL_Running: 为了保证数据完全一致性，在最快的时间内完成切换，MHA的 所有的showslavestatus的输出中Seconds_Behind_Master参数小于或者等于下running_updates_limit为1秒。在master端，通过showprocesslist [root@mycat~]#masterha_stop- [root@mycat~]#masterha_master_switch- f---new_master_host=slave2--new_master_port=3308--orig_master_is_new_slave--master

masterslave--running_updates_limit=10000,故障切换时,候选master如果有延迟的话，mha切换不能（单位为s），但是切换的时间长短是由recover时relay日志的大小决定 master：mysql>showslaveWaitingformastertosendeventMaster_Host:slave2Slave_IO_Running:Yesmysql>showslaveSlave_IO_State:WaitingformastertoeventMaster_Host:Slave_SQL_Running:Yesmysql>showslavestatus\GEmptyset(0.00sec) :2 /usr/local/bin/master_ip_failover详见 [root@masterbin]#/etc/init.d/mysql.serverstopShuttingdownMySQL MMM（Master-MasterreplicationmanagerforMySQL）是一套支持双主故障切换 程序。MMMPerl语言开发，主要用来 和管理MySQLMaster-Master（双主） 可以说MMM这套 MMM延迟较高的服务器的虚拟 ）server-id=1log_slave_updates1auto-increment-increment=2auto-increment-offset=1server-id=2log_slave_updatesauto-increment-increment=2auto-increment-offset=2server-id=3log_slave_updates通过changemaster语句建立 CHANGEMASTERTOMASTERLOGPOS=234;

[root@192.168.0.[root@0~]#[root@0~]#mv:mmm2:mysql-mmm-2.2.1.tar.gzmysql-mmm-2.2.1.tar.gz[root@0~]#tarxfmysql-mmm-[root@0~]#cdmysql-mmm-2.2.1[root@0mysql-mmm-2.2.1]#makeinstall[root@0~]#cd/etc/mysql-mmm/[root@0mysql-mmm]#cat <host

</host><host

</host><hostdb2></host><hostdb3></host></role>

db1,db2db2,</role>agent_user为agentmodemodeexclusive<rolewrite>中hosts表示目前的主库和备选主的真实主机ip或者主机名，ips为对外提供的虚拟ip地址，<rolereadr>中hosts代表从库真实的ip和主机名，ips代表从库的虚拟ip分别在db1，db2，db3三台主机的/etc/mysql-mmm配置mmm_agent.conf文件，分别用不同的字符标识，注意这三台机器的thisdb1，db1要配置thisdb1，db2要配置为thisdb2，而db3要配置为thisdb3[root@mastermysql-mmm]#catmmm_agent.conf this[root@slave1mysql-mmm]#catmmm_agent.conf this[root@slave2mysql-mmm]#catmmm_agent.conf this[root@mycatmysql-mmm]#catmmm_mon.confinclude<monitor></monitor><host

/usr/lib/mysql-28,30,</host>debug

主机的ip地址，而在<host 在3台服务器(db1,db2,db3）进行 mysql>GRANTSUPER, ,PROCESSON*.*'mmm_agent'@'192.168.237.%'IDENTIFIEDBY'mmm_agent';QueryOK,0rowsaffected(0.08sec)mysql>GRANT ON*.*'mmm_monitor'@'192.168.237.%'IDENTIFIEDBY'mmm_monitor';QueryOK,0rowsaffected(0.00sec)mysql>flushQueryOK,0rowsaffected(0.03sec)mysql>YuminstallYuminstall–ycpanYuminstall–ygcccpan-iAlgorithm::DiffClass::SingletonDBIDBD::mysqlLog::Log4perlMail::Send Proc::Daemoncpan-i最后分别在db1，db2，db3上启动agent，并在mycat（32）[root@mastermysql-mmm]#/etc/init.d/mysql-mmm-agentstartDaemonbin:'/usr/sbin/mmm_agentd'Daemonpid:StartingMMMAgentdaemon...启动[root@mycatmysql-mmm]#/etc/init.d/mysql-mmm-monitorDaemonbin:'/usr/sbin/mmm_mond'Daemonpid:StartingMMMMonitordaemon:

其中agent的日志存放在/var/log/mysql-mmm/mmm_agentd.log，monitor日志 [root@mycatmysql-mmm]#mmm_controlchecksalldb2[lastchange:2017/06/2923:31:47]OKdb2

[lastchange:2017/06/29

OKdb2change:2017/06/29

db2rep_backlog

change:2017/06/2923:31:47]OK:Backlognull2017/06/29db3 [lastchange:2017/06/29

23:31:47]

db3rep_threads[last

change:2017/06/29db3rep_backlog[lastchange:2017/06/2923:31:47]OK:Backlognull [last2017/06/29 db1 [lastchange:2017/06/2923:31:47]

[lastchange:2017/06/29db1rep_backlog[last

change:2017/06/2923:31:47]OK:Backlog在monitor[root@0~]#mmmcontroldb1(28)master/AWAITING_RECOVERdb2(30)master/AWAITING_RECOVEY. [root@mycatmysql-mmm]#mmm_controlset_onlineOK:Stateof'db1'changedtoONLINE.Nowyoucanwaitsometimeandcheckitsnewroles![root@mycatmysql-mmm]#mmm_controlset_onlineOK:Stateof'db2'changedtoONLINE.Nowyoucanwaitsometimeandcheckitsnewroles![root@mycatmysql-mmm]#mmm_controlset_onlineOK:Stateof'db3'changedtoONLINE.Nowyoucanwaitsometimeandcheckitsnewroles![root@mycatmysql-mmm]#mmm_controlshowdb1(28)master/ONLINE.Roles:writer(20)db2(30)master/ONLINE.Roles:db3(31)slave/ONLINE.Roles:reader(22)模拟db2（30）宕机，手动停止mysql服务，观察monitor日志：[root@32~]#tail-f/var/log/mysql-mmm/mmm_mond.log2017/06/2622:52:29FATALStateofhost'db2'changedfromONLINEtoHARD_OFFLINE( :OK,mysql:notOK)从日志发现db2的状态有ONLINE转换为 [root@mycatmysql-mmm]#mmm_controlshowdb1(28)master/ONLINE.Roles:writer(20)db2(30)master/HARD_OFFLINE.Role