Table of Contents
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.
The first screen of the MySQL Migration Toolkit is the 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.
The Configuration Type screen allows you to choose between a Direct Migration and a Agent-Based Migration:
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.
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 Advanced Settings panel. The Advanced Settings panel can be used to manually specify a JDBC driver and JDBC connection string for your migration session.
button that can be used to expose theThe Source Database screen appears as follows when you select Microsoft Access as the source database:
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.
The Source Database screen appears as follows when you select
MS SQL Server
as the source database:
Specify the host name, user name, and password to connect to the source Microsoft SQL Server to connect and click
.The Source Database screen appears as follows when you select Oracle as the source database:
If you encounter the following database selection screen, it means that you do not have the appropriate JDBC driver for Oracle installed:
If the Oracle JDBC driver is present on your system, click the
button to attach the driver.
If the Oracle JDBC driver is not present on your system, click
the ojdbc14.jar
file and then attach it by
clicking on the button.
After attaching the Oracle JDBC driver you need to restart the MySQL Migration Toolkit.
The Source Database screen appears as follows when you select MySQL as the source database:
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
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.
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:
Target database support for the MySQL Migration Toolkit is currently limited to MySQL 4.1 and MySQL 5.0.
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:
Click the
button to see a more detailed log of the connection process.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.
Once you select the databases you wish to migrate, the MySQL Migration Toolkit begins the process of reverse engineering the source database:
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
button to see a detailed log of the reverse engineering process.Use the Object Type Selection screen to choose which objects you wish to migrate:
Check the box next to the objects types (Tables, Views, Stored Procedures) that you wish to migrate.
If you only wish to migrate a sub-set of the available object types, click the
button next to the object type: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:
Patterns can include the *
and
?
characters, with *
representing multiple characters (including no characters at
all) and ?
representing a single character.
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:
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 Latin1
, Multilanguage
, or
User defined
.
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 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.
After you configure data object mapping, the MySQL Migration Toolkit
performs the conversion of the database objects and generates SQL
CREATE
statements.
The SQL CREATE
statements are not executed on
the target server at this stage of the migration, but will be
executed later.
Click the
button to view a detailed log of this stage of the migration process.
Use the Manual Editing screen to review the SQL
CREATE
statements generated by the
MySQL Migration Toolkit:
By default only objects that were not successfully converted are displayed in the Manual Editing screen. To view all objects select the Filter drop-down list. Choose the entry of the Filter drop-down list to view all object with status messages.
entry of the
To edit the SQL CREATE
statements created by
the MySQL Migration Toolkit, select the object and click the
button:
Make changes to the CREATE
statement and click
the button. If you make a
mistake while editing, click the button to undo the CREATE
statement.
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:
Select both options to create the target database objects and
preserve a backup copy of the CREATE
statements
for later use.
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).
Click the
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.
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:
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 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.
button. If you do not wish to move BLOB data to the target server, check the box next to theIf 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.
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.
Click the
button to view a detailed log of this stage of the migration process.Once the bulk data transfer is complete, a summary report of the migration will be displayed:
Click the 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
button to exit the MySQL Migration Toolkit.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
option of the menu.To later retrieve the application state and resume configuring the migration, choose the
option of the menu.Saving the application state will prove useful when examining scripted migration in Chapter 28, Scripted Migration.