Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
DB移行を支える技術
Search
onigra
October 25, 2013
Technology
12
18k
DB移行を支える技術
MySQL Casual Vol5 LT資料
http://www.zusaar.com/event/1086003
onigra
October 25, 2013
Tweet
Share
More Decks by onigra
See All by onigra
THE GOAL
onigra
3
110
devsumi-2024-summer
onigra
4
2.4k
第一種低層住居専用地域
onigra
0
270
jaws-ug-ecspresso-meetup-20230808
onigra
0
1.8k
ginza-ruby-kaigi-01
onigra
4
1k
PHP-CS-FixerとかAtomとか
onigra
1
1.4k
プログラミング初心者でも始められるコミュニティへの参加と貢献
onigra
4
780
Techblog Deep Dive Meetup #1
onigra
0
1.9k
とある業務オペレーション自動化の話
onigra
0
990
Other Decks in Technology
See All in Technology
Beyond Kaniko: Navigating Unprivileged Container Image Creation
f30
0
120
KubeCon + CloudNativeCon Japan 2025 Recap Opening & Choose Your Own Adventureシリーズまとめ
mmmatsuda
0
250
PHPでWebブラウザのレンダリングエンジンを実装する
dip_tech
PRO
0
220
あなたの声を届けよう! 女性エンジニア登壇の意義とアウトプット実践ガイド #wttjp / Call for Your Voice
kondoyuko
4
530
生成AIで小説を書くためにプロンプトの制約や原則について学ぶ / prompt-engineering-for-ai-fiction
nwiizo
6
4k
タイミーのデータモデリング事例と今後のチャレンジ
ttccddtoki
6
2.2k
OPENLOGI Company Profile
hr01
0
67k
OpenHands🤲にContributeしてみた
kotauchisunsun
1
510
CI/CD/IaC 久々に0から環境を作ったらこうなりました
kaz29
1
220
Glacierだからってコストあきらめてない? / JAWS Meet Glacier Cost
taishin
1
120
How Community Opened Global Doors
hiroramos4
PRO
1
140
React開発にStorybookとCopilotを導入して、爆速でUIを編集・確認する方法
yu_kod
1
120
Featured
See All Featured
Git: the NoSQL Database
bkeepers
PRO
430
65k
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
125
52k
Become a Pro
speakerdeck
PRO
28
5.4k
Building a Modern Day E-commerce SEO Strategy
aleyda
42
7.4k
The Pragmatic Product Professional
lauravandoore
35
6.7k
The World Runs on Bad Software
bkeepers
PRO
69
11k
Creating an realtime collaboration tool: Agile Flush - .NET Oxford
marcduiker
30
2.1k
ReactJS: Keep Simple. Everything can be a component!
pedronauck
667
120k
Principles of Awesome APIs and How to Build Them.
keavy
126
17k
Measuring & Analyzing Core Web Vitals
bluesmoon
7
500
Large-scale JavaScript Application Architecture
addyosmani
512
110k
VelocityConf: Rendering Performance Case Studies
addyosmani
332
24k
Transcript
MySQLͷҠߦΛࢧ͑Δٕज़ 1310݄25༵ۚ
Yudai @nekogeruge_987 WEBܥاۀۈ DB(RDBMS, NoSQL) Ruby Chef Fluentd https://github.com/onigra 1310݄25༵ۚ
ࠓͷ݄̍ʹ IBM DB2͔Β MySQL5.5ʹ Ҡߦͨ͠Λ͠·͢ 1310݄25༵ۚ
نײ 1310݄25༵ۚ
allཧ εΩʔϚ 13 ςʔϒϧ 330 ૯Ϩίʔυ 321,906,470 1ςʔϒϧ͋ͨΓͷϨίʔυ 975,474 Ұ൪Ϩίʔυ͕ଟ͍ςʔϒϧ
18,054,990 13ӡ༻͞ΕͯΔ جװγεςϜɺαʔϏεܥDB܈ JavaɺPHPɺVB6ͱ͔… euc-jp -> utf-8 1310݄25༵ۚ
݁Ռɺϯԯԁͷ ܦඅൃੜΛ્ࢭ 1310݄25༵ۚ
۩ମతͳ ࡞ۀ༰ 1310݄25༵ۚ
• DB2ͷϨίʔυΛCSVΤΫεϙʔτ • MySQLLOAD INFILE • /var/lib/mysql ΛslaveͷαʔόҠͯ͠෮ݩʢColdBackupʣ • ϨϓϦέʔγϣϯઃఆ
• FederatedΤϯδϯΛ༻͢ΔಛघͳϨϓϦέʔγϣϯͷߏங (MySQL -> DB2 ͷϨϓϦέʔγϣϯΛ͢Δඞཁ͕͋ͬͨͨΊ) • ͜ΕΒΛShellScript(bash)ͷόονͰߦ͏ 1310݄25༵ۚ
Time Limit 4࣌ؒ ※ΦϑϥΠϯϝϯς8࣌ؒͷ͏ͪ લ4࣌ؒͰྃ͢Δඞཁ͕͋Δ 1310݄25༵ۚ
ରࡦ 1310݄25༵ۚ
1. LOAD࣌ؒͷॖΛਤΔ 1310݄25༵ۚ
•Bulk Insert < LOAD INFILE < Cold Backup •Slow-logɺBin-log ͳͲࢭΊΕΔϩάࢭΊΔ
•INDEXΛDROP -> LOAD -> INDEXషΔ (ςʔϒϧʹΑΓૣ͔ͬͨΓɺషͬͨ··ͱ ͦΜͳʹ͕ࠩແ͔ͬͨΓ) • csvετϨʔδΤϯδϯΛ͏ͱͪΐͬͺΒ͍͚͠Ͳ͏ ·͍͔͘ͳ͔ͬͨʢΓ͔ͨѱ͔ͬͨͷ͔ʣ 1310݄25༵ۚ
͜ͷลάάͬͨΒ ׂͱग़ͯ͘Δ͕ɺ શମͷ࡞ۀͰݟΔͱ େ͖ͳॖʹ ͳΒͳ͔ͬͨ 1310݄25༵ۚ
2.δϣϒϑϩʔͷ࠷దԽ ʢJenkinsͷ׆༻ʣ 1310݄25༵ۚ
1310݄25༵ۚ
Jenkins Build Flow Plugin https://wiki.jenkins-ci.org/display/JENKINS/Build+Flow+Plugin 1310݄25༵ۚ
Build Pipeline Plugin͕ڧྗʹͳͬͨײ͡ Job FlowΛGroovyͰॻ͚Δ ฒྻ࣮ߦ(Parallel) ϦτϥΠ(Retry) ྫ֎ͷัଊ(guard / rescue)
ͳͲͷػೳΛඋ͍͑ͯΔ 1310݄25༵ۚ
࠷্Ґ 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”) } ) 1310݄25༵ۚ
ͪͳΈʹɺ͜ͷҊ݅ͷޙ Tivoliͱ͔JP1ͱ͔Γ·ͨ͠ ·ͩۀքྺͰݴ͏ͱएखͳΜͰ… 1310݄25༵ۚ
σʔλҠߦΛCI͢ΔΑ͏ͳײ͡Ͱ ຖ·Θ͢ ࠷ऴతʹ1ΫϦοΫͰҠߦ͕ྃ͢ΔBuild FlowΛ࡞ ↓ ͦΕΛຖ࣮ߦ͠ɺԿճޭͤ͞Δ ↓ Ҡߦຊ൪ʹର͢Δڪා৺Λແ͘͢ 1310݄25༵ۚ
̍ΫϦοΫσʔλҠߦ ʙ͍ͭ·ͰखͰDBҠߦͯ͠ΔΜͰ͔͢ʁʙ ※ @ryuzee͞ΜͷΦϚʔδϡͰ͢ http://www.slideshare.net/Ryuzee/devsumia 1310݄25༵ۚ
શࣗಈԽͰ͖͔ͨ ۃͳɺ࡞ۀऀ͕͍ͳ͍ʢԿ͠ͳ͍ʣ σʔλҠߦͰ͖Δؾ͕͢Δ 1310݄25༵ۚ
༨ஊ δϣϒεέδϡʔϥʔͷΦʔϓϯιʔειϑτΣΞͬͯΠέͯΔͷແ͍ΜͰ͔͢Ͷʁ δϣϒεέδϡʔϥʔͬͯΤϯλʔϓϥΠζք۾Ͱॏๅ͞Εͯͦ͏͔ͩΒ WEBͷਓୡڵຯແ͍ʁ DWHͱ͔݁ߏ͏ͱࢥ͏ΜͰ͚͢Ͳ jobschedulerͳΔάάϥϏϦςΟͷ͍ΦʔϓϯιʔεͷιϑτΣΞ͚͋ͬͨͲɺ ใগͳ͍ 1310݄25༵ۚ
͜ΕͰ·ͩؒʹ߹Θͳ͍ 1310݄25༵ۚ
3. σʔλసૹ࣌ؒͷॖ (Cold Backup) 1310݄25༵ۚ
/var/lib/mysql ԼͷશϑΝΠϧΛ ฒྻͰrsync͢Δ http://qiita.com/nekogeruge_987/ items/ed1bd8704b25b1f509ec 1310݄25༵ۚ
ҙ σΟϨΫτϦߏΛઌʹίϐʔઌʹίϐʔ͓͔ͯ͠ͳ͍ͱrsync͕͚͜Δ ωοτϫʔΫͷଳҬͷଠ͞ʹґଘ͍ͯ͠ΔͷͰɺଳҬ͕ڱ͍ͱ͋·ΓޮՌ͕ແ͍ ϑΝΠϧͷϓϩηεΛىಈ͢ΔͨΊɺ100ສݸϑΝΠϧ͕͋Δͱ100ສϓϩηε͕ͨͪ͋Δ ྃޙʹී௨ͷrsyncΛ࣮ߦ͠ɺ͕ࠩແ͍͔ΛνΣοΫ͢Δͱϕλʔ 1310݄25༵ۚ
1࣌ؒఔ͔͔ͬͯͨͷ͕ 20ఔͰྃ͢ΔΑ͏ʹ ͳΓ·ͨ͠ 1310݄25༵ۚ
൪֎ɿࠓࢥ͏ͱΓ͔ͨͬͨ͜ͱ ShellScripͷUnitTestΛॻ͘ 1310݄25༵ۚ
࣌ShellScript΄΅ॻ͍ͨࣄແ͍ ͍ͭͰʹςετॻ͍ͨࣄແ͍ ࣄલͷҠߦͰΘΕͯͨίʔυΛ͍ճ͢ ࣌ؒແ͔ͬͨͷͰແઅૢʹScript͕૿͍͑ͯ͘ ࠓshunit2ͱ͍͏ShellScriptͷTestingFrameworkͰςετॻ͍ͯΔ https://speakerdeck.com/yudaisuzuki/shell-script-testing-framework-shunit2 1310݄25༵ۚ
Thanks!! https://twitter.com/nekogeruge_987 http://onigra.github.io/ 1310݄25༵ۚ