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

MySQL Casual Talks vol.13

Transcript

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

   View Slide

2. View Slide

3. View Slide

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)
   

   View Slide

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

   View Slide

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
   

   View Slide

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

   View Slide

8. • check-mysql-repli-timeout
   

   View Slide

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

   View Slide

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ΑΓ௕͘ͳͬͯ͠·͏
    

    View Slide

11. View Slide

12. • check read_only
    

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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)
    

    View Slide

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

    View Slide

18. • check-mysql-gtid-state
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

25. • check-gtid-auto-succeed
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

29. • ·ͱΊ
    

    View Slide

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

    View Slide

31. View Slide