Chapter 28. Scripted Migration

Table of Contents

28.1. The Steps for Scripted Migration
28.2. Setting the Source and Target Connection
28.3. Reverse Engineering
28.4. Migration Methods
28.5. Map Objects and Migrate
28.6. The SQL Create Statements
28.7. Bulk Data Transfer

This section reviews a simple migration script and assumes some familiarity with the GRT shell. If you haven't yet done so, read Chapter 31, The Generic Runtime Environment (GRT) Shell.

As noted earlier in Section 27.17, “The Summary Screen”, if you choose, you can generate a migration script when migrating a database. Doing this creates a Lua script of the entire migration process. You can find out more about Lua by going to lua.org. However, if you have some familiarity with programming you should be able to make sense of this script without too much effort.

Note

The MySQL Migration Toolkit supports Lua version 5.0. Version 5.1 is not supported.

For a better understanding of the migration script, it is useful to have the GRT shell open within the MySQL Migration Toolkit. In this way you can examine the various objects created during migration by clicking on them in the Globals Tree panel. To save the application state and have access to these objects, migrate a database as described in Chapter 27, The Migration Process In-Depth, and when you reach the Summary step, choose the menu options File, Store current application state .... This will save an XML file of the entire migration process. You can now reload the migration state whenever you wish.

28.1. The Steps for Scripted Migration

The migration script is conveniently divided up into six sections or checkpoints:

  1. Set Source and Target Connection

  2. Do the Reverse Engineering

  3. Migration Methods and Ignore List

  4. Set Object Mappings and Do Migration

  5. Generate and Execute SQL Create Statements

  6. Bulk Data Transfer

Each checkpoint will be examined in turn. To reload the application state and have access to the objects created during migration, choose the menu options File, Reload stored application state .... Find the XML file that you previously saved. Open this file and you will see how the various objects in the GRT Globals Tree are referenced from the shell. Press F4 to open the GRT shell.

28.2. Setting the Source and Target Connection

With the GRT shell open and the Values tab selected, double click the migration object and find the sourceConnection object. Click this item and its parameters and the values of those parameters will be exposed in the frame beneath the Globals tree. The parameters are as follows:

  • _id

  • driver

  • modules

  • name

  • parameter values

Note

If you cannot find a sourceConnection object then the application state has not been reloaded.

Open the Lua script that you generated during migration and find the -- Set source connection line. Immediately below this line is the code that defines the source connection of the schema that is being migrated.

The names of the parameters of the source connection in the Lua script should match the parameters shown in the frame below the Globals tree (though they do not appear in the same order). The value shown for the _id parameter is the value created by the newGuid method of the grt object. The Universally Unique Identifier (UUID) of the driver show in this frame should match the value shown in your script.

The name is the name of the variable being created by the Lua script.

In the Values tab both the modules and parameterValues items show as objects of the dict type.

Click on modules to see the modules used during migration. The MigrationModule has a name specific to the migration source schema. If you are migrating from Access, for example, it is called MigrationAccess. Click on parameterValues and see the parameters you supplied for connecting to the source schema.

Below the -- set struct and types line find the definition of the data types used so far. A sourceConnection is a struct derived from the db.mgmt.Connection structure. modules and parameterValues are both strings.

The parameters and the modules used for a targetConnection are shown beneath the --Set target connection line. After examining a sourceConnection you should quickly be able to understand a targetConnection. The same applies to the structs and data types used by the targetConnection.

28.3. Reverse Engineering

The conversion of the source schema to a MySQL schema occurs in the lines immediately following the comment -- Do the reverse engineering. This calls the GRT reverse engineering module to convert from the current schema type to the target type.

Note

To convert GRT objects to Lua values requires using the toLua method. Future versions of the GRT may overload the assignment operator.

The reverse engineering modules used by the MySQL Migration Toolkit are also used by the MySQL Workbench.

28.4. Migration Methods

The code following the comment -- Migration methods and ignore list invokes the migration module appropriate to the source database.

If you are migrating from Access, for instance, the MigrationAccess module will be invoked. Find this module in the modules tab to examine its three methods:

  • migrationMethods

  • migrate

  • dataBulkTransfer

Any schema objects that you chose not to migrate, show up in the ignoreList object. This object is found beneath the migration object in the Globals tree panel.

28.5. Map Objects and Migrate

Instead of finding an object in the Values tab of the Objects tree, you may query an object from the command line in the GRT shell. For example, in the code following the comment -- Set object mappings and do migration a mappingDefaults object is created by the grtV.setGlobal method.

To see that this method has executed successfully enter the following command in the GRT shell:

/ > print(grtV.getGlobal("/migration/mappingDefaults"))

This should output a listing of the mapping defaults as shown in your script file. The results will vary depending upon the objects you have chosen to migrate. However, in all cases you should see a schema mapping.

Beneath the -- update _ids comment the migration methods are copied to a local variable and then executed.

28.6. The SQL Create Statements

The code to create the target schema follows the -- Generate and execute sql create statements comment. If you chose the Create Script File for Create Statements option on the Object Creation Options screen then a text file of the data definition statements required to create the migrated schema is written to a file. For information on setting this option see. Section 27.13, “The Object Creation Options Screen”.

The actual creation of the schema on the database server is done by the transformationModule module.

28.7. Bulk Data Transfer

The code to populated the target schema follows the -- Bulk data transfer comment. If you chose the Create Script File for Insert Statements option on the Data Mapping Options screen then a text file of the insert statements required to populate the migrated schema is written to a file. For more information on setting this option, see Section 27.15, “The Data Mapping Options Screen”.

The actual creation of the data in the new schema is done by the migrationModule module.