MHAの次 / Next to MHA

2017/05/17、GMOペパボで開催した技術部TechMTG #1（社内向け）の発表資料です。
資料中に登場するデモは https://github.com/hfm/sample-mysqlfailover を使って実施しました。

Transcript

1. ҆ఆੑͱ੒௕ੑͷཱ྆ͷͨΊʹ Ԟଜߊ߂
   
   (.0
   1FQBCP
   *OD
   
   ٕज़෦5FDI.5(
    .)"ͷ࣍
2. !UBLVNBLVNF
   ͞Μͷ
   l࣍z
   ͷ࿩Λ͠·͢ IUUQTTQFBLFSEFDLDPNUBLVNBLVNFNIBEFEFUBCFTVGBMTFZVOZPOHXPMFOJTJZPV
3. .)"ͷ؆қઆ໌ IUUQTTQFBLFSEFDLDPNUBLVNBLVNFNIBEFEFUBCFTVGBMTFZVOZPOHXPMFOJTJZPV
4. .)"ͷྑ͍ͱ͜ؾʹͳΔͱ͜ ྑ͍ͱ͜ • DBϚελͷSPoF͕ղܾ • ϑΣΠϧΦʔό༻εΫϦ ϓτΛ޷͖ʹॻ͚Δ • MyISAMͰ΋ಈ͘ • ࠷৽൛͸GTID΋αϙʔ τ ؾʹͳΔͱ͜ • ެࣜͰ͸rpmͷΈఏڙ • MySQL 5.7Ҏ߱ͷαϙʔ τ͕෼͔Βͳ͍ • master/slave͸खಈߏங • mha4managerσʔϞϯ ͕SPoF
5. .)"ͷؾʹͳΔͱ͜ ྑ͍ͱ͜ • DBϚελͷSPoF͕ղܾ • ϑΣΠϧΦʔό༻εΫϦ ϓτΛ޷͖ʹॻ͚Δ • MyISAMͰ΋ಈ͘ • ࠷৽൛͸GTID΋αϙʔ τ ؾʹͳΔͱ͜ • ެࣜͰ͸rpmͷΈఏڙ • MySQL 5.7Ҏ߱ͷαϙʔ τ͕෼͔Βͳ͍ • master/slave͸खಈߏங • mha4managerσʔϞϯ ͕SPoF ಛʹؾʹͳΔ
6. Ͳ͏ͯ͠ؾʹͳΔͷ͔ • MySQL 5.7͕stableɻ։ൃ൛͸8.0 • MySQL 8.0 + MHAͷಈ࡞͸ະ֬ೝͰɺࠓޙ΋α ϙʔτ͞ΕΔͷ͔෼͔Βͳ͍ • ύοέʔδ͕rpmͷΈɺެࣜϦϙδτϦ͸ແ͍ • ϖύϘ͸CentOSͰͣͬͱ΍ͬͯ͘ʁͱ͍͏ٙ໰
7. $FOU04͸ʮ଎ʯʁ • 30days AlbumͷappϩʔϧΛCentOS7ʹ͢Δͨ Ίɺ໿Ұϲ݄͔͚ͯffmpeg.spec͔Β࡞Γ্͛ͨ • ͱ͜Ζ͕ɺDebian͸apt-get install ffmpegͰεο ͱ͍͚Δ... • ੲ͔Βkernel΍ύοέʔδ͕ݹͯۤ͘࿑ͤ͞ΒΕ ͯΔ͚ͲɺNyahʹΑͬͯ։ൃ଎౓͕޲্͠ɺ CentOSͷʮ஗ʯͳͱ͜Ζ͕࿐ݟ͖ͯͨ͠ʁ
8. ࢀߟʢ
   ࣌఺ʣ IUUQTFOXJLJQFEJBPSHXJLJ$PNQBSJTPO@PG@-JOVY@EJTUSJCVUJPOT1BDLBHF@NBOBHFNFOU@BOE@JOTUBMMBUJPO %JTUSP "QQSPYJNBUF
   OVNCFS
   PG
   QSFDPNQJMFE
   QBDLBHFT 1LH
   GPSNBU 6CVOUV   EFC %FCJBO   EFC 'FEPSB   31. $FOU04   31. "SDI
   -JOVY   QLHUBSY[ "MQJOF
   -JOVY   BQL
9. ʢ࿩Λ໭͠·͢ʣ.)"ͷؾʹͳΔͱ͜ ྑ͍ͱ͜ • DBϚελͷSPoF͕ղܾ • ϑΣΠϧΦʔό༻εΫϦ ϓτΛ޷͖ʹॻ͚Δ • MyISAMͰ΋ಈ͘ • ࠷৽൛͸GTID΋αϙʔ τ ؾʹͳΔͱ͜ • ެࣜͰ͸rpmͷΈఏڙ • MySQL 5.7Ҏ߱ͷαϙʔ τ͕෼͔Βͳ͍ • master/slave͸खಈߏங • mha4managerσʔϞϯ ͕SPoF ಛʹؾʹͳΔ
10. .Z42-6UJMJUJFT • percona-toolkitΈ͍ͨͳίϚϯυϥΠϯπʔϧ ηοτ • Python੡ (2.6Ҏ߱Λαϙʔτɺ3.1ܥ͸·ͩ) • RedHat/Debian/macOS/FreeBSD౳Ͱύοέʔδ ೖखՄೳ • https://dev.mysql.com/doc/mysql-utilities/1.6/en/
11. .Z42-6UJMJUJFTͷπʔϧηοτ • mysqlauditadmin • mysqlauditgrep • mysqlbinlogmove • mysqlbinlogpurge • mysqlbinlogrotate • mysqldbcompare • mysqldbcopy • mysqldbexport • mysqldbimport • mysqldiff • mysqldiskusage • mysqlfailover • mysqlfrm • mysqlgrants • mysqlindexcheck • mysqlmetagrep • mysqlprocgrep • mysqlreplicate • mysqlrpladmin • mysqlrplcheck • mysqlrplms • mysqlrplshow • mysqlrplsync • mysqlserverclone • mysqlserverinfo • mysqlslavetrx • mysqluc • mysqluserclone
12. ʢԣಓʣศརͳNZTRMJOEFYDIFDL $ mysqlindexcheck --server user@host -d db # Source on db003.goope.lan: ... connected. # The following index for table db.a_table contains the clustered index and might be redundant: # CREATE UNIQUE INDEX `a_index` ON `db`.`a_table` (`a_field`) USING BTREE # # DROP/ADD statement: # ALTER TABLE `db`.`a_table` DROP INDEX `a_index`; #
13. .Z42-6UJMJUJFTͷπʔϧηοτ • mysqlauditadmin • mysqlauditgrep • mysqlbinlogmove • mysqlbinlogpurge • mysqlbinlogrotate • mysqldbcompare • mysqldbcopy • mysqldbexport • mysqldbimport • mysqldiff • mysqldiskusage • mysqlfailover • mysqlfrm • mysqlgrants • mysqlindexcheck • mysqlmetagrep • mysqlprocgrep • mysqlreplicate • mysqlrpladmin • mysqlrplcheck • mysqlrplms • mysqlrplshow • mysqlrplsync • mysqlserverclone • mysqlserverinfo • mysqlslavetrx • mysqluc • mysqluserclone
14. NZTRMGBJMPWFS • MHAΈ͍ͨͳౕɻ΄΅ಉ͜͡ͱ͕Ͱ͖Δ • GTIDϕʔε • MySQL 5.6.5͔Β࢖͑Δ • ͕ɺ5.7͔Βͷํ͕࢖͍΍͍͢
 ʢMySQL 5.7.6͔Βgtid_modeͷON/OFFΛΦϯ ϥΠϯͰ੾ସՄೳʹͳͬͨʣ
15. NZTRMGBJMPWFS༻ͷ࠷খNZDOG [mysqld] server_id = 1 log_bin = mysqld-bin log_slave_updates = 1 relay_log = relay-mysqld-bin master_info_repository = TABLE # gtid gtid_mode = ON enforce_gtid_consistency
16. NZTRMGBJMPWFSͷσϞ
17. ͜Ε͔Β΍͍ͬͯ͘͜ͱʢ਺೥͔͔ΔݟࠐΈʣ w .)" .Z42-Ͱɺ.Z42-
    ΁ΞοϓάϨʔυΛؤுΔ
    w .Z42-
    ͔Β(5*%ʹ੾Γସ͑Δ
    w .)"͔ΒNZTRMGBJMPWFSʹ੾Γସ͑Δ .Z42-Ϛελͷӡ༻Λ҆৺ɾ҆શʹ͍ͯͨ͘͠Ίʹ