MYSQL Data Migration Capabilities to RDS
RDS now allows you to set up a non-RDS mysql instance (on premises or EC2) as your master and vice versa.
This can be tested
Start an RDS instance.
Start an EC2 instance, and install mysql on it.
Configure the EC2 instance as a replication master.
Log in to the EC2 machine to edit the my.cnf and my.ini files.
Enter the following lines under the mysqld section
[mysqld]log-bin=mysql-binserver-id=1Restart the mysql server4.Create a user for replicationWe 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 ‘@’%’ IDIFIED BY the ‘password’;GRANT RESPLICATION SLAVE ON *. * TO’rep1 ‘@’%’;
5.Sync up slave data with master and make sure master is not under any updates.
FLUSH TABLES WITH READ LOCKING;
Now, do a mysqldump on the database.
mysqldump -u username -ppassword dbname > dump.sql
Transfer the contents to RDS.
mysql –u rdsuser –ppassword –host=RDS–endpoint –database=dbname
6. Next, we need the masters bin log coordinates. This is where replication slaves start processing events in binary log.
SHOW MASTER STATUS;+——————+———-+————–+——————+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+——————+———-+————–+——————+| mysql-bin.000001 | 107 | | |+——————+———-+————–+——————+
Log in to the RDS instance now to configure the slave.
7.Configure RDS for the EC2 instance to be its replication masterLogin with RDS database and run this command: The private ip of the ec2 instance, the password, your binary log name, and co-ordinates
8.Start replication using the command
call mysql.rds_start_replication;Confirm replication is happening by running the command
SHOW SLAVE STATUSG
Look out for
Slave_IO_Running: YesSlave_SQL_Running: Yes
9.Stop replicating with the command
10. Once you have stopped, you can set up a new replication master by using the command.
Then set a new master using the mysql.rds_set_external_master command.