MySQL-Migrationsjob mit Metadaten mit DEFINER-Klausel erstellen und ausführen
Mit Sammlungen den Überblick behalten
Sie können Inhalte basierend auf Ihren Einstellungen speichern und kategorisieren.
Übersicht
Bei einem MySQL-Migrationsjob werden keine Nutzerdaten migriert. Daher schlägt der Aufruf von Quellen, die von Nutzern mit der DEFINER-Klausel definierte Metadaten enthalten, beim Aufruf auf dem neuen Cloud SQL-Replikat fehl, da die Nutzer dort noch nicht vorhanden sind.
Um festzustellen, welche DEFINER-Werte in Ihren Metadaten vorhanden sind, können Sie die folgenden Abfragen in Ihrer MySQL-Quelldatenbank ausführen. Prüfen Sie die Ergebnisse auf Einträge für root%localhost oder für Nutzer, die in der Zielinstanz nicht vorhanden sind:
[[["Leicht verständlich","easyToUnderstand","thumb-up"],["Mein Problem wurde gelöst","solvedMyProblem","thumb-up"],["Sonstiges","otherUp","thumb-up"]],[["Schwer verständlich","hardToUnderstand","thumb-down"],["Informationen oder Beispielcode falsch","incorrectInformationOrSampleCode","thumb-down"],["Benötigte Informationen/Beispiele nicht gefunden","missingTheInformationSamplesINeed","thumb-down"],["Problem mit der Übersetzung","translationIssue","thumb-down"],["Sonstiges","otherDown","thumb-down"]],["Zuletzt aktualisiert: 2025-09-05 (UTC)."],[[["\u003cp\u003eMySQL migration jobs do not migrate user data, leading to potential failures when metadata defined with the \u003ccode\u003eDEFINER\u003c/code\u003e clause is invoked on the new Cloud SQL replica if the users are not present.\u003c/p\u003e\n"],["\u003cp\u003eYou can identify which \u003ccode\u003eDEFINER\u003c/code\u003e values exist in your metadata by running specific queries on your MySQL source database, checking for \u003ccode\u003eroot%localhost\u003c/code\u003e or users absent in the destination instance.\u003c/p\u003e\n"],["\u003cp\u003eTo ensure a successful migration, you must either create the necessary users on the destination Cloud SQL replica before starting the migration job, or update the \u003ccode\u003eDEFINER\u003c/code\u003e clause to \u003ccode\u003eINVOKER\u003c/code\u003e on the source MySQL instance.\u003c/p\u003e\n"],["\u003cp\u003eMetadata created by \u003ccode\u003e'root'@'localhost'\u003c/code\u003e will cause the process to fail, and you will need to change the definer to a different user before starting the migration.\u003c/p\u003e\n"]]],[],null,["# Create and run a MySQL migration job containing metadata with a DEFINER clause\n\n\u003cbr /\u003e\n\nOverview\n--------\n\nA MySQL migration job\n[doesn't migrate user data](/database-migration/docs/mysql/migration-fidelity). Therefore,\nsources which contain metadata defined by users with the `DEFINER` clause will\nfail when invoked on the new Cloud SQL replica, because the users don't yet\nexist there.\n\nTo identify which `DEFINER` values exist in your metadata, you can run the\nfollowing queries on your MySQL source database. Check the results for entries for\neither `root%localhost` or for users that don't exist in the destination instance: \n\n SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA NOT \n IN ('mysql', 'sys');\n SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA NOT \n IN ('mysql', 'sys');\n SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA NOT \n IN ('mysql', 'sys');\n SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA NOT \n IN ('mysql', 'sys');\n\nTo run a migration job from a source which includes such metadata, you can do\none of the following:\n\n- Create the users on the destination Cloud SQL replica instance before starting\n your migration job.\n\n 1. Create a migration job without starting it. That is, choose **Create**\n instead of **Create \\& Start**.\n\n 2. Create the users from your source MySQL instance on your destination\n Cloud SQL instance using the Cloud SQL API or UI.\n\n 3. Start the migration job from the migration job list or the specific job's page.\n\n- Update the `DEFINER` clause to `INVOKER` on your source MySQL instance prior\n to setting up your migration job.\n\n| **Important:** In both cases, if your metadata was created by `'root'@'localhost'`, the process will fail. Change the definer to a different user before starting the migration job."]]
