Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up for free
DB移行を支える技術
onigra
October 25, 2013
Technology
12
17k
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
ginza-ruby-kaigi-01
onigra
4
640
PHP-CS-FixerとかAtomとか
onigra
1
1.2k
プログラミング初心者でも始められるコミュニティへの参加と貢献
onigra
4
500
Techblog Deep Dive Meetup #1
onigra
0
1.5k
とある業務オペレーション自動化の話
onigra
0
730
about tsudura
onigra
0
170
最近やった仕事の話 #shibuyarb
onigra
1
2.1k
TDD(Twitter Driven Datsu-Syoshinsya) / Twitter駆動脱初心者 #yapcasia
onigra
16
3.5k
Other Decks in Technology
See All in Technology
What's new in Vision
satotakeshi
0
170
多様な成熟度のデータ活用を総合支援するKADOKAWA Connectedのデータ組織について
kadokawaconnected
PRO
0
190
HoloLens2とMetaQuest2どちらも動くWebXRアプリをBabylon.jsで作る
iwaken71
0
170
Build 2022で発表されたWindowsアプリ開発のあれこれ振り返ろう
hatsunea
1
370
JSAI 2022チュートリアル講演 AI哲学マップ / JSAI 2022 Tutorial "AI Philosophy Map"
ykiyota
0
390
スクラムのスケールとチームトポロジー / Scaled Scrum and Team Topologies
daiksy
1
400
ログ基盤をCloudWatchLogからNewRelic Logs + S3に変えたら 利便性も上がってコストも下がった話
onohiroshi1
0
170
現状のFedCMの動作解説と OIDCとの親和性について- OpenID TechNight vol.19
ritou
2
420
What's Data Lake ? Azure Data Lake best practice
ryomaru0825
2
730
Data in Google I/O - IO Extended GDG Seoul
kennethanceyer
0
150
Camp Digital 2022: tailored advice
kyliehavelock
0
140
FoodTechにおける商流・金流・物流の進化/Evolution of Commercial, Financial, and Logistics in FoodTech
dskst
0
380
Featured
See All Featured
Creatively Recalculating Your Daily Design Routine
revolveconf
207
10k
Statistics for Hackers
jakevdp
781
210k
How STYLIGHT went responsive
nonsquared
85
3.9k
It's Worth the Effort
3n
172
25k
CSS Pre-Processors: Stylus, Less & Sass
bermonpainter
349
27k
I Don’t Have Time: Getting Over the Fear to Launch Your Podcast
jcasabona
12
920
Put a Button on it: Removing Barriers to Going Fast.
kastner
56
2.3k
Reflections from 52 weeks, 52 projects
jeffersonlam
337
17k
What’s in a name? Adding method to the madness
productmarketing
11
1.6k
Embracing the Ebb and Flow
colly
73
3.4k
The Invisible Customer
myddelton
110
11k
WebSockets: Embracing the real-time Web
robhawkes
57
5.1k
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༵ۚ