On-prem MySQL to Cloud SQL using DMS — The Migration Story
Cloud SQL fall under the category of Database as a service (DBAAS). In other words, Cloud SQL is a virtual machine hosted on Google Compute Engine, which runs a MySQL image through which you get to access MySQL server without having to SSH.
Whether you are using MySQL on-prem or on cloud, Database Migration Service (DMS) provides connectivity options such as IP allowlists and VPC peering to migrate your data. Choosing a connectivity method depends on the source DB and where it resides.
Database migration has to happen in phases or waves. Outlining the order of various tasks and allocating time to each of these migrations is the first step before we decide the migration path. It is always advisable to migrate the less critical or less dependent databases first. The duration of migration depends upon factors such as size of database, availability, usage etc. A team might choose to go with:
One-time Migration: As the name says, source DB is completely moved to destination at a single point-in-time affecting the applications, which may experience downtime.
Ongoing Migration: A snapshot of source DB is taken and loaded into destination. Source would continue to accept data writes, and these changes are processed to destination through Change Data Capture. Any application that depends on your DB will not experience any downtime. At some point source would be decommissioned and destination becomes the new source.
Let us look at how to configure an ongoing migration of on-prem MySQL DB using Database Migration Service, which currently supports MySQL versions 5.5, 5.6, 5.7, and 8.0.
We will look at this in five simple steps:
Step 1: Configuring the Source DB
- All the DDL write operations to stop before the start of migration.
- Modify global transaction ID (GTID) system variables to configure a replication slave.
- Configure a new account to connect to MySQL DB and enable it to accept all the connections. (Always advisable to create a new connection than using the existing connections).
- Ensure to enable privileges as required. For example, you may want to enable privileges such as:
- “REPLICATION SLAVE”, which enables DMS to connect to the server as source.
- “EXECUTE” to enable the use of statements that execute stored procedures and functions.
- “RELOAD” to enable flush operations.
5. Since we are performing an ongoing migration, binary log are to be enabled in order to capture changes to table data or database.
Step 2: Create a connection profile to source from Google Cloud.
To do this, you need to enable “Database Migration API” from APIs and Services under navigation menu.
Once enabled, navigate to Database Migration service, create a connection profile, and pass the connection information such as profile name, profile ID, hostname/IP address etc. On entering the profile name, profile ID comes up by default. Unless you want to change it, you can use the same.
Depending upon the choice of connectivity method, you either enter the Hostname or IP address. The port number 3306 is the default for MySQL.
Enter the username and password of the source. Click “Create” for the connection profile to get created.
Step 3: Create a migration job on DMS.
To create a migration job, navigate to “Migration Jobs” tab and click “Create Migration Job” at the top.
You need to enter the information such as,
1. Migration job name.
2. Select the source db engine. In our case MySQL. (You cannot change the destination db engine since DMS currently supports only homogeneous migration).
3. Whether the migration type is One-time or Continuous (Or ongoing).
4. Select source connection profile you have created in step 2.
5. Within “Create the destination”, choose whether to connect to the instance via Public IP or Private IP. This again depends upon the migration type (VPC-peering, Reverse SSH tunnel, IP allowlisting etc.)
6. In here, you also need to select the machine type for the Cloud SQL instance. The disk size you choose here should be either equal to or greater than the source database size.
7. You then select the connectivity method in “Define Connectivity method” tab.
8. At the final step, you can test the connectivity before creating the job.
Step 4: Review and confirm the whether the data is being migrated continuously.
Clicking on the migration job we created in the above step will take us to the details page where we can review the status of the job.
If you often visit the page, the status will continue to show as “Running” since we chose to perform a continuous migration. Hence the status “CDC in progress”.
Once the job is successful, you can navigate to Databases > SQL from the navigation menu, click instance ID you have created above.
In the Replica Instance menu, when you click databases, you will see the DBs that were migrated from On-prem DB to CloudSQL using DMS.
Under the same Replica Instance menu, you can click “Connect using Cloud Shell” which pre populates MySQL connection within the cloud shell.
You enter the root password and voila! You can now execute MySQL queries from Cloud Shell.
Step 5: Promote CloudSQL to be your primary database.
Once it is time to move reads and writes to the destination, we can initiate the process of promoting the Cloud SQL instance. When this is done, the destination Cloud SQL instance gets disconnected from the source.
If you are performing the continuous migration or CDC, remember there will be replication delay. You can view the replication delay on the migration job page. Wait until this becomes zero before you stop scripts or writes and promote the CloudSQL instance.
You can also use gcloud commands to create Database Migration Service migration job.
To create a job :
gcloud alpha database-migration migration-jobs createTo start a job:
gcloud alpha database-migration migration-jobs startTo stop a job:
gcloud alpha database-migration migration-jobs stopTo promote a job:
gcloud alpha database-migration migration-jobs promote
