gunosy-beer-2016-07-27

Db8ec54bcaba4695821acf233a25afe9?s=47 aibou
July 28, 2016

 gunosy-beer-2016-07-27

RDS for MySQLからDMSを使ってAmazon Auroraにノーメンテで移行し(ようとし)た話

Db8ec54bcaba4695821acf233a25afe9?s=128

aibou

July 28, 2016
Tweet

Transcript

  1. RDS for MySQL͔ΒDMSΛ࢖ͬͯ Amazon AuroraʹϊʔϝϯςͰ Ҡߦ͠ʢΑ͏ͱ͠ʣͨ࿩ גࣜձࣾGunosy ඿஍྄ี

  2. ͓·͑ͩΕ • @aibou (͸·͡ Γΐ͏͚͢) • ࠷ۙຊ໊Ͱݺ͹Εͯͳ͍ • GunosyαʔϏεͷΠϯϑϥશൠ୲౰ •

    Java(Spring Boot), Ruby, Chef, Goͱ͔ • AWSྺ1೥ • ޷͖ͳAWSαʔϏεɿElasticTranscoder • Ԍ্ྺ͋Γ
  3. GunosyͷΠϯϑϥ • AWS Opsworks + Scheduled Lambda + EMR •

    RDS + ElastiCache(Redis) + Redshift(ϩάอଘ) • աڈʹ Docker ΍ ElasticBeanstalk ΛҰ෦࠾༻͕ͯͨ͠
 ݁ہOpsworksʹམͪண͘ • EC2Πϯελϯεͷ͏ͪ99%͕Opsworks؅ཧԼ • EMR & ͱ͋ΔϨΨγʔαʔό͕؅ཧ֎ • ৄ͘͠͸ https://speakerdeck.com/koid/yokuwakaru-aws-opsworks
  4. ࠓճͷ͖͔͚ͬ

  5. AWS DMSͱ͸ AWS Database Migration Service (Ҏ߱DMS) • DB to

    DBͷσʔλϚΠάϨʔγϣϯΛαϙʔτ • ΦϯϓϨͷDBΛAWSʹɺతͳ • ϚΠάϨʔγϣϯޙͷϨϓϦέʔγϣϯ΋
  6. DMSͷ࢓૊Έ

  7. SHOW PROCESSLIST Source DataBase SELECT `id`,`client_name`,`action_name`, ~~ FROM `hoge`.`action_logs` SELECT

    `creative_id`,`media_id`, ~~~~~~~~~~ FROM
 `hoge`.`creatives_approval_medias` SELECT `id`,`campaign_id`,`date`, ~~~~~~~~~ FROM `hoge`.`campaign_stats` SELECT `id`,`campaign_id`,`os`, ~~~~~~~~~~~ FROM `hoge`.`os_stats` SELECT `id`,`campaign_id`,`creative_id`, ~~ FROM
 `hoge`.`campaign_creative_stats`
  8. SHOW PROCESSLIST Target DataBase load data local infile "/rdsdbdata/data/tasks/XXXXX/data_files/6/LOAD00000003.csv" into

    table `hoge`.`action_logs` CHARACTER SET UTF8 fields terminated by ',' enclosed by '"' lines terminated by '\n'( `id`,`client_name`,`action_name` ~~~~~ ) load data local infile "/rdsdbdata/data/tasks/XXXXX/data_files/50/ LOAD00000007.csv" into table `hoge`.`creatives_approval_medias` CHARACTER SET UTF8 fields terminated by ',' enclosed by '"' lines terminated by '\n'( `creative_id`,`media_id`, ~~~~~ )
  9. ޿ࠂ഑৴γεςϜͷRDSࣄ৘

  10. ϚΠάϨʔγϣϯ࣌ͷߏ੒

  11. ͕ɺ͔͠͠

  12. ϨϓϦ௥͍͔ͭͳ͍໰୊ • ݕূޙͷਖ਼౰ੑ֬ೝͨ͠Βɺ਺࣌ؒલʙ൒೔લͷσʔλ͕ͳ͍ • ݪҼɿఆظతʹ૸ΔَUPDATEʹΑΓɺ
 ɹɹɹϚΠάϨʔτޙͷϨϓϦέʔγϣϯ͕௥͍͍͍ͭͯͳ͍ • ͲΜͲΜ޿͕Δ஗Ԇ • ΞϓϦέʔγϣϯࣄ৘ʹΑΔ΋ͷͰվमඞਢ

    • Ͱ΋ϝϯςφϯεࠂ஌͸ࡁʢ͋ͱ਺೔ʣ
  13. ࢒೦ͳ͕Β ※MySQLͷεφοϓγϣοτΛAuroraԽͰ͖Δ௒ઈศརϘλϯͰ͢

  14. DMSͷϋϚΓͲ͜Ζ ͋͘·Ͱ΋ݱ࣌఺Ͱͷ΋ͷͳͷͰ ΞοϓσʔτͰมΘΔ΋ͷ΋͋Δͱࢥ͍·͢

  15. ύϥϝʔλ(SourceDB) ม͑ͳ͍ͱϚΠάϨʔτͰ͖ͳ͍ DMS༻ϨϓϦΧ͚ͩผPG෇༩͠·͠ΐ͏ QBSBNFUFS EFGBVMU DIBOHFE CJOMPH@GPSNBU .*9&% 308 CJOMPH@DIFDLTVN

    FOHJOFEFGBVMU /0/&
  16. ϚΠάϨʔτ͞Εͳ͍΋ͷ • INDEX౳͸DMSͰ࡞੒͞Εͳ͍ • Ҋᶃ ϚΠάϨʔτલʹINDEXੜ੒ʢ͓͢͢Ίʣ • Ҋᶄ ϚΠάϨʔτޙʹALTER TABLE

    • MySQLϢʔβʢMySQLεΩʔϚʣ͸ϚΠάϨʔτ͞Εͳ͍ • ϚΠάϨʔτલޙͰखಈ࡞੒
  17. ϨϓϦέʔγϣϯΠϯελϯε • t2 vs c4 ʢຊ൪ͳΒc4ͷํ͕҆શʣ • Source DBͷετϨʔδαΠζͱಉ౳͔ͦΕҎ্ •

    ద੾ͳωοτϫʔΫઃఆΛ • Security Group, Subnet • ϓϥΠϕʔτNWͰ௨৴Ͱ͖ΔͳΒ΍Δ
  18. id = 0໰୊ • auto incrͳϑΟʔϧυʹ0͕ೖͬͯͨ৔߹ɺ࿈൪͕શͯͣΕΔ • ݕূ࣌͸ id =

    0ͷΧϥϜ͚ͩεΩοϓ͞ΕͯҠߦ • sql_mode = NO_AUTO_VALUE_ON_ZERO Ͱ΋ղܾͰ͖ͣ • DMSܾߦ࣌͸id = 0ͷߦΛҰ୴࡟আͯ͠ҠߦޙखಈINSERTܭը • ϨϓϦΧ͸ read_only = true ʹͳͬͯΔͷͰ஫ҙ
  19. ͦͷଞ஫ҙࣄ߲ • ϩά͸ඞͣ༗ޮʹʢΤϥͬͨͱ͖ͷௐࠪʣ • ϨϓϦΠϯελϯε͸EC2ͷ؅ཧ֎ • ׬ྃޙͷετοϓ͠๨Εʹ஫ҙ • λεΫΛ࡟আ͠ͳ͍ͱ࡟আͰ͖ͳ͍…

  20. ·ͱΊ • DMSͰϚΠάϨʔγϣϯ͔ͨͬͨ͠ͳʔ • ϨϓϦέʔγϣϯͪΌΜͱ௥͍͍ͭͯͨΒܾߦͯͨ͠ • ࣄલݕূ͸େࣄɺࠂ஌લϦϋʔαϧ΋େࣄ • ΞϓϦέʔγϣϯࣄ৘Λ೺Ѳ •

    ϨϓϦΧ૿͑࢝ΊͨΒAuroraԽݕ౼ • ૣΊʹಈ͍ͨ΄͏͕