MYSQL Data Migration Capabilities for RDS

RDS now has a provision to set a non RDS mysql instance (on premise or EC2) as its master and vice-versa.

To test this out

  1. Start up an RDS instance.
  2. Start up an EC2 instance and install mysql server on it.
  3. Configure the EC2 instance to be a replication master.

To do this login into the EC2 machine and edit the my.cnf or my.ini file.

Under the mysqld section enter the following two lines


Restart the mysql server
4.Create a user for replication
We have to create a user account on the master that the slave can use for replication.
Log into mysql and run the following commands

CREATE USER 'rep1'@'%' IDENTIFIED BY ‘password';

5.Sync-up slave data with that of master and make sure the master is not undergoing any further updates.To achieve this first login to mysql and block all write statements with the below command.


Now take a mysqldump of the database.

mysqldump –u username –ppassword dbname > dump.sql

Transfer the contents to the RDS database.

mysql -u rdsuser -ppassword --host=RDS-endpoint --database=dbname < dump.sql

6.Next we need the masters binary log co-ordinates .During replication slave start processing events in the binary log from this point.

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin.000001 | 107 | | |

Now, login into the RDS instance to configure the slave.

7.Configure RDS to use the EC2 instance as its replication master
Login to RDS database and run the following command with the private ip of your ec2 instance, your replication username, password,your binary log name and co-ordinates.

call mysql.rds_set_external_master('',3306,'rep1','password','mysql-bin.000001',107,0);

8.Start replication with the command.

call mysql.rds_start_replication;
Confirm replication is happening by running the command


Look for

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

9.Stop replication with the command.

call mysql.rds_stop_replication;

10.Once stopped you can configure a new replication master as follows.
First reset the external mysql master using the command.

call mysql.rds_reset_external_master;

Then set a new master using the mysql.rds_set_external_master command.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s