Database Migration-2: Migrating MySQL Database to Amazon RDS by using AWS Database Migration Services (AWS DMS).
We will migrate a MySQL Database of a real web application to Amazon RDS by using AWS DMS. To do this, firstly, we will create a new “RDS MySQL Database” for the target Database. Then, we will create a replication instance, target and source endpoints, and a migration task. When we run the migration task, we will migrate the MySQL database to the Amazon RDS MySQL Database that we have just created. Finally, we will monitor our database migration by using MySQL Workbench, AWS DMS, and Amazon Cloudwatch logs. We will do all the subjects practically (Hands-on) and step by step.
Topics we will cover in this article:
- Migrating MySQL Database to Amazon RDS by using AWS DMS
1. a. Creating an RDS MySQL Database for the target Database
1. b. Creating a Replication Instance
1. c. Creating Target and Source Endpoints
1. d. Creating and Running a Migration Task - Monitoring and Checking The Migration Task’s Output
2. a. By Using MySQL Workbench 8.0 CE
2. b. By Using Amazon Cloudwatch Logs
2. c. By Using AWS DMS - As a result
- Next post: “Database Migration-3: Migrating a MySQL DB to The Amazon RDS by using S3 bucket and MySQL dump”
- References
If you like the article, I will be happy if you click on the Medium Follow button to encourage me to write more, and not miss future articles.
Your clap, follow, or subscribe, they help my articles to reach the broader audience. Thank you in advance for them.
In the previous article of this “Database Migration” series, we learned the database migration strategies, terms, and solutions. Then we examined the AWS Database Migration Service in detail. Finally, we looked at the types of migration/transfer services in AWS and other most popular Data Migration Tools.
1. Migrating MySQL Database to Amazon RDS by Using AWS DMS
In this hands-on, we are migrating from MySQL to MySQL. Thus, there is no requirement for SCT(Schema conversations tool).
Note: (Optional) Turn on logging with Amazon CloudWatch. Amazon CloudWatch logs can alert you to potential issues when migrating. For more information, see Monitoring replication tasks using Amazon CloudWatch. I will turn on logging in with Amazon CloudWatch while I doing this hands-on.
1. a. Creating an RDS Database for the target Database
We will create a new “RDS MySQL Database” for the target Database on the AWS Cloud. Thus, when we run the migration task, we will migrate the MySQL database of a real web application to this Amazon RDS MySQL Database that we will create.
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/. Create a database with the following values and steps;
Engine options: MySQL
- Version : 5.7.39
- Templates: Free tier
- DB instance identifier: target-database
- Master username: root
- Master password: petclinic
- Public access: Yes
- Initial database name: petclinica. In the upper-right corner of the Amazon RDS console, choose the AWS Region in which you want to create the DB instance. In the navigation pane, choose Databases. Choose “Create database”, as shown in Figure 1.
b. then choose “Standard create”. For Engine type, choose MySQL, as shown in Figure 2.
c. For Version, choose the engine version “MySQL 5.7.39”, as shown in Figure 3.
d. In Templates, choose the “Free tier” :-) as shown in Figure 4.
We chose the free tier because we are doing it for trial purposes right now and it doesn’t cost too much, but under normal conditions, we should have chosen Production.
e. To enter your master password, do the following, as shown in Figure 5.
- DB instance identifier:
target-database - In the Settings section, open Credential Settings;
- Change the Master username value :
root - Enter the same password as the Master password :
the database password value you useand Confirm the password.
CPU: choose db.t3.micro, as shown in Figure 6.
Choose Storage type: General Purpose SSD (gp2) and Allocated storage: 20 Gib, as shown in Figure 7.
Storage autoscaling (Provides dynamic scaling support for your database’s storage based on your application’s needs.) We didn’t choose it because we’re doing it for testing purposes right now. The best practice is to choose this in a real environment.
In the connectivity section, we selected only public access as “Yes” and left all other settings as default, as shown in Figure 8.
Create a Security Group named “mysql-sg” and make sure the port “3360” is open in the inbound rule, set it if not open. Choose “mysql-sg” Security Group, as shown in Figure 9.
In Additional configuration, enter;
Initial database name: “petclinic”, as shown in Figure 10.
Enable automated backups: Remove the “tick” here as we are working for trial purposes. The best practice is to choose this in a real environment.
We left the other settings as default. Click on the Create databasebutton.
The database started to be created, as shown in Figure 11.
RDS MySQL Database named target-databasewas created, as shown in Figure 12.
1. b. Create a Replication Instance
Open the AWS DMS console, and choose Replication Instances from the navigation pane. Then choose to “Create a replication instance”, as shown in Figure 13.
Enter the following values in the window that opens, as shown in Figure 14.
Name: cumhur-vet-clinic-database
Descriptive Amazon Resource Name (ARN): DMS-replication-instance-cumhur
Description - optional: Dearing veterinarian clinic database
Instance class: dms.t3.medium #Note: Choose an instance class that's sufficient for your migration workload. If the instance isn't sufficient for your workload, you can modify the replication instance later.
Engine version: 3.5.1
High Availability: Dev or test workload (Single-AZ)
Allocated storage (GiB): 20
Network type: IPv4
Virtual private cloud (VPC) for IPv4 and Subnet: Choose your VPC and Subnet
Public accessible: Ticked
Advanced settings (Availability zone): Choose your AZ
VPC security groups: Choose your SG (open port 3360) #Note: In the Advanced security and network configuration section. For the VPC security group(s) configuration, choose the same security group that you attached to your Amazon RDS database. This allows your replication instance to access your Amazon RDS database.
AWS KMS key: aws/dms
After you click on the “Create replication instance” button, AWS provisions your replication instance. It shows a “Creating” status while AWS provisions and initializes your instance, as shown in Figure 15.
When your replication instance is ready to run, its status is “Available”, as shown in Figure 16.
Note: While you are waiting for your replication instance to be available, go to the Security Groups section in the Amazon EC2 console. You need to check whether your security group allows your replication instance to access your database. In the Security Groups section, find the security group you attached to your MySQL database instance and your replication instance, and choose it. (1)
If your security group has an existing rule that allows for access to your MySQL database instance from the “IP address”, as shown in Figure 17.
Remove the existing “IP address” and enter the “name of the security group” used for your Amazon RDS database instance and replication instance. Choose “Save rules” to save the updated rules for your security group, as shown in Figure 18.
1. c. Creating Target and Source Endpoints
Target endpoint
First, create the endpoint for your target database. This is the database that you have just created in Amazon RDS.
Navigate to the Endpoints section of the AWS DMS console. Choose “Create endpoint” to create a new endpoint, as shown in Figure 19.
Enter the following values for the target endpoint, as shown in Figure 20.
In the “Username and password” boxes; enter the “Username and password” of the database that you will connect to it.
Click on the “Run test” button to test the target endpoint. After a few seconds, you should see a Status of successful. After seeing the status value is “successful”, click on the “Create endpoint” button, as shown in Figure 21.
The target database endpoint was created successfully, as shown in Figure 22.
Source endpoint
Follow these same steps again to create an endpoint for your source database, as shown in Figure 28. Repeat the same in this step, click on the “Run test” button to test the target endpoint. After a few seconds, you should see a Status of successful. After seeing the status value is “successful”, click on the “Create endpoint” button, as shown in Figure 23.
The target and source database endpoints were created successfully, as shown in Figure 24.
Note: You also need to ensure that your replication instance has network access to your source database. If your source database is hosted on Amazon EC2, allow traffic from your replication instance security group into the source database security group. If your source database is not hosted on Amazon EC2, you need to handle the network settings according to the location of your source database. (2)
1. d. Creating a Migration Task
A replication task is responsible for migrating data from a source database to a target database. In our hands-on, we are moving data from an existing database to our newly created database in Amazon RDS.
In order to start, navigate to the Database migration tasks section of the AWS DMS console. Choose “Create task” to create a new Database migration task, as shown in Figure 25.
Enter the following values for the Database migration task page, as shown in Figure 26.
Choose the source endpoint for your existing database (for me, “petclinic”) and your target endpoint that you have just created in Amazon RDS (for me, “target-database”).
In the “Migration type” section; select the “Migrate existing data” to perform a one-time process to copy data from your source database to your target database. Use migration type one-time migrations.
“Migrate existing data and replicate ongoing changes ”. Use this to migrate large databases with minimal downtime.
“Replicate data changes only”, use migration type when you have already migrated existing data and want to synchronize ongoing change.
To check and see the Cloudwatch logs, tick the “Turn on Cloudwatch logs” box.
In the “Table mappings” section, tell AWS DMS which tables to copy. Enter the name of the schemas and tables you want to copy. You can use % as a wildcard character to copy multiple tables or schemas.
Finally, click on the “Create task” button to create the migration task, as shown in Figure 26.
The migration task started to migrate Databases, as shown in Figure 27.
The migration task was completed successfully Database migration, as shown in Figure 28.
2. Monitoring and Checking The Migration Task’s Output
We have a real database running on the web belonging to the veterinary clinic web application, as shown in Figure 29.
We migrated this real MySQL Database (which belongs to the veterinary clinic web application) to Amazon RDS (which we created as a target database) by using AWS DMS.
Now, we will see the data in the target database, thanks to MySQL Workbench 8.0. When we examined it, we saw that they have the same values as the MySQL Database belonging to the veterinary clinic web application.
2. a. By Using MySQL Workbench 8.0 CE
Open MySQL Workbench, and then choose the ⊕ sign beside MySQL Connections to set up a new connection. In the Setup New Connection dialog box, enter a name for your connection. In the Parameters section, enter these details:
Hostname: Enter the RDS endpoint (target-database RDS endpoint).
Port: Enter the Port number (3306). Then, click on the “Test Connection”, then “OK”, as shown in Figure 29.
Database connections will appear on the MySQL Workbench main page, as shown in Figure 30. (3)
Click on the “RDS target database”, navigate to “petclinic” in the Shemas section. Here, we can see “customer records” in the Petclinic Database, as shown in Figure 31.
2. b. By Using Amazon Cloudwatch Logs
Navigate to the “Overview details” section, and then click on the “View Cloudwatch Logs”, as shown in Figure 32.
We can see logs in “Log events”. The “JI” indicates “Information”, The “JE” indicates “Error”, and The “JW” indicates “Warning”, as shown in Figures 33–34–35.
There are no “errors”, as shown in Figure 35.
2. c. By Using AWS DMS
Navigate to the “Table statics” section, as shown in Figure 36.
We can see migration info “Table statistics”, as shown in Figure 37.
Also, we can check the progress of data migration as a percentage in the “Database migration task” tab, “progress” section, as shown in Figure 38.
3. As a result
We migrated a MySQL Database to Amazon RDS by using AWS DMS. To do this, we created a target “RDS MySQL Database”, a replication instance, target and source endpoints, and a migration task. Finally, we monitored our database migration by using MySQL Workbench and Cloudwatch logs.
If you liked the article, I would be happy if you click on the Medium Following button to encourage me to write and not miss future articles.
Your clap, follow, or subscribe, they help my articles to reach the broader audience. Thank you in advance for them.
For more info and questions, please contact me on Linkedin or Medium.
4. Next post
In the next post, “Database Migration-3: Migrating a MySQL DB to The Amazon RDS by using S3 bucket and MySQL dump”.
We will use the Native Backup method, and migrate a MySQL Database to the Amazon RDS by using an Amazon S3 bucket and MySQL dump. Monitoring our database migration by using MySQL Workbench and Cloudwatch logs. We will do all the subjects practically (Hands-on) and step by step.
Happy Clouding…
Don’t forget to follow my Medium account to be informed about new articles.
