Share
Embed
Transcript
17 slides
-
timakin/gopli • Plain DB import tool with toml configuration. •
Without complex replication settings. $ gopli sync -f production -t local -c config/repli.toml -
طଘͷํ๏ • ͔͍͋ͬͨshell • mysqlpump • maxbube/mydumper • mysqldump͕γϯάϧεϨουͳͷʹରͯ͠ɺ ্ه̎ͭϚϧνεϨου
• উखʹઃఆܾΊͱ͍ͯҙͷλΠϛϯάʹ ίϚϯυΛࢥ͍ग़ͣ͞ʹར༻ग़དྷΔͷ͕ͳ͍… -
ࡶͳσʔλίϐʔͷϝϦοτ • ࡶͳೖྗσʔλͰͷςετ࠷దͳݟͨɺUXͷअຐΛ͠·͢Ͷʁ • ݟͨɿϨΠΞτͷͨΊʹtruncate͢Δจࣈྔௐɺ͕ͬͭΓͣ ΕΔը૾αΠζ • ։ൃ໘ɿόάͬͨ࣌ʹຊ൪ΛߥΒͣ͞ϩʔΧϧͰ࠶ݱͰ͖Δ֬Λ ্͛ΒΕΔɺϩʔΧϧ։ൃ࣌ͱຊ൪orεςʔδϯάͰվળʢਏ ͍ΫΤϦൃݟʣΛΪϟοϓͳ͘ߦ͑Δɻ
• ࣌ؒɿʢʮϒϥϯνAΛςετ͍ͨ͠ͷͰϒϥϯνBͷεςʔδϯά ͷσϓϩΠ15࣌͘Β͍͔Β͓ئ͍͠·͢ʯ͕ͳ͍ʣ -
σʔλͷͱ͖ͬͯํ • ςʔϒϧҰཡͷऔಘ • goroutineΛར༻ͭͭ͠ɺςʔϒϧ͝ͱʹλϒ۠ΓͷSELECT݁ՌΛ tmpϑΝΠϧͱͯ͠txtʹอଘ • schema_migrations, repli_chkͷςʔϒϧϒϥοΫϦετͱͯ͠আ֎ •
ೖઌͷDBͷςʔϒϧΛҰ୴DELETE͢Δ • LOAD DATA LOCAL INFILEͰσʔλΛϦετΞɻ͜ͷࡍʹ֤ςʔϒϧ ʹಉ࣌ʹσʔλΛೖΕΔͨΊʹgoroutineΛར༻ -
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 = "" -
tomlͷϝϦοτ • ίϝϯτ͕͚ΒΕΔ • Array͕දݱ͍͢͠ • Tableͱ͍͏୯Ґ(gopliͩͱdatabase, sshͱ͍͏୯ҐΛ༻͍· ͨ͠)Ͱkey-valueΛάϧʔϓԽͰ͖Δɻ͜ΕʹΑͬͯෳڥ ͷଓઃఆ͕֊ߏͰॻ͚ΔΑ͏ʹͳΓ·͢
• BurntSushi/tomlͰtomlΛparse͢ΔɻValidator͜ͷϥΠϒϥ Ϧ͕ఏڙͯ͘͠ΕΔɻ -
mysqlͷଓ • go-mysql-driver/mysqlܦ༝ͰͷϦϞʔτͷଓconstͰܾΊΒΕͯΔ maxPacketSizeΛ͑Δͱଈࢮ͠·ͨ͠ͷͰ͑ͣɻ • mysql -h ͰͷଓࢼΈ·͕ࣦͨ͠ഊɻgo-mysql-driver/mysqlΈͨ͘ Keep-Aliveͯ͘͠Εͳ͍ͷ͔ɺଈࢮ͠·ͨ͠ɻ •
Ͳ͏ΒϩʔΧϧͷmysqlΫϥΠΞϯτʹଓॲཧΛͤͯgoroutineΛ ͓͏ͱͯ͠ɺίωΫγϣϯ͕૿͑ΔͷͰࢮɻɹ • sshͰଓͨ͠ޙɺsession.RunͰʹΫΤϦΛ࣮ߦ͢ΔखஈΛऔΕɺί ωΫγϣϯΛ̍ͭுΓͭͭηογϣϯΛ૿ͤΔ & ఆͷ੍Λ֎ͤΔͷ Ͱɺฒྻ࣮ߦͰ͖Δ༷ࢠɻʢਏ͍ʣ -
্࣮ͷ • golangύεϑϨʔζ͖ͷൿີ伴ΛparseͰ͖ͳ͍ͷͰɺopensslͰͷ ෮߸ͨ͠ΩʔΛར༻͠ͳͯ͘ͳΒͳ͍ɻ • σʔλྔ͕ଟ͍ͱϨεϙϯεͰࢮ͵ͷͰηϚϑΥͰಉ࣌ʹ3~5ݸఔͷ ςʔϒϧʹฒྻʹϦΫΤετΛ͛Δɻ • ϨεϙϯεͷύέοτྔΛ͍͍ײ͡ʹ੍ݶͰ͖ΔϥΠϒϥϦ͕͋ͬͨΒڭ ͍͑ͯͩ͘͞…
• mysqldumpͩͱsqlͷσʔλྔ͕૿͑ͯ͠·͍ͦ͏ͩͬͨͷͱɺ ൿಗͷͨΊʹޙ͔ΒσʔλΛॻ͖͍͑ͨ࣌ͱ͔ʹศརͦ͏ͩͬͨͷͰɺ ςʔϒϧ͝ͱʹSELECTͨ݁͠ՌΛtxtʹॻ͖ग़͢खஈΛͱΓ·ͨ͠ɻ -
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) MySQL1ΫΤϦΛΑ͠ͳʹϚ ϧνίΞͰͬͯ͘ΕΔΈ ͕ͳ͍ͷͰɺμϯϓ/ϦετΞΛ goroutineͰฒྻԽ͢Δ͜ͱͰɺ γϡοͱσʔλΛऔͬͯ͜Ε· ͢ɻ -
ࠓޙͷ՝ • sshͷηογϣϯͷඪ४ग़ྗΛͦͷ··͖࣋ͬͯͯɺϩʔΧϧͰtxtʹىͯ͜͠Δͷ ͰɺҰϦϞʔτͰॻ͖ग़ͨ͠txtΛgzipѹॖͯͦ͠ΕΛऔͬͯ͘Δɺͱ͔͕Ͱ͖ͳ ͯ͘ࠔͬͯΔɻ • sshͷϨεϙϯε͕Ͱ͔͗͢Δͱͨ·ʹଓ͕ΕΔͷͰϦτϥΠ/సૹྔ੍ޚ͕ඞ ཁ • γʔΫϨοτΧϥϜͷӅṭ
• mysqlҎ֎ͷରԠ • ύεϑϨʔζ͖ͷ伴Ͱ͑ΔΑ͏ʹ… • IDʹҙͷoffsetΛઃఆͰ͖ΔΑ͏ʹ͢Δ