Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MHAの次 / Next to MHA

MHAの次 / Next to MHA

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

47e8318347fb8cd6a2c39bc6cf6e333e?s=128

Okumura Takahiro

May 18, 2017
Tweet

More Decks by Okumura Takahiro

Other Decks in Technology

Transcript

  1. ҆ఆੑͱ੒௕ੑͷཱ྆ͷͨΊʹ Ԟଜߊ߂(.01FQBCP *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 "QQSPYJNBUFOVNCFSPG QSFDPNQJMFEQBDLBHFT 1LHGPSNBU 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-Ϛελͷӡ༻Λ҆৺ɾ҆શʹ͍ͯͨ͘͠Ίʹ