Architecture & DesignMigrating a MySQL Database from CloudSQL To AWS RDS

Migrating a MySQL Database from CloudSQL To AWS RDS

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Google CloudSQL is a managed service for databases with support for MySQL and PostgreSQL. CloudSQL is integrable with some of the other Google Cloud Platform services, including data replication across multiple zones with automatic failover. In this article, we shall discuss migrating a MySQL database instance from Google CloudSQL to AWS RDS using Database Migration Service (DMS). This article has the following sections:

Setting the Environment

A Google Cloud Platform billing account is required. Google Cloud Platform Free Tier account provides a free trial for the first year. An AWS account is also required; this may be created at https://aws.amazon.com/resources/create-account/.

Creating a MySQL RDS DB Instance

Because we shall be migrating a MySQL database from CloudSQL to AWS RDS, we need to create a MySQL database instance on RDS. Navigate to RDS at https://aws.amazon.com/rds and, in the RDS wizard, select engine as MySQL, as shown in Figure 1.

Selecting MySQL Database Engine
Figure 1: Selecting MySQL Database Engine

In Production?, select Dev/Test, as shown in Figure 2.

Selecting Development & Test
Figure 2: Selecting Development & Test

In Specify DB Details, select the checkbox Only show options that are eligible for RDS Free Tier, as shown in Figure 3. Select License Model as general-public-license. Select DB Engine Version as MySQL 5.6.35. MySQL versions 5.5, 5.6, and 5.7 are supported for DMS target. Select DB Instance Class as db.t2.micro, which has a capacity of 1 vCPU and 1GB RAM. Select Multi-AZ Deployment as No. Select Storage Type as General Purpose (SSD), and allocated storage as 5 GB.

Specify DB Details
Figure 3: Specify DB Details

In database settings, specify a DB Instance identifier (mysqldb), Master Username, and Master Password, as shown in Figure 4. Click Next Step.

Specifying Database Settings
Figure 4: Specifying Database Settings

In Configure Advanced Settings, select a VPC, as shown in Figure 5. The DMS migration shall be created in the same VPC as the RDS DB instance. Set Availability Zone as us-east-1b. Select the option to Create new Security Group. In Database Options, specify a Database Name (mysqldb). Specify a Database Port (3306), and select a DB Parameter Group.

Configuring Advanced Settings
Figure 5: Configuring Advanced Settings

Most or all of the other settings—including Option Group, Enable IAM DB Authentication, Enable Encryption, Backup, and Monitoring—may be kept as the default (see Figure 6). Automatic backups are required to be enabled for MySQL on RDS as DMS source. Click Launch DB Instance.

Launch DB Instance
Figure 6: Launch DB Instance

A new DB Instance starts to get created, as shown by the message in Figure 7. Click View Your DB Instances to view the DB instances.

Your DB Instance is being created
Figure 7: Your DB Instance is being created

A new MySQL DB instance on RDS gets started, as shown in Figure 8.

MySQL DB Instance on RDS
Figure 8: MySQL DB Instance on RDS

Click the Security Group link, as shown in Figure 9, to modify the Inbound/Outbound rules to allow traffic from/to all IP addresses.

Security Group Link
Figure 9: Security Group Link

Select the Inbound tab and click the Edit button to modify the inbound rules, as shown in Figure 10.

Modifying Inbound rules
Figure 10: Modifying Inbound rules

In Edit Inbound rules, set Type to All Traffic, Protocol to All, Port Range to 0-65535, Source to Anywhere, and click Save (see Figure 11).

Edit inbound rules
Figure 11: Edit inbound rules

The modified Inbound rules are shown in Figure 12.

Inbound Rules
Figure 12: Inbound Rules

The Outbound rules are not required to be modified because they are already set to allow traffic to all destination IPs, as shown in Figure 13.

Outbound rules
Figure 13: Outbound rules

Connecting to MySQL Database on RDS

Obtain the RDS DB instance host from the Endpoint; the host address is the Endpoint with the port suffix :3306 removed. Connect to the MySQL database instance on RDS using the host address and port with the following mysql command to start a MySQL command line interface.

mysql --host=mysqldb.crbmlbxmp8qi.us-east-1.rds.amazonaws.com
      --port=3306 --user=dvohra --password=mysql5db

The host, user, and password would be different for different users. Grant MySQL account access to the DMS user with the following commands, in which the DMS user would be different for different users.

GRANT ALTER, CREATE, DROP, INDEX, INSERT, UPDATE, DELETE,
   SELECT ON mysqldb.* TO 'dvohra'@'%';
GRANT ALL PRIVILEGES ON awsdms_control.* TO 'dvohra'@'%';

Creating a Google Cloud Platform Project

You must create a Google Cloud Platform project to use CloudSQL. To create a project, click Create an empty project link on the Getting started page, as shown in Figure 14.

Create an empty project
Figure 14: Create an empty project

In the New Project wizard, specify a Project name (MySQLDB) and click Create, as shown in Figure 15.

New Project
Figure 15: New Project

A new project gets created, as shown in the Dashboard in Figure 16.

New Google Cloud Platform project
Figure 16: New Google Cloud Platform project

Creating a CloudSQL Instance

In this section, we shall create a CloudSQL instance. Navigate to the CloudSQL URL https://cloud.google.com/sql/ in a browser, as shown in Figure 17. Click View Console.

View Console
Figure 17: View Console

Select the MySQLDB project and click Create instance, as shown in Figure 18.

Create Instance
Figure 18: Create Instance

In the Create an instance wizard, select MySQL and click Next, as shown in Figure 19.

Choosing engine as MySQL
Figure 19: Choosing engine as MySQL

In Choose a MySQL instance type, click Choose Second Generation to select MySQL Second Generation instance type, as shown in Figure 20. Some of the benefits of the Second Generation instance type are also listed.

Choosing a Second Generation MySQL instance
Figure 20: Choosing a Second Generation MySQL instance

In Create a MySQL Second Generation instance (see Figure 21), specify an Instance ID and a Root password, and select a Region and Zone. Click Create.

Create a MySQL Second Generation instance
Figure 21: Create a MySQL Second Generation instance

A MySQL instance starts to get created, as shown in the Instances table in Figure 22.

CloudSQL MySQL Instance
Figure 22: CloudSQL MySQL Instance

Adding a Route for CloudSQL DB Instance IP Address to the VPC Route Table

We need to modify the Route Table for the VPC in which the RDS DB instance and DMS migration are created to allow access to the IP Address of the CloudSQL instance. First, obtain the IP address of the CloudSQL instance from the CloudSQL Console, as shown in Figure 23.

IP Address of the CloudSQL DB Instance
Figure 23: IP Address of the CloudSQL DB Instance

Click the Route table link, as shown in Figure 24, for the VPC in which the RDS DB instance is created and DMS shall be subsequently created.

Route Table link
Figure 24: Route Table link

Select the route listed and click Edit, as shown in Figure 25.

Route>Edit
Figure 25: Route>Edit

Click Add another route (see Figure 26).

Add another route
Figure 26: Add another route

Specify the IP address of the CloudSQL instance in Destination, as shown in Figure 27.

Specifying Destination as the IP Address of the CloudSQL instance
Figure 27: Specifying Destination as the IP Address of the CloudSQL instance

Select the Target as the Internet Gateway associated with the VPC, as shown in Figure 28. An Internet Gateway allows communication between the instances in a VPC and the Internet.

Selecting Target as the Internet Gateway
Figure 28: Selecting Target as the Internet Gateway

Click Save to save the route. A route gets added, as shown in Figure 29.

Route for CloudSQL Instance
Figure 29: Route for CloudSQL Instance

Creating a DMS Replication Instance

In this section, we shall create a replication instance for migrating a CloudSQL instance to RDS. Click Create migration in the Database Migration Service start page, as shown in Figure 30.

Create migration
Figure 30: Create migration

In the Welcome page, click Next (see Figure 31).

Welcome page>Next
Figure 31: Welcome page>Next

In the Create replication instance page, specify a name and description, as shown in Figure 32. Select an Instance Class as dms.t2.medium. Select Replication engine version as 2.2.1. Select the same VPC in which the RDS DB instance was created. Select Multi-AZ A No. Select the checkbox Publicly Accessible.

Configuring a Replication Instance
Figure 32: Configuring a Replication Instance

In Advanced, specify Allocated storage (50 GB), select a Replication Subnet Group, and select Availability zone as the same as for RDS DB instance, us-east-1b, as shown in Figure 33. Select the default VPC Security Group. Select the KMS master key created for DMS.

Configuring Advanced Options
Figure 33: Configuring Advanced Options

Select the default Maintenance settings and click Next, as shown in Figure 34.

Replication instance>Next
Figure 34: Replication instance>Next

The “Your replication instance is being created” message gets displayed, as shown in Figure 35. The source and target database endpoints may be configured only after the replication instance has been created.

Your replication instance is being created
Figure 35: Your replication instance is being created

When the replication instance has been created, the message “Replication instance created successfully” gets displayed (see Figure 36).

Replication instance created successfully
Figure 36: Replication instance created successfully

The Replication Instances lists the new replication instance created, as shown in Figure 37.

Replication Instances
Figure 37: Replication Instances

Adding a Network for Replication Instance Connectivity to CloudSQL DB Instance

First, determine the IP address on which the replication instance is running. The IP address may be obtained from the Replication instance console, as shown in Figure 38.

IP Address for Replication Instance
Figure 38: IP Address for Replication Instance

Having obtained the IP address, next we shall add a network consisting of the IP address to the MySQL instance on CloudSQL. Click the CloudSQL MySQL instance link, as shown in Figure 39.

CloudSQL MySQL DB Instance
Figure 39: CloudSQL MySQL DB Instance

Click Authorization MySQL Database Instance detail page, as shown in Figure 40.

Authorization
Figure 40: Authorization

Click Add network (see Figure 41).

Add network
Figure 41: Add network

In New network, specify a network Name, and Network IP address as the IP address of the replication instance, as shown in Figure 42, and click Done.

Adding New Network to CloudSQL
Figure 42: Adding New Network to CloudSQL

A new network gets added, as shown in Figure 43. Click Save to save the network.

Saving new network
Figure 43: Saving new network

An “Instance is being updated” message gets displayed, as shown in Figure 44.

Instance is being updated
Figure 44: Instance is being updated

When the instance has been updated, the instance icon indicates the same, as shown in Figure 45.

Instance updated
Figure 45: Instance updated

Click Overview (see Figure 46) to display the instance overview.

Overview
Figure 46: Overview

The new network gets listed in Authorized networks on the Instance Details page, as shown in Figure 47.

Authorized networks
Figure 47: Authorized networks

Creating Database Replication Endpoints

Next, we shall create the source and target database endpoints. Specify Endpoint identifiers for source and target databases. For Source engine, select mysql, as shown in Figure 48.

Selecting Source engine as mysql
Figure 48: Selecting Source engine as mysql

Select Target engine as mysql also, as shown in Figure 49.

Selecting Target Engine as mysql
Figure 49: Selecting Target Engine as mysql

For the source Server name, copy the IPv4 address of the CloudSQL instance, as shown in Figure 50.

IPv4 Address of the CloudSQL Instance
Figure 50: IPv4 Address of the CloudSQL Instance

For the target database, specify the server name as the Endpoint minus the port suffix “:3306” for the RDS DB instance for MySQL database (see Figure 51). Specify Port for source and target databases as 3306. Select SSL mode as none for both source and target connections. Specify User name for source database as root and the password as set when creating the CloudSQL instance. Similarly, set the user name and password for the target database on RDS.

Configuring Source and Target Database Connection Details
Figure 51: Configuring Source and Target Database Connection Details

For the Advanced section of both the source and target databases, select the KMS master key dms, which was created for DMS. Click Run test, as shown in Figure 52, for the source connection. Similarly, click Run test for the target connection.

Run test
Figure 52: Run test

If the result of the test is “Connection tested successfully” for both the source and target database connections, click Next, as shown in Figure 53.

Testing Connections
Figure 53: Testing Connections

Creating a Replication Task

In Create task, specify a task name and task description, as shown in Figure 54. The Source endpoint, Target endpoint, and Replication instance are hard coded. Select Migration type as Migrate existing data. Select the checkbox Start task on create.

Create task
Figure 54: Create task

In Task Settings, select/set Target table preparation mode as Do nothing, Include LOB columns in replication as Limited LOB mode, and Max LOB size as 32 kb, as shown in Figure 55. Select the Enable logging option.

Task Settings
Figure 55: Task Settings

In Table mappings, shown in Figure 56, at least one selection rule needs to be added.

Table mappings
Figure 56: Table mappings

Select the Enter a schema option in the Schema name is field. Specify Schema name is like mysql, as shown in Figure 57. Specify Table name is like innodb_index_stats. Select Action as Include. Click Add selection rule.

Adding a Selection Rule
Figure 57: Adding a Selection Rule

To add another selection rule, click the add selection rule link, as shown in Figure 58.

Add selection rule
Figure 58: Add selection rule

Add a selection rule to include table mysql.innodb_table_stats, as shown in Figure 59.

Adding a selection rule for mysql.innodb_table_stats
Figure 59: Adding a selection rule for mysql.innodb_table_stats

Selection rules may be added, one for each table, or by using a wildcard regular expression. Next, we shall use a % to add a selection rule. Click add selection rule, as shown in Figure 60.

Add selection rule link
Figure 60: Add selection rule link

Specify Schema as mysql and specify Table name as time_zone%, which represents all tables starting with “time_zone” (see Figure 61).

Adding a Selection rule to include all tables in mysql schema starting with time_zone
Figure 61: Adding a Selection rule to include all tables in mysql schema starting with time_zone

Click Create task to create a task, as shown in Figure 62.

Create task
Figure 62: Create task

A new task gets added with status as Creating initially, as shown in Figure 63. Click Refresh data from server periodically to refresh the task status.

Task added with status Creating
Figure 63: Task added with status Creating

When the task has been created and is ready to run, the Status becomes Ready, as shown in Figure 64.

Task Status Ready
Figure 64: Task Status Ready

Running the Replication Task

To start the replication instance, click Start/Resume button, as shown in Figure 65.

Start/Resume
Figure 65: Start/Resume

The task status becomes Starting (see Figure 66). Periodically, click the Refresh data from server button to refresh the status as shown in Figure 66.

Task Status Starting
Figure 66: Task Status Starting

Task status becomes Running when the task starts to run. When the full load has completed the status becomes Load complete, as shown in Figure 67.

Load complete
Figure 67: Load complete

Exploring the Migrated Tables

The Tables loaded column has a value of 7 for the seven tables migrated, as shown in Figure 68.

Seven tables migrated
Figure 68: Seven tables migrated

To find details about the tables migrated, select the Table statistics tab, as shown in Figure 69.

Table statistics
Figure 69: Table statistics

All the tables that have been migrated get listed, as shown in Figure 70.

Tables migrated
Figure 70: Tables migrated

Exploring the CloudWatch Logs

To explore the CloudWatch logs, click the link in the Logs tab (see Figure 71).

Logs link
Figure 71: Logs link

CloudWatch logs list a log entry for each table migrated, as shown in Figure 72. When all tables that were configured using the selection and transformation rules have been migrated, the log entry “All tables are loaded” gets added.

CloudWatch Logs
Figure 72: CloudWatch Logs

When all tables have been loaded, a log entry Task management terminated gets added, as shown in Figure 73.

Task management terminated
Figure 73: Task management terminated

Deleting a Migration

To delete a migration, the sequence is reverse of creating a migration.

  1. Delete the migration task/s.
  2. Delete the Database endpoints.
  3. Delete the replication instance.

To delete the task, select the task and click Delete, as shown in Figure 74.

Task>Delete
Figure 74: Task>Delete

In the Delete task dialog, click Delete, as shown in Figure 75.

Delete task confirmation dialog
Figure 75: Delete task confirmation dialog

The task status becomes Deleting (see Figure 76).

Task Deleting
Figure 76: Task Deleting

To delete the Endpoints, select the endpoints and click Delete, as shown in Figure 77.

Endpoints>Delete
Figure 77: Endpoints>Delete

In the Delete endpoint confirmation dialog, click Delete, as shown in Figure 78.

Delete endpoint confirmation dialog
Figure 78: Delete endpoint confirmation dialog

The status for the endpoints becomes Deleting, as shown in Figure 79.

Deleting Endpoints
Figure 79: Deleting Endpoints

To delete a replication instance, select the replication instance and click Delete, as shown in Figure 80.

Replication Instances>Delete
Figure 80: Replication Instances>Delete

In the Delete replication instance confirmation dialog, click Delete (see Figure 81).

Confirmation dialog Delete replication instance
Figure 81: Confirmation dialog Delete replication instance

The status for the replication instance becomes Deleting, as shown in Figure 82.

Deleting Replication Instance
Figure 82: Deleting Replication Instance

Deleting DB Instances

Next, delete the source and target databases. To delete the target database, which is a MySQL database on RDS, select the database instance in the All Instances table and click Instance Actions>Delete, as shown in Figure 83.

Instance Actions>Delete
Figure 83: Instance Actions>Delete

In Delete DB instance, select the option to not create a final snapshot and select the acknowledgement checkbox, and click Delete, as shown in Figure 84.

Delete DB Instance Dialog
Figure 84: Delete DB Instance Dialog

The RDS DB instance starts to get deleted, as indicated by the Deleting status in Figure 85.

RDS DB Instance Deleting
Figure 85: RDS DB Instance Deleting

To delete the CloudSQL DB instance, right-click the mysql DB instance management icon and select Delete (see Figure 86).

CloudSQL mysql>Delete
Figure 86: CloudSQL mysql>Delete

In the Delete instance? confirmation dialog, specify the instance name in the field provided to confirm that the instance is to be deleted, and click Delete, as shown in Figure 87.

Delete instance confirmation dialog
Figure 87: Delete instance confirmation dialog

Conclusion

In this article, we discussed migrating MySQL on CloudSQL to AWS RDS using AWS Database Migration Service.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories