您的位置 首页 百科

免费好用的MySQL高可用方案

徐晨亮,MySQL DBA,知数堂学员。热衷于数据库优化,自动化运维及数据库周边工具开发,对MySQL源码有一定的兴趣。
环境说明地址端口角色说明127.0.0.13306master-127.0.0.13307slave-127.0.0.16032/6033ProxySQL版本2.0.1127.0.0.110001replication-manager-一、MySQL搭建这里省略,推荐增强半同步复制账号:repl/repl4slave
二、ProxySQL2.1安装[root@izbp12nspj47ypto9t6vyezsrc]#rpm-ivhproxysql-2.0.1-1-centos7.x86_64.rpmPreparing…#################################[100%]Updating/installing…1:proxysql-2.0.1-1warning:groupproxysqldoesnotexist-usingroot#################################[100%]2.2用户配置在master上创建连接账户及监控账户
grantselect,update,delete,inserton*.*toxucl@’%’identifiedby’xuclxucl’;grantreplicationclienton*.*tomonitor@’%’identifiedby’monitor’;2.3server配置w_hg : 10ro_hg : 11
监控账号monitor monitor
配置读写组
insertintomysql_replication_hostgroups(writer_hostgroup,reader_hostgroup)values(10,11);配置MySQL实例
insertintomysql_servers(hostgroup_id,hostname,port,max_connections,max_replication_lag)values(10,’127.0.0.1′,3306,100,300),(11,’127.0.0.1′,3307,100,300);配置USERS
insertintomysql_users(username,password,default_hostgroup,default_schema,max_connections)values(‘xucl’,’xuclxucl’,10,’xucl’,90);配置监控账号
setmysql-monitor_username=’monitor’;setmysql-monitor_password=’monitor’;savemysqluserstodisk;loadmysqluserstoruntime;savemysqlserverstodisk;loadmysqlserverstoruntime;savemysqlvariablestodisk;loadmysqlvariablestoruntime;2.4配置读写分离INSERTINTOmysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)VALUES(1,1,’^SELECT.*FORUPDATE$’,10,1),(2,1,’^SELECT’,11,1);LOADMYSQLQUERYRULESTORUNTIME;SAVEMYSQLQUERYRULESTODISK;2.5简单测试读写分离(xucl:db1@xucl:09:29:57)[(none)]>select@@port;+——–+|@@port|+——–+|3307|+——–+1rowinset(0.05sec)(xucl:db1@xucl:09:30:00)[(none)]>select@@portforupdate;+——–+|@@port|+——–+|3306|+——–+1rowinset(0.00sec)三、replication-manager3.1安装rpm-ivhreplication-manager-osc-2.0.0_rc2-1.x86_64.rpm3.2配置文件[root@izbp12nspj47ypto9t6vyezsrc]#cat/etc/replication-manager/config.toml[db3306]title=”db3306″db-servers-hosts=”127.0.0.1:3306,127.0.0.1:3307″db-servers-prefered-master=”127.0.0.1:3306″db-servers-credential=”xucl:xuclxucl”replication-credential=”repl:repl4slave”failover-mode=”manual”proxysql=trueproxysql-server=”127.0.0.1″proxysql-port=6033proxysql-admin-port=6032proxysql-writer-hostgroup=10proxysql-reader-hostgroup=11proxysql-user=”admin”proxysql-password=”admin”proxysql-bootstrap=false[Default]monitoring-datadir=”/data/replication-manager”monitoring-sharedir=”/data/share_replication-manager”log-level=7log-file=”/var/log/replication-manager.log”replication-multi-master=falsereplication-multi-tier-slave=falsefailover-readonly-state=truehttp-server=truehttp-bind-address=”0.0.0.0″http-port=”10001″3.3启动replication-manager[root@izbp12nspj47ypto9t6vyezsrc]#servicereplication-managerrestartRestartingreplication-manager(viasystemctl):[OK]日志2019/02/2209:34:38[db3306]INFO-Failoverininteractivemode2019/02/2209:34:38[db3306]INFO-Loading1proxies2019/02/2209:34:38[db3306]INFO-LoadingProxySQL…2019/02/2209:34:38[db3306]DEBUG-Monitoringserverloop2019/02/2209:34:38[db3306]DEBUG-Server[0]:URL:127.0.0.1:3306State:SuspectPrevState:Suspect2019/02/2209:34:38[db3306]DEBUG-Server[1]:URL:127.0.0.1:3307State:SuspectPrevState:Suspect2019/02/2209:34:38[db3306]DEBUG-Stateunconnectedsetbynon-masterruleonserver127.0.0.1:33072019/02/2209:34:38[db3306]DEBUG-Lookupserver127.0.0.1:3306ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:38[db3306]DEBUG-Lookupserver127.0.0.1:3307ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:38[db3306]DEBUG-Lookupserver127.0.0.1:3306ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:38[db3306]DEBUG-Lookupserver127.0.0.1:3307ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:38[db3306]DEBUG-GetMasterFromReplicationserver3307lookupifserver127.0.0.1:3307istheone:33072019/02/2209:34:38[db3306]DEBUG-Server127.0.0.1:3306isconfiguredasaslave2019/02/2209:34:38[db3306]DEBUG-Privilegecheckon127.0.0.1:33062019/02/2209:34:38[db3306]DEBUG-Clientconnectionfoundonserver127.0.0.1:3306withIP127.0.0.1forhost127.0.0.12019/02/2209:34:38[db3306]DEBUG-Server127.0.0.1:3307wassetmasteraslastnonslave2019/02/2209:34:38[db3306]DEBUG-Privilegecheckon127.0.0.1:33072019/02/2209:34:38[db3306]DEBUG-Clientconnectionfoundonserver127.0.0.1:3307withIP127.0.0.1forhost127.0.0.12019/02/2209:34:38[db3306]DEBUG-GetMasterFromReplicationserver3307lookupifserver127.0.0.1:3307istheone:33072019/02/2209:34:38[db3306]DEBUG-Checkingifserver127.0.0.1isaslaveofserver127.0.0.12019/02/2209:34:38[db3306]DEBUG-GetMasterFromReplicationserver3307lookupifserver127.0.0.1:3307istheone:33072019/02/2209:34:38[db3306]INFO-InitProxyType:proxysqlHost:127.0.0.1Port:60322019/02/2209:34:38[db3306]STATE-OPENEDWARN0048:Nosemisyncsettingsonslave127.0.0.1:33062019/02/2209:34:38[db3306]STATE-OPENEDWARN0058:NoGTIDstrictmodeonslave127.0.0.1:33062019/02/2209:34:38[db3306]STATE-OPENEDWARN0060:Nosemisyncsettingsonmaster127.0.0.1:33072019/02/2209:34:38[db3306]STATE-OPENEDWARN0070:NoGTIDstrictmodeonmaster127.0.0.1:33072019/02/2209:34:38[db3306]STATE-OPENEDERR00021:Allclusterdbserversdown2019/02/2209:34:38[db3306]DEBUG-Lookupserver127.0.0.1:3306ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:38[db3306]DEBUG-Lookupserver127.0.0.1:3307ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:39[db3306]DEBUG-Lookupserver127.0.0.1:3306ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:39[db3306]DEBUG-Lookupserver127.0.0.1:3307ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:39[db3306]DEBUG-Lookupserver127.0.0.1:3306ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:39[db3306]DEBUG-Lookupserver127.0.0.1:3307ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:40[db3306]DEBUG-Lookupserver127.0.0.1:3306ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:40[db3306]DEBUG-Lookupserver127.0.0.1:3307ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:40[db3306]DEBUG-Lookupserver127.0.0.1:3306ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:40[db3306]DEBUG-Lookupserver127.0.0.1:3307ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:40[db3306]DEBUG-Monitoringserverloop2019/02/2209:34:40[db3306]DEBUG-Server[0]:URL:127.0.0.1:3306State:SlavePrevState:Slave2019/02/2209:34:40[db3306]DEBUG-Server[1]:URL:127.0.0.1:3307State:MasterPrevState:StandAlone2019/02/2209:34:40[db3306]DEBUG-Master[]:URL:127.0.0.1:3307State:MasterPrevState:StandAlone2019/02/2209:34:40[db3306]DEBUG-Slave[0]:URL:127.0.0.1:3306State:SlavePrevState:Slave2019/02/2209:34:40[db3306]DEBUG-Server127.0.0.1:3306isconfiguredasaslave2019/02/2209:34:40[db3306]DEBUG-Privilegecheckon127.0.0.1:33062019/02/2209:34:40[db3306]DEBUG-Clientconnectionfoundonserver127.0.0.1:3306withIP127.0.0.1forhost127.0.0.12019/02/2209:34:40[db3306]DEBUG-Server127.0.0.1:3307wassetmasteraslastnonslave2019/02/2209:34:40[db3306]DEBUG-Privilegecheckon127.0.0.1:33072019/02/2209:34:40[db3306]DEBUG-Clientconnectionfoundonserver127.0.0.1:3307withIP127.0.0.1forhost127.0.0.12019/02/2209:34:40[db3306]DEBUG-GetMasterFromReplicationserver3307lookupifserver127.0.0.1:3307istheone:33072019/02/2209:34:40[db3306]DEBUG-Checkingifserver127.0.0.1isaslaveofserver127.0.0.12019/02/2209:34:40[db3306]DEBUG-GetMasterFromReplicationserver3307lookupifserver127.0.0.1:3307istheone:33072019/02/2209:34:40[db3306]STATE-RESOLVERR00021:Allclusterdbserversdown2019/02/2209:34:41[db3306]DEBUG-Lookupserver127.0.0.1:3306ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:41[db3306]DEBUG-Lookupserver127.0.0.1:3307ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:41[db3306]DEBUG-Lookupserver127.0.0.1:3306ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:41[db3306]DEBUG-Lookupserver127.0.0.1:3307ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:42[db3306]DEBUG-Lookupserver127.0.0.1:3306ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:42[db3306]DEBUG-Lookupserver127.0.0.1:3307ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:42[db3306]DEBUG-Monitoringserverloop2019/02/2209:34:42[db3306]DEBUG-Server[0]:URL:127.0.0.1:3306State:SlavePrevState:Slave2019/02/2209:34:42[db3306]DEBUG-Server[1]:URL:127.0.0.1:3307State:MasterPrevState:Master2019/02/2209:34:42[db3306]DEBUG-Master[]:URL:127.0.0.1:3307State:MasterPrevState:Master2019/02/2209:34:42[db3306]DEBUG-Slave[0]:URL:127.0.0.1:3306State:SlavePrevState:Slave2019/02/2209:34:42[db3306]DEBUG-Server127.0.0.1:3306isconfiguredasaslave2019/02/2209:34:42[db3306]DEBUG-Privilegecheckon127.0.0.1:33062019/02/2209:34:42[db3306]DEBUG-Clientconnectionfoundonserver127.0.0.1:3306withIP127.0.0.1forhost127.0.0.12019/02/2209:34:42[db3306]DEBUG-Server127.0.0.1:3307wassetmasteraslastnonslave2019/02/2209:34:42[db3306]DEBUG-Privilegecheckon127.0.0.1:33072019/02/2209:34:42[db3306]DEBUG-Clientconnectionfoundonserver127.0.0.1:3307withIP127.0.0.1forhost127.0.0.12019/02/2209:34:42[db3306]DEBUG-GetMasterFromReplicationserver3307lookupifserver127.0.0.1:3307istheone:33072019/02/2209:34:42[db3306]DEBUG-Checkingifserver127.0.0.1isaslaveofserver127.0.0.12019/02/2209:34:42[db3306]DEBUG-GetMasterFromReplicationserver3307lookupifserver127.0.0.1:3307istheone:33072019/02/2209:34:43[db3306]DEBUG-Lookupserver127.0.0.1:3306ifmaxscalebinlogserver:127.0.0.1:33062019/02/2209:34:43[db3306]DEBUG-Lookupserver127.0.0.1:3307ifmaxscalebinlogserver:127.0.0.1:33063.4切换
手动点击switch over即可实现切换
切换日志
2019/02/2209:38:22[db3306]INFO-RestAPIreceiveSwitchoverrequest2019/02/2209:38:22[db3306]INFO-SignalingSwitchover…2019/02/2209:38:22[db3306]INFO—————————2019/02/2209:38:22[db3306]INFO-Startingmasterswitchover2019/02/2209:38:22[db3306]INFO—————————2019/02/2209:38:22[db3306]INFO-Checkinglongrunningupdatesonmaster102019/02/2209:38:22[db3306]INFO-Flushingtablesonmaster127.0.0.1:33072019/02/2209:38:22[db3306]INFO-Electinganewmaster2019/02/2209:38:22[db3306]INFO-Slave127.0.0.1:3306hasbeenelectedasanewmaster2019/02/2209:38:22[db3306]INFO-Terminatingallthreadson127.0.0.1:33072019/02/2209:38:22[db3306]INFO-Rejectingupdateson127.0.0.1:3307(oldmaster)2019/02/2209:38:22[db3306]INFO-Waitingforcandidatemastertoapplyrelaylog2019/02/2209:38:22[db3306]INFO-Readingallrelaylogson127.0.0.1:33062019/02/2209:38:22[db3306]INFO-Stoppingslavethreadonnewmaster2019/02/2209:38:22[db3306]INFO-FailoverProxyType:proxysqlHost:127.0.0.1Port:60322019/02/2209:38:22[db3306]INFO-Resettingslaveonnewmasterandsetread/writemodeon2019/02/2209:38:22[db3306]ERROR-Couldnotsetnewmasterasread-write2019/02/2209:38:22[db3306]INFO-Injectfaketransactiononnewmaster127.0.0.1:33062019/02/2209:38:22[db3306]INFO-Switchingoldmasterasaslave2019/02/2209:38:22[db3306]INFO-DoingMySQLGTIDswitchoftheoldmaster2019/02/2209:38:22[db3306]INFO-Switchingotherslavestothenewmaster2019/02/2209:38:22[db3306]INFO-Masterswitchon127.0.0.1:3306complete2019/02/2209:38:22[db3306]STATE-OPENEDERR00041:Skipslaveinelection127.0.0.1:3307hasmorethan30secondsofreplicationdelay(35484)2019/02/2209:38:22[db3306]STATE-OPENEDERR00039:Skipslaveinelection127.0.0.1:3307replnotelectable2019/02/2209:38:22[db3306]STATE-OPENEDERR00032:Nocandidatesfoundinslaveslist2019/02/2209:38:34[db3306]STATE-RESOLVERR00041:Skipslaveinelection127.0.0.1:3307hasmorethan30secondsofreplicationdelay(35481)2019/02/2209:38:34[db3306]STATE-RESOLVERR00039:Skipslaveinelection127.0.0.1:3307replnotelectable2019/02/2209:38:34[db3306]STATE-RESOLVERR00032:Nocandidatesfoundinslaveslist四、replication切换逻辑在线切换逻辑
failover切换逻辑
总结总得来说这是一套简单又非常高效的MySQL高可用方案,并且是可上生产的方案,并且replication manager又自带web界面,操作起来非常简单
这个方案前面有ProxySQL,可以实现读写分离,应用程序连接ProxySQL即可
replication manager负责数据库高可用切换,切换完成后,ProxySQL通过read only感知读写节点,对应用程序来说可能会有短暂的影响,确保程序重连机制
看到replication manager的在线切换和failover切换逻辑,涉及到多个步骤的freeze,具体整个实现逻辑与过程将在下篇文章进行剖析

叶老师的「MySQL核心优化」大课已升级到MySQL 8.0,扫码开启MySQL 8.0修行之旅吧

为您推荐

发表评论

邮箱地址不会被公开。 必填项已用*标注

返回顶部