Create and run a MySQL migration job containing metadata with a DEFINER clause

Overview

A MySQL migration job doesn't migrate user data. Therefore, sources which contain metadata defined by users with the DEFINER clause will fail when invoked on the new Cloud SQL replica, because the users don't yet exist there.

To identify which DEFINER values exist in your metadata, you can run the following queries on your MySQL source database. Check the results for entries for either root%localhost or for users that don't exist in the destination instance:

SELECTDISTINCTDEFINERFROMINFORMATION_SCHEMA.EVENTSWHEREEVENT_SCHEMANOT
IN('mysql','sys');
SELECTDISTINCTDEFINERFROMINFORMATION_SCHEMA.ROUTINESWHEREROUTINE_SCHEMANOT
IN('mysql','sys');
SELECTDISTINCTDEFINERFROMINFORMATION_SCHEMA.TRIGGERSWHERETRIGGER_SCHEMANOT
IN('mysql','sys');
SELECTDISTINCTDEFINERFROMINFORMATION_SCHEMA.VIEWSWHERETABLE_SCHEMANOT
IN('mysql','sys');

To run a migration job from a source which includes such metadata, you can do one of the following:

• Create the users on the destination Cloud SQL replica instance before starting your migration job.

  1. Create a migration job without starting it. That is, choose Create instead of Create & Start.

  2. Create the users from your source MySQL instance on your destination Cloud SQL instance using the Cloud SQL API or UI.

  3. Start the migration job from the migration job list or the specific job's page.

• Update the DEFINER clause to INVOKER on your source MySQL instance prior to setting up your migration job.