Replicate RDS-MySQL to a non-amazon host?

Looks like Amazon has chosen to forbid this for now:

https://forums.aws.amazon.com/thread.jspa?threadID=46377&tstart=0


If you have the following scenario

  • all your data is innodb
  • you have binary logging enabled on RDS

you could create a user in RDS like this

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'leopd'@'%' IDENTIFIED BY 'repl_password'; 

If Amazon does not permit '%' for the host name, you will need a specfic public IP address

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'leopd'@'xxx.xx.xx.xxx';

Next, mysqldump the data out of RDS as a single transaction

mysqldump -u... -p... --single-transaction --master-data=1 --all-databases --routines --triggers > /root/MySQLData.sql

Run the CHANGE MASTER TO command using leopd@'xxx.xx.xx.xxxx' as the user (xxx.xx.xx.xxxx is the IP address of RDS)

CHANGE MASTER TO
master_host = 'xxx.xx.xx.xxxx',
master_port = 3306,
master_user = 'leopd',
master_passwowrd = 'repl_pass'
master_log_file='slsnbj',
master_log_pos=1;

Load the data into a new server. Don't worry about the master_log_file='slsnbj' and master_log_pos=1. Line 22 of the dump will have the correct log file and position.

Run START SLAVE; on the new server

It should start working. You may have to worry about firewall considerations.

Give it a Try !!!

UPDATE 2012-03-23 17:11 EDT

You only have one chance left. See if you can set that last privilege with this:

UPDATE mysql.user SET Repl_slave_priv = 'Y' WHERE user='root' AND host='%';
FLUSH PRIVILEGES;

Perhaps this is being blocked for users that have % in the host column of mysql.user.

You may need to create another user with a hard public IP as I suggested earlier

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'leopd'@'xxx.xx.xx.xxx';

It is possible replication slaves in RDS must also be RDS as well.