Upgrade Your MySQL Version with Minimal Downtime: Our Journey with Google Cloud’s Data Migration Service
Upgrading your Google Cloud SQL instance from MySQL 5.7 to version 8.0 is crucial in ensuring that your database runs efficiently and securely. However, the migration process can be challenging and time-consuming and may result in significant downtime for your application. Luckily, Google Cloud provides a reliable and efficient tool for upgrading your MySQL instance using its Data Migration Service (DMS).
The DMS tool simplifies migration by providing step-by-step guidance to move your database from the source to the destination instance with minimal downtime.
Plan description
The migration process involves creating a connection profile of the source instance in the DMS console, creating a read replica on the source instance, creating a dump from the read replica to minimise the load on the primary instance during the dump process, creating and running a migration job in the DMS console.
Once you start the migration job, you must wait for the initial load to complete. Then, the DMS will start the Change Data Capture (CDC) process to capture any changes made to the source instance during the migration.
Make sure to stop all writes and consumers on the application to avoid any data loss or inconsistencies before initiating the promotion process. When you see Running CDC in progress, you can start the promotion process, making the destination instance a writable and readable standalone instance.
Once you initiate a promotion process, change the application’s configuration to point to the new MySQL instance. When the application’s configuration is correct, normal operations can continue. This way we don’t lose data during the migration process and have minimal downtime.
Step-by-Step plan
Create the SSL client certificates on the source MySQL instance
Before migrating, you must create the SSL client certificates on the source MySQL instance. This is necessary for secure communication between the source and destination instances. You can create them if you go to your source instance, click Connections, Security, and then Create Client Certificate.
Create a connection profile of the source MySQL instance
Next, you must create a connection profile of the source MySQL instance in the DMS console. This will allow DMS to connect to the source instance and perform the migration. Make sure to provide the SSL client certificates in the connection profile.
Create a read replica on the source instance
Create a read replica of the source MySQL instance in the same region as the destination instance. A read replica will help us offload the dump process, which could impact our source database performance.
Create a dump from the read replica
Create a dump of the read replica using the mysqldump command and upload it to Cloud Storage. This will generate a SQL file containing your MySQL instance’s schema and data. There are some specific flags that need to be included with mysqldump command. You can read about them here:
Exporting a MySQL database using mysqldump | Database Migration Service | Google Cloud
Create a migration job
In the DMS console, create a new migration job and select the manual dump file created earlier as the source. Provide the connection details of the destination instance. If you want minimal downtime, it is best to pick Continuous (snapshot + ongoing changes) migration job. Select MySQL 8 as the database version for the destination instance. Because you’re creating a new instance during this step, you can change the settings to what you need — add more memory, remove storage that was allocated but deleted, etc.
Start the migration job and wait till you see CDC in progress
Start the migration job and wait for the initial load to complete. The DMS will then start the Change Data Capture (CDC) process to capture any changes made to the source instance during the migration. Wait until the replication delay is at zero.
Suggestion: Verify that the data is replicated correctly
Connect to both the old and the new instances and check for any inconsistencies in the data. The data should be the same in both instances so you can proceed to the next steps.
Stop writing to the source database
Once the CDC is in progress, stop all writes to the source database. You must stop all writes to the source database because, by promoting the migration job, the destination Cloud SQL database will become your primary database.
Click on Promote and promote the destination instance to be a writable
Click the Promote button in the DMS console to promote the new instance.
Point application to the destination instance and resume writes
Once the promotion is complete, change the application configuration to point to the new MySQL instance. Then start the writes to resume normal operations.
The migration is finished
Congratulations! You have successfully upgraded your MySQL instance to version 8.0 using Google Cloud’s Data Migration Service. Your new Cloud SQL database instance is ready to use.
Conclusion
It’s worth noting that the DMS tool is not only useful for upgrading from MySQL 5.7 to version 8.0 on Google Cloud SQL instances. It can also be used in other scenarios, such as migrating from on-premise instances to Google Cloud or even from different cloud providers to Google Cloud. The tool is a reliable and efficient way to migrate your database with minimal downtime and maximum data integrity.
In conclusion, upgrading your MySQL instance version using Google Cloud’s Data Migration Service is straightforward and requires careful planning and execution. The DMS tool simplifies the migration process by providing step-by-step guidance.
Google Cloud has excellent documentation about this service. You can read more about it here:
Database Migration Service for MySQL documentation | Google Cloud.
