Step-by-step Database Migration to AWS RDS using native MySQL tools

Smoothly Transition Your MySQL Database to AWS Cloud

Photo by C Dustin on Unsplash

Sections:

1. Key concepts
2. Configure the infrastructure
   – Create a VPC
   – Create an Amazon RDS DB instance
   – Setup an EC2 bastion host
   – Connect EC2 to RDS
3. Migrate the data
   – Export the data from the local db
   – Upload the dump files to S3
   – Download the dump files to EC2
   – Import the dump files to RDS

Database migration can be a daunting task, especially when it comes to moving data from an on-premises environment to the cloud. However, with the right tools and strategies, it can be a smooth and straightforward process. In this article, we’ll take a look at how to migrate a MySQL database from an on-premises environment to the Amazon Web Services (AWS) cloud using MySQL native tools. We’ll go through the steps involved in setting up the AWS environment, preparing the on-premises database, and executing the migration itself, so you can follow along and migrate your own database with ease.

I will be outlining the process of database migration based on the AWS Prescriptive Guidance, which can be accessed via the following link:

Migrate an on-premises MySQL database to Amazon RDS for MySQL

If the database in question allows for downtime, then the database migration process using MySQL native tools is a good and relatively simple approach. However, it’s important to note that if your database cannot afford any downtime, then alternative migration methods that involve minimal to no downtime may be more suitable for your needs.

I will also be documenting database migration using a CDC tool that enables migration with minimal downtime. Stay tuned on my next post for this!

Key concepts:

Before diving into the migration process, it’s important to first define some key concepts we will be using later:

• RDS: Amazon Relational Database Service is a fully managed web service that simplifies the process of setting up, operating, and scaling a relational database in the cloud, with support for popular database engines like MySQL, PostgreSQL, and SQL Server.
• VPC: Amazon Virtual Private Cloud is a secure and flexible service that allows you to create your own isolated virtual network within the AWS cloud, complete with customizable IP ranges, subnets, and routing tables.
• S3: Amazon Simple Storage Service is a highly scalable and cost-effective cloud storage service that offers industry-leading durability, availability, and performance, allowing you to store and retrieve data from anywhere on the web.
• EC2: Amazon Elastic Compute Cloud provides resizable compute capacity in the cloud, allowing you to easily launch and manage virtual servers in a wide range of configurations, with full control over security, networking, and storage.
• AZ: Amazon Availability Zone is a geographically separate and redundant physical location within an AWS region, designed to provide high availability and fault tolerance for your applications and services, even in the event of a data center outage or disaster.
• Bastion Host: A bastion host, also known as a jump host, is a special-purpose server that is designed to provide secure access to your virtual private cloud (VPC) instances using SSH (Secure Shell) or RDP (Remote Desktop Protocol) connections. The bastion host acts as a single entry point into your VPC, and is configured with strong security controls to prevent unauthorized access.
• Security Group: A security group is a virtual firewall that controls inbound and outbound traffic to and from AWS resources, such as EC2 instances and RDS databases.

Here’s the data migration architecture:

Migrating data using native MySQL Tools (from AWS Prescriptive Guidance)

In summary, to migrate a database to RDS using MySQL native tools, we first need to create an RDS DB instance and configure the appropriate network settings. After creating the DB instance, we will use the MySQL dump command to export the database from our on-prem or local machine. We can then use the MySQL import command to import the database into the RDS instance with the help of EC2.

Note: All the steps below are performed using Free Tier account.

Configure the infrastructure

Create a VPC

To begin the database migration process from on-premises to AWS cloud, we first need to configure the Virtual Private Cloud (VPC), which allows us to launch resources into a virtual network and provides complete control over the virtual networking environment.

To do this, sign in to the AWS Console, navigate to the Services section, and search for VPC. Once the VPC Dashboard loads, click on “Create VPC” and specify a VPC name and the IPv4 CIDR block based on the number of IPs you want to provision. For example, you can set 10.0.0.0/24 to provision 256 IPs.

For RDS, it is required to configure at least two availability zones, even if you plan on using a single availability zone for the database. This is because RDS needs to prepare the network in case you decide to implement a Multi-AZ setup in the future. Additionally, you must choose the availability zones and determine the number of public and private subnets needed for your VPC setup.

You can choose to use NAT gateways, VPC endpoints, and DNS options. These settings can be configured as shown below. Once you have configured the necessary options, click on “Create VPC” to create your virtual private cloud.

This is the VPC network architecture we’ve just created. You can see that the route tables and network connections are also automatically configured.

Create an Amazon RDS DB instance

The next step in the database migration process is to create an RDS DB instance to where the data will be migrated. However, before creating the DB instance, we must first create a subnet group.

To create a subnet group, go to the RDS Management Console and click on “Subnet groups” in the left navigation pane. An RDS Subnet Group is a collection of subnets that can be designated to an RDS database instance in a VPC. From there, click on “Create DB subnet group” and specify a name and description. Then, select the previously created VPC.

Select the 2 availability zones we used in the VPC setup earlier, as well as the 2 private subnets. We will only allow the RDS db instance to run in the private subnets to secure the database from external access. Then, click “Create”.

Once the RDS subnet group is created, we can now proceed to create the MySQL RDS database instance. We will create a MySQL RDS DB instance with a Single-AZ deployment setup for this tutorial. However, it is recommended to use a Multi-AZ setup to provide high availability and fault tolerance.

To create the database instance, go to the RDS Management Console, click on “Dashboard”, and select “Create database”. Then choose the engine type based on your on-prem or local setup. Here, we choose “MySQL”. You can also choose the latest version as the engine version.

In the Templates section, you can choose the “Free Tier” option. However, as mentioned earlier, if you’re using this setup for production databases, choose the “Production” option.

Set a DB instance identifier, and then enter the master username and password.

We will select the DB instance class that is covered in the Free Tier for this tutorial, i.e. “db.t3.micro”, but you should ultimately choose the DB instance class that best suits your needs. Also, choose storage options accordingly, but we can leave those options to the default values.

At the moment, we do not need to connect an EC2 resource, so choose that option. This will be done at a later step. Leave the network type as IPv4, and choose the previously created VPC in the VPC field.

Continuing on the connectivity section, for the subnet group, choose the one created in the earlier steps. Disable the public access, since we want the database to be private. Then, create a new VPC security group. The security group manages access to the RDS, especially for MySQL port 3306.

Set the options in the monitoring and additional configuration options as you see fit. For instance, we can enable enhanced monitoring, automated backups, encryption and auto minor version upgrade. We can also enable deletion protection.

Finally, click “Create database”. It may take a few minutes, just wait for the process to be completed. This is the created RDS DB instance:

Setup an EC2 bastion host

The next step after creating an RDS instance is to set up an EC2 bastion host to connect to it. To do this, navigate to the RDS instance page and scroll down to the “Connected compute resources section”. Click on “Set up EC2 connection” to proceed.

Next, click on “Create EC2 instance” which will open up a wizard for creating EC2 instances. In the wizard, you will need to specify a name for your instance, select an appropriate Amazon Machine Image (AMI), and choose an EC2 instance type. For this tutorial, we can select a Free Tier instance, which is “t3.micro”. After that, you need to select a key pair or create a new one and download the file for it. This key pair file is needed to be able to connect to EC2 from our local machines using SSH.

To change the default network settings, click “Edit” and select the VPC we created earlier. Choose a public subnet and enable auto-assign public IP to allow SSH connections from outside the VPC to this bastion host. Keep in mind that the public IP will change each time you stop the EC2 instance. If you require a static public IP, you can associate one using Elastic IP addresses.

Choose “Create security group”, and set a name. In the inbound security group, allow SSH connections from Source type “My IP”, or “Custom” and specify the IP manually in the Source field. This allows SSH connection to this EC2 from the specified IP only.

Leave the remaining options as the default and expand Advanced details. Scroll down to the bottom of the page and in the User data field, paste the following code:

#!/bin/bash
wget https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm -P ~/.
yum localinstall -y ~/mysql80-community-release-el9–1.noarch.rpm
yum install -y mysql

This script will install the mysql client automatically when the instance is launched. Finally, click “Launch instance”.

Wait for the EC2 instance to be created and for the status check to display “2/2 checks passed”. This process may take several minutes, so click refresh occasionally.

Take note of the public IP address and test the SSH connection to the EC2 instance. Also check if the MySQL client is installed properly. Here is how you can connect to the EC2 instance and test MySQL.

ssh -i <key_pair_file> ec2-user@<ec2-public-ip>
mysql -–version

Connect EC2 to the RDS

After creating the EC2 instance to use as a bastion host, we need to establish a connection between the EC2 and the RDS. To do this, we can go back to the RDS Management Console from the previous section, in the “Setup EC2 connection” wizard. Click the refresh button to load the newly created EC2, and select it. Then click “Continue”.

The next page will prompt the creation of security groups that will allow connections between the RDS and the EC2 instances, named “rds-ec2–1” and “ec2-rds-1”. Review the values and click “Confirm and set up” to proceed.

After this, the infrastructure is now ready for the data migration.

Migrate data

Export the data from the local DB

Now that we have the necessary infrastructure in place, we can begin the process of migrating our data from the on-prem or local database to the RDS DB instance we’ve created. In production databases, this should be done during the maintenance window, as we should stop receiving incoming data to the database at this point.

It is good to take note of the database statistics before proceeding with the migration.

USE employees;
SHOW TABLES;
SELECT 'employees' AS 'table_name', COUNT(*) FROM employees UNION
SELECT 'salaries' AS 'table_name', COUNT(*) FROM salaries UNION
SELECT 'dept_emp' AS 'table_name', COUNT(*) FROM dept_emp UNION
SELECT 'dept_manager' AS 'table_name', COUNT(*) FROM dept_manager UNION
SELECT 'departments' AS 'table_name', COUNT(*) FROM departments UNION
SELECT 'titles' AS 'table_name', COUNT(*) FROM titles;

Export the data from the client’s on-premises databases using mysqldump.

mysqldump -u <username> -p --databases employees > backup_employeesdb.mysql
tar -czvf backup_employeesdb.mysql.tar.gz backup_employeesdb.mysql

If transferring a sensitive data, it is also advisable to encrypt for additional security.

mysqldump -u <username> -p --databases employees | gzip | openssl enc -aes-256-cbc -md sha512 -pbkdf2 -iter 1000000 -salt -pass pass:<secret_phrase> > backup_employeesdb.xb.enc

Upload the dump files to S3

The next step is to upload the dump file to S3. To create an S3 bucket, go to the AWS S3 console and click the “Create bucket” button. Enter a unique name in the bucket name field. Then, in the Object Ownership section, disable ACLs to ensure that only the account can access the bucket and block all public access. Finally, keep all other settings as default and click “Create bucket”.

Navigate to the bucket you’ve just created and click the ‘Upload’ button. Add the dump files generated in the previous step, and click ‘Upload’ to transfer them to the S3 bucket.

To enable the EC2 instance to download data from the S3 bucket, we need to create an IAM role with S3 read access. Begin by accessing the IAM management console and selecting “Roles” from the navigation pane. Click on “Create role”, choose AWS service as the Trusted entity type, and select EC2 as the Use case. Click “Next” and search for the AmazonS3ReadOnlyAccess policy. Select it, and then proceed to the next step where you can enter a role name. Finally, click “Create role”.

Download the dump files to EC2

Map the created role to the EC2 instance. Go to the instances in the EC2 management console, and select the EC2 instance we’ve created earlier. Go to Actions > Security > Modify IAM role. Choose the newly created IAM role from the dropdown then click “Update IAM role”.

Connect again to the EC2 instance using SSH as shown earlier. Then connect to the RDS instance by specifying the RDS endpoint in -h parameter.

ssh -i <key_pair_file> ec2-user@<ec2-public-ip>
mysql -u admin -p -h prod-database-instance.cjmarukirhyb.eu-north-1.rds.amazonaws.com

The dump files in the S3 bucket can now be accessed from the SSH terminal of the EC2. To list and copy the files to the local directory of the EC2, use the following commands:

aws s3 ls s3://onprem-database-bkup
aws s3 cp s3://onprem-database-bkup/backup_employeesdb.xb.enc ~/.
ls -l backup_employeesdb.xb.enc

If you encrypted the files, you’ll need to decrypt them using the secret key that was used earlier. Then, extract the files and confirm that the decryption and/or extraction was successful.

openssl enc -aes-256-cbc -md sha512 -pbkdf2 -iter 1000000 -salt -pass pass:<secret_phrase> -d -in backup_employeesdb.xb.enc -out backup_employees.sql.gz
gzip -d backup_employees.sql.gz
ls -l backup_employees.sql
head backup_employees.sql

To avoid access restriction errors during the import process, it is recommended to remove the “DEFINER” clause from the SQL dump.

sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i backup_employees.sql

Import the dump files to RDS

The necessary infrastructure and files are now prepared, and we can proceed with the final step of importing the dump files to the RDS DB instance we created earlier. To do this, connect to the RDS DB instance from the EC2 terminal and create the database.

mysql -u admin -p -h prod-database-instance.cjmarukirhyb.eu-north-1.rds.amazonaws.com
CREATE DATABASE employees;
SHOW databases;

Import the dump files to the RDS databases by executing the following command.

mysql -u admin -p -D employees -h prod-database-instance.cjmarukirhyb.eu-north-1.rds.amazonaws.com < backup_employees.sql

Verify that the databases have been imported successfully and compare the table statistics with those of the on-prem/local database.

mysql -u admin -p -h prod-database-instance.cjmarukirhyb.eu-north-1.rds.amazonaws.com
USE employees;
SHOW tables;
SELECT 'employees' AS 'table_name', COUNT(*) FROM employees UNION
SELECT 'salaries' AS 'table_name', COUNT(*) FROM salaries UNION
SELECT 'dept_emp' AS 'table_name', COUNT(*) FROM dept_emp UNION
SELECT 'dept_manager' AS 'table_name', COUNT(*) FROM dept_manager UNION
SELECT 'departments' AS 'table_name', COUNT(*) FROM departments UNION
SELECT 'titles' AS 'table_name', COUNT(*) FROM titles;

Congratulations, you did it! You have successfully migrated your on-prem/local database to AWS RDS. With the help of this guide, you were able to create the necessary infrastructure, export and import data, and make necessary modifications to ensure a successful migration. With your database now in AWS, you can take advantage of its scalability and reliability, ensuring that your data is safe and always available.

Thank you for reading this guide, and I hope that you found it helpful in simplifying the migration process. If this guide has helped you, don’t hesitate to show your support by giving it a clap. Happy migrating!