DB移行を支える技術

MySQL Casual Vol5 LT資料
http://www.zusaar.com/event/1086003

Transcript

1. MySQL΁ͷҠߦΛࢧ͑Δٕज़
   13೥10݄25೔༵ۚ೔
   

   View Slide

2. Yudai
   @nekogeruge_987
   WEBܥاۀۈ຿
   DB(RDBMS, NoSQL)
   Ruby
   Chef
   Fluentd
   https://github.com/onigra
   13೥10݄25೔༵ۚ೔
   

   View Slide

3. ࠓ೥ͷ݄̍ʹ
   IBM DB2͔Β
   MySQL5.5ʹ
   Ҡߦͨ͠࿩Λ͠·͢
   13೥10݄25೔༵ۚ೔
   

   View Slide

4. ن໛ײ
   13೥10݄25೔༵ۚ೔
   

   View Slide

5. all෺ཧ
   εΩʔϚ਺ 13
   ςʔϒϧ਺ 330
   ૯Ϩίʔυ਺ 321,906,470
   1ςʔϒϧ͋ͨΓͷϨίʔυ਺ 975,474
   Ұ൪Ϩίʔυ͕ଟ͍ςʔϒϧ 18,054,990
   13೥ӡ༻͞ΕͯΔ
   جװγεςϜɺαʔϏεܥDB܈
   JavaɺPHPɺVB6ͱ͔…
   euc-jp -> utf-8
   13೥10݄25೔༵ۚ೔
   

   View Slide

6. ݁Ռɺ΢ϯԯԁͷ
   ܦඅൃੜΛ્ࢭ
   13೥10݄25೔༵ۚ೔
   

   View Slide

7. ۩ମతͳ
   ࡞ۀ಺༰
   13೥10݄25೔༵ۚ೔
   

   View Slide

8. • DB2ͷϨίʔυΛCSVΤΫεϙʔτ
   • MySQL΁LOAD INFILE
   • /var/lib/mysql Λslaveͷαʔό΁Ҡͯ͠෮ݩʢColdBackupʣ
   • ϨϓϦέʔγϣϯઃఆ
   • FederatedΤϯδϯΛ࢖༻͢ΔಛघͳϨϓϦέʔγϣϯͷߏங
   (MySQL -> DB2 ͷϨϓϦέʔγϣϯΛ͢Δඞཁ͕͋ͬͨͨΊ)
   • ͜ΕΒΛShellScript(bash)ͷόονͰߦ͏
   13೥10݄25೔༵ۚ೔
   

   View Slide

9. Time Limit
   4࣌ؒ
   ※ΦϑϥΠϯϝϯς8࣌ؒͷ͏ͪ
   લ൒4࣌ؒͰ׬ྃ͢Δඞཁ͕͋Δ
   13೥10݄25೔༵ۚ೔
   

   View Slide

10. ରࡦ
    13೥10݄25೔༵ۚ೔
    

    View Slide

11. 1. LOAD࣌ؒͷ୹ॖΛਤΔ
    13೥10݄25೔༵ۚ೔
    

    View Slide

12. •Bulk Insert < LOAD INFILE < Cold Backup
    •Slow-logɺBin-log ͳͲࢭΊΕΔϩά͸ࢭΊΔ
    •INDEXΛDROP -> LOAD -> INDEXషΔ
    (ςʔϒϧʹΑΓૣ͔ͬͨΓɺషͬͨ··ͱ
    ͦΜͳʹ͕ࠩແ͔ͬͨΓ)
    • csvετϨʔδΤϯδϯΛ࢖͏ͱͪΐͬͺ΍Β͍͚͠Ͳ͏
    ·͍͔͘ͳ͔ͬͨʢ΍Γ͔ͨѱ͔ͬͨͷ͔΋ʣ
    13೥10݄25೔༵ۚ೔
    

    View Slide

13. ͜ͷล͸άάͬͨΒ
    ׂͱग़ͯ͘Δ͕ɺ
    શମͷ࡞ۀͰݟΔͱ
    େ͖ͳ୹ॖʹ͸
    ͳΒͳ͔ͬͨ
    13೥10݄25೔༵ۚ೔
    

    View Slide

14. 2.δϣϒϑϩʔͷ࠷దԽ
    ʢJenkinsͷ׆༻ʣ
    13೥10݄25೔༵ۚ೔
    

    View Slide

15. 13೥10݄25೔༵ۚ೔
    

    View Slide

16. Jenkins Build Flow Plugin
    https://wiki.jenkins-ci.org/display/JENKINS/Build+Flow+Plugin
    13೥10݄25೔༵ۚ೔
    

    View Slide

17. Build Pipeline Plugin͕ڧྗʹͳͬͨײ͡
    Job FlowΛGroovyͰॻ͚Δ
    ฒྻ࣮ߦ(Parallel)
    ϦτϥΠ(Retry)
    ྫ֎ͷัଊ(guard / rescue)
    ͳͲͷػೳΛඋ͍͑ͯΔ
    13೥10݄25೔༵ۚ೔
    

    View Slide

18. ࠷্Ґ
    parallel (
    { build(“order” ) },
    { build(“user”) },
    { build(“master”) }
    )
    user
    build (“export”)
    build (“rsync”)
    build (“load”)
    build (“mysqld stop”)
    build (“rsync”)
    parallel (
    { build(“master mysqld start”) },
    { build(“slave mysqld start”) }
    )
    13೥10݄25೔༵ۚ೔
    

    View Slide

19. ͪͳΈʹɺ͜ͷҊ݅ͷޙ
    Tivoliͱ͔JP1ͱ͔஌Γ·ͨ͠
    ·ͩۀքྺͰݴ͏ͱएखͳΜͰ…
    13೥10݄25೔༵ۚ೔
    

    View Slide

20. σʔλҠߦΛCI͢ΔΑ͏ͳײ͡Ͱ
    ຖ೔·Θ͢
    ࠷ऴతʹ1ΫϦοΫͰҠߦ͕׬ྃ͢ΔBuild FlowΛ࡞੒
    ↓
    ͦΕΛຖ೔࣮ߦ͠ɺԿճ΋੒ޭͤ͞Δ
    ↓
    Ҡߦຊ൪ʹର͢Δڪා৺Λແ͘͢
    13೥10݄25೔༵ۚ೔
    

    View Slide

21. ̍ΫϦοΫσʔλҠߦ
    ʙ͍ͭ·ͰखͰDBҠߦͯ͠ΔΜͰ͔͢ʁʙ
    ※ @ryuzee͞ΜͷΦϚʔδϡͰ͢
    http://www.slideshare.net/Ryuzee/devsumia
    13೥10݄25೔༵ۚ೔
    

    View Slide

22. ׬શࣗಈԽ΋Ͱ͖͔ͨ΋
    ۃ୺ͳ࿩ɺ࡞ۀऀ͕͍ͳ͍ʢԿ΋͠ͳ͍ʣ
    σʔλҠߦ΋Ͱ͖Δؾ͕͢Δ
    13೥10݄25೔༵ۚ೔
    

    View Slide

23. ༨ஊ
    δϣϒεέδϡʔϥʔͷΦʔϓϯιʔειϑτ΢ΣΞͬͯΠέͯΔͷແ͍ΜͰ͔͢Ͷʁ
    δϣϒεέδϡʔϥʔͬͯΤϯλʔϓϥΠζք۾Ͱॏๅ͞Εͯͦ͏͔ͩΒ
    WEBͷਓୡڵຯແ͍ʁ
    DWHͱ͔݁ߏ࢖͏ͱࢥ͏ΜͰ͚͢Ͳ
    jobschedulerͳΔάάϥϏϦςΟͷ௿͍Φʔϓϯιʔεͷιϑτ΢ΣΞ΋͚͋ͬͨͲɺ
    ৘ใগͳ͍
    13೥10݄25೔༵ۚ೔
    

    View Slide

24. ͜ΕͰ΋·ͩؒʹ߹Θͳ͍
    13೥10݄25೔༵ۚ೔
    

    View Slide

25. 3. σʔλసૹ࣌ؒͷ୹ॖ
    (Cold Backup)
    13೥10݄25೔༵ۚ೔
    

    View Slide

26. /var/lib/mysql ഑ԼͷશϑΝΠϧΛ
    ฒྻͰrsync͢Δ
    http://qiita.com/nekogeruge_987/
    items/ed1bd8704b25b1f509ec
    13೥10݄25೔༵ۚ೔
    

    View Slide

27. ஫ҙ఺
    σΟϨΫτϦߏ଄Λઌʹίϐʔઌʹίϐʔ͓͔ͯ͠ͳ͍ͱrsync͕͚͜Δ
    ωοτϫʔΫͷଳҬͷଠ͞ʹґଘ͍ͯ͠ΔͷͰɺଳҬ͕ڱ͍ͱ͋·ΓޮՌ͕ແ͍
    ϑΝΠϧ਺ͷϓϩηεΛىಈ͢ΔͨΊɺ100ສݸϑΝΠϧ͕͋Δͱ100ສϓϩηε͕ͨͪ͋Δ
    ׬ྃޙʹී௨ͷrsyncΛ࣮ߦ͠ɺࠩ෼͕ແ͍͔ΛνΣοΫ͢Δͱϕλʔ
    13೥10݄25೔༵ۚ೔
    

    View Slide

28. 1࣌ؒఔ͔͔ͬͯͨͷ͕
    20෼ఔͰ׬ྃ͢ΔΑ͏ʹ
    ͳΓ·ͨ͠
    13೥10݄25೔༵ۚ೔
    

    View Slide

29. ൪֎ɿࠓࢥ͏ͱ΍Γ͔ͨͬͨ͜ͱ
    ShellScripͷUnitTestΛॻ͘
    13೥10݄25೔༵ۚ೔
    

    View Slide

30. ౰࣌ShellScript΄΅ॻ͍ͨࣄແ͍
    ͍ͭͰʹςετ΋ॻ͍ͨࣄແ͍
    ࣄલͷҠߦͰ࢖ΘΕͯͨίʔυΛ࢖͍ճ͢
    ࣌ؒ΋ແ͔ͬͨͷͰແઅૢʹScript͕૿͍͑ͯ͘
    ࠓ͸shunit2ͱ͍͏ShellScriptͷTestingFrameworkͰςετॻ͍ͯΔ
    https://speakerdeck.com/yudaisuzuki/shell-script-testing-framework-shunit2
    13೥10݄25೔༵ۚ೔
    

    View Slide

31. Thanks!!
    https://twitter.com/nekogeruge_987 http://onigra.github.io/
    13೥10݄25೔༵ۚ೔
    

    View Slide