This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .
We are pleased to announce the preview of MySQL Data Migration – Schema Migration feature which enables users to migrate the schema for objects such as tables, views, triggers, events, stored procedures, and functions. This feature is useful for automating some of the target database preparation work prior to starting a migration.
To complete the migration successfully with schema migration you need to ensure the below listed prerequisites are met.
Prerequisites:
- Please ensure that the user on the source server has the following permissions:
- “SELECT” privilege for the ability to select objects from the database
- If migrating views, user must have the “SHOW VIEW” privilege.
- If migrating triggers, user must have the “TRIGGER” privilege.
- If migrating routines (procedures and/or functions), the user must be named in the definer clause of the routine. Alternatively, based on version, the user must have the following privilege:
- For 5.6 and 5.7, have “SELECT” access to the “mysql. proc” table.
- For 8.0, have “SHOW_ROUTINE” privilege or have the “CREATE ROUTINE,” “ALTER ROUTINE,” or “EXECUTE” privilege granted at a scope that includes the routine.
- If migrating events, the user must have the “EVENT” privilege for the database from which the event is to be shown.
- Please ensure that the user credentials on the target server has the following permissions:
- To create tables on the target, the user must have the “CREATE” privilege.
- If migrating a table with “DATA DIRECTORY” or “INDEX DIRECTORY” partition options, the user must have the “FILE” privilege.
- If migrating to a table with a “UNION” option, the user must have the “SELECT,” “UPDATE,” and “DELETE” privileges for the tables you map to a MERGE table.
- If migrating views, you must have the “CREATE VIEW” privilege.
- Keep in mind that some privileges may be necessary depending on the contents of the views. Please refer to the MySQL docs specific to your version for “CREATE VIEW STATEMENT” for details
- If migrating events, the user must have the “EVENT” privilege.
- If migrating triggers, the user must have the “TRIGGER” privilege.
- If migrating routines, the user must have the “CREATE ROUTINE” privilege.
- Please ensure the target database is created before you proceed to further steps. There is no requirement for the database to be populated with tables/views etc. Please be sure to set the appropriate character, collations, and any other applicable schema settings prior to starting the migration as this may affect the DEFAULT set in some of the object definitions. Additionally, if migrating non-table objects, be sure to use the same name for the target schema as is used on the source.
How to get started:
To begin schema migration, data migration needs to be initiated from the wizard. The options to enable schema migration are described below.
A schema migration can be started from the Azure Portal. Please follow the Tutorial: Migrate MySQL to Azure Database for MySQL offline using DMS tutorial for prerequisites for setting up DMS for the MySQL migration. The schema for migration can be either selected for individual tables and all other non-table types will be migrated in full by following the steps for either object types or tables below:
- To migrate the schema for non-table objects, proceed to the “Select databases” tab. There you can select which objects you wish to migrate.
All the objects selected here will be migrated for the databases that are selected below. Databases and object types within those databases there were not selected will be skipped.
- To migrate the schema for table objects, proceed to the “Select tables” tab. Before the tab populates, DMS will fetch the tables from the selected database(s) from the source and target. With that information, DMS is able to determine if the table exists and if it has data. There you can select which tables to migrate the schema for.
If a table does not exist on the target database, the schema migration option will be checked by default if the table is selected for data migration. If the table exists, there will be a note that the table selected already has data and will be truncated. Also, if the existing table schema does not match the schema on the source, the table will be dropped before the migration.
After you continue from this tab to the next, DMS will validate your inputs and confirm that the tables selected match if they were selected without the schema migration input. If the validation passes, you will be able to begin the migration scenario.
- After you begin the migration and as the migration progresses, each table will be created prior to migrating the table’s data from the source to the target. For other objects, the objects will be created prior to the data migration for the tables except for views and triggers: those will be migrated after the data migration portion completes.
How schema migration works:
Schema migration is supported by MySQL’s “SHOW CREATE” syntax to gather schema information for objects from the source. When migrating the schema for the objects from the source to the target, DMS processes the input and individually migrates the objects. DMS also wraps the collation, character set, and other relevant information that is provided by the “SHOW CREATE” query to the create query that is then processed on to the target.
Routines and Events are migrated . The schema for each individual table is migrated immediately prior to data movement starting for the table. Triggers are migrated after the data migration portion. For views, since MySQL validates the contents of views and they can depend on other tables, DMS first creates tables for views before the start of database data movement and then drops the temporary table and creates the view.
When querying the source and target, if a transient error occurs, DMS will retry the queries. However, if an error occurs that DMS cannot recover from – as an example, an invalid syntax when performing a version upgrade migration – DMS will fail and report that error message on completion. If the failure occurs when creating a table, the data for that table will not be migrated, but the data and schema migration for the other selected tables will still be attempted. If an unrecoverable error occurs for events, routines, or when creating the temporary table for views, the migration will fail prior to running the migration for the selected tables and the objects that are migrated following the data migration portion.
Since a temporary table is created for views, if there is a failure migrating a view, the temporary table will be left on the target. After the underlying issue is fixed and the migration is retried, DMS will delete that table prior to creating the view. Alternatively, if electing not to use schema migration for views in a future migration, the temporary table will need to be manually deleted prior to manually migrating the view.
Limitations:
- When migrating non table objects support does not exist for renaming databases.
- When migrating to a target server that has bin_log enabled, log_bin_trust_function_creators should be enabled to allow for creation of routines and triggers.
- When migrating please ensure that the database exists on the target server. Schema migration only supports migrating objects and does not support creating the database on the target server.
- Currently there is no support for migrating the DEFINER clause for objects. All object types with definers on source will get dropped and after the migration the default definer for tables will be set to the login used to run the migration.
- We have not tested version upgrade scenarios and results are not guaranteed. Some version upgrades are not supported if there are breaking changes in version compatibility. Refer to the MySQL docs for more information on version upgrades.
- Currently we can only migrate schema as part of data movement. If nothing is selected for data movement, no schema migration will happen. If a table is selected for schema migration, it will also be selected for data movement.
- MySQL Migration tutorials will be updated in the future and currently schema migration preview is not covered in the tutorials.
You can also find detailed information on MySQL migration to Azure Database for MySQL using DMS here: Migrate MySQL to Azure Database for MySQL offline using DMS.
We will add support for additional migration features in future releases.