Migrate data from a MySQL database running on an Amazon EC2 instance to an Amazon Aurora RDS instance.

Services used in this lab

AWS Database Migration Service : AWS Database Migration Service helps you migrate databases to AWS easily and securely. With AWS Database Migration Service, the source database remains fully operational during the migration, minimizing downtime to applications that rely on the database. The AWS Database Migration Service can migrate your data to and from the most widely used commercial and open-source databases. The service supports homogenous migrations such as Oracle to Oracle, as well as heterogeneous migrations between different database platforms, such as Oracle to Amazon Aurora or Microsoft SQL Server to MySQL.

Amazon Aurora: Amazon Aurora is a fully managed, MySQL-compatible relational database engine that combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases. Amazon Aurora provides up to five times better performance than MySQL with the security, availability, and reliability of a commercial database at one tenth the cost.

Amazon Elastic Compute Cloud (Amazon EC2): Amazon EC2 is a web service that provides resizable compute capacity in the cloud. It is designed to make web-scale cloud computing easier for developers. Amazon EC2 reduces the time required to obtain and boot new server instances to minutes, allowing you to quickly scale capacity, both up and down, as your computing requirements change.

Amazon RDS: Amazon Relational Database Service (Amazon RDS) makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while managing time-consuming database administration tasks, freeing you up to focus on your applications and business. Amazon RDS provides you with six familiar database engines to choose from, including Amazon Aurora, Oracle, Microsoft SQL Server, PostgreSQL, MySQL and MariaDB.

Additional services and tools
MySQL Workbench: MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. MySQL Workbench is available on Windows, Linux and Mac OS X.

Migration Steps

Step 1: Connect to your Amazon EC2 instance

• Open the AWS Systems Manager console.
• In the navigation pane at the left of the page, under Node Tools, choose Fleet Manager.
• Select the instance on which MySQL is running.
• From the Node actions drop-down list, choose Connect and then choose Connect with Remote Desktop.
• For preferred Authentication type choose Key pair.
• For Key pair content, choose the following option
• Browse your local machine to select the key pair file.
• Select Choose file to upload the PEM key from your local directory that is associated with your instance.
• Select Connect.

Step 2: Configure and connect to your source MySQL database

• On your remote desktop, open (double-click) mysql-connector-net-8.4.0.msi and complete the installation.
• On your remote desktop, open (double-click) mysql-installer-community-8.0.37.0.
• On the Choosing a Setup Type page:Select Custom
• On the Select Products page, expand and select each of the products below one at a time then choose the right arrow to move them to the right window.
• MySQL Servers: MySQL Server 8.0.37 - X64
• Applications: MySQL Workbench - 8.0.36 - X64
• On the Installation page, choose Execute.

This install the server and workbench.

• Continue until you get to the Accounts and Roles page.
• On the Accounts and Roles page, configure:
  • MySQL Root Password
  • Repeat Password
• Choose Add User , then configure:
  • Username: admin
  • Password:
  • Confirm Password:

Continue till Apply Configuration and select execute.
This configures and starts your source SQL server.

Step 3: Connect and configure your source MySQL server

• choose the Local Instance MySQL80 box to connect.
• On the Please enter password for the following service popup, configure:
  • Password:
• Select Save password in vault

In the Query 1 window, enter:
CREATE DATABASE mydb;

• Import data into your database by choosing Server > Data Import.
• In the Import from Disk window:
• Choose the ellipsis
• Browse to and choose the dumps folder if it is not already selected.
• In the Import Progress window:
• Choose Start Import.

The data is imported into your database.

Step 4: Verify your data was imported

In the left navigation pane, choose the refresh icon next to SCHEMAS.You should see your mydb database.
At the top of the screen, choose the Query 1 tab, then:
Delete the existing Query
Run the following query:
Select * from mydb.employee;

Step 5: Use MySQL Workbench to connect to your RDS instance

• Open your Ec2 instance in a different tab and also open you cluster endpoint in another tab.
• In you Ec2 instance terminal run the below command
• `cd C:\Users\Administrator\Desktop\
• echo "labstack-c75a29ae-b79e-45ef-be37-37a-auroracluster-qxcm3vw430qn.cluster-cjug6kxfhyno.ap-southeast-2.rds.amazonaws.com" > ClusterEndpoint.txt`
• Return to the Fleet Manager - Remote Desktop browser tab and on your remote dekstop, open the ClusterEndpoint.txt file that you created in the previous step.
• Now go to the MySQL Workbench and at the top of the screen, choose the Home button.
• Create a new MySQL connection by choosing the plus button.
• In the Setup New Connection window, configure the following:
  • Connection Name: Aurora
  • Hostname: Delete 127.0.0.1. Copy the ClusterEndpoint from the text file named ClusterEndpoint.txt that you opened in the earlier step and paste in the window.
  • Username: admin
  • Choose Store in Vault…
  • Password: paste the DBPassword value

• Choose Test Connection.
  
  
  
  

• Complete setup and the window returns you to MySQL Workbench. You should now see two connections.

• In the MySQL Workbench menu, choose Aurora to connect to the database.

• The SQL Editor window opens, showing a successful connection to the database.

• Execute the following query to verify that no data exists in your Aurora instance.

Select * from mydb.employee;
It should retrun result that the table does not exist.

Step 6: Migrate your source MySQL database to your Aurora instance using AWS Database Migration Service

Create your replication instance

• The first step in migrating data using AWS Database Migration Service is to create a replication instance. An AWS DMS replication instance runs on an Amazon Elastic Compute Cloud (Amazon EC2) instance. A replication instance provides high availability and failover support using a Multi-AZ deployment.

• AWS DMS uses a replication instance that connects to the source data store, reads the source data, and formats the data for consumption by the target data store. A replication instance also loads the data into the target data store. Most of this processing happens in memory. However, large transactions might require some buffering on disk. Cached transactions and log files are also written to disk.

• In the AWS Management Console, go to Database Migration Service.

• In the left navigation pane, choose Replication instances.

• Choose Create replication instance then configure:

• Name: replicationInstance

• Description - optional: replicationInstance

• Instance class: dms.t3.micro

• High Availability: Dev or test workload(Single-AZ)

• Virtual private cloud (VPC) for IPv4: Lab-VPC

• Public accessible: Deselect

• Choose Create replication instance.

• Wait for the status of your replication instance to display Available.
  
  
  
  

Step 7: Create your source endpoint

An endpoint provides connection, data store type, and location information about your data store. AWS Database Migration Service uses this information to connect to a data store and migrate data from a source endpoint to a target endpoint. You can specify additional connection attributes for an endpoint by using extra connection attributes. These attributes can control logging, file size, and other parameters; for more information about extra connection attributes, see the documentation section for your data store.

• In the left navigation pane, choose Endpoints.
• Choose Create endpoint then configure:
  • Source endpoint
  • Endpoint identifier: MySQL
  • Source engine: MySQL
  • Access to endpoint database: Provide access information manually
  • Server name: input WindowsPrivateIP
  • Port: 3306
  • User name: admin
  • Password: copy and paste the DBPassword
  • Expand Test endpoint connection (optional), then configure:
  • VPC: Lab-VPC
• Choose Run test Once your test is successful, choose Create endpoint.

Step 8: Create your target endpoint to your Aurora instance

At the top of the screen, choose Create endpoint , then configure:

• Select Target endpoint
• Check Select RDS DB instance
• For RDS Instance, select the RDS instance that appears.
• In the Endpoint configuration section, configure:
  • Endpoint identifier: aurora
  • Access to endpoint database: Provide access information manually
  • User name: admin
  • Password:paste the DBPassword
• Expand Test endpoint connection, then configure:
• VPC: Lab-VPC
• Choose Run test
• Once your test is successful, choose Create endpoint.

Step 9: Create a database migration task

An AWS Database Migration Service (AWS DMS) task is where all the work happens. You use tasks to migrate data from the source endpoint to the target endpoint, and the task processing is done on the replication instance. You specify what tables and schemas to use for your migration and any special processing, such as logging requirements, control table data, and error handling.

• In the left navigation pane, choose Database migration tasks.
• Choose Create Database migration task then configure:
  • Task identifier: MySQL-Aurora
  • Replication instance: Select your replication instance
  • Source database endpoint: mysql
  • Target database endpoint: aurora
  • Migration type: Migrate
• Under Table mappings, choose Add new selection rule and then configure:
  • Schema: Enter a schema
  • Source name: mydb
• In the Premigration assessment section, uncheck Turn on premigration assessment.
• Choose Create database migration task.
• Wait for the Status of your task to change from running to Load complete.
• Choose your mysql-aurora task.
• Choose the Table statistics tab. Caution: This shows the table statistics for your database migration task. You should see that 3 tables were loaded.

Step 10: Verification of Migration

Return to the Fleet Manager - Remote Desktop browser tab.
In MySQL Workbench, on the Aurora connection tab, Execute :

Select * from mydb.employee;

Above query should return data confirming the successful usage of the AWS Database Migration Service to migrate data from your source MySQL server to your target Aurora instance.

Conclusion

Connected to a pre-created Amazon EC2 instance
Configured MySQL Server as your source database for migration
Connected to a pre-created Amazon Aurora instance
Migrated data from your MySQL server to your Aurora instance
Verified that your migration was successful