Upgrade to Pro — share decks privately, control downloads, hide ads and more …

DB移行を支える技術

onigra
October 25, 2013

 DB移行を支える技術

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

onigra

October 25, 2013
Tweet

More Decks by onigra

Other Decks in Technology

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