RDS Replicate to External MySQL Server Howto

RDS Replicate to External MySQL Server Howto

### 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](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html).

### 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 safetly delete it.