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
80
devsumi-2024-summer
onigra
4
2.1k
第一種低層住居専用地域
onigra
0
250
jaws-ug-ecspresso-meetup-20230808
onigra
0
1.7k
ginza-ruby-kaigi-01
onigra
4
1k
PHP-CS-FixerとかAtomとか
onigra
1
1.3k
プログラミング初心者でも始められるコミュニティへの参加と貢献
onigra
4
760
Techblog Deep Dive Meetup #1
onigra
0
1.9k
とある業務オペレーション自動化の話
onigra
0
970
Other Decks in Technology
See All in Technology
Raycast AI APIを使ってちょっと便利な拡張機能を作ってみた / created-a-handy-extension-using-the-raycast-ai-api
kawamataryo
0
210
ESXi で仮想化した ARM 環境で LLM を動作させてみるぞ
unnowataru
0
170
設計を積み重ねてシステムを刷新する
sansantech
PRO
0
160
データエンジニアリング領域におけるDuckDBのユースケース
chanyou0311
9
2.1k
システム・ML活用を広げるdbtのデータモデリング / Expanding System & ML Use with dbt Modeling
i125
1
320
(機械学習システムでも) SLO から始める信頼性構築 - ゆる SRE#9 2025/02/21
daigo0927
0
260
サイト信頼性エンジニアリングとAmazon Web Services / SRE and AWS
ymotongpoo
7
1.4k
データベースの負荷を紐解く/untangle-the-database-load
emiki
2
500
日経のデータベース事業とElasticsearch
hinatades
PRO
0
230
PHPで印刷所に入稿できる名札データを作る / Generating Print-Ready Name Tag Data with PHP
tomzoh
0
180
ディスプレイ広告(Yahoo!広告・LINE広告)におけるバックエンド開発
lycorptech_jp
PRO
0
330
遷移の高速化 ヤフートップの試行錯誤
narirou
6
1.1k
Featured
See All Featured
Evolution of real-time – Irina Nazarova, EuRuKo, 2024
irinanazarova
6
570
[RailsConf 2023] Rails as a piece of cake
palkan
53
5.3k
Building Applications with DynamoDB
mza
93
6.2k
Done Done
chrislema
182
16k
Design and Strategy: How to Deal with People Who Don’t "Get" Design
morganepeng
129
19k
CoffeeScript is Beautiful & I Never Want to Write Plain JavaScript Again
sstephenson
160
15k
Mobile First: as difficult as doing things right
swwweet
223
9.4k
How to Ace a Technical Interview
jacobian
276
23k
The Illustrated Children's Guide to Kubernetes
chrisshort
48
49k
Measuring & Analyzing Core Web Vitals
bluesmoon
6
250
Typedesign – Prime Four
hannesfritz
40
2.5k
Improving Core Web Vitals using Speculation Rules API
sergeychernyshev
10
510
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༵ۚ