Upgrade to Pro — share decks privately, control downloads, hide ads and more …

RDS for MySQLからAuroraに移行した話 / RDS for MySQL to Aurora

RDS for MySQLからAuroraに移行した話 / RDS for MySQL to Aurora

2016.11.02 JAWS-UG横浜 - Reboot!!
http://jawsug-yokohama.connpass.com/event/40497/

FUJIWARA Shunichiro

November 02, 2016
Tweet

More Decks by FUJIWARA Shunichiro

Other Decks in Technology

Transcript

  1. 2014೥9݄ϦϦʔε શ໘తʹAWSΛར༻ • ELB + EC2 • RDS for MySQL

    • ElastiCache Redis • CloudFront • S3 • Redshift
  2. MySQL → Aurora ͳͥҠߦ͔͔ͨͬͨ͠ (2) ϦʔυϨϓϦΧͷஆؾ͕ͭΒ͍ • ϦʔυϨϓϦΧ͸EBS snapshot͔Β෮ݩ͞ΕΔ •

    EBSͷ First Touch Penalty • EC2ͳΒ dd ίϚϯυͰͰ͖Δ͕RDS͸… • primary key + secondary index͢΂ͯᢞΊΔඞཁ • ΠϕϯτલʹΠϯελϯεαΠζมߋ͕΍ΓͮΒ͍
  3. MySQL࣌୅ͱͷมߋ఺ tkuchiki.hatenablog.com/entry/2016/07/14/180046 HAProxy1.6ͷಈత໊લղܾ + ֎෦script listen mysql-slave bind 127.0.0.1:3307 mode

    tcp option external-check external-check path "/usr/bin:/bin:/usr/local/bin" external-check command /usr/local/bin/check_aurora_slave.sh balance roundrobin server master db01.xxx.rds.amazonaws.com:3306 check backup resolvers mydns server slave1 db02.xxx.rds.amazonaws.com:3306 check resolvers mydns server slave2 db03.xxx.rds.amazonaws.com:3306 check resolvers mydns
  4. MySQL࣌୅ͱͷมߋ఺ HAProxyͷ֎෦script innodb_read_only͕ON → ReadReplica #!/bin/bash READ_ONLY=$(mysql -u haproxy -h

    ${HAPROXY_SERVER_ADDR} \ -P ${HAPROXY_SERVER_PORT} -BN\ -e "SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';"\ --connect-timeout=5 | awk '{print $2}') [ "${READ_ONLY}" = "ON" ]
  5. MySQL࣌୅ͱͷมߋ఺ show status like '%Innodb_rows%' ʹมߋ +----------------------+----------+ | Variable_name |

    Value | +----------------------+----------+ | Innodb_rows_deleted | 1 | | Innodb_rows_inserted | 111002 | | Innodb_rows_read | 15682572 | | Innodb_rows_updated | 1020 | +----------------------+----------+