GCP – Closing the gap: Migration completeness when using Database Migration Service
Database Migration Service (DMS) provides high-fidelity, minimal downtime migrations for MySQL (Preview) and PostgreSQL (available in Preview by request) workloads to Cloud SQL. Since DMS is serverless, you don’t have to worry about provisioning, managing, or monitoring any migration-specific resources.
In this post, we’ll focus on what is and is not included in database migration for MySQL, and what you can do to ensure migration completeness when using DMS. The source database’s data, schema, and additional database features (triggers, stored procedures, and more) are replicated to the Cloud SQL destination reliably, and at scale, with no user intervention required. Due to the peculiarities of MySQL, there are a few things that won’t be migrated, though. Let’s look at what is and isn’t migrated with DMS in more detail.
What’s included in MySQL database migration
DMS for MySQL uses the database’s own native replication technology to provide a high-fidelity way to migrate database objects from one database to another. The migration fidelity section of the documentation goes into detail about what is included in the migration. At the time of this Preview launch, all of the following data, schema, and metadata components are migrated as part of the database migration:
Data Migration
All tables from all databases and schemas, excluding the following default databases and schemas: sys, mysql, performance_schema, and information_schema.
Schema Migration
- Naming
- Primary key
- Data type
- Ordinal position
- Default value
- Nullability
- Auto-increment attributes
- Secondary indexes
Metadata Migration
- Stored procedures
- Functions
- Triggers
- Views
- Foreign key constraints
What’s not included in database migration
MySQL
There are certain things that are not migrated as part of a MySQL database migration, as well as some known limitations and quotas that you should be aware of.
Users definition
When you’re migrating a MySQL database, the MySQL system database, which contains information about users and privileges, is not migrated. That means that user account and login information must be managed in the destination Cloud SQL instance directly.
The root account will need to be set up before the instance can be used. You can add users to the Cloud SQL destination instance either from the Users tab in the UI, or from the mysql client. The Cloud SQL documentation contains more information about managing MySQL user accounts.
Usage of Definer clause
Since a MySQL migration job doesn’t migrate users data, sources which contain metadata defined by users with the DEFINER clause will fail when invoked on the new Cloud SQL replica, as the users don’t yet exist there.
To run a migration from a source that includes the DEFINER clause:
- Create a migration job without starting it (choose Create instead of Create & Start).
- Create the users on the new Cloud SQL destination instance using the Cloud SQL API or the Users tab in the UI.
- Start the migration job from the migration job list or the specific job’s page.
Alternatively, you can update the DEFINER clause to INVOKER on the source prior to setting up the migration job. Note that if the metadata was created by ’root’@’localhost’, the process will fail. Change the DEFINER before starting the migration job.
Next Steps with DMS
Ready to learn more about migrating your MySQL or PostgreSQL database to Cloud SQL? These resources will help you gather the information you need to get started:
- This blog post announces the launch of DMS and provides an overview of the capabilities it supports
- The DMS documentation goes into more detail about requirements and steps to set up a MySQL database migration
- An in-depth look at configuring connectivity for DMS
- Fill out this form to express interest in DMS for PostgreSQL
Read More for the details.
