Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a fairly large MySQL (5.1) database running in Amazon as an RDS instance. I'm working on migrating it out of Amazon. To do that smoothly I'd like to set up the new hardware as a read-only replica of the RDS instance.

I know RDS supports replication within RDS. Is there any way to configure it to replicate to a host which is not in RDS?

share|improve this question
    
Is all your data InnoDB ??? –  RolandoMySQLDBA Mar 22 '12 at 23:41
    
Let's say "yes, it's all in innodb" - close enough to true for this forum. ;) –  Leopd Mar 23 '12 at 3:45
add comment

3 Answers 3

up vote 1 down vote accepted

It's now possible to replicate to external mysql slaves from RDS: http://www.ruempler.eu/2013/07/07/replicating-aws-rds-mysql-databases-to-external-slaves/

share|improve this answer
add comment

Looks like Amazon has chosen to forbid this for now:

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

share|improve this answer
    
The end of the thread confirms that with 5.6 it is possible and points to this blog post: ruempler.eu/2013/07/07/… –  Diego F. Durán Dec 17 '13 at 16:07
add comment

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.

share|improve this answer
    
When logged in as root, GRANT REPLICATION SLAVE results in ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES) –  Leopd Mar 23 '12 at 20:13
    
Please run SHOW GRANTS. Do you have all privileges on . ? –  RolandoMySQLDBA Mar 23 '12 at 20:31
    
This is running as the RDS root user, which has all the privileges that are possible: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD <snip> WITH GRANT OPTION. Looks like Amazon just doesn't allow this. –  Leopd Mar 23 '12 at 21:07
    
Updated my answer based on your last comment... –  RolandoMySQLDBA Mar 23 '12 at 21:11
1  
@Radek the original poster (OP) answered it back on March 23rd : dba.stackexchange.com/a/15492/877 –  RolandoMySQLDBA Jun 29 '12 at 15:02
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.