10.2.1 A visual guide to performing a database migration

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.

Figure 10.2 MySQL Workbench migration: Start

MySQL Workbench migration: Start

Read the migration wizard overview:

Overview

Figure 10.3 MySQL Workbench migration: Overview

MySQL Workbench migration: 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.

Source Selection

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.

Figure 10.4 MySQL Workbench migration: Source Selection (Parameters)

MySQL Workbench migration: Source Selection (Parameters)

Target Selection

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.

Figure 10.5 MySQL Workbench migration: Target selection

MySQL Workbench migration: Target selection

Fetch Schemata List

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.

Figure 10.6 MySQL Workbench migration: Fetch Schemata List

MySQL Workbench migration: Fetch Schemata List

Schemata Selection

Choose the schemata you want to migrate.

"Schema Name Mapping Method" options while migrating Microsoft SQL Server:

Figure 10.7 MySQL Workbench migration: Schemata Selection

MySQL Workbench migration: Schemata Selection

Reverse Engineer Source

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.

Figure 10.8 MySQL Workbench migration: Reverse Engineer Source

MySQL Workbench migration: Reverse Engineer Source

Source Objects

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.

Figure 10.9 MySQL Workbench migration: Source Objects

MySQL Workbench migration: Source Objects

Migration

The migration process now converts the selected objects into MySQL compatible objects. View the logs and then proceed.

Figure 10.10 MySQL Workbench migration: Migration

MySQL Workbench migration: Migration

Manual Editing

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.

Figure 10.11 MySQL Workbench migration: Manual Editing (Migration Problems)

MySQL Workbench migration: Manual Editing (Program Problems)

Figure 10.12 MySQL Workbench migration: Manual Editing (All Objects)

MySQL Workbench migration: Manual Editing (All Objects)

Figure 10.13 MySQL Workbench migration: Manual Editing (Column Mappings)

MySQL Workbench migration: Manual Editing (Column Mappings)

Target Creation Options

The schema may be created by either adding it to the target RDBMS, creating an SQL script file, or both.

Figure 10.14 MySQL Workbench migration: Target Creation Options

MySQL Workbench migration: Target Creation Options

Create Schemata

Now the schemata is created. The complete log is also available here.

Figure 10.15 MySQL Workbench migration: Create Schemata

MySQL Workbench migration: Create Schemata

Create Target Results

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.

Note

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.

Figure 10.16 MySQL Workbench migration: Create Target Results

MySQL Workbench migration: Create Target Results

Data Transfer Setup

The next steps involve transferring data from the source RDBMS to the target MySQL database. The setup screen includes the following options:

Data Copy:

Options:

Figure 10.17 MySQL Workbench migration: Data Transfer Setup

MySQL Workbench migration: Data Transfer Setup

Bulk Data Transfer

And now the data is transferred to the target RDBMS. Optionally, view the logs to confirm.

Figure 10.18 MySQL Workbench migration: Bulk Data Transfer

MySQL Workbench migration: Bulk Data Transfer

Migration Report

And finally, the migration report is available and summarizes the entire migration process.

Figure 10.19 MySQL Workbench migration: Migration Report

MySQL Workbench migration: Migration Report

Pressing Finish will close the migration window. The database may now be viewed within the MySQL Workbench SQL editor.

Figure 10.20 MySQL Workbench migration: Viewing the migrated database

MySQL Workbench migration: Viewing the migrated database

Note

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.