GSP861

Overview

Database Migration Service efficiently migrates MySQL database objects—including schema, data, and metadata—from a source to a destination instance. When a migration job runs, it moves all tables across all databases and schemas, excluding the following system databases: sys, mysql, performance_schema, and information_schema.

Information about users and privileges is held within the MySQL system databases. Since Database Migration Service jobs do not migrate these system databases, you need to manage users and privileges in your destination Cloud SQL instance after it is created. For more detailed information on migration fidelity, consult the Migration fidelity Guide.

If the user specified in an object's DEFINER clause does not exist on the destination instance, the object's invocation may fail after migration. For more details, consult the Create and run a MySQL migration job containing metadata with a DEFINER clause Guide.

To avoid errors when invoking objects on the destination instance post-migration, complete one of the two actions below before running the migration job:

• Ensure user presence: Create the necessary users on the MySQL destination instance. This guarantees that all users associated with DEFINER clauses exist on the destination.
• Update security privileges: On the MySQL source instance, change DEFINER clauses to INVOKER. This sets the security privileges for data access on the destination to those of the user running the query, rather than the user who defined the object.

After these prerequisites are met, you create and save a Database Migration Service job, then initiate the saved job. Upon successful completion of the migration, the final step is to verify the user metadata on the Cloud SQL for MySQL instance.

What you'll do

In this lab, you learn how to migrate MySQL user data when running Database Migration Service jobs:

• Identify existing MySQL users on the source instance.
• 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.
• Check user metadata in the Cloud SQL destination instance.

Setup and requirements

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources are made available to you.

This hands-on lab lets you do the lab activities in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

• Access to a standard internet browser (Chrome browser recommended).

Note: Use an Incognito (recommended) or private browser window to run this lab. This prevents conflicts between your personal account and the student account, which may cause extra charges incurred to your personal account.

• Time to complete the lab—remember, once you start, you cannot pause a lab.

Note: Use only the student account for this lab. If you use a different Google Cloud account, you may incur charges to that account.

How to start your lab and sign in to the Google Cloud console

1. Click the Start Lab button. If you need to pay for the lab, a dialog opens for you to select your payment method. On the left is the Lab Details pane with the following:

   • The Open Google Cloud console button
   • Time remaining
   • The temporary credentials that you must use for this lab
   • Other information, if needed, to step through this lab

2. Click Open Google Cloud console (or right-click and select Open Link in Incognito Window if you are running the Chrome browser).

   The lab spins up resources, and then opens another tab that shows the Sign in page.

   Tip: Arrange the tabs in separate windows, side-by-side.

   Note: If you see the Choose an account dialog, click Use Another Account.

3. If necessary, copy the Username below and paste it into the Sign in dialog.

   {{{user_0.username | "Username"}}}

   You can also find the Username in the Lab Details pane.

4. Click Next.

5. Copy the Password below and paste it into the Welcome dialog.

   {{{user_0.password | "Password"}}}

   You can also find the Password in the Lab Details pane.

6. Click Next.

   Important: You must use the credentials the lab provides you. Do not use your Google Cloud account credentials. Note: Using your own Google Cloud account for this lab may incur extra charges.

7. Click through the subsequent pages:

   • Accept the terms and conditions.
   • Do not add recovery options or two-factor authentication (because this is a temporary account).
   • Do not sign up for free trials.

After a few moments, the Google Cloud console opens in this tab.

Note: To access Google Cloud products and services, click the Navigation menu or type the service or product name in the Search field.

Activate Cloud Shell

Cloud Shell is a virtual machine that is loaded with development tools. It offers a persistent 5GB home directory and runs on the Google Cloud. Cloud Shell provides command-line access to your Google Cloud resources.

1. Click Activate Cloud Shell at the top of the Google Cloud console.

2. Click through the following windows:

   • Continue through the Cloud Shell information window.
   • Authorize Cloud Shell to use your credentials to make Google Cloud API calls.

When you are connected, you are already authenticated, and the project is set to your Project_ID, . The output contains a line that declares the Project_ID for this session:

Your Cloud Platform project in this session is set to {{{project_0.project_id | "PROJECT_ID"}}}

gcloud is the command-line tool for Google Cloud. It comes pre-installed on Cloud Shell and supports tab-completion.

3. (Optional) You can list the active account name with this command:

gcloud auth list

4. Click Authorize.

Output:

ACTIVE: * ACCOUNT: {{{user_0.username | "ACCOUNT"}}} To set the active account, run: $ gcloud config set account `ACCOUNT`

5. (Optional) You can list the project ID with this command:

gcloud config list project

Output:

[core] project = {{{project_0.project_id | "PROJECT_ID"}}} Note: For full documentation of gcloud, in Google Cloud, refer to the gcloud CLI overview guide.

Verify that the Database Migration API is enabled

1. In the Google Cloud console title bar, enter Database Migration API in the Search field, and then click Database Migration API in the search results.

2. If needed, click Enable to enable the API.

Task 1. Identify the existing MySQL users on the source instance

Similar to other Cloud resources, access and permissions for the destination Cloud SQL instance are controlled at the project level using Identity and Access Management (IAM). For details, refer to the IAM for Cloud SQL Guide.

In this task, you identify database users managed through database access control (e.g., admins and superusers). These users, distinct from those managed via IAM, may need access to the destination Cloud SQL instance to test the migration job results before broader IAM access is granted. More information is available in the About MySQL users Guide.

Connect to the MySQL source instance

1. In the Google Cloud console title bar, on the Navigation menu (), click Compute Engine > VM instances.

2. Locate the line with the instance called dms-mysql-training-vm.

3. For Connect, click on SSH to open a terminal window.

4. If prompted, click Authorize.

You can ignore any warnings about upgrades that appear in the SSH window.

5. To connect to the MySQL interactive console within the terminal window, run the following command:

mysql -u admin -p

6. When prompted for a password, enter:

changeme!

Identify the existing MySQL users

1. Run the following query to identify existing MySQL users:

select host, user, authentication_string from mysql.user order by user;

Notice the system users named debian-sys-maint, mysql.session, and mysql.sys. These users do not need to be recreated as they are created by MySQL when the destination instance is created.

2. Run the modified query to exclude these system users:

select host, user, authentication_string from mysql.user where user not like '%mysql%' and user not like '%debian%' order by user;

3. Review the list of the users that need to be recreated in the destination Cloud SQL instance:

host	user
localhost	admin
%	admin
localhost	bsmith
localhost	dwilliams
localhost	mhill
localhost	root
%	root

In a later task, you create the root user when you run the Database Migration Service job. You also create the other users, after the Cloud SQL destination instance has been created by the migration job.

4. Leave the terminal window open for use in the next task.

Task 2. Update DEFINER to INVOKER on the MySQL source instance

In the previous task, you identified the existing MySQL users on the source instance. In this task, you identify and update database objects that have DEFINER entries for either root or the other users that do not yet exist in the destination instance.

If the user in the source instance is not created in the destination instance, all associated DEFINER entries must be changed to INVOKER. Specifically, any DEFINER entries for root should be updated to INVOKER using a different user, such as admin.

These actions prevent failures when the objects are invoked on the destination instance after migration (review additional documentation in the Create and run a MySQL migration job containing metadata with a DEFINER clause Guide).

Identify objects with DEFINER entries

To obtain information about DEFINER in a MySQL instance, you can query the INFORMATION_SCHEMA tables to identify DEFINER entries that require review before migration (for example, DEFINER entries that are not associated with system databases such as mysql and thus are migrated to the destination instance).

1. Run the following query to identify DEFINER entries:

select table_schema, table_name from information_schema.columns where column_name = 'DEFINER' and table_schema != 'mysql';

The results indicate that you need to check events, routines, triggers, and views for DEFINER entries.

Note that some of these DEFINER entries may be associated with system users, which do not require additional action. These system users are excluded in the queries to identify DEFINER entries for the various database objects.

2. Run the following query to identify DEFINER in events:

select definer, event_schema, event_name from information_schema.events where definer not like '%mysql%' and definer not like '%debian%';

There are no instances of DEFINER in events associated with non-system users.

3. Run the following query to identify DEFINER in routines:

select definer, routine_schema, routine_name from information_schema.routines where definer not like '%mysql%' and definer not like '%debian%';

There are no instances of DEFINER in routines associated with non-system users.

4. Run the following query to identify DEFINER in triggers:

select definer, trigger_schema, trigger_name from information_schema.triggers where definer not like '%mysql%' and definer not like '%debian%';

There are no instances of DEFINER in triggers associated with non-system users.

5. Run the following query to identify DEFINER in views:

select definer, security_type, table_schema, table_name from information_schema.views where definer not like '%mysql%' and definer not like '%debian%' order by definer;

6. Review the details for the view named invoices_storenum_3656, which is associated with mhill:

definer	security_type	table_schema	table_name
admin@localhost	DEFINER	customers_data	customers_single
admin@localhost	DEFINER	sales_data	invoices_storenum_5173
bsmith@localhost	DEFINER	customers_data	customers_married
bsmith@localhost	DEFINER	sales_data	invoices_storenum_3980
mhill@localhost	DEFINER	sales_data	invoices_storenum_3656

In the next section, you update DEFINER to INVOKER for this view.

Notice that one of the previously identified users (dwilliams) is not associated with any DEFINER entries in views. Additionally, notice that there are no instances of DEFINER for the root user. No action is needed for either dwilliams or root.

Update DEFINER to INVOKER

In this subtask, you update the DEFINER entries associated with mhill to INVOKER.

1. Run the following command to see the details of the view associated with mhill:

select definer, security_type, table_schema, view_definition from information_schema.views where table_name = 'invoices_storenum_3656';

2. Select the database associated with the view:

use sales_data;

3. Run the following command to update DEFINER to INVOKER for the view:

alter sql security INVOKER view invoices_storenum_3656 as (select * from invoices where storeNum = 3656);

4. Review the DEFINER entries again to see that the view has been updated with INVOKER:

select definer, security_type, table_schema, table_name from information_schema.views where definer not like '%mysql%' and definer not like '%debian%' order by definer;

Notice that the DEFINER entry associated with mhill has been updated to INVOKER, while the DEFINER entries associated with admin and bsmith remain.

End the terminal session

1. Exit the MySQL interactive console:

exit

2. Exit the terminal session:

exit

Click Check my progress to verify the objective. Update DEFINER clauses to INVOKER on the MySQL source instance.

Task 3. Create and save a Database Migration Service job without starting it

In this task, you create and save a migration job without starting the job. Specifically, you create a one-time migration job using VPC peering as the connectivity option; however, you can create and save any migration job to run at a later time.

This allows you to create the destination Cloud SQL instance without migrating data until you have completed necessary tasks such as creating new users on the destination instance.

Get the connectivity information for the MySQL source instance

1. In the Google Cloud console, on the Navigation menu (), click Compute Engine > VM instances.

2. Locate the line with the instance called dms-mysql-training-vm.

3. Copy the value for Internal IP (e.g. 10.128.0.2).

Create a new connection profile for the MySQL source instance

1. In the console title bar, type Database Migration in the Search field, and then click Database Migration from the results.

2. Click Connection profiles in the left pane, and then click Create profile.

3. Set the required connection profile information below, leaving all other settings at their default values.:

Property	Value
Source Engine	MySQL
Destination Engine	Cloud SQL for MySQL
Profile Type	Source
Connection profile name	mysql-vm
Connection profile ID	keep the auto-generated value
Hostname or IP address	enter the internal IP for the MySQL source instance that you copied in the previous task (e.g. 10.128.0.2)
Port	3306
Username	admin
Password	changeme!
Region

4. Click Create.

A new connection profile named mysql-vm appears in the Connections profile list.

Create a new one-time migration job

1. In the left pane, click Migration jobs, and then click Create migration job.

2. For Create a migration job, on the Get Started tab, set the following values:

Property	Value
Migration job name	vm-to-cloudsql
Migration job ID	keep the auto-generated value
Source database engine	MySQL
Destination region
Migration job type	One-time

Leave all other settings as the default.

3. Click Save & continue.

Define the source instance

1. On the Define a source tab, for source connection profile, select mysql-vm.

2. Leave the defaults for the other settings.

3. Click Save & continue.

Define the destination instance

1. For the Define a destination tab, for Type of destination cluster, select Existing instance.

2. For destination instance, select .

3. Click Select & continue.

4. Enter the instance name again and click Confirm & continue.

Note: This step may take a few minutes to complete.

If asked to retry the request, click the Retry button to refresh the Service Networking API.

If you receive an error without a prompt to retry, be sure that you have completed the steps in the Setup and requirements section of the lab to verify that the Service Networking API has been enabled.

When this step is complete, an updated message notifies you that this instance uses the existing managed service connection.

It may take a few minutes to save the destination.

Define the connectivity method

1. For the Define connectivity method tab, for Connectivity method, select VPC peering.

2. For VPC, select default.

VPC peering is configured by Database Migration Service using the information provided for the VPC network (the default network in this example).

3. Click Configure & Continue.

Save the one-time migration job

1. Review the details of the migration job.

2. Click Create Job.

You do not need to test the migration job. Recall that testing the job now result in an error message because there are `DEFINER entries associated with users that have not yet been created in the destination instance. You create these users in the next task.

3. If prompted to confirm, click Create.

The migration job has been created but has not been started. You start the job in a later task, after you create the new users in the Cloud SQL destination instance.

Click Check my progress to verify the objective. Create and save a Database Migration Service job without starting the job.

Task 4. Create the necessary users on the Cloud SQL destination instance

Before running a migration job in Cloud SQL, you should create necessary MySQL users. This includes users like admins and superusers for testing the migration results before granting wider access via IAM. Additionally, you need to create users with associated DEFINER clauses before starting the migration.

In this task, you create the users named admin, bsmith, and mhill.

1. In the Google Cloud console title bar, type Cloud SQL in the Search field and click Cloud SQL from the results.

2. Expand the mysql-cloudsql-master instance and click the mysql-cloudsql instance.

3. In the Replica Instance menu, click Users.

4. Click Add User Account.

5. Set the properties in the table below, leave all others as their default:

Property	Value
User name	admin
Password	changeme!
Host name	Restrict host by IP address or address range
Host	localhost

6. Click Add.

7. Repeat steps 4-6 to create two more users with localhost access:

User	Password
bsmith	mustchangeasap!
mhill	update!

Click Check my progress to verify the objective. Create users on the Cloud SQL destination instance.

Task 5. Run a previously created migration job

Start a migration job

1. In the Google Cloud console title bar, type Database Migration in the Search field, and then click Database Migration from the results. > Migration jobs**.

2. In the left pane, click Migration jobs**, and then click the migration job vm-to-cloudsql to see the details page.

3. Click the Start button to run the migration job.

If prompted to confirm, click Start.

4. Review the migration job status.
   • If you have not started the job, the status is Not started. You can choose to start or delete the job.
   • After the job has started, the status is Starting and then transitions to Running.
   • When the job status changes to Completed, the migration job has completed successfully, and you can move on to the next task.

Click Check my progress to verify the objective. Run a previously created Database Migration Service job.

Task 6. Confirm the user metadata in Cloud SQL for MySQL

Connect to the MySQL instance

1. In the Google Cloud console title bar, type Cloud SQL in the Search field and click Cloud SQL from the results.

2. Click on the instance ID called mysql-cloudsql.

3. In the Primary Instance menu, click Overview.

4. Scroll down to Connect to this instance panel, and then click Open Cloud Shell.

The command to connect to MySQL pre-populates in Cloud Shell:

gcloud sql connect mysql-cloudsql --user=root --quiet

5. Run the pre-populated command.

If prompted, click Authorize for the API.

6. When prompted for a password, which you previously set, enter:

supersecret!

You have now activated the MySQL interactive console.

Review the user metadata in the Cloud SQL for MySQL instance

1. Run the following query to see non-system users:

select host, user, authentication_string from mysql.user where user not like '%mysql%' and user not like '%debian%' order by user;

Notice the users that you created in the previous task.

2. Run the following query to see that the user metadata has been migrated successfully:

select definer, security_type, table_schema, table_name from information_schema.views where definer not like '%mysql%' and definer not like '%debian%' order by definer;

3. Review the entries to see that the metadata for invoices_storenum_3656 was migrated successfully:

definer	security_type	table_schema	table_name
admin@localhost	DEFINER	customers_data	customers_single
admin@localhost	DEFINER	sales_data	invoices_storenum_5173
bsmith@localhost	DEFINER	customers_data	customers_married
bsmith@localhost	DEFINER	sales_data	invoices_storenum_3980
mhill@localhost	INVOKER	sales_data	invoices_storenum_3656

4. To select the database in the MySQL interactive console, run the following command:

use sales_data;

5. Query the view associated with mhill to check that it executes successfully:

select * from invoices_storenum_3656;

The query returns 27 rows.

6. Exit the MySQL interactive console:

exit

Click Check my progress to verify the objective. Confirm the user metadata in Cloud SQL for MySQL

Congratulations!

You learned how to migrate MySQL user data when running Database Migration Service jobs.

Next steps / Learn more

Learn more about data migration, Cloud SQL databases, and Database Migration Service:

• Database Migration Service documentation
• Cloud SQL for MySQL documentation
• Types of migration

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual Last Updated March 11, 2026

Lab Last Tested March 11, 2026

Copyright 2026 Google LLC. All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.