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

MHAの次 / Next to MHA

MHAの次 / Next to MHA

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

Okumura Takahiro

May 18, 2017
Tweet

More Decks by Okumura Takahiro

Other Decks in Technology

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