「orchestratorとGTID運用を支える監視」の勉強 / Monitoring orchestrator and GTID operation

MySQL Casual Talks vol.13

Transcript

1. ʮorchestratorͱGTIDӡ༻Λࢧ͑Δ؂ࢹʯͷษڧ MySQL Casual Talks vol.13 kazeburo ٯʹͳΓ·ͨ͠
4. • 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)
5. • After 3 months • Upgraded majority of DB shards to MySQL 5.7.x and enabled GTID
6. • 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
7. • orchestrator ʹΑΔ FailOver, SwitchOverΛ࣮֬ʹߦ͏ͨΊͷ؂ࢹ • GTIDӡ༻ͷͨΊͷ؂ࢹ • ichirin2501ࢯɾtaka-hࢯʹΑͬͯߏங͞Εͨ͜ΕΒΛษڧ͢Δͷ͕ຊεϥΠυͷ໨త • αʔό؂ࢹ͸MackerelΛར༻͍ͯ͠·͢
8. • check-mysql-repli-timeout
9. • timeoutॏཁ • orchestrator͸masterͷμ΢ϯݕ஌ʹ֤slave͔Βͷૄ௨Λར༻
10. • check-mysql-repli-timeout • MASTER_CONNECT_RETRY, MASTER_RETRY_COUNT, slave_net_timeout ͷ֬ೝ • MASTER_HEARTBEAT_PERIOD ͕ slave_net_timeout ͷ൒෼ • slave_net_timeout͚ͩԼ͛Δͱɺheartbeatͷִ͕ؒtimeoutΑΓ௕͘ͳͬͯ͠·͏
12. • check read_only
13. • check read_only • ҙਤ͠ͳ͍ slave Ͱͷߋ৽ΫΤϦͷ࣮ߦʹΑΔෆ੔߹ͷൃੜΛ๷͙ • orchestrator ಋೖͰ read_only ͷӡ༻ʹมߋ • orchestratorͰͷmasterঢ֨࣌ʹ (super_)?read_only ͸ղআ͞ΕΔ • ҙਤ͠ͳ͍ master Ͱͷ read_only=on ͸ճආͰ͖Δ
14. • check read_only • slave ͸ read_only=on Ͱ͋Δ͜ͱΛ֬ೝ • master͸ read_only=off Ͱ͋Δ͜ͱΛ֬ೝ
15. $ /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
16. /usr/bin/check-mysql readonly $(if /sbin/ip a | grep -qF $(dig +short {{ master }} | tail -n 1); then echo 'OFF'; else echo 'ON'; fi)
17. • check read_only • ىಈ࣌σϑΥϧτΛ read_only=on ΁มߋ • orchestrator͔Βͷҙਤ͠ͳ͍ Pseudo-GTID ͷૠೖΑΔ Errant GTID Λճආ
18. • check-mysql-gtid-state
19. • slave ʹ͔͠ͳ͍ transaction (Errant transaction) • read_only ӡ༻Ҏલ͸ slave Ͱߋ৽ΫΤϦ͕ҙਤ࣮ͤͣߦ͞Εͯ͠·͏Մೳੑ • Rolling Schema Upgrade • GTIDͰ͸ errant transaction (Errant GTID) ͕ଘࡏ͢Δ slave ͕ master ʹঢ֨ ͢Δ͜ͱͰෆ੔߹͕ൃੜ͢ΔϦεΫ
20. • check-mysql-gtid-state • slave Ͱ gtid_executed Λऔಘ • master Ͱ gtid_executed Λऔಘ • MSRͷ৔߹͸શͯͷmaster͔Βऔಘ • GTID_SUBSET(slave, master) Ͱൺֱ
21. • GTID_SUBSET(subset{slave},set{master}) • 2 ηοτͷάϩʔόϧτϥϯβΫγϣϯ ID subset ͱ set ͕ࢦఆ͞Εͨ৔߹ɺ subset ಺ͷ͢΂ͯͷ GTID ͕ set ʹ΋ଘࡏ͢Ε͹ true (1) Λฦ͠·͢ɻͦΕҎ֎ ͷ৔߹͸ɺfalse (0) Λฦ͠·͢ɻ
22. • How to fix Errant GTID • Insert Empty transaction • Remove from binlog
23. • 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
24. 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
25. • check-gtid-auto-succeed
26. FailOver/SwitchOver ࣌ʹ master ͱͳΔαʔό͕ద੾ʹGTIDσʔληοτΛ౉ͤΔ͔ ʢը໘͸Πϝʔδ)
27. • check-gtid-auto-succeed • orchestrator ͷ DB ͔Β cluster ʹଐ͢Δ αʔόΛऔಘ • masterީิ*slaveީิ ͷ N * (N - 1) ύλʔϯΛ؂ࢹ
28. • GTID_SUBTRACT(masterީิͷgtid_purgedɺslaveީิͷgtid_executed) • slave ީิͷGTIDηοτΛ master ީิ͕͢΂͍ͯ࣋ͬͯΕ͹ empty => OK
29. • ·ͱΊ
30. • orchestrator εΰΠϕϯϦ • orchestrator ͱ GTID ͷ࣮֬ͳӡ༻ͷͨΊͷ؂ࢹΛண࣮ʹ࡞͍ͬͯΔ • ichirin2501 ͸͍͢͝ਓ