Chapter 9 Database Migration Wizard

An ODBC Driver Manager library must be present. Both Windows and OS X provides one.

Linux

iODBC: MySQL Workbench binaries provided by Oracle already include iODBC and no additional action is required. If you compile it yourself, you must install iODBC or unixODBC. iODBC is recommended. You can use the iODBC library provided by your distribution.

pyodbc: is the Python module used by MySQL Workbench to interface with ODBC, and may be used to migrate ODBC compliant databases such as PostgreSQL and DB2. In Windows and OS X, it is included with Workbench. In Linux, binaries provided by Oracle also include pyodbc.

If you're using a self-compiled binary, make sure you have the latest version, and that it is compiled against the ODBC manager library that you chose, whether it is iODBC or unixODBC. As of version 3.0.6, pyodbc will compile against unixODBC by default. If you are compiling against iODBC then you must perform the following steps:

For each RDBMS, you need its corresponding ODBC driver, which must also be installed on the same machine that MySQL Workbench is running on. This driver is usually provided by the RDBMS manufacturer, but in some cases they can also be provided by third party vendors or open source projects.

Operating systems usually provide a graphical interface to help set up ODBC drivers and data sources. Use that to install the driver (i.e., make the ODBC Manager "see" a newly installed ODBC driver). You can also use it to create a data source for a specific database instance, to be connected using a previously configured driver. Typically you need to provide a name for the data source (the DSN), in addition to the database server IP, port, username, and sometimes the database the user has access to.

If MySQL Workbench is able to locate an ODBC manager GUI for your system, a Tools, Start ODBC - Administrator menu item be present under the Tools menu as a convenience shortcut to start it.

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

Read the migration wizard overview:

Overview

Figure 9.3 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 9.4 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 9.5 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 9.6 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 9.7 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 9.8 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 9.9 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 9.10 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 9.11 MySQL Workbench migration: Manual Editing (Migration Problems)

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

Figure 9.13 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 9.14 MySQL Workbench migration: Target Creation Options

Create Schemata

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

Figure 9.15 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.

Figure 9.16 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 9.17 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 9.18 MySQL Workbench migration: Bulk Data Transfer

Migration Report

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

Figure 9.19 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 9.20 MySQL Workbench migration: Viewing the migrated database

When a supported RDBMS product is being migrated, the MySQL Workbench Migration Wizard will automatically convert as much information as it can, but you may still be required to manually edit the automatically migrated schema for difficult cases, or when the default mapping is not as desired.

Generally speaking, only table information and its data are automatically converted to MySQL. Code objects such as views, stored procedures, and triggers, are not. But supported RDBMS products will be retrieved and displayed in the wizard. You can then manually convert them, or save them for converting at a later time.

The following RDBMS products and versions are currently tested and supported by the MySQL Workbench Migration Wizard, although other RDBMS products can also be migrated with Section 9.2.3, “Migrating from unsupported (generic) databases”

Most ODBC compliant databases may be migrated using the generic database support. In this case, code objects will not be retrieved from the source database; only tables and data.

When using the generic support, column data types are mapped using the following steps:

To be able to migrate from Microsoft SQL Server, ensure the following:

General thoughts on the topic.

Using an ODBC DataSource

Using Connection Parameters

Before proceeding, you will need the following:

9.6.1.2 Linux

After installing iODBC, proceed to install the PostgreSQL ODBC drivers.

Download the psqlODBC source tarball file from http://www.postgresql.org/ftp/odbc/versions/src/. Use the latest version available for download, which will be at the bottom of the downloads page. The file will look similar to psqlodbc-09.01.0200.tar.gz. Extract this tarball to a temporary location, open a terminal, and cd into that directory. The installation process is:

shell> cd the/src/directory
shell> ./configure --with-iodbc --enable-pthreads
shell> make
shell> sudo make install          

Verify the installation by confirming that the file psqlodbcw.so is in the /usr/local/lib directory.

Next, you must register your new ODBC Driver.

Open the iODBC Data Source Administrator application by either executing iodbcadm-gtk in the command-line, or by launching it from the Overview page of the MySQL Workbench Migration Wizard by clicking the Open ODBC Administrator button.

Go to the ODBC Drivers tab in the iODBC Data Source Administrator. It should look similar to:

Figure 9.41 The iODBC Data Source Administrator

Click Add a driver then fill out the form with the following values:

• Description of the driver: psqlODBC

• Driver file name: /usr/local/lib/psqlodbcw.so

• Setup file name: No value is needed here

And lastly, clicking OK will complete the psqlODBC driver registration.

shell> cd the/src/directory
shell> ./configure --with-iodbc --enable-pthreads
shell> CFLAGS="-arch i386 -arch x86_64" make
shell> sudo make install          

If you are compiling psqlodb, first configure with the --without-libpq option.

After loading the Migration Wizard, click on the Start Migration button in the Overview page to begin the migration process. You will first connect to the source PostgreSQL database. Here you will provide the information about the PostgreSQL RDBMS that you are migrating from, the ODBC driver that will be used for the migration, and all of the parameters required for the connection. The name of the ODBC driver is the one you set up when you registered your psqlODBC driver with the driver manager.

Opening the Database System dropdown will reveal each RDBMS that is supported on your system. Select PostgreSQL from the list. Below that is the Stored Connection dropdown, which is optional. Stored connections will be listed here, which are connections saved after defining a connection with the Store connection for future use as checkbox enabled.

The three Connection Method options are:

The migration process is similar to other databases. See Section 9.6.4, “PostgreSQL Type Mapping” for information on how the migration wizard migrates types from PostgreSQL to MySQL, and Section 9.2.1, “A visual guide to performing a database migration” for a general migration guide.

A connection is made to the source and target database servers.

Source Connection Setup

The Source Connection offers the MySQL, Microsoft SQL Server, and Generic RDBMS database system options. This selection determines the available Parameters and Advanced configuration options.

This connection definition may be saved using the Store connection for future use as option, and there is also the Test Connection option.

Target Connection Setup

The MySQL Server that will be home to the newly migrated database.

Fetch Schemata List

The names of available schemas will be retrieved from the source RDBMS. The account used for the connection will need to have appropriate privileges for listing and reading the schemas you want to migrate. Target RDBMS connection settings will also be validated.

The steps that are performed include: connects to the source DBMS, checks the connection, and retrieves the schema list from the source.

Schemata Selection

Select the schemata that you want to migrate. The Schema Name Mapping Method options are:

This is an automated step, where the actions include: Connect to the source DBMS, Reverse engineer the selected schemata, and perform post-processing if needed.

By default, all table objects will be migrated. Use the Show Selection button in order to disable individual table objects from being migrated.

Reverse engineered objects from the source RDBMS are automatically converted to MySQL compatible objects. Default data type and default column value mappings are used, and the generated objects and column definitions may be reviewed and edited in the next step.

The steps performed include Migrating the selected objects, and generating the SQL CREATE statements.

The migrated objects may be reviewed and edited here. You can manually edit the generated SQL before applying them to the target database. Target schemas and tables may be renamed, and column definitions may be changed, by double-clicking on them.

By default, the All Objects View is loaded. Other View options include Migration Problems and Column Mappings.

Defines addition settings for the target schema.

Configuration options include:

The SQL scripts generated for the migrated schema objects will now be executed in the target database. You can monitor execution in the logs, if errors exist then they will be fixed in the next step. Table data will be migrated in a later step as well.

This is an automated step, where the actions include: Create Script File, Connect to Target Database, and Create Schemata and Objects.

Scripts to create the target schemas were executed, but the data has not yet been migrated. This step allows reviewing a creation report. If there are any errors, then you can manually fix the scripts and click Recreate Objects to retry the schema creation or return to the Manual Editing page to correct them there, and then retry the target creation.

To edit, first select the object, and then the SQL CREATE Script will be shown for the selected object. Edit it there, then press Apply to save.

Provides additional options for data transfer, including the ability to set up a script to automate this transfer in the future.

The transfer is executed here.

Displays the final report, that can be reviewed to ensure a proper migration was executed.