Chapter 27. The Migration Process In-Depth

Table of Contents

27.1. Introduction
27.2. The Welcome Screen
27.3. The Configuration Type Screen
27.4. The Source Database Screen
27.4.1. Microsoft Access
27.4.2. Microsoft SQL Server
27.4.3. Oracle
27.4.4. MySQL
27.4.5. Saving Connection Information
27.5. The Target Database Screen
27.6. The Connect to Server Screen
27.7. The Source Schema Selection Screen
27.8. The Reverse Engineering Screen
27.9. The Object Type Selection Screen
27.9.1. Migrating a Sub-Set of Object Types
27.10. The Object Mapping Screen
27.10.1. GRT Object
27.10.2. Table Objects
27.11. The Meta Migration Screen
27.12. The Manual Editing Screen
27.13. The Object Creation Options Screen
27.14. The Creating Objects Screen
27.15. The Data Mapping Options Screen
27.16. The Bulk Data Transfer Screen
27.17. The Summary Screen
27.18. Saving the Current Application State

27.1. Introduction

In this chapter we will cover the different steps of the MySQL Migration Toolkit in depth. The different steps will be covered in the order that they appear in the MySQL Migration Toolkit.

27.2. The Welcome Screen

The first screen of the MySQL Migration Toolkit is the Welcome Screen:

Figure 27.1. The MySQL Migration Toolkit Welcome Screen

The MySQL Migration Toolkit welcome
          screen

The welcome screen indicates the progress of loading the various components of the MySQL Migration Toolkit. If you encounter any errors on the welcome screen you should close the MySQL Migration Toolkit and confirm that you have properly installed the Java Runtime Environment. See Chapter 25, Introduction to the MySQL Migration Toolkit for information on downloading and installing the Java Runtime Environment.

27.3. The Configuration Type Screen

The Configuration Type screen allows you to choose between a Direct Migration and a Agent-Based Migration:

Figure 27.2. The Configuration Type Screen

The Configuration Type screen

Use the Direct Migration if the MySQL Migration Toolkit is installed on either the source or target machine. The Direct Migration should not be used when the MySQL Migration Toolkit is not located on either the source or target machine, as it will create a large amount of network traffic and will result in decreased performance.

Use the Agent-Based Migration when migrating between two server machines that do not support the use of the MySQL Migration Toolkit. The MySQL Migration agent should be installed on the source machine before using the Agent-Based Migration.

27.4. The Source Database Screen

Use the Source Database screen to select the source RDBMS used in the migration and to specify the connection parameters.

The Source Database screen's appearance will vary depending on the type of source database selected.

All Data Source screens will feature a Details button that can be used to expose the Advanced Settings panel. The Advanced Settings panel can be used to manually specify a JDBC driver and JDBC connection string for your migration session.

27.4.1. Microsoft Access

The Source Database screen appears as follows when you select Microsoft Access as the source database:

Figure 27.3. Source Database – Microsoft Access

Source database – Microsoft
            Access

Specify the path to the .mdb database file in the Database File field and specify the database user name and password information if applicable.

You must make special modifications to your Access database file before it can be used with MySQL Migration Toolkit. Please see Chapter 30, Preparing a Microsoft Access Database for Migration for further information.

27.4.2. Microsoft SQL Server

The Source Database screen appears as follows when you select MS SQL Server as the source database:

Figure 27.4. Source Database – Microsoft SQL Server

Source database – Microsoft SQL
            Server

Specify the host name, user name, and password to connect to the source Microsoft SQL Server to connect and click Next.

27.4.3. Oracle

The Source Database screen appears as follows when you select Oracle as the source database:

Figure 27.5. Source Database – Oracle

Source database – Oracle

If you encounter the following database selection screen, it means that you do not have the appropriate JDBC driver for Oracle installed:

Figure 27.6. Oracle JDBC Driver Not Attached

Oracle JDBC driver not attached

If the Oracle JDBC driver is present on your system, click the Locate JDBC Driver on Harddisk button to attach the driver.

If the Oracle JDBC driver is not present on your system, click the Download JDBC Driver from the Web button to download it. Download the ojdbc14.jar file and then attach it by clicking on the Locate JDBC Driver on Harddisk button.

After attaching the Oracle JDBC driver you need to restart the MySQL Migration Toolkit.

27.4.4. MySQL

The Source Database screen appears as follows when you select MySQL as the source database:

Figure 27.7. Source Database – MySQL

Source database – MySQL

27.4.5. Saving Connection Information

After entering the connection information for the source database, click the + button to save the connection information.

When prompted, enter a name for the connection information and click the Ok button to save the connection information for later re-use.

You can discard saved connection information by selecting the saved connection from the Connection drop-down list and clicking the button.

27.5. The Target Database Screen

Use the Target Database screen to select the target RDBMS used in the migration and to specify the connection parameters.

The target Database screen uses an interface that is identical to that of the Source Database screen:

Figure 27.8. Target Database – MySQL

Target Database – MySQL

Target database support for the MySQL Migration Toolkit is currently limited to MySQL 4.1 and MySQL 5.0.

27.6. The Connect to Server Screen

After you specify your source and target database servers, the MySQL Migration Toolkit will connect to each server and retrieve the schema information from the source server:

Figure 27.9. The Connect to Servers Screen

The Connect to Servers screen

Click the Details button to see a more detailed log of the connection process.

27.7. The Source Schema Selection Screen

Use the Source Schema Selection screen to choose which databases from the source server you would like to migrate.

If there are a large number of databases to choose from, you can search for a specific database by entering the database name in the schemata textbox as shown in the following image.

Figure 27.10. The Source Schema Selection Screen

The Source Schema Selection screen

27.8. The Reverse Engineering Screen

Once you select the databases you wish to migrate, the MySQL Migration Toolkit begins the process of reverse engineering the source database:

Figure 27.11. The Reverse Engineering Screen

The Reverse Engineering screen

The MySQL Migration Toolkit collects column and index information for each table in the source database, along with information on stored procedures and views.

Click the Details button to see a detailed log of the reverse engineering process.

27.9. The Object Type Selection Screen

Use the Object Type Selection screen to choose which objects you wish to migrate:

Figure 27.12. The Object Type Selection Screen

The Object Type Selection screen

Check the box next to the objects types (Tables, Views, Stored Procedures) that you wish to migrate.

27.9.1. Migrating a Sub-Set of Object Types

If you only wish to migrate a sub-set of the available object types, click the Detailed Selection button next to the object type:

Figure 27.13. The Detail View of the Object Type Selection Screen

The detail view of the Object Type
            Selection screen

The objects in the left window will be migrated. To ignore an object, select it and click the > button. To move an object out of the ignore list, select it and click the < button.

To exclude objects based on pattern matching, click the + button:

Figure 27.14. The Add Ignore Pattern Dialog

The Add Ignore Pattern dialog

Patterns can include the * and ? characters, with * representing multiple characters (including no characters at all) and ? representing a single character.

27.10. The Object Mapping Screen

Use the Object Mapping screen to configure the methods used to migrate the database objects to MySQL. The MySQL Migration Toolkit comes with multiple migration methods that can be used to convert the database objects of an external RDBMS to MySQL.

In most cases the default settings will be adequate.

There are four areas of migration to be addressed: Generic RunTime (GRT ) Object, Table, View, and Stored Procedures:

Figure 27.15. The Object Mapping Screen

The Object Mapping screen

27.10.1. GRT Object

The GRT Object section of the Object Mapping screen dictates the properties of the database itself. By default a generic profile is used, with a Latin1 character set.

To modify the character set used on the database level, click the Set Parameter button. Choose from Latin1, Multilanguage, or User defined.

27.10.2. Table Objects

The Table section of the Object Mapping screen dictates the properties of the individual tables. By default a generic profile is used, with an InnoDB storage engine.

To modify the storage engine used with the migrated tables, click the Set Parameter button. Choose the Data consistency option to use the InnoDB storage engine for transactional and foreign key support. Choose the Statistical data option to use the MyISAM storage engine with increased performance but no transaction safety. Choose the Data consistency / multilanguage option to use the InnoDB storage engine with UTF8 as the default charset. If none of the provided options meet your needs, choose the User defined option and provide your own settings.

27.11. The Meta Migration Screen

After you configure data object mapping, the MySQL Migration Toolkit performs the conversion of the database objects and generates SQL CREATE statements.

Figure 27.16. The Meta Migration Screen

The Meta Migration screen

The SQL CREATE statements are not executed on the target server at this stage of the migration, but will be executed later.

Click the Details button to view a detailed log of this stage of the migration process.

27.12. The Manual Editing Screen

Use the Manual Editing screen to review the SQL CREATE statements generated by the MySQL Migration Toolkit:

Figure 27.17. The Manual Editing Screen

The Manual Editing screen

By default only objects that were not successfully converted are displayed in the Manual Editing screen. To view all objects select the Show All Objects entry of the Filter drop-down list. Choose the Show All Objects with Messages entry of the Filter drop-down list to view all object with status messages.

To edit the SQL CREATE statements created by the MySQL Migration Toolkit, select the object and click the Details button:

Figure 27.18. The Manual Editing Screen – Detailed View

The Manual Editing screen – detailed
          view

Make changes to the CREATE statement and click the Apply Changes button. If you make a mistake while editing, click the Discard Changes button to undo the CREATE statement.

27.13. The Object Creation Options Screen

After performing manual object editing the MySQL Migration Toolkit is ready to create the database objects on the target server. You have the option of either creating the database objects directly on the target server or to create a script file of the CREATE statements for later execution:

Figure 27.19. The Object Creation Options Screen

The Object Creation Options screen

Select both options to create the target database objects and preserve a backup copy of the CREATE statements for later use.

27.14. The Creating Objects Screen

Once you choose object creation objects, the MySQL Migration Toolkit connects to the target server and creates the database objects (assuming you chose to have the MySQL Migration Toolkit connect to the target server to create the database objects).

Figure 27.20. The Creating Objects Screen

The Creating Objects screen

Click the Details button to view a detailed log of this stage of the migration process.

Choose the Create Script File for Create Statements check box to save a copy of the CREATE statements to disk.

27.15. The Data Mapping Options Screen

After the database objects are created on the target database server the MySQL Migration Toolkit is ready to move the server data to the target server. You have the option of either inserting the data directly on the target server or to create a script file of the INSERT statements for later execution:

Figure 27.21. The Data Mapping Options Screen

The Data Mapping Options screen

Select both options to move the data and preserve a backup copy of the INSERT statements for later use.

You can access additional options by clicking the Details button. If you do not wish to move BLOB data to the target server, check the box next to the Exclude BLOB values option. The BLOB data will not be moved to the target server and will not be written to the script file. If you do not wish to move BLOB data to the target server, but wish to have the BLOB data written to the script file, also check the box next to the Write BLOBs to Insert Script option.

If you wish to limit the number of rows transferred to the target server, check the box next to the Maximum Numbers of Rows to Transfer for a Table option and enter the desired number of rows. This option can be useful when generating test data.

27.16. The Bulk Data Transfer Screen

Once the data mapping options are set, the MySQL Migration Toolkit will begin the bulk data transfer process. Data will be converted to a MySQL compatible format and inserted into the target database server using bulk INSERT statements. Data is typically inserted in batches of 15,000 rows at a time to maximize insertion speed.

Figure 27.22. The Bulk Data Transfer Screen

The Bulk Data Transfer screen

Click the Details button to view a detailed log of this stage of the migration process.

27.17. The Summary Screen

Once the bulk data transfer is complete, a summary report of the migration will be displayed:

Figure 27.23. The Summary Screen

The Summary screen

Click the Generate Migration Script to create a Lua script file of the migration process. With this file you can script the migration of a database or customize it. For more information about this topic, see Chapter 28, Scripted Migration.

Click the Finish button to exit the MySQL Migration Toolkit.

27.18. Saving the Current Application State

If you need to close the MySQL Migration Toolkit part way through the migration process, you can save the state of the MySQL Migration Toolkit to disk and continue later.

To save the current state of the MySQL Migration Toolkit, choose the Store current application state option of the File menu.

To later retrieve the application state and resume configuring the migration, choose the Reload stored application state option of the File menu.

Saving the application state will prove useful when examining scripted migration in Chapter 28, Scripted Migration.