$30 off During Our Annual Pro Sale. View Details »

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

kazeburo
January 28, 2020

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

MySQL Casual Talks vol.13

kazeburo

January 28, 2020
Tweet

More Decks by kazeburo

Other Decks in Technology

Transcript

  1. • 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)
  2. • 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
  3. • check-mysql-repli-timeout • MASTER_CONNECT_RETRY, MASTER_RETRY_COUNT, slave_net_timeout ͷ֬ೝ • MASTER_HEARTBEAT_PERIOD ͕

    slave_net_timeout ͷ൒෼ • slave_net_timeout͚ͩԼ͛Δͱɺheartbeatͷִ͕ؒtimeoutΑΓ௕͘ͳͬͯ͠·͏
  4. • check read_only • ҙਤ͠ͳ͍ slave Ͱͷߋ৽ΫΤϦͷ࣮ߦʹΑΔෆ੔߹ͷൃੜΛ๷͙ • orchestrator ಋೖͰ

    read_only ͷӡ༻ʹมߋ • orchestratorͰͷmasterঢ֨࣌ʹ (super_)?read_only ͸ղআ͞ΕΔ • ҙਤ͠ͳ͍ master Ͱͷ read_only=on ͸ճආͰ͖Δ
  5. $ /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
  6. /usr/bin/check-mysql readonly $(if /sbin/ip a | grep -qF $(dig +short

    {{ master }} | tail -n 1); then echo 'OFF'; else echo 'ON'; fi)
  7. • slave ʹ͔͠ͳ͍ transaction (Errant transaction) • read_only ӡ༻Ҏલ͸ slave

    Ͱߋ৽ΫΤϦ͕ҙਤ࣮ͤͣߦ͞Εͯ͠·͏Մೳੑ • Rolling Schema Upgrade • GTIDͰ͸ errant transaction (Errant GTID) ͕ଘࡏ͢Δ slave ͕ master ʹঢ֨ ͢Δ͜ͱͰෆ੔߹͕ൃੜ͢ΔϦεΫ
  8. • check-mysql-gtid-state • slave Ͱ gtid_executed Λऔಘ • master Ͱ

    gtid_executed Λऔಘ • MSRͷ৔߹͸શͯͷmaster͔Βऔಘ • GTID_SUBSET(slave, master) Ͱൺֱ
  9. • GTID_SUBSET(subset{slave},set{master}) • 2 ηοτͷάϩʔόϧτϥϯβΫγϣϯ ID subset ͱ set ͕ࢦఆ͞Εͨ৔߹ɺ

    subset ಺ͷ͢΂ͯͷ GTID ͕ set ʹ΋ଘࡏ͢Ε͹ true (1) Λฦ͠·͢ɻͦΕҎ֎ ͷ৔߹͸ɺfalse (0) Λฦ͠·͢ɻ
  10. • 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
  11. • check-gtid-auto-succeed • orchestrator ͷ DB ͔Β cluster ʹଐ͢Δ αʔόΛऔಘ

    • masterީิ*slaveީิ ͷ N * (N - 1) ύλʔϯΛ؂ࢹ