GSP861

Overview

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 in the Migration fidelity Guide). 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 in the Create and run a MySQL migration job containing metadata with a DEFINER clause Guide).

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:

1. Create the necessary users on the MySQL destination instance, so that all users associated with DEFINER clauses are present in the destination instance; OR:
2. 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.

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 will be made available to you.

This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that 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 or private browser window to run this lab. This prevents any 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: If you already have your own personal Google Cloud account or project, do not use it for this lab to avoid extra charges to your 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 pop-up opens for you to select your payment method. On the left is the Lab Details panel 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 panel.

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 panel.

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 view a menu with a list of Google Cloud products and services, click the Navigation menu at the top-left.

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.

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.

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

gcloud auth list

3. Click Authorize.

Output:

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

4. (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, enter Database Migration API in the top search bar. Click on the result for Database Migration API.

This page either shows the status information, or gives you the option to enable the API.

2. If necessary, enable the API.

Verify that the Service Networking API is enabled

1. In the Google Cloud Console, enter Service Networking API in the top search bar. Click on the result for Service Networking API.

This page either shows the status information, or gives you the option to enable the API.

2. If necessary, enable the API.

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

Similar to other Cloud resources, you can control Cloud SQL project access and permissions on the destination instance using Identity and Access Management (IAM) (details available in the IAM for Cloud SQL Guide).

In this task, you identify database users that are managed through database access control (read more in the About MySQL users Guide); for example, admins and superusers that can test the results of the migration job before others are provided access to the destination Cloud SQL instance via IAM.

Connect to 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. For Connect, click on SSH to open a terminal window.

If prompted, click Authorize.

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

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

mysql -u admin -p

5. 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 as needed 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 you have not or will not create a user in the destination instance, then all DEFINER entries associated with that user need to be updated to INVOKER. Any DEFINER entries for root need to be updated to INVOKER using another 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 DEFINERs 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 will be 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 DEFINERs 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 DEFINERs 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 DEFINERs 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 DEFINERs 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 Google Cloud Console, on the Navigation menu (), click Database Migration > Connection profiles.

2. Click Create Profile.

3. For Database engine, select MySQL.

4. Enter the required information for a connection profile:

Property	Value
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

5. For the Encryption Type, select None.

6. 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 Google Cloud Console, on the Navigation menu (), click Database Migration > Migration jobs.

2. Click Create Migration Job.

3. For Create a migration job, on the Get Started tab, use 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 default.

4. Click Save & Continue.

Define the source instance

1. For source connection profile, select mysql-vm.

2. Leave the defaults for the other settings.

3. Click Save & Continue.

Create the destination instance

1. Enter the required information to create the destination instance on Cloud SQL:

Property	Value
Destination Instance ID	mysql-cloudsql
Root password	supersecret!
Choose a Cloud SQL edition	Enterprise
Database version	Cloud SQL for MySQL 5.7
Zonal availability	Multiple zones (Highly available)

2. For Instance connectivity, select Private IP and Public IP.

3. Select Use an automatically allocated IP range.

4. Click Allocate & Connect.

Note: This step may take a few minutes. If asked to retry the request, click the Retry button to refresh the Service Networking API.

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

5. Enter the additional information needed to create the destination instance on Cloud SQL:

Property	Value
Machine shapes	2 vCPU, 8 GB
Storage type	SSD
Storage capacity	10

6. Click Create & Continue.

If prompted to confirm, click Create Destination & Continue.

Define the connectivity method

A message states that your destination database instance is being created. Continue to step 1 while you wait.

1. 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. Wait for the Cloud SQL for MySQL instance to be created.

When you see an updated message that the destination instance was created, proceed to the next step.

4. 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 will 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

In Cloud SQL, you can create MySQL users, such as admins and superusers, who can test the results of the migration job before others are provided access via IAM. In addition, you want to create users that have DEFINER clauses associated with them, before you run a migration job.

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

1. In the Google Cloud Console, on the Navigation menu (), click SQL.

2. Expand the mysql-cloudsql-master instance and click on the instance ID called mysql-cloudsql.

3. In the Replica Instance menu, click Users.

4. Click Add User Account.

5. For User name, enter: admin

6. For Password, enter: changeme!

7. For Host name, select Restrict host by IP address or address range.

8. For Host, enter: localhost

9. Click Add.

10. Repeat steps 4-9 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, on the Navigation menu (), click Database Migration > Migration jobs.

2. 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, on the Navigation menu (), click SQL.

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

3. In the Primary Instance menu, click Overview.

4. In Connect to this instance panel, click Open Cloud Shell.

The command to connect to MySQL will pre-populate 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;

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.

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 October 17, 2023

Lab Last Tested October 17, 2023

Copyright 2024 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.