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 migrationDMS 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 MigrationNamingPrimary keyData typeOrdinal positionDefault valueNullabilityAuto-increment attributesSecondary indexesMetadata MigrationStored proceduresFunctionsTriggersViewsForeign key constraintsWhat’s not included in database migrationMySQLThere 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 definitionWhen 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 clauseSince 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 DMSReady 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 supportsThe DMS documentation goes into more detail about requirements and steps to set up a MySQL database migrationAn in-depth look at configuring connectivity for DMSFill out this form to express interest in DMS for PostgreSQLRelated ArticleDatabase Migration Service Connectivity—A technical introspectiveMigrating your database is hard. So is network connectivity. See how Google’s Database Migration Service can make migration reliable, eas…Read Article
Quelle: Google Cloud Platform