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. The opening screen will look like this:
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.
The source is the RDBMS that will be migrated to MySQL. Define the connection parameters and related information here by first choosing the Database System, as the other parameters will change accordingly to this choice.
The optional Store connection option will save the connection details. It must be set before proceeding to the next step by clicking Next.
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 steps involve transferring 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.
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.
And now the data is transferred to the target RDBMS. 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. The database may 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.
Copyright © 2006, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices