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.