This example will migrate a Microsoft SQL Server database to MySQL, and include a screenshot for each step.
From MySQL Workbench, choose Database, Migrate to open the migration wizard.
Read the migration wizard overview:
It describes the prerequisites and requirements that should be
understood before proceeding further. The Open ODBC
Administrator option will load
odbcad32.exe, and is used to confirm that
the ODBC Driver for SQL Server is installed, and to make
configuration changes if needed.
Click Start Migration to continue.
Select the source RDBMS that is migrating to MySQL. Choose the Database System that is being migrated, and the other connection parameters will change accordingly.
The target is the MySQL database that will contain the newly migrated database. The current Workbench MySQL connections will be available here, or you can choose Manage DB Connections to create a new connection.
The Schemata list is retrieved from both the source and target RDBMS. This is an automated and informational step that reports connection related errors and/or general log information. Press Next to continue.
Choose the schemata you want to migrate.
"Schema Name Mapping Method" options while migrating Microsoft SQL Server:
Keep schemata as they are: Catalog.Schema.Table -> Schema.Table: This will create multiple databases, one per schema.
Only one schema: Catalog.Schema.Table -> Catalog.Table: Merges each schema into a single database.
Only one schema, keep current schema names as a prefix: Catalog.Schema.Table -> Catalog.Schema_table: Preserves the schema name as a prefix.
The source metadata is fetched from the source RDBMS, and reverse engineered. This is an automated and informational step that reports related errors and/or general log information. View the logs and then press Next to continue.
The discovered objects from the Reverse Engineer Source stage are revealed and made available. This includes Table, View, and Routine objects, with only the Table objects being selected by default.
The migration process now converts the selected objects into MySQL compatible objects. View the logs and then proceed.
There are three sections to edit here, which are selected via the View select box on the top right. The Show Code and Messages button is available with every view, and it will show the generated MySQL code that corresponds to the selected object.
Migration Problems: This will either report problems or display "No mapping problems found." It is an informational screen.
All Objects: An object view that allows you to view and edit the object definitions. Double-click on a row to modify a target objects name.
Column Mappings: Shows all of the table column mappings, and allows you to individually review and fix the mapping for all column types, default values, and other attributes.
The schema may be created by either adding it to the target RDBMS, creating an SQL script file, or both.
Now the schemata is created. The complete log is also available here.
The generated objects are listed here, along with the error messages if any exist.
The migration code may also be viewed and edited here. To make changes, select an object, edit the query code, and press Apply. Repeat this process for each object that will be edited. And then, press Recreate Objects to save the results.
The Recreate Objects operation is required to save any changes here. It will then execute the previous migration step (Create Schemata) with the modified code, and then continue the migration process. This also means that the previously saved schema will be dropped.
The next step transfers data from the source RDBMS to the target MySQL database. The setup screen includes the following options:
Online copy of table data to target RDBMS: This (default) will copy the data to the target RDBMS.
Create a batch file to copy the data at another time: The data may also be dumped to a file that can be executed at a later time, or be used as a backup. This script uses a MySQL connection to transfer the data.
Create a shell script to use native server dump and load abilities for fast migration: Unlike the simple batch file that performs a live online copy, this generates a script to be executed on the source host to then generate a Zip file containing all of the data and information needed to migrate the data locally on the target host. Copy and extract the generated Zip file on the target host and then execute the import script (on the target host) to import the data into MySQL using a LOAD DATA call.
This faster method avoids the need to traffic all data through MySQL Workbench, or to have a permanent network connection between the MySQL servers.
This option was added in MySQL Workbench 6.3.0.
Truncate target tables before copying data: In case the target database already exists, this will delete said data.
Worker tasks: The default value is
This is the number of tasks (database connections) used
while copying the data.
Enable debug output for table copy: Shows debugging information.
Depending on the selected option, this will either transfer the data to the target RDMS (default), generate a simple script for the online data transfer, or generate script to execute on the source host that then generates a Zip file containing both the transfer script and data that will be executed on the target host. Optionally, view the logs to confirm.
And finally, the migration report is available and summarizes the entire migration process.
Pressing Finish will close the migration window. If you chose the online copy then the database can now be viewed within the MySQL Workbench SQL editor.
If a MySQL Workbench SQL Editor tab is already opened, then the schema list within the Object Browser must be refreshed in order to view the newly imported schema.