Update DEFINER clauses to INVOKER on the MySQL source instance.
Create and save a Database Migration Service job without starting the job.
Create users on the Cloud SQL destination instance.
Start a previously created Database Migration Service job.
Confirm the user metadata in Cloud SQL for MySQL
Migrating MySQL User Data When Running Database Migration Service Jobs
Database Migration Service provides a high-fidelity way to migrate MySQL database objects (including schema, data, and metadata) from a source database instance to a destination database instance. When you run a Database Migration Service job, all tables from all databases and schemas are migrated, with the exception of the following system databases: sys, mysql, performance_schema, and information_schema.
MySQL system databases, which are not migrated during Database Migration Service jobs, contain information about users and privileges (additional details available at https://cloud.google.com/database-migration/docs/mysql/migration-fidelity). You can manage users and privileges in the destination Cloud SQL database instance after it is created.
Objects that contain metadata defined with the DEFINER clause can fail when invoked on the destination instance, if the user associated with the DEFINER clause does not already exist in the destination instance (additional details available at https://cloud.google.com/database-migration/docs/mysql/mysql-definer).
To prevent errors when these objects are invoked on the destination instance after migration, you can complete one of the following actions before running the migration job:
- Create the necessary users on the MySQL destination instance, so that all users associated with DEFINER clauses are present in the destination instance; OR:
- Update DEFINER clauses to INVOKER on the MySQL source instance. This ensures that the security privileges used to access the data on the destination instance are set to the privileges for the user running the query, rather than the privileges for the user who defined the object.
In this lab, you learn how to ensure that your Cloud SQL for MySQL instance contains the relevant user metadata that was available on the MySQL source instance by completing both of the identified actions before running the migration job. First, you identify the existing MySQL users on the source instance and update DEFINER clauses to INVOKER for database objects on the source instance. Next, you create and save a Database Migration Service job. Before running the migration job, you create the necessary users on the destination database instance. Finally, you start the saved migration job; after the job runs successfully, you check the user metadata in the Cloud SQL for MySQL instance.
Join Qwiklabs to read the rest of this lab...and more!
- Get temporary access to the Google Cloud Console.
- Over 200 labs from beginner to advanced levels.
- Bite-sized so you can learn at your own pace.