16

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?

2
  • Is all your data InnoDB ??? Mar 22, 2012 at 23:41
  • Let's say "yes, it's all in innodb" - close enough to true for this forum. ;)
    – Leopd
    Mar 23, 2012 at 3:45

3 Answers 3

1

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/

8

Looks like Amazon has chosen to forbid this for now:

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

1
3

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.

6
  • When logged in as root, GRANT REPLICATION SLAVE results in ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)
    – Leopd
    Mar 23, 2012 at 20:13
  • Please run SHOW GRANTS. Do you have all privileges on . ? Mar 23, 2012 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, 2012 at 21:07
  • Updated my answer based on your last comment... Mar 23, 2012 at 21:11
  • 1
    @Radek the original poster (OP) answered it back on March 23rd : dba.stackexchange.com/a/15492/877 Jun 29, 2012 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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