Plain db import with Go

by Seiji Takahashi

Slide 1

Slide 1 text

golang.tokyo #2 @__timakin__ Plain db import with Go

Slide 2

Slide 2 text

ࣗݾ঺հ • timakinͱ͍͍·͢ɻ • twitter: @__timakin__  github: timakin • ࠷ۙboot.fmͱ͍͏  ϙουΩϟετΛ࢝Ί·ͨ͠ɻ

Slide 3

Slide 3 text

timakin/gopli https://github.com/timakin/gopli

Slide 4

Slide 4 text

timakin/gopli • Plain DB import tool with toml conﬁguration. • Without complex replication settings.   $ gopli sync -f production -t local -c config/repli.toml

Slide 5

Slide 5 text

ͳͥ࡞͔ͬͨ • http://techlife.cookpad.com/entry/2014/10/03/110806

Slide 6

Slide 6 text

ͳͥ࡞͔ͬͨ • ຊ൪ͷσʔλΛ؆୯ʹϩʔΧϧʹ͖࣋ͬͯͨ͘ ͳΓ·͢ΑͶʁʢεςʔδϯάʹϨϓϦͯ͘͠ Δͷ͸ɺผ్͋Δͱͯ͠ʣ • Կ౓ʮname: testʯͳϢʔβʔɺʮcontent: aaaaaaaaaaaaaaaaʯͳ౤ߘ಺༰࡞͔ͬͨʁ • seedσʔλ࡞Δͷਏ͘ͳ͍Ͱ͔͢ʁ

Slide 7

Slide 7 text

طଘͷํ๏ • ͔͍͋ͬͨshell • mysqlpump • maxbube/mydumper • mysqldump͕γϯάϧεϨουͳͷʹରͯ͠ɺ  ্ه̎ͭ͸ϚϧνεϨου • উखʹઃఆܾΊͱ͍ͯ೚ҙͷλΠϛϯάʹ  ίϚϯυΛࢥ͍ग़ͣ͞ʹར༻ग़དྷΔ΋ͷ͕ͳ͍…

Slide 8

Slide 8 text

ࡶͳσʔλίϐʔͷϝϦοτ • ࡶͳೖྗσʔλͰͷςετ͸࠷దͳݟͨ໨ɺUXͷअຐΛ͠·͢Ͷʁ • ݟͨ໨ɿϨΠΞ΢τͷͨΊʹtruncate͢Δจࣈྔௐ੔ɺ͕ͬͭΓͣ ΕΔը૾αΠζ • ։ൃ໘ɿόάͬͨ࣌ʹຊ൪ΛߥΒͣ͞ϩʔΧϧͰ࠶ݱͰ͖Δ֬཰Λ ্͛ΒΕΔɺϩʔΧϧ։ൃ࣌ͱຊ൪orεςʔδϯάͰ଎౓վળʢਏ ͍ΫΤϦൃݟʣΛΪϟοϓͳ͘ߦ͑Δɻ • ࣌ؒɿʢʮϒϥϯνAΛςετ͍ͨ͠ͷͰϒϥϯνBͷεςʔδϯά ΁ͷσϓϩΠ͸15࣌͘Β͍͔Β͓ئ͍͠·͢ʯ͕ͳ͍ʣ

Slide 9

Slide 9 text

σʔλͷͱ͖ͬͯํ • ςʔϒϧҰཡͷऔಘ • goroutineΛར༻ͭͭ͠ɺςʔϒϧ͝ͱʹλϒ۠੾ΓͷSELECT݁ՌΛ tmpϑΝΠϧͱͯ͠txtʹอଘ • schema_migrations, repli_chk౳ͷςʔϒϧ͸ϒϥοΫϦετͱͯ͠আ֎ • ஫ೖઌͷDBͷςʔϒϧΛҰ୴DELETE͢Δ • LOAD DATA LOCAL INFILEͰσʔλΛϦετΞɻ͜ͷࡍʹ΋֤ςʔϒϧ ʹಉ࣌ʹσʔλΛೖΕΔͨΊʹgoroutineΛར༻

Slide 10

Slide 10 text

toml conﬁguration • 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 = ""

Slide 11

Slide 11 text

tomlͷϝϦοτ • ίϝϯτ͕෇͚ΒΕΔ • Array͕දݱ͠΍͍͢ • Tableͱ͍͏୯Ґ(gopliͩͱdatabase, sshͱ͍͏୯ҐΛ༻͍· ͨ͠)Ͱkey-valueΛάϧʔϓԽͰ͖Δɻ͜ΕʹΑͬͯෳ਺؀ڥ ΁ͷ઀ଓઃఆ͕֊૚ߏ଄Ͱॻ͚ΔΑ͏ʹͳΓ·͢ • BurntSushi/tomlͰtomlΛparse͢ΔɻValidator΋͜ͷϥΠϒϥ Ϧ͕ఏڙͯ͘͠ΕΔɻ

Slide 12

Slide 12 text

ઃఆϑΝΠϧΛಡΈࠐΉ • ssh༻ͷ伴ͷઃఆͳͲΛॻ͘ͱ͖ɺઈର”~/”ͱॻ͖·͕͢ɺ ৺഑ͩͬͨͷͰﬁlepathʹ͸ਖ਼֬ͳύεΛಡ·͍ͤͨɻ • os/userͷuser.Current()ͱɺͦͷuserͷHomeDir͕औΕΔ ͷͰɺ”~/”Λstrings.ReplaceΛ࢖ͬͯஔ͖׵͑Δɻ • ʮ͜ΕΈΜͳͷGoݴޠͰ΍ͬͨ͜ͱͩʂʯ  ͜ΕͰೋ౓ͱstrings.Replacerʹٽ͔͞Εͳ͍ͰࡁΉͧɻ

Slide 13

Slide 13 text

mysql΁ͷ઀ଓ • go-mysql-driver/mysqlܦ༝ͰͷϦϞʔτ΁ͷ઀ଓ͸constͰܾΊΒΕͯΔ maxPacketSizeΛ௒͑Δͱଈࢮ͠·ͨ͠ͷͰ࢖͑ͣɻ • mysql -h Ͱͷ઀ଓ΋ࢼΈ·͕ࣦͨ͠ഊɻgo-mysql-driver/mysqlΈͨ͘ Keep-Aliveͯ͘͠Εͳ͍ͷ͔ɺଈࢮ͠·ͨ͠ɻ • Ͳ͏΍ΒϩʔΧϧͷmysqlΫϥΠΞϯτʹ઀ଓॲཧΛ೚ͤͯgoroutineΛ ࢖͓͏ͱͯ͠΋ɺίωΫγϣϯ਺͕૿͑ΔͷͰࢮɻɹ • sshͰ઀ଓͨ͠ޙɺsession.RunͰ௚ʹΫΤϦΛ࣮ߦ͢ΔखஈΛऔΕ͹ɺί ωΫγϣϯΛ̍ͭுΓͭͭηογϣϯΛ૿΍ͤΔ & ఆ਺ͷ੍໿Λ֎ͤΔͷ Ͱɺฒྻ࣮ߦͰ͖Δ༷ࢠɻʢਏ͍ʣ

Slide 14

Slide 14 text

࣮૷্ͷ໰୊఺ • golang͸ύεϑϨʔζ෇͖ͷൿີ伴ΛparseͰ͖ͳ͍ͷͰɺopensslͰͷ ෮߸ͨ͠ΩʔΛར༻͠ͳͯ͘͸ͳΒͳ͍ɻ • σʔλྔ͕ଟ͍ͱϨεϙϯεͰࢮ͵ͷͰηϚϑΥͰಉ࣌ʹ3~5ݸఔ౓ͷ ςʔϒϧʹฒྻʹϦΫΤετΛ౤͛Δɻ • ϨεϙϯεͷύέοτྔΛ͍͍ײ͡ʹ੍ݶͰ͖ΔϥΠϒϥϦ͕͋ͬͨΒڭ ͍͑ͯͩ͘͞… • mysqldumpͩͱsql෼ͷσʔλྔ͕૿͑ͯ͠·͍ͦ͏ͩͬͨͷͱɺ  ൿಗͷͨΊʹޙ͔ΒσʔλΛॻ͖׵͍͑ͨ࣌ͱ͔ʹศརͦ͏ͩͬͨͷͰɺ  ςʔϒϧ͝ͱʹSELECTͨ݁͠ՌΛtxtʹॻ͖ग़͢खஈΛͱΓ·ͨ͠ɻ

Slide 15

Slide 15 text

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ͰฒྻԽ͢Δ͜ͱͰɺ  γϡοͱσʔλΛऔͬͯ͜Ε· ͢ɻ

Slide 16

Slide 16 text

ࠓޙͷ՝୊ • sshͷηογϣϯͷඪ४ग़ྗΛͦͷ··͖࣋ͬͯͯɺϩʔΧϧͰtxtʹىͯ͜͠Δͷ ͰɺҰ౓ϦϞʔτͰॻ͖ग़ͨ͠txtΛgzipѹॖͯͦ͠ΕΛऔͬͯ͘Δɺͱ͔͕Ͱ͖ͳ ͯ͘ࠔͬͯΔɻ • sshͷϨεϙϯε͕Ͱ͔͗͢Δͱͨ·ʹ઀ଓ͕੾ΕΔͷͰϦτϥΠ/సૹྔ੍ޚ͕ඞ ཁ • γʔΫϨοτΧϥϜͷӅṭ • mysqlҎ֎΁ͷରԠ • ύεϑϨʔζ෇͖ͷ伴Ͱ΋࢖͑ΔΑ͏ʹ… • IDʹ೚ҙͷoffsetΛઃఆͰ͖ΔΑ͏ʹ͢Δ

Slide 17

Slide 17 text

timakin/gopli https://github.com/timakin/gopli Կଔ࢖ͬͯΈ͍ͯͩ͘͞ʂ