Simulating Database Migration with AWS DMS

I came across Adrian Cantrill's mini AWS projects on Reddit and decided to give it a try. There are about twelve projects on his Github repo currently and I figured I'll try doing all of them.

The very first project that caught my attention was the AWS DMS Database Migration. The task is to migrate a simple web application (WordPress) from an on-premises environment into AWS. The on-premises environment is a virtual web server (simulated using EC2) and a self-managed MariaDB database server (also simulated via EC2).

The very first step was infrastructure creation. A CloudFormation template was provided, I just had to click the link to create the base infrastructure.

The next thing was network connectivity. Since the project was to simulate database migration, I created a VPC peering connection between my on-premises and AWS VPCs with the requester VPC being the on-premises VPC and the accepter being the AWS VPC as shown in the image below. After doing that, I created routes on the on-premises and AWS route tables to enable data transfer between both environments.

The next stage of the project was to create & configure the AWS side infrastructure. I created an RDS database (MariaDB), an EC2 instance, installed WordPress requirements on my instance, and migrated the WordPress content over from the catWeb instance which was serving as the on-prem server into my newly created awsCatWeb instance.

Stage four introduced the DMS service. This part took me longer to figure out because I had no prior experience with DMS. I had to create a DMS subnet group, replication instance, source endpoint, target endpoint, and migration task.

• The replication instance connects to your source data store, reads the source data, and formats the data for consumption by the target data store.
• The source endpoint allows AWS DMS to read data from a database (on-premises or in the cloud), or from other data sources such as Amazon S3. My source was the CatDB EC2 instance created by the CloudFormation stack. I kept getting connection failed for my target endpoint because I apparently did not accept the VPC peering connection I created at the beginning of the project.
• The target endpoint allows AWS DMS to write data to a database, or to other data sources. My target endpoint was the RDS instance I created earlier.
• The migration task is where all the work happens. You specify what tables (or views) and schemas to use for your migration and any special processing. I selected my replication instance, source database endpoint, target database endpoint, and the migration type I wanted and waited for the task to run till the status said 'Load Complete'. At that point, data had been migrated into my RDS database instance.

I changed the DB_HOST on my awsCatWeb instance to the endpoint of my RDS instance and ran the script below to update the WordPress database with the new instance DNS name.

source <(php -r 'require("/var/www/html/wp-config.php"); echo("DB_NAME=".DB_NAME."; DB_USER=".DB_USER."; DB_PASSWORD=".DB_PASSWORD."; DB_HOST=".DB_HOST); ')
SQL_COMMAND="mysql -u $DB_USER -h $DB_HOST -p$DB_PASSWORD $DB_NAME -e"
OLD_URL=$(mysql -u $DB_USER -h $DB_HOST -p$DB_PASSWORD $DB_NAME -e 'select option_value from wp_options where option_id = 1;' | grep http)
HOST=$(curl http://169.254.169.254/latest/meta-data/public-hostname)
$SQL_COMMAND "UPDATE wp_options SET option_value = replace(option_value, '$OLD_URL', 'http://$HOST') WHERE option_name = 'home' OR option_name = 'siteurl';"
$SQL_COMMAND "UPDATE wp_posts SET guid = replace(guid, '$OLD_URL','http://$HOST');"
$SQL_COMMAND "UPDATE wp_posts SET post_content = replace(post_content, '$OLD_URL', 'http://$HOST');"
$SQL_COMMAND "UPDATE wp_postmeta SET meta_value = replace(meta_value,'$OLD_URL','http://$HOST');"

The final thing was copying the public IP address of my instance and loading it on the browser which showed the application now pointed at my RDS instance after a full migration.

Final Words

The project was worthwhile. I got to work with the Database Migration Service and I'm pretty stoked. Looking forward to when I handle real-world migration🤞🏻.