ʮorchestratorͱGTIDӡ༻Λࢧ͑Δ؂ࢹʯͷษڧ MySQL Casual Talks vol.13 kazeburo ٯʹͳΓ·ͨ͠

No content

No content

• After 3 months • Applied orchestrator to JP and US infrastructure • Introduced ALL DB shards (cluster) • Developed Fast (less than 1 sec) master SwitchOver(Graceful TakeOver) using VIP • Experienced successful FailOver caused by manual operation error (me)

• After 3 months • Upgraded majority of DB shards to MySQL 5.7.x and enabled GTID

• How to upgrade MySQL 1. mysqldump from MySQL 5.6 backup server 2. Restore on Newer MySQL 5.7 and replicate from 5.6 master 3. Query replay test using custom go-sniffer 4. Do Fast master SwitchOver at mid night • After complete upgrading, Enable GTID on each servers

• orchestrator ʹΑΔ FailOver, SwitchOverΛ࣮֬ʹߦ͏ͨΊͷ؂ࢹ • GTIDӡ༻ͷͨΊͷ؂ࢹ • ichirin2501ࢯɾtaka-hࢯʹΑͬͯߏங͞Εͨ͜ΕΒΛษڧ͢Δͷ͕ຊεϥΠυͷ໨త • αʔό؂ࢹ͸MackerelΛར༻͍ͯ͠·͢

• check-mysql-repli-timeout

• timeoutॏཁ • orchestrator͸masterͷμ΢ϯݕ஌ʹ֤slave͔Βͷૄ௨Λར༻

• check-mysql-repli-timeout • MASTER_CONNECT_RETRY, MASTER_RETRY_COUNT, slave_net_timeout ͷ֬ೝ • MASTER_HEARTBEAT_PERIOD ͕ slave_net_timeout ͷ൒෼ • slave_net_timeout͚ͩԼ͛Δͱɺheartbeatͷִ͕ؒtimeoutΑΓ௕͘ͳͬͯ͠·͏

No content

• check read_only

• check read_only • ҙਤ͠ͳ͍ slave Ͱͷߋ৽ΫΤϦͷ࣮ߦʹΑΔෆ੔߹ͷൃੜΛ๷͙ • orchestrator ಋೖͰ read_only ͷӡ༻ʹมߋ • orchestratorͰͷmasterঢ֨࣌ʹ (super_)?read_only ͸ղআ͞ΕΔ • ҙਤ͠ͳ͍ master Ͱͷ read_only=on ͸ճආͰ͖Δ

• check read_only • slave ͸ read_only=on Ͱ͋Δ͜ͱΛ֬ೝ • master͸ read_only=off Ͱ͋Δ͜ͱΛ֬ೝ

$ /usr/bin/check-mysql readonly -h Usage: check-mysql readonly [OPTIONS] [ON|OFF] Application Options: -H, --host= Hostname (default: localhost) -p, --port= Port (default: 3306) -S, --socket= Path to unix socket -u, --user= Username (default: root) -P, --password= Password [$MYSQL_PASSWORD] Help Options: -h, --help Show this help message mackerel-plugin

/usr/bin/check-mysql readonly $(if /sbin/ip a | grep -qF $(dig +short {{ master }} | tail -n 1); then echo 'OFF'; else echo 'ON'; fi)

• check read_only • ىಈ࣌σϑΥϧτΛ read_only=on ΁มߋ • orchestrator͔Βͷҙਤ͠ͳ͍ Pseudo-GTID ͷૠೖΑΔ Errant GTID Λճආ

• check-mysql-gtid-state

• slave ʹ͔͠ͳ͍ transaction (Errant transaction) • read_only ӡ༻Ҏલ͸ slave Ͱߋ৽ΫΤϦ͕ҙਤ࣮ͤͣߦ͞Εͯ͠·͏Մೳੑ • Rolling Schema Upgrade • GTIDͰ͸ errant transaction (Errant GTID) ͕ଘࡏ͢Δ slave ͕ master ʹঢ֨ ͢Δ͜ͱͰෆ੔߹͕ൃੜ͢ΔϦεΫ

• check-mysql-gtid-state • slave Ͱ gtid_executed Λऔಘ • master Ͱ gtid_executed Λऔಘ • MSRͷ৔߹͸શͯͷmaster͔Βऔಘ • GTID_SUBSET(slave, master) Ͱൺֱ

• GTID_SUBSET(subset{slave},set{master}) • 2 ηοτͷάϩʔόϧτϥϯβΫγϣϯ ID subset ͱ set ͕ࢦఆ͞Εͨ৔߹ɺ subset ಺ͷ͢΂ͯͷ GTID ͕ set ʹ΋ଘࡏ͢Ε͹ true (1) Λฦ͠·͢ɻͦΕҎ֎ ͷ৔߹͸ɺfalse (0) Λฦ͠·͢ɻ

• How to fix Errant GTID • Insert Empty transaction • Remove from binlog

• How to fix Errant GTID (Remove from binlog) 1. [slave] STOP SLAVE ͠ɺ@@global. gtid_executed Λ֬ೝ 2. [master] SHOW MASTER STATUS ͠ɺslaveʹ͔͠ͳ͍GTIDηοτΛ֬ೝ 3. [slave] RESET SLAVE; RESET MASTERͯ͠ gtid_executed ΛΫϦΞ 4. [slave] SET GLOBAL gtid_purged = '1ͷGTIDηοτ͔Β2Ͱ֬ೝͨ͠ηοτΛ࡟আ' 5. [slave] CHANGE MASTER TO MASTER_AUTO_POSITION=1; START SLAVE

orchestrator (>= v3.0.14) https://www.percona.com/live/19/sites/default/files/slides/Errant%20GTIDs%20Breaking%20Replication_%20How%20to%20Detect%20and%20Avoid%20Them%20-%20FileId%20-%20187306.pdf

• check-gtid-auto-succeed

FailOver/SwitchOver ࣌ʹ master ͱͳΔαʔό͕ద੾ʹGTIDσʔληοτΛ౉ͤΔ͔ ʢը໘͸Πϝʔδ)

• check-gtid-auto-succeed • orchestrator ͷ DB ͔Β cluster ʹଐ͢Δ αʔόΛऔಘ • masterީิ*slaveީิ ͷ N * (N - 1) ύλʔϯΛ؂ࢹ

• GTID_SUBTRACT(masterީิͷgtid_purgedɺslaveީิͷgtid_executed) • slave ީิͷGTIDηοτΛ master ީิ͕͢΂͍ͯ࣋ͬͯΕ͹ empty => OK

• ·ͱΊ

• orchestrator εΰΠϕϯϦ • orchestrator ͱ GTID ͷ࣮֬ͳӡ༻ͷͨΊͷ؂ࢹΛண࣮ʹ࡞͍ͬͯΔ • ichirin2501 ͸͍͢͝ਓ

No content