CityMob ã§ã¯ãäž»èŠãªæ°žç¶ããŒã¿ ã¹ãã¬ãŒãžãšã㊠MySQL ããŒã¿ããŒã¹ã䜿çšããŠããŸãã åŒç€Ÿã§ã¯ãããŸããŸãªãµãŒãã¹ãç®çã«åãããŠããã€ãã®ããŒã¿ããŒã¹ ã¯ã©ã¹ã¿ãŒãçšæããŠããŸãã
ãã¹ã¿ãŒãåžžã«å©çšã§ãããã©ããã¯ãã·ã¹ãã å
šäœãšãã®åã
ã®éšåã®ããã©ãŒãã³ã¹ã瀺ãéèŠãªææšã§ãã ãã¹ã¿ãŒé害ãçºçããå Žåã®ã¯ã©ã¹ã¿ãŒã®èªåå埩ã«ãããã€ã³ã·ãã³ã察å¿æéãšã·ã¹ãã ã®ããŠã³ã¿ã€ã ã倧å¹
ã«ççž®ãããŸãã ãã®èšäºã§ã¯ã以äžã«åºã¥ãã MySQL ã¯ã©ã¹ã¿ãŒã®é«å¯çšæ§ (HA) èšèšã«ã€ããŠèª¬æããŸãã
VIPããŒã¹ã®HAãœãªã¥ãŒã·ã§ã³
ãŸããããŒã¿ã¹ãã¬ãŒãžã·ã¹ãã ãã©ã®ãããªãã®ããç°¡åã«èª¬æããŸãã
XNUMX ã€ã®æžã蟌ã¿ã¢ã¯ã»ã¹å¯èœãªãã¹ã¿ãŒãšè€æ°ã®èªã¿åãå°çšã¬ããªã«ãåããå€å žçãªã¬ããªã±ãŒã·ã§ã³ ã¹ããŒã ã䜿çšããŸãã ã¯ã©ã¹ã¿ãŒã«ã¯ãäžéãã¹ã¿ãŒ (ã¬ããªã«ã§ãããä»ã®ããŒãã®ãã¹ã¿ãŒã§ãããããŒã) ãå«ããããšãã§ããŸãã ã¯ã©ã€ã¢ã³ã㯠HAProxy ãä»ããŠã¬ããªã«ã«ã¢ã¯ã»ã¹ãããããåçãªè² è·åæ£ãšç°¡åãªã¹ã±ãŒãªã³ã°ãå¯èœã«ãªããŸãã HAProxy ã®äœ¿çšã¯æŽå²çãªçç±ã«ãããã®ã§ãããçŸåš ProxySQL ãžã®ç§»è¡éçšã«ãããŸãã
ã¬ããªã±ãŒã·ã§ã³ã¯ã以äžã«åºã¥ããŠæºåæã¢ãŒãã§å®è¡ãããŸãã GTID
ã ããã¯ããã©ã³ã¶ã¯ã·ã§ã³ãæåãããšã¿ãªãããåã«ãå°ãªããšã XNUMX ã€ã®ã¬ããªã«ããã©ã³ã¶ã¯ã·ã§ã³ããã°ã«èšé²ããå¿
èŠãããããšãæå³ããŸãã ãã®ã¬ããªã±ãŒã·ã§ã³ ã¢ãŒãã¯ããã¹ã¿ãŒ ããŒãã«é害ãçºçããå Žåã®ããã©ãŒãã³ã¹ãšããŒã¿ã®å®å
šæ§ã®éã§æé©ãªãã©ã³ã¹ãæäŸããŸãã åºæ¬çã«ãã¹ãŠã®å€æŽã¯ã次ã䜿çšããŠãã¹ã¿ãŒããã¬ããªã«ã«è»¢éãããŸãã Row Based Replication (RBR)
ããã ããäžéšã®ããŒãã§ã¯ mixed binlog format
.
ãªãŒã±ã¹ãã¬ãŒã¿ãŒã¯ã¯ã©ã¹ã¿ãŒ ããããžã®ç¶æ ãå®æçã«æŽæ°ããåãåã£ãæ å ±ãåæããåé¡ãçºçããå Žåã¯èªåå埩æé ãéå§ããŸãã éçºè ã¯æé èªäœã«è²¬ä»»ãè² ããŸããããã¯ãVIPãDNSããµãŒãã¹æ€åºãµãŒãã¹ã®äœ¿çšããŸãã¯èªäœã®ã¡ã«ããºã ã®äœ¿çšãªã©ãããŸããŸãªæ¹æ³ã§å®è£ ã§ããããã§ãã
ãã¹ã¿ãŒã«é害ãçºçããå Žåã«ãã¹ã¿ãŒã埩å ããç°¡åãªæ¹æ³ã® XNUMX ã€ã¯ããããŒãã£ã³ã° VIP ã¢ãã¬ã¹ã䜿çšããããšã§ãã
次ã«é²ãåã«ããã®ãœãªã¥ãŒã·ã§ã³ã«ã€ããŠç¥ã£ãŠããã¹ãããš:
- VIP ã¯ãç¹å®ã®ç©çãããã¯ãŒã¯ ã€ã³ã¿ãŒãã§ã€ã¹ã«é¢é£ä»ããããŠããªã IP ã¢ãã¬ã¹ã§ãã ããŒãã«é害ãçºçããå ŽåããŸãã¯å®æã¡ã³ããã³ã¹äžã«ãæå°éã®ããŠã³ã¿ã€ã 㧠VIP ãå¥ã®ãªãœãŒã¹ã«åãæ¿ããããšãã§ããŸãã
- ä»®æ³ IP ã¢ãã¬ã¹ã®è§£æŸãšçºè¡ã¯ãäœã³ã¹ãã§é«éãªæäœã§ãã
- VIP ã䜿çšããã«ã¯ãSSH çµç±ã§ãµãŒããŒã«ã¢ã¯ã»ã¹ãããã次ã®ãããªç¹å¥ãªãŠãŒãã£ãªãã£ã䜿çšããå¿
èŠããããŸãã
keepalived
.
ãŠã£ã¶ãŒãã§èããããåé¡ãèŠãŠãèªåå埩ã¡ã«ããºã ãã©ã®ããã«æ©èœããããæ³åããŠã¿ãŸãããã
ãã¹ã¿ãŒãžã®ãããã¯ãŒã¯æ¥ç¶ã倱ãããããããŒããŠã§ã¢ ã¬ãã«ã§åé¡ãçºçãããµãŒããŒã䜿çšã§ããªããªããŸãã
- ãªãŒã±ã¹ãã¬ãŒã¿ãŒã¯ã¯ã©ã¹ã¿ãŒ ããããžãæŽæ°ããåã¬ããªã«ã¯ãã¹ã¿ãŒã䜿çšã§ããªãããšãå ±åããŸãã ãªãŒã±ã¹ãã¬ãŒã¿ãŒã¯ãæ°ãããã¹ã¿ãŒã®åœ¹å²ã«é©ããã¬ããªã«ãéžæããããã»ã¹ãéå§ãããªã«ããªãéå§ããŸãã
- å€ããã¹ã¿ãŒãã VIP ãåé€ããããšããŠããŸãããæåããŸããã
- ã¬ããªã«ã¯ãã¹ã¿ãŒã®åœ¹å²ã«åãæ¿ãããŸãã ããããžãåæ§ç¯ãããŠããŸãã
- VIP ã䜿çšããæ°ãããããã¯ãŒã¯ ã€ã³ã¿ãŒãã§ã€ã¹ãè¿œå ããŸãã VIP ãåé€ã§ããªãã£ããããããã¯ã°ã©ãŠã³ãã§å®æçã«ãªã¯ãšã¹ãã®éä¿¡ãéå§ããŸã ç¡åARPã ãã®ã¿ã€ãã®èŠæ±/å¿çã«ãããæ¥ç¶ãããã¹ã€ããäžã® IP ã¢ãã¬ã¹ãš MAC ã¢ãã¬ã¹ã®ãããã³ã° ããŒãã«ãæŽæ°ã§ããããã«ãã£ãŠ VIP ã移åããããšãéç¥ã§ããŸãã ããã«ããå¯èœæ§ãæå°éã«æããããŸã
split brain
å€ããã¹ã¿ãŒãè¿ããšãã - ãã¹ãŠã®æ°ããæ¥ç¶ã¯ããã«æ°ãããã¹ã¿ãŒã«ãªãã€ã¬ã¯ããããŸãã å€ãæ¥ç¶ã¯å€±æããã¢ããªã±ãŒã·ã§ã³ ã¬ãã«ã§ããŒã¿ããŒã¹ãžã®åŒã³åºããç¹°ãè¿ãè¡ãããŸãã
ãµãŒããŒã¯éåžžã¢ãŒãã§åäœããŠããŸãããDBMS ã¬ãã«ã§é害ãçºçããŸãã
ã¢ã«ãŽãªãºã ã¯åã®ã±ãŒã¹ãšäŒŒãŠãããããããžãæŽæ°ããŠå埩ããã»ã¹ãéå§ããŸãã ãµãŒããŒã䜿çšå¯èœãªãããå€ããã¹ã¿ãŒäžã® VIP ãæ£åžžã«è§£æŸãããæ°ãããã¹ã¿ãŒã«è»¢éãããããã€ãã® ARP ãªã¯ãšã¹ããéä¿¡ãããŸãã å€ããã¹ã¿ãŒãæ»ã£ãŠããå¯èœæ§ããã£ãŠããåæ§ç¯ãããã¯ã©ã¹ã¿ãŒãšã¢ããªã±ãŒã·ã§ã³ã®åäœã«åœ±é¿ãäžããããšã¯ãããŸããã
ãã®ä»ã®åé¡
ã¬ããªã«ãŸãã¯äžéãã¹ã¿ãŒã®é害 å°ããªã èªåã¢ã¯ã·ã§ã³ã«äŸåãããæåä»å ¥ãå¿ èŠã§ãã
ä»®æ³ãããã¯ãŒã¯ ã€ã³ã¿ãŒãã§ã€ã¹ã¯åžžã«äžæçã«è¿œå ãããŸããã€ãŸãããµãŒããŒã®åèµ·ååŸãVIP ã¯èªåçã«å²ãåœãŠãããŸããã åããŒã¿ããŒã¹ ã€ã³ã¹ã¿ã³ã¹ã¯ããã©ã«ãã§èªã¿åãå°çšã¢ãŒãã§èµ·åãããªãŒã±ã¹ãã¬ãŒã¿ãŒã¯èªåçã«æ°ãããã¹ã¿ãŒãæžã蟌ã¿çšã«åãæ¿ããŠã€ã³ã¹ããŒã«ãè©Šè¡ããŸãã read only
å€ããã¹ã¿ãŒã«ã€ããŠã ãããã®æªçœ®ã¯ãå¯èœæ§ãæžããããšãç®çãšããŠããŸãã split brain
.
å埩ããã»ã¹äžã«åé¡ãçºçããå¯èœæ§ããããŸãããã®å Žåã¯ãæšæºã®ç£èŠããŒã«ã«å ããŠãªãŒã±ã¹ãã¬ãŒã¿ãŒ UI ãéããŠéç¥ããå¿
èŠããããŸãã ãã®æ©èœãè¿œå ããããšã§ REST API ãæ¡åŒµããŸãã (
HA ãœãªã¥ãŒã·ã§ã³ã®å šäœå³ã以äžã«ç€ºããŸãã
æ°ãããã¹ã¿ãŒã®éžæ
ãªãŒã±ã¹ãã¬ãŒã¿ãŒã¯ååã«è³¢ããéžæããããšããŸã
- ã¬ããªã«ã¯ãã¹ã¿ãŒããé ããŠããŸãã
- ãã¹ã¿ãŒãšã¬ããªã«ã® MySQL ããŒãžã§ã³ã
- ã¬ããªã±ãŒã·ã§ã³ ã¿ã€ã (RBRãSBRããŸãã¯æ··å)ã
- åããŸãã¯ç°ãªãããŒã¿ã»ã³ã¿ãŒã«èšçœ®ãããŸãã
- å¯çšæ§
errant GTID
â ã¬ããªã«äžã§å®è¡ããããã¹ã¿ãŒäžã§ã¯å®è¡ãããªããã©ã³ã¶ã¯ã·ã§ã³ã - ã«ã¹ã¿ã éžæã«ãŒã«ãèæ ®ãããŸãã
ãã¹ãŠã®ãã¥ãŒããã¹ã¿ãŒã®çæ³çãªåè£ã§ããããã§ã¯ãããŸããã ããšãã°ãã¬ããªã«ãããŒã¿ã®ããã¯ã¢ããã«äœ¿çšãããããµãŒããŒã®ããŒããŠã§ã¢æ§æã匱ãå ŽåããããŸãã ãªãŒã±ã¹ãã¬ãŒã¿ãŒ
å¿çãšå埩æé
ã€ã³ã·ãã³ããçºçããå Žåã¯ãã·ã¹ãã ã®ããŠã³ã¿ã€ã ãæå°éã«æããããšãéèŠã§ãããã®ããããªãŒã±ã¹ãã¬ãŒã¿ãŒã«ããã¯ã©ã¹ã¿ãŒ ããããžã®äœæãšæŽæ°ã«åœ±é¿ãäžãã MySQL ãã©ã¡ãŒã¿ãŒãèæ ®ããŠã¿ãŸãããã
â æ¥ç¶ã倱ããããšèªèãããŠåæ¥ç¶ããããŸã§ã«ãã¬ããªã«ããã¹ã¿ãŒããã®æ°ããããŒã¿ãŸãã¯ããŒãããŒãä¿¡å·ã®å°çãåŸ æ©ããç§æ°ã å€ãäœãã»ã©ãã¬ããªã«ã¯ãã¹ã¿ãŒãšã®éä¿¡ãåæãããããšãããéãå€æã§ããŸãã ãã®å€ã 5 ç§ã«èšå®ããŸããslave_net_timeout
â åæ¥ç¶è©Šè¡éã®ç§æ°ã ãããã¯ãŒã¯ã«åé¡ãçºçããå Žåããã®ãã©ã¡ãŒã¿ãŒã®å€ãäœããããšãè¿ éãªåæ¥ç¶ãå¯èœã«ãªããã¯ã©ã¹ã¿ãŒã®å埩ããã»ã¹ãéå§ãããªããªããŸãã æšå¥šå€ã¯ 1 ç§ã§ããMASTER_CONNECT_RETRY
MASTER_RETRY_COUNT
â åæ¥ç¶è©Šè¡ã®æ倧æ°ãMASTER_HEARTBEAT_PERIOD
â ãã¹ã¿ãŒãããŒãããŒãä¿¡å·ãéä¿¡ãããŸã§ã®ç§åäœã®ééã ããã©ã«ãã¯å€ã®ååã§ãslave_net_timeout
.
ãªãŒã±ã¹ãã¬ãŒã¿ãŒã®ãªãã·ã§ã³:
DelayMasterPromotionIfSQLThreadNotUpToDate
- çããå Žåtrue
ã®å Žåãã¬ããªã«ã® SQL ã¹ã¬ããããªã¬ãŒ ãã°ããã®æªé©çšã®ãã©ã³ã¶ã¯ã·ã§ã³ããã¹ãŠå®äºãããŸã§ããã¹ã¿ãŒ ããŒã«ã¯åè£ã¬ããªã«ã«é©çšãããŸããã ãã®ãªãã·ã§ã³ã¯ããã¹ãŠã®åè£ã¬ããªã«ãé ããå Žåã«ãã©ã³ã¶ã¯ã·ã§ã³ã倱ãããªãããã«ããããã«äœ¿çšããŸããInstancePollSeconds
â ããããžã®æ§ç¯ãšæŽæ°ã®é »åºŠãRecoveryPollSeconds
â ããããžãŒåæã®é »åºŠã åé¡ãæ€åºãããå Žåã¯ãããããžã®å埩ãéå§ãããŸãã ããå®æ° ã1ç§ã«çžåœããŸãã
åã¯ã©ã¹ã¿ãŒ ããŒãã¯ããªãŒã±ã¹ãã¬ãŒã¿ãŒã«ãã£ãŠæ¯ã« XNUMX åããŒãªã³ã°ãããŸãã InstancePollSeconds
ç§åé¡ãæ€åºããããšãã¯ã©ã¹ã¿ãŒã®ç¶æ
ã匷å¶ãããŸãã
ãã¹ãã¹ã¿ã³ã
ããŒã«ã«ã®éçºã«ãã HA ã¹ããŒã ã®ãã¹ããéå§ããŸããã
æŒç¿ã§ã¯ãåé¡ã®ãšãã¥ã¬ãŒã·ã§ã³æ¹æ³ã® XNUMX ã€ãéžæããŸãã次ã®æ¹æ³ã䜿çšããŠãã¹ã¿ãŒãç¬æã«æ®åœ±ããŸãã kill -9
ãããã»ã¹ããœããçµäºãããµãŒããŒãåæ¢ããŸã(docker-compose stop
)ã次ã䜿çšããŠãããã¯ãŒã¯ã®åé¡ãã·ãã¥ã¬ãŒãããŸãã iptables -j REJECT
ãŸã㯠iptables -j DROP
ã 次ã®ãããªçµæãæåŸ
ãããŸãã
- ãªãŒã±ã¹ãã¬ãŒã¿ãŒã¯ãã¹ã¿ãŒã®åé¡ãæ€åºãã10 ç§ä»¥å ã«ããããžãæŽæ°ããŸãã
- å埩æé ãèªåçã«éå§ãããŸãããããã¯ãŒã¯æ§æãå€æŽããããã¹ã¿ãŒã®åœ¹å²ãã¬ããªã«ã«æž¡ãããããããžãåæ§ç¯ãããŸãã
- æ°ãããã¹ã¿ãŒã¯æžã蟌ã¿å¯èœã«ãªããã©ã€ãã¬ããªã«ã¯åæ§ç¯ããã»ã¹äžã«å€±ãããŸããã
- ããŒã¿ã¯æ°ãããã¹ã¿ãŒã«æžã蟌ãŸããè€è£œããå§ããŸãã
- åèšå埩æé㯠30 ç§ä»¥å ã§ãã
ãåç¥ã®ãšãããããŒããŠã§ã¢ãšãããã¯ãŒã¯ã®æ§æãåæè² è·ãšå®éã®è² è·ã®éããªã©ã«ãããã·ã¹ãã ã®åäœã¯ãã¹ãç°å¢ãšå®çšŒåç°å¢ã§ç°ãªãå ŽåããããŸãã ãã®ãããå®éã®ç¶æ³ã§å®æçã«æŒç¿ãå®æœãããããã¯ãŒã¯æ¥ç¶ã倱ãããããåã ã®éšåãå£åãããšãã«ã·ã¹ãã ãã©ã®ããã«åäœãããã確èªããŸãã å°æ¥çã«ã¯ãäž¡æ¹ã®ç°å¢ã«å®å šã«åäžã®ã€ã³ãã©ã¹ãã©ã¯ãã£ãæ§ç¯ãããã®ãã¹ããèªååããããšèããŠããŸãã
æèŠ
ã¡ã€ã³ ã¹ãã¬ãŒãž ã·ã¹ãã ããŒãã®å¥å šæ§ã¯ãSRE ããã³éçšããŒã ã®äž»èŠãªã¿ã¹ã¯ã® XNUMX ã€ã§ãã VIP ã«åºã¥ããªãŒã±ã¹ãã¬ãŒã¿ãŒãš HA ãœãªã¥ãŒã·ã§ã³ã®å®è£ ã«ããã次ã®çµæãéæããããšãã§ããŸããã
- ããŒã¿ããŒã¹ã¯ã©ã¹ã¿ãŒã®ããããžãŒã®åé¡ã確å®ã«æ€åºããŸãã
- ãã¹ã¿ãŒé¢é£ã®ã€ã³ã·ãã³ãã«èªåçãã€è¿ éã«å¯Ÿå¿ããã·ã¹ãã ã®ããŠã³ã¿ã€ã ãåæžããŸãã
ãã ãããã®ãœãªã¥ãŒã·ã§ã³ã«ã¯æ¬¡ã®ãããªå¶éãšæ¬ ç¹ããããŸãã
- HA ã¹ããŒã ãè€æ°ã®ããŒã¿ã»ã³ã¿ãŒã«æ¡åŒµããã«ã¯ããããã®éã«åäžã® L2 ãããã¯ãŒã¯ãå¿ èŠã«ãªããŸãã
- æ°ãããã¹ã¿ãŒã« VIP ãå²ãåœãŠãåã«ãå€ããã¹ã¿ãŒã§ VIP ã解æŸããå¿ èŠããããŸãã ãã®ããã»ã¹ã¯é 次è¡ããããããå埩æéãé·ããªããŸãã
- VIP ã解æŸããã«ã¯ããµãŒããŒãžã® SSH ã¢ã¯ã»ã¹ããŸãã¯ãªã¢ãŒã ããã·ãŒãžã£ãåŒã³åºããã®ä»ã®æ¹æ³ãå¿
èŠã§ãã ãµãŒããŒãŸãã¯ããŒã¿ããŒã¹ã§å埩ããã»ã¹ã®åå ãšãªã£ãåé¡ãçºçããŠãããããVIP ã®åé€ãæ£åžžã«å®äºãããã©ããã¯ããããŸããã ããã«ãããåãä»®æ³ IP ã¢ãã¬ã¹ãæ〠XNUMX ã€ã®ãµãŒããŒãåºçŸããåé¡ãçºçããå¯èœæ§ããããŸãã
split brain
.
é¿ããããã« split brain
ãã¡ãœããã䜿çšã§ããŸã
MySQL ãã§ã€ã«ãªãŒã㌠ã¯ã©ã¹ã¿ãŒãäœæããããã®ã¢ãããŒãã«ã€ããŠèª¬æããŸããã å®è£
ã¯ç°¡åã§ãçŸåšã®ç¶æ³ã§ã¯èš±å®¹å¯èœãªã¬ãã«ã®ä¿¡é Œæ§ãæäŸããŸãã äžè¬ã«ã·ã¹ãã å
šäœãç¹ã«ã€ã³ãã©ã¹ãã©ã¯ãã£ãçºå±ããã«ã€ããŠããã®ã¢ãããŒãã¯ééããªãé²åããŸãã
åºæïŒ habr.com