MySQL Workbench Manual  /  ...  /  A Visual Guide to Performing a Database Migration

10.2.1 A Visual Guide to Performing a Database Migration

This example migrates a Microsoft SQL Server database to MySQL and includes an image for each step.

From MySQL Workbench, choose Database and then Migrate to open the migration wizard and display the migration wizard overview (see the figure that follows).

Overview

Figure 10.2 MySQL Workbench migration: Overview

Content is described in the surrounding text.

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. The following figure shows an example of the connection session.

Figure 10.3 MySQL Workbench migration: Source Selection (Parameters)

Content is described in the surrounding text.

Target Selection

The target is the MySQL database that will contain the newly migrated database (see the figure that follows). The current Workbench MySQL connections will be available here, or you can choose Manage DB Connections to create a new connection.

Figure 10.4 MySQL Workbench migration: Target selection

Content is described in the surrounding text.

Fetch Schemas List

The Schemas list is retrieved from both the source and target RDBMS (see the figure that follows). This is an automated and informational step that reports connection related errors and/or general log information. Click Next to continue.

Figure 10.5 MySQL Workbench migration: Fetch Schemas List

Content is described in the surrounding text.

Schemas Selection

Choose the schemas you want to migrate.

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

  • Keep schemas 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. The following figure shows an example of this mapping method.

  • Only one schema, keep current schema names as a prefix: Catalog.Schema.Table -> Catalog.Schema_table: Preserves the schema name as a prefix.

Figure 10.6 MySQL Workbench migration: Schemas Selection

Content is described in the surrounding text.

Reverse Engineer Source

The source metadata is fetched from the source RDBMS and then is reverse-engineered. This is an automated and informational step that reports related errors, general log information, or both (see the figure that follows). View the logs and then click Next to continue.

Figure 10.7 MySQL Workbench migration: Reverse Engineer Source

Content is described in the surrounding text.

Source Objects

The discovered objects from the Reverse Engineer Source stage are revealed and made available. As the next figure shows, the results include Table, View, and Routine objects, with only the Table objects being selected by default.

Figure 10.8 MySQL Workbench migration: Source Objects

Content is described in the surrounding text.

Migration

The migration process now converts the selected objects into MySQL compatible objects (see the figure that follows). View the logs and then proceed.

Figure 10.9 MySQL Workbench migration: Migration

Content is described in the surrounding text.

Manual Editing

There are three sections to edit, which are selected by using 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. The following figure shows an example of this type of message.

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

    Content is described in the surrounding text.

  • 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. The following figure shows an example of this type of message.

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

    Content is described in the surrounding text.

  • 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 following figure shows an example of this type of message.

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

    Content is described in the surrounding text.

Target Creation Options

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

Figure 10.13 MySQL Workbench migration: Target Creation Options

Content is described in the surrounding text.

Create Schema

Now the schema is created (see the figure that follows). The complete log is also available here.

Figure 10.14 MySQL Workbench migration: Create Schema

Content is described in the surrounding text.

Create Target Results

The generated objects in this example are listed in the figure that follows, 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 then click Apply. Repeat this process for each object that will be edited. Finally, click 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 Schema) with the modified code, and then continue the migration process. This also means that the previously saved schema will be dropped.

Figure 10.15 MySQL Workbench Migration: Create Target Results

Content is described in the surrounding text.

Data Transfer Setup

The next step transfers data from the source RDBMS to the target MySQL database. The setup screen includes the following options:

Data Copy:

  • Online copy of table data to target RDBMS: This method (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.

    Note

    This option was added in MySQL Workbench 6.3.0.

Options (see the figure that follows for an example):

  • Truncate target tables before copying data: In case the target database already exists, this will delete said data.

  • Worker tasks: The default value is 2. This is the number of tasks (database connections) used while copying the data.

  • Enable debug output for table copy: Shows debugging information.

Figure 10.16 MySQL Workbench Migration: Data Transfer Setup

Content is described in the surrounding text.

Bulk Data Transfer

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 (see the figure that follows).

Figure 10.17 MySQL Workbench Migration: Bulk Data Transfer

Content is described in the surrounding text.

Migration Report

And finally, the migration report is available and summarizes the entire migration process as depicted in the following figure.

Figure 10.18 MySQL Workbench Migration: Migration Report

Content is described in the surrounding text.

Clicking 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 as the following figure shows.

Figure 10.19 MySQL Workbench Migration: Viewing the Migrated Database

Content is described in the surrounding text.

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.