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

700669515ee872152d8b9403c2a0cf8c?s=47 kazeburo
January 28, 2020

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

MySQL Casual Talks vol.13

700669515ee872152d8b9403c2a0cf8c?s=128

kazeburo

January 28, 2020
Tweet

Transcript

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

  2. None
  3. None
  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ΑΓ௕͘ͳͬͯ͠·͏
  11. None
  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

    ͸͍͢͝ਓ
  31. None