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

2012-04-19-mysql-casual-vol3.pdf

Akira Maeda
April 19, 2012
8.3k

 2012-04-19-mysql-casual-vol3.pdf

Akira Maeda

April 19, 2012
Tweet

Transcript

  1. ϨϓϦέʔγϣϯΤϥʔ͔ΒࣗಈͰ෮چ
    MySQL Casual Talks Vol.3 (2012-04-19)
    @glidenote
    12೥4݄19೔໦༵೔

    View Slide

  2. ࣗݾ঺հ
    • @glidenote(άϥΠυϊʔτ)
    • લా ষ / Akira Maeda
    • paperboy&co. ͷΠϯϑϥΤϯδχΞ
    • http://blog.glidenote.com/
    • ࣾ಺ҰͷλʔϛφϧڰɻԿͰ΋CUIɻ
    • MySQLͷϨϕϧ˒ˑˑˑˑˑˑˑˑˑ
    • ϖύϘMySQL࢛ఱԦͷதͰ࠷ऑ
    12೥4݄19೔໦༵೔

    View Slide

  3. ૣ଎ຊ୊ʹ
    12೥4݄19೔໦༵೔

    View Slide

  4. ͋Δ೔ͷग़དྷࣄ
    12೥4݄19೔໦༵೔

    View Slide

  5. εϨʔϒαʔόͷϨϓϦέʔγϣϯ͕Ұ੪ఀࢭ
    Master
    Slave 20୆ऑ

    12೥4݄19೔໦༵೔

    View Slide

  6. ෮چͷͨΊͷରԠͱௐࠪ1
    • ΤϥʔϩάͰάάͬͨΒͲ͏΍ΒMySQLͷό
    άʹىҼ͢Δࣄ৅
    • MySQLͷόʔδϣϯΞοϓͰ௚ΔΒ͍͠
    • SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
    ΛԿճ͔ൃߦ
    12೥4݄19೔໦༵೔

    View Slide

  7. ͱΓ͋͑ͣ෮چɻ
    12೥4݄19೔໦༵೔

    View Slide

  8. ͦͷ೔ͷ༦ํ
    12೥4݄19೔໦༵೔

    View Slide

  9. ·ͨεϨʔϒαʔόͷϨϓϦέʔγϣϯҰ੪ఀࢭ
    Master
    Slave 20୆ऑ

    12೥4݄19೔໦༵೔

    View Slide

  10. ෮چͷͨΊͷରԠͱௐࠪ2
    • ·ͨSET GLOBAL
    SQL_SLAVE_SKIP_COUNTER=1; ΛԿճ͔ൃߦ
    ͯ͠ͱΓ͋͑ͣશ୆෮چ
    • Ͱ΋ো֐ݕ஌͔Β෮چ·Ͱ࠷୹Ͱ10෼͘Β͍
    ͔͔Δ(εϨʔϒαʔό20୆ऑ)
    • ͜ͷରԠํ๏͸·͍ͣ͠ɺslave-skip-errors͸ӡ
    ༻ํ਑ͱͯ͠ར༻ͨ͘͠ͳ͍...
    12೥4݄19೔໦༵೔

    View Slide

  11. ͦ͜Ͱొ৔͢Δͷ͕
    12೥4݄19೔໦༵೔

    View Slide

  12. mk-slave-restart
    • ݴΘͣͱ஌Εͨmaatkitͷ1πʔϧ
    • ಛఆͷΤϥʔΛແݶεΩοϓͯ͠ϨϓϦ
    έʔγϣϯΛ࠶։ͯ͘͠ΕΔ
    • mk-slave-restart -uhoge -pxxxx --error-
    numbers 1100 --verbose
    12೥4݄19೔໦༵೔

    View Slide

  13. mk-slave-restartΛ࢖ͬͯ΋...
    • SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
    ͰͷରԠʹൺ΂Δͱᴴ͔ʹૣ͍
    • Ͱ΋֤αʔόͰ1୆ͣͭ֬ೝ͠ͳ͕Β࣮ߦͯ͠
    ͍ͨͷͰɺରԠ͸ૣ͘ͳ͚ͬͨͲखಈରԠ͸
    มΘΒͳ͍
    • mk-slave-restartͷ࣮ߦΛࣗಈԽ͍ͨ͠!!
    12೥4݄19೔໦༵೔

    View Slide

  14. ͦ͜Ͱొ৔͢Δͷ͕
    12೥4݄19೔໦༵೔

    View Slide

  15. • IBMۘ੡ϩά؂ࢹπʔϧ
    • PerlͰॻ͔Ε͍ͯΔ
    • εΫϦϓτຊମɺઃఆϑΝΠϧɺىಈεΫϦ
    ϓτͷ3ݸ͔Βߏ੒
    • ઃஔɺઃఆָ͕(swatchΑΓ΋ָͩͱࢥ͍·͢)
    logmon
    12೥4݄19೔໦༵೔

    View Slide

  16. ઃఆϑΝΠϧlogmon.confͷॻ͖ํ
    :/var/lib/mysql/err.log ←؂ࢹର৅ͷϩά
    (Error_code: 1100) ←؂ࢹର৅ͷจࣈྻ
    mk-slave-restart -uhoge --error-numbers 1100 --verbose -
    pxxxx >> /var/lib/mysql/skip_query.log ←จࣈྻΛൃݟͨ͠ͱ͖࣮ߦ
    ͢ΔίϚϯυ
    3ߦ͕جຊηοτ
    ࠓճͷઃఆྫ
    :؂ࢹର৅ͷϩά
    (؂ࢹର৅ͷจࣈྻ)
    จࣈྻΛൃݟͨ͠ͱ͖࣮ߦ͢ΔίϚϯυ(࣮ߦίϚϯυߦ͸ෳ਺ߦՄ)
    12೥4݄19೔໦༵೔

    View Slide

  17. logmonͰΤϥʔ؂ࢹ
    • ϨϓϦέʔγϣϯ͕ࢦఆΤϥʔͰࢭ·ͬͨΒɺmk-slave-
    restartࣗಈͰൃಈ
    • ࣮ӡ༻Ͱ͸mk-slave-restartͷ࣮ߦ͚ͩͰͳ͘ɺࣾ಺
    IRCɺϝʔϧͰͷ௨஌
    • logmon͕མͪͯͯಇ͔ͳ͔ͬͨ͜ͱ͕͋ΔͷͰࠓ͸
    SupervisordͰσʔϞϯ؅ཧ
    • ผ݅Ͱ͕͢RAIDίϯτϩʔϥ΍શจݕࡧͰར༻͍ͯ͠
    ͨTrittonͷIndex͕յΕΔෆ۩߹΋logmonͰ؂ࢹͯ͠·
    ͢
    12೥4݄19೔໦༵೔

    View Slide

  18. ·ͱΊ
    • ϨϓϦέʔγϣϯΤϥʔʹ͸mk-slave-
    restart
    • ϩά؂ࢹʹ͸logmon
    • mk-slave-restart + logmonͰϨϓϦέʔ
    γϣϯΤϥʔ͔Βࣗಈ෮چ
    12೥4݄19೔໦༵೔

    View Slide

  19. MySQLͷӡ༻͸ָΛ͠·͠ΐ͏
    12೥4݄19೔໦༵೔

    View Slide

  20. ͝੩ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠
    12೥4݄19೔໦༵೔

    View Slide