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

[mysqld]
log-bin=mysql-bin
server-id=1
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';
GRANT REPLICATION SLAVE ON *.* TO 'rep1'@'%';

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.

FLUSH TABLES WITH READ LOCK;

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.

SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| 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

SHOW SLAVE STATUS\G

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.

WRITTEN BY CloudThat

CloudThat is a leading provider of cloud training and consulting services, empowering individuals and organizations to leverage the full potential of cloud computing. With a commitment to delivering cutting-edge expertise, CloudThat equips professionals with the skills needed to thrive in the digital era.