DB Replication from RDS to External MySQL Server - DoSomethingArchive/legacy-website GitHub Wiki

Overview

In order to replicate from an AWS RDS database to an external server, you need 3 components, and to keep two things in mind:

Components

  • RDS Master DB - rds-master
  • RDS Read-Only Slave - rds-slave
  • External DB Server - external-db

Two Things to Keep in Mind

  • This process is fairly brittle and not fully supported by AWS except for temporary data extraction.
  • Full AWS documentation on this here.

Instructions

  1. Create an RDS Read-Only instance rds-slave with RDS Master DB rds-master as the source.
  2. Wait for rds-slave and rds-master to both have the Available status.
  3. Open a MySQL connection to rds-master and run the following command:
    • call mysql.rds_set_configuration('binlog retention hours', 24);
  4. On rds-master create replication user with the following commands (substitute in your own secure values):
    • CREATE USER 'repl'@'external-db' IDENTIFIED BY 'pass';
    • GRANT REPLICATION SLAVE ON *.* TO 'repl'@'external-db';
  5. Open a MySQL connection to rds-slave and run the commands:
    • call mysql.rds_stop_replication;
    • SHOW SLAVE STATUS;, and note down the values of Exec_Master_Log_Pos and Relay_Master_Log_File.
  6. Run a mysqldump from rds-slave with the following format:
    • mysqldump -h rds-slave -u user -p password --single-transaction --routines --triggers --databases database1 database2 | gzip > rds-slave-dump.sql.gz
  7. Import the DB dump to external-db:
    • zcat rds-slave-dump.sql.gz | mysql -h external-db -u user -p password
  8. Open a MySQL connection to external-db and set the source as rds-master using the following command (note the Exec_Master_Log_Pos arg does not have single quotes around it):
    • CHANGE MASTER TO MASTER_HOST='rds-master', MASTER_USER='repl', MASTER_PASSWORD='pass', MASTER_LOG_FILE='Relay_Master_Log_File', MASTER_LOG_POS=Exec_Master_Log_Pos;
  9. Create a replication filter to ignore tables created by AWS only on RDS and will break the replication (you can add additional filters if needed):
    • CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('mysql.rds\_%')
  10. Start replication by running START SLAVE; on external-db, and monitoring progress using SHOW SLAVE STATUS;.
  11. If you have no further use for rds-slave, you can safely delete it.