We're pleased to announce the preview of the MySQL Schema Migration feature for Azure Database Migration Service (DMS)! This functionality 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 work required to prepare the target database prior to starting a migration.
Prerequisites
To complete a schema migration successfully, ensure that the following prerequisites are in place.
- On the source server, the user performing the migration requires the following privileges:
- “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.
- On the target server, the user performing the migration requires 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.
- A target database, though it need not be populated with tables/views etc.
- 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, start DMS, and then initiate data migration using the wizard. The options to enable schema migration are described below.
Schema Migration is supported for a wide range of different MySQL objects. 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, on the Select databases tab, select the objects you want to migrate.
- Then, under Source Database, select the database(s) to migrate.
Selected objects in the selected databases will be migrated, while all unselected items are skipped.
- To migrate the schema for table objects, navigate to the Select tables tab. Before the tab populates, DMS fetches the tables from the selected database(s) on the source and target, and then determines whether the table exists and contains data.
If you select a table in the source database that does not exist on the target database, the box under Migrate schema is selected by default. For tables that do exist in the target database, a note indicates that the selected table already contains and will be truncated. In addition, Also, if the schema of a table on the target server does not match the schema on the source, the table will be dropped before the migration continues.
When you continue to the next tab, 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 is created prior to migrating its data from the source to the target. Except for triggers and views, which are migrated after data migration is complete, other objects are created for tables prior to the data migration.
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 before any data is 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, DMS does not support 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.
In future releases, we'll include support for additional migration features, so stay tuned!
