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. 2.

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

    Java(Spring Boot), Ruby, Chef, Goͱ͔ • AWSྺ1೥ • ޷͖ͳAWSαʔϏεɿElasticTranscoder • Ԍ্ྺ͋Γ
  2. 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
  3. 5.

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

    DBͷσʔλϚΠάϨʔγϣϯΛαϙʔτ • ΦϯϓϨͷDBΛAWSʹɺతͳ • ϚΠάϨʔγϣϯޙͷϨϓϦέʔγϣϯ΋
  4. 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`
  5. 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`, ~~~~~ )
  6. 17.

    ϨϓϦέʔγϣϯΠϯελϯε • t2 vs c4 ʢຊ൪ͳΒc4ͷํ͕҆શʣ • Source DBͷετϨʔδαΠζͱಉ౳͔ͦΕҎ্ •

    ద੾ͳωοτϫʔΫઃఆΛ • Security Group, Subnet • ϓϥΠϕʔτNWͰ௨৴Ͱ͖ΔͳΒ΍Δ
  7. 18.

    id = 0໰୊ • auto incrͳϑΟʔϧυʹ0͕ೖͬͯͨ৔߹ɺ࿈൪͕શͯͣΕΔ • ݕূ࣌͸ id =

    0ͷΧϥϜ͚ͩεΩοϓ͞ΕͯҠߦ • sql_mode = NO_AUTO_VALUE_ON_ZERO Ͱ΋ղܾͰ͖ͣ • DMSܾߦ࣌͸id = 0ͷߦΛҰ୴࡟আͯ͠ҠߦޙखಈINSERTܭը • ϨϓϦΧ͸ read_only = true ʹͳͬͯΔͷͰ஫ҙ