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
- Creating a MySQL RDS DB Instance
- Connecting to MySQL Database on RDS
- Creating a Google Cloud Platform Project
- Creating a CloudSQL Instance
- Adding a Route for CloudSQL DB Instance IP Address to the VPC Route Table
- Creating a DMS Replication Instance
- Adding a Network for Replication Instance Connectivity to CloudSQL DB Instance
- Creating Database Replication Endpoints
- Creating a Replication Task
- Running the Replication Task
- Exploring the Migrated Tables
- Exploring the CloudWatch Logs
- Deleting a Migration
- Deleting DB Instances
- Conclusion
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.
Figure 1: Selecting MySQL Database Engine
In Production?, select Dev/Test, as shown in Figure 2.
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.
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.
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.
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.
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.
Figure 7: Your DB Instance is being created
A new MySQL DB instance on RDS gets started, as shown in Figure 8.
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.
Figure 9: Security Group Link
Select the Inbound tab and click the Edit button to modify the inbound rules, as shown in Figure 10.
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).
Figure 11: Edit inbound rules
The modified Inbound rules are shown in Figure 12.
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.
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.
Figure 14: Create an empty project
In the New Project wizard, specify a Project name (MySQLDB) and click Create, as shown in Figure 15.
Figure 15: New Project
A new project gets created, as shown in the Dashboard in Figure 16.
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.
Figure 17: View Console
Select the MySQLDB project and click Create instance, as shown in Figure 18.
Figure 18: Create Instance
In the Create an instance wizard, select MySQL and click Next, as shown in Figure 19.
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.
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.
Figure 21: Create a MySQL Second Generation instance
A MySQL instance starts to get created, as shown in the Instances table in Figure 22.
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.
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.
Figure 24: Route Table link
Select the route listed and click Edit, as shown in Figure 25.
Figure 25: Route>Edit
Click Add another route (see Figure 26).
Figure 26: Add another route
Specify the IP address of the CloudSQL instance in Destination, as shown in Figure 27.
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.
Figure 28: Selecting Target as the Internet Gateway
Click Save to save the route. A route gets added, as shown in Figure 29.
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.
Figure 30: Create migration
In the Welcome page, click Next (see Figure 31).
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.
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.
Figure 33: Configuring Advanced Options
Select the default Maintenance settings and click Next, as shown in Figure 34.
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.
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).
Figure 36: Replication instance created successfully
The Replication Instances lists the new replication instance created, as shown in Figure 37.
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.
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.
Figure 39: CloudSQL MySQL DB Instance
Click Authorization MySQL Database Instance detail page, as shown in Figure 40.
Figure 40: Authorization
Click Add network (see Figure 41).
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.
Figure 42: Adding New Network to CloudSQL
A new network gets added, as shown in Figure 43. Click Save to save the network.
Figure 43: Saving new network
An “Instance is being updated” message gets displayed, as shown in Figure 44.
Figure 44: Instance is being updated
When the instance has been updated, the instance icon indicates the same, as shown in Figure 45.
Figure 45: Instance updated
Click Overview (see Figure 46) to display the instance overview.
Figure 46: Overview
The new network gets listed in Authorized networks on the Instance Details page, as shown in Figure 47.
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.
Figure 48: Selecting Source engine as mysql
Select Target engine as mysql also, as shown in Figure 49.
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.
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.
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.
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.
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.
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.
Figure 55: Task Settings
In Table mappings, shown in Figure 56, at least one selection rule needs to be added.
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.
Figure 57: Adding a Selection Rule
To add another selection rule, click the add selection rule link, as shown in Figure 58.
Figure 58: Add selection rule
Add a selection rule to include table mysql.innodb_table_stats, as shown in Figure 59.
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.
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).
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.
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.
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.
Figure 64: Task Status Ready
Running the Replication Task
To start the replication instance, click Start/Resume button, as shown in Figure 65.
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.
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.
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.
Figure 68: Seven tables migrated
To find details about the tables migrated, select the Table statistics tab, as shown in Figure 69.
Figure 69: Table statistics
All the tables that have been migrated get listed, as shown in Figure 70.
Figure 70: Tables migrated
Exploring the CloudWatch Logs
To explore the CloudWatch logs, click the link in the Logs tab (see Figure 71).
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.
Figure 72: CloudWatch Logs
When all tables have been loaded, a log entry Task management terminated gets added, as shown in Figure 73.
Figure 73: Task management terminated
Deleting a Migration
To delete a migration, the sequence is reverse of creating a migration.
- Delete the migration task/s.
- Delete the Database endpoints.
- Delete the replication instance.
To delete the task, select the task and click Delete, as shown in Figure 74.
Figure 74: Task>Delete
In the Delete task dialog, click Delete, as shown in Figure 75.
Figure 75: Delete task confirmation dialog
The task status becomes Deleting (see Figure 76).
Figure 76: Task Deleting
To delete the Endpoints, select the endpoints and click Delete, as shown in Figure 77.
Figure 77: Endpoints>Delete
In the Delete endpoint confirmation dialog, click Delete, as shown in Figure 78.
Figure 78: Delete endpoint confirmation dialog
The status for the endpoints becomes Deleting, as shown in Figure 79.
Figure 79: Deleting Endpoints
To delete a replication instance, select the replication instance and click Delete, as shown in Figure 80.
Figure 80: Replication Instances>Delete
In the Delete replication instance confirmation dialog, click Delete (see Figure 81).
Figure 81: Confirmation dialog Delete replication instance
The status for the replication instance becomes Deleting, as shown in Figure 82.
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.
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.
Figure 84: Delete DB Instance Dialog
The RDS DB instance starts to get deleted, as indicated by the Deleting status in Figure 85.
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).
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.
Figure 87: Delete instance confirmation dialog
Conclusion
In this article, we discussed migrating MySQL on CloudSQL to AWS RDS using AWS Database Migration Service.
