Is it possible to use an EC2 RDS MySQL instance as a slave to an external master?

Question

I have a master MySQL instance running on our local network, but I was wondering if I can get an EC2 RDS instance to replicate the master or is this locked down by amazon?

Solution 1

Apparently this is now possible, however it is not fully "supported". Amazon will now allow you to replicate an RDS to an external slave, and replicate to an RDS from an external master, however their disclaimer suggests that they don't intend for this to be a permanent part of your server setup.

I have personally not tested this, but I plan on using it to help migrate off of some old non-AWS servers in which I host various databases and applications.

Given your question the below AWS doc will best apply.

Replication to RDS from external Master: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html

Proof of concept on Sönke Ruempler's blog post (RDS to External Slave): http://www.ruempler.eu/2013/07/07/replicating-aws-rds-mysql-databases-to-external-slaves/

Solution 2

This is still not supported as of February 2011, see RDS instance as a replication slave:

Non-RDS to RDS replication is not currently supported [...]. However, we will log your interest for our future road map planning.

Solution 3

This is now officially supported. In addition to John C's link (http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html), AWS created a (lengthy) webcast about it as well: http://youtu.be/TT1M_XRAlQo

I won't fully reproduce the AWS documentation, but the abbreviated steps are:

1. Create RDS instance that will act as slave (either MySQL 5.5 version 5.5.33 or later, or MySQL 5.6 version 5.6.13 or later)
2. Edit the RDS security group to authorized the the IP address of your external master
3. If the external master is an EC2 machine, update that machine's security group to allow inbound and outbound connections to your RDS IP address over TCP 3306
4. Set the server variables on the external master and the RDS slave to allow replication (e.g., log-bin, server-id, etc.; beyond the scope of this question)
5. Create a replicant user on the external master
6. Run your mysqldump on the external master
7. Run head dump.sql -n80 | grep "MASTER_LOG_POS" to get the MASTER_LOG_FILE and MASTER_LOG_POS values
8. Run mysql -u[RDS username] -h'[RDS IP address]' -p'[password]' < ~/dump.sql
9. Once the dump file is loaded into the RDS machine, you can't run the same types of commands that you would run in standard MySQL to set up the replication variables, so instead you log into mysql on the RDS instance and run

mysql> CALL mysql.rds_set_external_master ('[external master ip]', 3306, '[replicant username form step 5]', '[replicant password]' , '[MASTER_LOG_FILE value from step 7, e.g., mysql-bin.000042]', [MASTER_LOG_POS value from step 7 e.g., 107] , 1); mysql> call mysql.rds_start_replication;

Solution 4

You can do this with Tungsten Replicator, which is an open source replacement for MySQL native replication. It now supports replication from a MySQL master to an Amazon RDS slave. For more information, please see the following blog article:

http://scale-out-blog.blogspot.com/2013/01/replicating-from-mysql-to-amazon-rds.html

Cheers, Robert Hodges (Tungsten committer)

View more solutions

Comments

[Steffen Opel]

-1: this announcement covers the AWS internal replication feature only, thus doesn't answer the question - please correct or delete your answer accordingly, thanks!

[Dave M]

Some detail from the link would improve this answer. Links do break.

[WooDzu]

In 2016 it is possible. See docs.aws.amazon.com/AmazonRDS/latest/UserGuide/…