MHAの次 / Next to MHA

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

Transcript

1. ҆ఆੑͱ੒௕ੑͷཱ྆ͷͨΊʹ
   Ԟଜߊ߂(.01FQBCP *OD
   ٕज़෦5FDI.5(
   .)"ͷ࣍
   

   View Slide

2. !UBLVNBLVNF͞Μͷl࣍zͷ࿩Λ͠·͢
   IUUQTTQFBLFSEFDLDPNUBLVNBLVNFNIBEFEFUBCFTVGBMTFZVOZPOHXPMFOJTJZPV
   

   View Slide

3. .)"ͷ؆қઆ໌
   IUUQTTQFBLFSEFDLDPNUBLVNBLVNFNIBEFEFUBCFTVGBMTFZVOZPOHXPMFOJTJZPV
   

   View Slide

4. .)"ͷྑ͍ͱ͜ؾʹͳΔͱ͜
   ྑ͍ͱ͜
   • DBϚελͷSPoF͕ղܾ
   • ϑΣΠϧΦʔό༻εΫϦ
   ϓτΛ޷͖ʹॻ͚Δ
   • MyISAMͰ΋ಈ͘
   • ࠷৽൛͸GTID΋αϙʔ
   τ
   ؾʹͳΔͱ͜
   • ެࣜͰ͸rpmͷΈఏڙ
   • MySQL 5.7Ҏ߱ͷαϙʔ
   τ͕෼͔Βͳ͍
   • master/slave͸खಈߏங
   • mha4managerσʔϞϯ
   ͕SPoF
   

   View Slide

5. .)"ͷؾʹͳΔͱ͜
   ྑ͍ͱ͜
   • DBϚελͷSPoF͕ղܾ
   • ϑΣΠϧΦʔό༻εΫϦ
   ϓτΛ޷͖ʹॻ͚Δ
   • MyISAMͰ΋ಈ͘
   • ࠷৽൛͸GTID΋αϙʔ
   τ
   ؾʹͳΔͱ͜
   • ެࣜͰ͸rpmͷΈఏڙ
   • MySQL 5.7Ҏ߱ͷαϙʔ
   τ͕෼͔Βͳ͍
   • master/slave͸खಈߏங
   • mha4managerσʔϞϯ
   ͕SPoF
   ಛʹؾʹͳΔ
   

   View Slide

6. Ͳ͏ͯ͠ؾʹͳΔͷ͔
   • MySQL 5.7͕stableɻ։ൃ൛͸8.0
   • MySQL 8.0 + MHAͷಈ࡞͸ະ֬ೝͰɺࠓޙ΋α
   ϙʔτ͞ΕΔͷ͔෼͔Βͳ͍
   • ύοέʔδ͕rpmͷΈɺެࣜϦϙδτϦ͸ແ͍
   • ϖύϘ͸CentOSͰͣͬͱ΍ͬͯ͘ʁͱ͍͏ٙ໰
   

   View Slide

7. $FOU04͸ʮ଎ʯʁ
   • 30days AlbumͷappϩʔϧΛCentOS7ʹ͢Δͨ
   Ίɺ໿Ұϲ݄͔͚ͯffmpeg.spec͔Β࡞Γ্͛ͨ
   • ͱ͜Ζ͕ɺDebian͸apt-get install ffmpegͰεο
   ͱ͍͚Δ...
   • ੲ͔Βkernel΍ύοέʔδ͕ݹͯۤ͘࿑ͤ͞ΒΕ
   ͯΔ͚ͲɺNyahʹΑͬͯ։ൃ଎౓͕޲্͠ɺ
   CentOSͷʮ஗ʯͳͱ͜Ζ͕࿐ݟ͖ͯͨ͠ʁ
   

   View Slide

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
   

   View Slide

9. ʢ࿩Λ໭͠·͢ʣ.)"ͷؾʹͳΔͱ͜
   ྑ͍ͱ͜
   • DBϚελͷSPoF͕ղܾ
   • ϑΣΠϧΦʔό༻εΫϦ
   ϓτΛ޷͖ʹॻ͚Δ
   • MyISAMͰ΋ಈ͘
   • ࠷৽൛͸GTID΋αϙʔ
   τ
   ؾʹͳΔͱ͜
   • ެࣜͰ͸rpmͷΈఏڙ
   • MySQL 5.7Ҏ߱ͷαϙʔ
   τ͕෼͔Βͳ͍
   • master/slave͸खಈߏங
   • mha4managerσʔϞϯ
   ͕SPoF
   ಛʹؾʹͳΔ
   

   View Slide

10. .Z42-6UJMJUJFT
    • percona-toolkitΈ͍ͨͳίϚϯυϥΠϯπʔϧ
    ηοτ
    • Python੡ (2.6Ҏ߱Λαϙʔτɺ3.1ܥ͸·ͩ)
    • RedHat/Debian/macOS/FreeBSD౳Ͱύοέʔδ
    ೖखՄೳ
    • https://dev.mysql.com/doc/mysql-utilities/1.6/en/
    

    View Slide

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
    

    View Slide

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`;
    #
    

    View Slide

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
    

    View Slide

14. NZTRMGBJMPWFS
    • MHAΈ͍ͨͳౕɻ΄΅ಉ͜͡ͱ͕Ͱ͖Δ
    • GTIDϕʔε
    • MySQL 5.6.5͔Β࢖͑Δ
    • ͕ɺ5.7͔Βͷํ͕࢖͍΍͍͢

    ʢMySQL 5.7.6͔Βgtid_modeͷON/OFFΛΦϯ
    ϥΠϯͰ੾ସՄೳʹͳͬͨʣ
    

    View Slide

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
    

    View Slide

16. NZTRMGBJMPWFSͷσϞ
    

    View Slide

17. ͜Ε͔Β΍͍ͬͯ͘͜ͱʢ਺೥͔͔ΔݟࠐΈʣ
    w .)".Z42-Ͱɺ.Z42-΁ΞοϓάϨʔυΛؤுΔ
    w .Z42-͔Β(5*%ʹ੾Γସ͑Δ
    w .)"͔ΒNZTRMGBJMPWFSʹ੾Γସ͑Δ
    .Z42-Ϛελͷӡ༻Λ҆৺ɾ҆શʹ͍ͯͨ͘͠Ίʹ
    

    View Slide