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

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