Kwi-Citymobil sisebenzisa i-database ye-MySQL njengeyona nto ingundoqo yokugcina idatha. Sinamaqela amaninzi edatha yeenkonzo ezahlukeneyo kunye neenjongo.

Ukufumaneka rhoqo kwenkosi kuyisibonakaliso esibalulekileyo sokusebenza kwenkqubo yonke kunye neengxenye zayo. Ukubuyiswa kweqela ngokuzenzekelayo kwimeko yokungaphumeleli kwenkosi kunciphisa kakhulu ixesha lokuphendula isiganeko kunye nexesha lokunciphisa inkqubo. Kule nqaku, ndiza kujonga ukufumaneka okuphezulu (HA) uyilo lweqela le-MySQL esekelwe MySQL Orchestrator kunye needilesi ze-IP ezibonakalayo (VIP).

HA isisombululo esekelwe VIP

Okokuqala, ndiza kukuxelela ngokufutshane ukuba yintoni inkqubo yethu yokugcina idatha.

Sisebenzisa isikimu sokuphindaphinda sakudala kunye nenkosi enye efikelelekayo kunye neekopi ezininzi zokufunda kuphela. Iqela linokuqulatha i-master intermediate - i-node eyi-replica kunye ne-master kwabanye. Abathengi bafikelela kwii-replicas nge-HAProxy, evumela ukuhanjiswa komthwalo kunye nokulinganisa okulula. Ukusetyenziswa kwe-HAProxy ngenxa yezizathu zembali, kwaye ngoku sisendleleni yokufudukela kwi-ProxySQL.

Uphindaphindo lwenziwa kwimo ye-semi-synchronous esekwe kwi GTID. Oku kuthetha ukuba ubuncinane enye ireplica kufuneka ifake intengiselwano phambi kokuba ithathwe njengeyimpumelelo. Le ndlela yokuphindaphinda inika ibhalansi efanelekileyo phakathi kokusebenza kunye nokhuseleko lwedatha kwimeko yokungaphumeleli kwe-master node. Ngokusisiseko zonke iinguqu zidluliswa ukusuka kwinkosi ukuya kwi-replicas usebenzisa Row Based Replication (RBR), kodwa ezinye iindawo zisenokuba nazo mixed binlog format.

I-orchestrator ngamaxesha athile ihlaziya imeko ye-cluster topology, ihlalutya ulwazi olufunyenweyo, kwaye ukuba kuvela iingxaki, inokuqalisa inkqubo yokubuyisela ngokuzenzekelayo. Umthuthukisi unoxanduva lwenkqubo ngokwayo, kuba inokuphunyezwa ngeendlela ezahlukeneyo: ngokusekelwe kwi-VIP, i-DNS, usebenzisa iinkonzo zokufumanisa iinkonzo okanye iindlela zokuzibhala.

Enye indlela elula yokubuyisela inkosi ukuba iyasilela kukusebenzisa iidilesi zeVIP ezidadayo.

Yintoni ekufuneka uyazi malunga nesi sisombululo ngaphambi kokuba uqhubele phambili:

• I-VIP yidilesi ye-IP engadityaniswanga nojongano oluthile lothungelwano olubonakalayo. Ukuba i-node iyasilela okanye ngexesha lokugcinwa okucwangcisiweyo, sinokutshintshela i-VIP kwesinye isibonelelo kunye nexesha elincinci lokunciphisa.
• Ukukhulula kunye nokukhupha idilesi ye-IP enexabiso eliphantsi kunye nokusebenza ngokukhawuleza.
• Ukuze usebenze kunye neVIP, kufuneka ufike kumncedisi nge-SSH, okanye ukusetyenziswa kwezinto ezikhethekileyo, umzekelo, keepalived.

Makhe sijonge iingxaki ezinokubakho kunye ne-wizard yethu kwaye sicinge ukuba indlela yokubuyisela ngokuzenzekelayo kufuneka isebenze njani.

Umdibaniso womnatha kwinkosi unyamalele, okanye ingxaki iye yavela kwinqanaba le hardware, kwaye umncedisi akafumaneki.

1. I-orchestrator ihlaziya i-cluster topology, i-replica nganye ibika ukuba umphathi akafumaneki. I-orchestrator iqala inkqubo yokukhetha i-replica efanelekileyo indima yenkosi entsha kwaye iqala ukubuyisela.
2. Sizama ukususa i-VIP kwinkosi endala - ngaphandle kwempumelelo.
3. I-replica itshintshela kwindima yenkosi. I-topology iyakhiwa kwakhona.
4. Ukongeza ujongano olutsha lwenethiwekhi ngeVIP. Ekubeni kwakungenakwenzeka ukususa i-VIP, siqala ukuthumela isicelo ngasemva ARP yasimahla. Olu hlobo lwesicelo / impendulo ikuvumela ukuba uhlaziye itafile ye-IP kunye ne-MAC yedilesi yeemephu kwiiswitshi ezixhunyiwe, ngaloo ndlela ikwazisa ukuba i-VIP yethu ihambile. Oku kunciphisa ukuba nokwenzeka split brain xa kubuya inkosi endala.
5. Lonke unxibelelwano olutsha luthunyelwa ngokukhawuleza kwinkosi entsha. Uqhagamshelwano oludala aluphumeleli kwaye iifowuni eziphindaphindiweyo kwi-database zenziwe kwinqanaba lesicelo.

Umncedisi usebenza ngendlela eqhelekileyo, ukusilela kwenzeke kwinqanaba leDBMS

I-algorithm ifana nemeko yangaphambili: ukuhlaziya i-topology kunye nokuqalisa inkqubo yokubuyisela. Ekubeni iseva ikhona, sikhulula ngempumelelo i-VIP kwinkosi endala, siyidlulisele kwintsha, kwaye sithumele izicelo ezininzi ze-ARP. Ukubuyiswa okunokwenzeka kwenkosi endala akufanele kuchaphazele i-cluster eyakhiwe ngokutsha kunye nokusebenza kwesicelo.

Ezinye iingxaki

Ukungaphumeleli kwee-replicas okanye iinkosi eziphakathi akakhokeli ukwenza izenzo ezizenzekelayo kwaye kufuna ungenelelo olwenziwa ngesandla.

I-interface yenethiwekhi yenyani ihlala yongezwa okwethutyana, oko kukuthi, emva kokuqalisa iseva, i-VIP ayibelwanga ngokuzenzekelayo. Umzekelo ngamnye wesiseko sedatha uqala kwindlela yokufunda-kuphela ngokungagqibekanga, iokhestra itshintsha ngokuzenzekelayo inkosi entsha ukuba ibhale kwaye izama ukuyifaka. read only kwinkosi endala. Ezi ntshukumo zijolise ekunciphiseni ukuba nokwenzeka split brain.

Iingxaki zinokuvela ngexesha lenkqubo yokubuyisela, ekufuneka ikwaziswe nge-orchestrator UI ngaphezu kwezixhobo eziqhelekileyo zokubeka iliso. Siye sandise i-REST API ngokongeza eli nqaku (PR ngoku iphantsi kovavanyo).

Umzobo oqhelekileyo wesisombululo se-HA uboniswe ngezantsi.

Ukukhetha inkosi entsha

Iokhestra ihlakaniphe ngokwaneleyo kwaye izama ukukhetha eyona replica ifanelekileyo njengenkosi entsha ngokwezi khrayitheriya zilandelayo:

• i-replica isala emva kwenkosi;
• Inguqulelo ye-MySQL yenkosi kunye nekopi;
• uhlobo lokuphindaphinda (i-RBR, i-SBR okanye ixutywe);
• indawo kumaziko edatha afanayo okanye ahlukeneyo;
• ukufumaneka errant GTID - ukuthengiselana okwenziwa kwi-replica kwaye akukho kwi-master;
• imithetho yokukhetha isiko nayo ithathelwa ingqalelo.

Ayiyiyo yonke i-cue engumgqatswa ofanelekileyo wenkosi. Umzekelo, ikopi ingasetyenziselwa ukugcina idatha, okanye iseva inobumbeko lwehardware olubuthathaka. Iokhestra ixhasa Imithetho yesandla onokuthi ngayo ulungiselele ukhetho lwakho lomgqatswa olukhethwayo oluninzi olukhethwayo lungahoywa.

Ixesha lokuphendula kunye nexesha lokubuyisela

Kwimeko yesiganeko, kubalulekile ukunciphisa ixesha lokunciphisa inkqubo, ngoko makhe siqwalasele iiparamitha ze-MySQL ezichaphazela ukudala kunye nokuhlaziywa kwe-cluster topology yi-orchestrator:

• slave_net_timeout - inani lemizuzwana apho i-replica ilinde idatha entsha okanye isignali ye-heartbeat ukuba ifike kwinkosi ngaphambi kokuba uxhulumaniso lubonwe njengelahlekileyo kwaye luxhunywe kwakhona. Ixabiso eliphantsi, ngokukhawuleza i-replica inokugqiba ukuba unxibelelwano kunye nenkosi luphukile. Sibeka eli xabiso kwimizuzwana emi-5.
• MASTER_CONNECT_RETRY - inani lemizuzwana phakathi kwemizamo yokudibanisa kwakhona. Kwimeko yeengxaki zenethiwekhi, ixabiso eliphantsi le parameter liza kuvumela uxhulumaniso olukhawulezayo kwaye luthintele inkqubo yokubuyisela iqoqo ukuqala. Ixabiso elicetyiswayo ngumzuzwana we-1.
• MASTER_RETRY_COUNT - inani eliphezulu lemizamo yokudibanisa kwakhona.
• MASTER_HEARTBEAT_PERIOD — ikhefu kwimizuzwana emva kokuba inkosi ithumele uphawu lokubetha kwentliziyo. Ukungagqibeki ukuya kwisiqingatha sexabiso slave_net_timeout.

Iiparamitha zeOkhestra:

• DelayMasterPromotionIfSQLThreadNotUpToDate - ukuba iyalingana true, emva koko indima enkulu ayiyi kusetyenziswa kwi-replica yomviwa de i-replica's SQL thread igqibe zonke iitransekshini ezingasetyenziswanga kwi-Relay Log. Sisebenzisa olu khetho ukunqanda ukulahleka kwentengiselwano xa zonke iikopi zabaviwa zisilela ngasemva.
• InstancePollSeconds - ukuphindaphindwa kokwakhiwa kunye nokuhlaziya i-topology.
• RecoveryPollSeconds - ukuphindaphinda kohlalutyo lwe-topology. Ukuba kufunyenwe ingxaki, i-topology recovery iqaliswa. Oku rhoqo, ilingana nomzuzwana omnye.

Iqela ngalinye leqela livotelwa yiokhestra kube kanye InstancePollSeconds imizuzwana Xa ingxaki ifunyenwe, i-cluster state iyanyanzeliswa ihlaziywe, kwaye ke isigqibo sokugqibela senziwe ukwenza ukubuyisela. Ngokuzama i-database eyahlukeneyo kunye neeparamitha ze-orchestrator, sakwazi ukunciphisa ixesha lokuphendula kunye nexesha lokubuyisela kwimizuzwana ye-30.

ibhentshi yovavanyo

Saqala ukuvavanya iskimu se-HA ngophuhliso lwendawo ibhentshi yovavanyo kunye nokuphunyezwa ngakumbi kwiindawo zovavanyo kunye nemveliso. Indawo yokuma yengingqi izenzekela ngokupheleleyo ngokusekwe kwiDocker kwaye ikuvumela ukuba ulinge uqwalaselo lweokhestra kunye nenethiwekhi, ukukala iqela ukusuka kwiiseva ezi-2 ukuya kwezi-3 ukuya kwishumi elinesibini, kwaye wenze umthambo kwindawo ekhuselekileyo.

Ngexesha lokuzilolonga, sikhetha enye yeendlela zokulinganisa ingxaki: dubula inkosi kwangoko kill -9, phelisa inkqubo kwaye umise umncedisi (docker-compose stop), linganisa iingxaki zenethiwekhi usebenzisa iptables -j REJECT okanye iptables -j DROP. Silindele iziphumo ezilandelayo:

• i-orchestrator iya kubona iingxaki kunye nenkosi kwaye ihlaziye i-topology ngaphandle kwemizuzwana ye-10;
• inkqubo yokubuyisela iya kuqala ngokuzenzekelayo: ukucwangciswa kwenethiwekhi kuya kutshintsha, indima yenkosi iya kudlula kwi-replica, i-topology iya kwakhiwa kwakhona;
• inkosi entsha iya kubhalwa, iikopi eziphilayo aziyi kulahleka ngexesha lenkqubo yokwakha kwakhona;
• idatha iya kuqala ukubhalwa kwinkosi entsha kwaye iphindwe;
• Ixesha lokubuyisela lilonke aliyi kuba ngaphezu kwemizuzwana ye-30.

Njengoko uyazi, inkqubo inokuziphatha ngokungafaniyo kwiindawo zokuvavanya kunye nemveliso ngenxa ye-hardware eyahlukeneyo kunye nokucwangciswa kwenethiwekhi, ukungafani kwi-synthetic kunye nomthwalo wangempela, njl. Ke ngoko, ngamaxesha athile siqhuba umthambo kwiimeko zokwenyani, sijonga indlela eziphatha ngayo inkqubo xa uqhagamshelo lwenethiwekhi lulahlekile okanye iindawo zalo zonakaliswa. Kwixesha elizayo, sifuna ukwakha isiseko esifana ngokupheleleyo kuzo zombini iindawo ezisingqongileyo kwaye sizenzele uvavanyo lwaso.

ezifunyanisiweyo

Impilo ye-node yenkqubo yokugcina eyona nto iphambili yemisebenzi ephambili ye-SRE kunye neqela lokusebenza. Ukuphunyezwa kwe-orchestrator kunye ne-HA isisombululo esisekelwe kwi-VIP kusivumele ukuba sifezekise ezi ziphumo zilandelayo:

• ukufunyanwa okuthembekileyo kweengxaki nge-topology yeqoqo ledatha;
• ukuphendula okuzenzekelayo kunye nokukhawuleza kwiziganeko ezinxulumene nenkosi, ukunciphisa ixesha lokunciphisa inkqubo.

Nangona kunjalo, isisombululo sinemida kunye nokungalunganga:

• ukulinganisa iskimu se-HA kumaziko amaninzi edatha kuya kufuna inethiwekhi ye-L2 enye phakathi kwabo;
• Ngaphambi kokwabela i-VIP kwinkosi entsha, kufuneka siyikhulule kwindala. Inkqubo ilandelelana, eyandisa ixesha lokubuyisela;
• ukukhulula i-VIP kufuna ukufikelela kwi-SSH kumncedisi, okanye nayiphi na enye indlela yokubiza iinkqubo ezikude. Ekubeni iseva okanye i-database ihlangabezana neengxaki ezibangele inkqubo yokubuyisela, asikwazi ukuqiniseka ukuba ukususwa kwe-VIP kuya kugqiba ngempumelelo. Kwaye oku kunokukhokelela ekubonakaleni kweeseva ezimbini ezinedilesi ye-IP efanayo kunye nengxaki split brain.

Ukubaleka split brain, ungasebenzisa indlela USTONITH ("Dubula enye iNode kwiNtloko"), ehlukanisa ngokupheleleyo okanye ikhubaze i-node yengxaki. Kukho ezinye iindlela zokuphumeza ukufumaneka okuphezulu kwe-cluster: indibaniselwano ye-VIP kunye ne-DNS, ukufunyanwa kwenkonzo kunye neenkonzo ze-proxy, ukuphindaphinda okuhambelanayo kunye nezinye iindlela ezinobungozi kunye neenzuzo zabo.

Ndathetha ngendlela yethu yokwenza i-MySQL failover cluster. Kulula ukuphumeza kwaye inika inqanaba elamkelekileyo lokuthembeka phantsi kweemeko zangoku. Njengoko yonke inkqubo ngokubanzi kunye neziseko ezingundoqo ngokukodwa ziphuhla, le ndlela ngokungathandabuzekiyo iya kuvela.

umthombo: www.habr.com