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. all෺ཧ εΩʔϚ਺ 13 ςʔϒϧ਺ 330 ૯Ϩίʔυ਺ 321,906,470 1ςʔϒϧ͋ͨΓͷϨίʔυ਺ 975,474 Ұ൪Ϩίʔυ͕ଟ͍ςʔϒϧ

    18,054,990 13೥ӡ༻͞ΕͯΔ جװγεςϜɺαʔϏεܥDB܈ JavaɺPHPɺVB6ͱ͔… euc-jp -> utf-8 13೥10݄25೔༵ۚ೔
  2. • DB2ͷϨίʔυΛCSVΤΫεϙʔτ • MySQL΁LOAD INFILE • /var/lib/mysql Λslaveͷαʔό΁Ҡͯ͠෮ݩʢColdBackupʣ • ϨϓϦέʔγϣϯઃఆ

    • FederatedΤϯδϯΛ࢖༻͢ΔಛघͳϨϓϦέʔγϣϯͷߏங (MySQL -> DB2 ͷϨϓϦέʔγϣϯΛ͢Δඞཁ͕͋ͬͨͨΊ) • ͜ΕΒΛShellScript(bash)ͷόονͰߦ͏ 13೥10݄25೔༵ۚ೔
  3. •Bulk Insert < LOAD INFILE < Cold Backup •Slow-logɺBin-log ͳͲࢭΊΕΔϩά͸ࢭΊΔ

    •INDEXΛDROP -> LOAD -> INDEXషΔ (ςʔϒϧʹΑΓૣ͔ͬͨΓɺషͬͨ··ͱ ͦΜͳʹ͕ࠩແ͔ͬͨΓ) • csvετϨʔδΤϯδϯΛ࢖͏ͱͪΐͬͺ΍Β͍͚͠Ͳ͏ ·͍͔͘ͳ͔ͬͨʢ΍Γ͔ͨѱ͔ͬͨͷ͔΋ʣ 13೥10݄25೔༵ۚ೔
  4. ࠷্Ґ 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೔༵ۚ೔