Plain db import with Go

golang.tokyo#2での発表資料です。

Transcript

1. golang.tokyo #2 @__timakin__ Plain db import with Go
2. ࣗݾ঺հ • timakinͱ͍͍·͢ɻ • twitter: @__timakin__
 github: timakin • ࠷ۙboot.fmͱ͍͏
 ϙουΩϟετΛ࢝Ί·ͨ͠ɻ
3. timakin/gopli https://github.com/timakin/gopli
4. timakin/gopli • Plain DB import tool with toml configuration. • Without complex replication settings. 
 $ gopli sync -f production -t local -c config/repli.toml
5. ͳͥ࡞͔ͬͨ • http://techlife.cookpad.com/entry/2014/10/03/110806
6. ͳͥ࡞͔ͬͨ • ຊ൪ͷσʔλΛ؆୯ʹϩʔΧϧʹ͖࣋ͬͯͨ͘ ͳΓ·͢ΑͶʁʢεςʔδϯάʹϨϓϦͯ͘͠ Δͷ͸ɺผ్͋Δͱͯ͠ʣ • Կ౓ʮname: testʯͳϢʔβʔɺʮcontent: aaaaaaaaaaaaaaaaʯͳ౤ߘ಺༰࡞͔ͬͨʁ • seedσʔλ࡞Δͷਏ͘ͳ͍Ͱ͔͢ʁ
7. طଘͷํ๏ • ͔͍͋ͬͨshell • mysqlpump • maxbube/mydumper • mysqldump͕γϯάϧεϨουͳͷʹରͯ͠ɺ
 ্ه̎ͭ͸ϚϧνεϨου • উखʹઃఆܾΊͱ͍ͯ೚ҙͷλΠϛϯάʹ
 ίϚϯυΛࢥ͍ग़ͣ͞ʹར༻ग़དྷΔ΋ͷ͕ͳ͍…
8. ࡶͳσʔλίϐʔͷϝϦοτ • ࡶͳೖྗσʔλͰͷςετ͸࠷దͳݟͨ໨ɺUXͷअຐΛ͠·͢Ͷʁ • ݟͨ໨ɿϨΠΞ΢τͷͨΊʹtruncate͢Δจࣈྔௐ੔ɺ͕ͬͭΓͣ ΕΔը૾αΠζ • ։ൃ໘ɿόάͬͨ࣌ʹຊ൪ΛߥΒͣ͞ϩʔΧϧͰ࠶ݱͰ͖Δ֬཰Λ ্͛ΒΕΔɺϩʔΧϧ։ൃ࣌ͱຊ൪orεςʔδϯάͰ଎౓վળʢਏ ͍ΫΤϦൃݟʣΛΪϟοϓͳ͘ߦ͑Δɻ • ࣌ؒɿʢʮϒϥϯνAΛςετ͍ͨ͠ͷͰϒϥϯνBͷεςʔδϯά ΁ͷσϓϩΠ͸15࣌͘Β͍͔Β͓ئ͍͠·͢ʯ͕ͳ͍ʣ
9. σʔλͷͱ͖ͬͯํ • ςʔϒϧҰཡͷऔಘ • goroutineΛར༻ͭͭ͠ɺςʔϒϧ͝ͱʹλϒ۠੾ΓͷSELECT݁ՌΛ tmpϑΝΠϧͱͯ͠txtʹอଘ • schema_migrations, repli_chk౳ͷςʔϒϧ͸ϒϥοΫϦετͱͯ͠আ֎ • ஫ೖઌͷDBͷςʔϒϧΛҰ୴DELETE͢Δ • LOAD DATA LOCAL INFILEͰσʔλΛϦετΞɻ͜ͷࡍʹ΋֤ςʔϒϧ ʹಉ࣌ʹσʔλΛೖΕΔͨΊʹgoroutineΛར༻
10. toml configuration • TOML࠾༻͠·ͨ͠ɻ
 ؀ڥ͝ͱͷ઀ଓઃఆ౳ɻ [database] [database.local] host = "localhost" management_system = "mysql" name = "app_development" user = "root" password = "" [database.staging] host = "xxx.xxx.xxx.xxx" management_system = "mysql" name = "app_staging" user = "root" password = "" [database.production] host = "yyy.yyy.yyy.yyy" management_system = "mysql" name = "app_production" user = "root" password = ""
11. tomlͷϝϦοτ • ίϝϯτ͕෇͚ΒΕΔ • Array͕දݱ͠΍͍͢ • Tableͱ͍͏୯Ґ(gopliͩͱdatabase, sshͱ͍͏୯ҐΛ༻͍· ͨ͠)Ͱkey-valueΛάϧʔϓԽͰ͖Δɻ͜ΕʹΑͬͯෳ਺؀ڥ ΁ͷ઀ଓઃఆ͕֊૚ߏ଄Ͱॻ͚ΔΑ͏ʹͳΓ·͢ • BurntSushi/tomlͰtomlΛparse͢ΔɻValidator΋͜ͷϥΠϒϥ Ϧ͕ఏڙͯ͘͠ΕΔɻ
12. ઃఆϑΝΠϧΛಡΈࠐΉ • ssh༻ͷ伴ͷઃఆͳͲΛॻ͘ͱ͖ɺઈର”~/”ͱॻ͖·͕͢ɺ ৺഑ͩͬͨͷͰfilepathʹ͸ਖ਼֬ͳύεΛಡ·͍ͤͨɻ • os/userͷuser.Current()ͱɺͦͷuserͷHomeDir͕औΕΔ ͷͰɺ”~/”Λstrings.ReplaceΛ࢖ͬͯஔ͖׵͑Δɻ • ʮ͜ΕΈΜͳͷGoݴޠͰ΍ͬͨ͜ͱͩʂʯ
 ͜ΕͰೋ౓ͱstrings.Replacerʹٽ͔͞Εͳ͍ͰࡁΉͧɻ
13. mysql΁ͷ઀ଓ • go-mysql-driver/mysqlܦ༝ͰͷϦϞʔτ΁ͷ઀ଓ͸constͰܾΊΒΕͯΔ maxPacketSizeΛ௒͑Δͱଈࢮ͠·ͨ͠ͷͰ࢖͑ͣɻ • mysql -h Ͱͷ઀ଓ΋ࢼΈ·͕ࣦͨ͠ഊɻgo-mysql-driver/mysqlΈͨ͘ Keep-Aliveͯ͘͠Εͳ͍ͷ͔ɺଈࢮ͠·ͨ͠ɻ • Ͳ͏΍ΒϩʔΧϧͷmysqlΫϥΠΞϯτʹ઀ଓॲཧΛ೚ͤͯgoroutineΛ ࢖͓͏ͱͯ͠΋ɺίωΫγϣϯ਺͕૿͑ΔͷͰࢮɻɹ • sshͰ઀ଓͨ͠ޙɺsession.RunͰ௚ʹΫΤϦΛ࣮ߦ͢ΔखஈΛऔΕ͹ɺί ωΫγϣϯΛ̍ͭுΓͭͭηογϣϯΛ૿΍ͤΔ & ఆ਺ͷ੍໿Λ֎ͤΔͷ Ͱɺฒྻ࣮ߦͰ͖Δ༷ࢠɻʢਏ͍ʣ
14. ࣮૷্ͷ໰୊఺ • golang͸ύεϑϨʔζ෇͖ͷൿີ伴ΛparseͰ͖ͳ͍ͷͰɺopensslͰͷ ෮߸ͨ͠ΩʔΛར༻͠ͳͯ͘͸ͳΒͳ͍ɻ • σʔλྔ͕ଟ͍ͱϨεϙϯεͰࢮ͵ͷͰηϚϑΥͰಉ࣌ʹ3~5ݸఔ౓ͷ ςʔϒϧʹฒྻʹϦΫΤετΛ౤͛Δɻ • ϨεϙϯεͷύέοτྔΛ͍͍ײ͡ʹ੍ݶͰ͖ΔϥΠϒϥϦ͕͋ͬͨΒڭ ͍͑ͯͩ͘͞… • mysqldumpͩͱsql෼ͷσʔλྔ͕૿͑ͯ͠·͍ͦ͏ͩͬͨͷͱɺ
 ൿಗͷͨΊʹޙ͔ΒσʔλΛॻ͖׵͍͑ͨ࣌ͱ͔ʹศརͦ͏ͩͬͨͷͰɺ
 ςʔϒϧ͝ͱʹSELECTͨ݁͠ՌΛtxtʹॻ͖ग़͢खஈΛͱΓ·ͨ͠ɻ
15. goroutineΛ࢖͏৔໘ sem := make(chan int, MaxFetchSession) var wg sync.WaitGroup for _, table := range tables { wg.Add(1) go func(table string) { sem <- 1 defer wg.Done() defer func() { <-sem }() session, err := conn.NewSession() if err != nil { panic("Failed to create session: " + err.Error()) } defer session.Close() var fetchTableStdoutBuf bytes.Buffer session.Stdout = &fetchTableStdoutBuf fetchRowsCmd := fmt.Sprintf(SelectTablesCmd, srcDBConf.User, srcDBConf.Password, srcDBConf.Name, table) err = session.Run(fetchRowsCmd) MySQL͸1ΫΤϦΛΑ͠ͳʹϚ ϧνίΞͰ΍ͬͯ͘ΕΔ࢓૊Έ ͕ͳ͍ͷͰɺμϯϓ/ϦετΞΛ goroutineͰฒྻԽ͢Δ͜ͱͰɺ
 γϡοͱσʔλΛऔͬͯ͜Ε· ͢ɻ
16. ࠓޙͷ՝୊ • sshͷηογϣϯͷඪ४ग़ྗΛͦͷ··͖࣋ͬͯͯɺϩʔΧϧͰtxtʹىͯ͜͠Δͷ ͰɺҰ౓ϦϞʔτͰॻ͖ग़ͨ͠txtΛgzipѹॖͯͦ͠ΕΛऔͬͯ͘Δɺͱ͔͕Ͱ͖ͳ ͯ͘ࠔͬͯΔɻ • sshͷϨεϙϯε͕Ͱ͔͗͢Δͱͨ·ʹ઀ଓ͕੾ΕΔͷͰϦτϥΠ/సૹྔ੍ޚ͕ඞ ཁ • γʔΫϨοτΧϥϜͷӅṭ • mysqlҎ֎΁ͷରԠ • ύεϑϨʔζ෇͖ͷ伴Ͱ΋࢖͑ΔΑ͏ʹ… • IDʹ೚ҙͷoffsetΛઃఆͰ͖ΔΑ͏ʹ͢Δ
17. timakin/gopli https://github.com/timakin/gopli Կଔ࢖ͬͯΈ͍ͯͩ͘͞ʂ