Migrate from External MySQL to AWS RDS

To import data from a MySQL database that is running external to Amazon RDS, you can configure replication from that database to a MySQL RDS instance. The external MySQL database you are importing from can be running either on-premises in your data center, or in an Amazon EC2 instance. The MySQL RDS instance must be running either MySQL 5.5 version 5.5.33 or later, or MySQL 5.6 version 5.6.13 or later. Using replication to import the data reduces downtime.

The following list shows the steps to take. Each step is discussed in more detail in later sections.

1. Prepare a MySQL RDS instance that is either MySQL 5.5 version or later, configure it to be a read replica of the MySQL instance running external to Amazon RDS.
2. Configure the MySQL instance running external to Amazon RDS to be the replication source.
3. Use mysqldump to import the database from the MySQL instance running external to Amazon RDS into the Amazon RDS instance.
4. Start replication to the MySQL RDS instance to capture updates made since the mysqldump files were created. Because the source MySQL DB instance is not in Amazon RDS, you do not use the standard Amazon RDS process to create a replica, you instead use stored procedures to establish replication.
5. After the import completes, point the applications to the MySQL RDS instance. Stop replication by running stored procedures that terminate the replication process. After the stored procedures are run, the replica runs as a standard MySQL DB instance without any need to promote the instance.

Create MySQL RDS Instance :

• Select RDS Instance type that can handle your production workload and configure it by the DB parameter group.
• Add rules in the ACL to allow connection from the external non  AWS MySQL server.

Prepare MySQL Master Server :

• Set up the server id in the my.cnf file and that should be unique and cannot match any other server-id in your replication group.

server-id               = 1

• Next step is to set log_bin file path, this is where the real details of the replication are kept. The slave is going to copy all of the changes that are registered in the log

log_bin                 = /var/log/mysql/mysql-bin.log

• Now we need to select the database that will be replicated on the slave server. You can include more than one database by repeating this line for all of the databases you will need.

binlog_do_db            = database_name

• Restart MySQL in last to apply all changes that we have made in my.cnf file.

sudo service mysql restart

• Login on mysql and create a replication User and grant permission to that user.

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

• Now we make a write Lock on the database so that no write operation can perform on all database till we take the complete dump of DB.

USE database_name;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

You will get a table as output of this command that contains bin_log file name, bin_log position and Database name, bin_log is the position from where slave start replication.

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | database_name  |                |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysqldump -u root -p --opt database_name > database_name.sql;
UNLOCK TABLES;

Start Replication:

• Source Database dump from the non AWS MySQL server to RDS Instance with using MySQL source command.
• Run the mysql.rds_set_external_master procedure on the MySQL DB instance to configure it as a read replica of the MySQL instance running external to Amazon RDS. Specify the connection information for connecting to the MySQL instance running external to Amazon RDS, the replication user credentials, and the binary log location.

call mysql.rds_set_external_master('Sourcedb.com',3306,'Slave_user','Password','mysql-bin.000001',107,0);

• Run the mysql.rds_start_replication procedure on the Amazon RDS instance to start the replication process.

CALL mysql.rds_start_replication;

Run the MySQL SHOW SLAVE STATUS statement on the Amazon RDS instance to verify that it is operating as a read replica.  

Show Slave Status;

Stop Replication : 

• After the databases have been replicated and you have verified the databases are synchronized, change all applications and services to connect to the MySQL DB instance. Run the mysql.rds_stop_replication procedure to stop the replication process.

CALL mysql.rds_stop_replication;

• Verify that the applications and services are running correctly. Run the mysql.rds_reset_external_master procedure to remove the replication configuration.

CALL mysql.rds_reset_external_master;