Data Management

SolutionBase: Migrating a SQL Server database to MySQL 5.0

MySQL helps you migrate databases by using a wizard

This gallery is also available as a TechRepublic article.

Prior to version 5.0 of MySQL, migrating other databases to MySQL was largely a manual process. Version 5.0 introduces the MySQL Migration Toolkit, which provides in graphical form a step by step process for migration. This extensible tool generates scripts that recreate tables from other database systems such as Microsoft SQL Server, Microsoft Access, Oracle, and older versions of MySQL. The tool then copies the data to populate the new tables. In this article, we'll take a guided tour of using the MySQL Migration Toolkit to migrate a database from Microsoft SQL Server, as well as examine a few other alternatives.

Overview of the migration process

Migrating an existing database to another vendor's platform is all about metadata: the data dictionary entries that a database uses to define the objects within it. The structure of the existing database must be understood, including not only the data tables but other objects such as constraints, views, stored procedures, and triggers. Then equivalents must be created in the target database, or workarounds must be found. Finally, the data must be transformed into the new formats and loaded into the target database.

The MySQL Migration Toolkit

The MySQL Migration Toolkit is not a single tool, but a framework for migrating databases. Beneath its graphical interface is a Generic RunTime environment (GRT) that executes a series of modules to perform the actual migration tasks. Reverse Engineering modules extract the metadata from existing databases into GRT definitions called objects; Migration modules convert source GRT objects to target GRT objects; and Transformation modules convert target GRT objects into actual SQL CREATE statements.

The result is a structure that can be extended to support additional databases and to provide transformations for new feature sets. End users may also develop their own modules in C, Java, PHP, and Lua. Support for Python and Mono are in development.

The MySQL Migration Toolkit divides the migration process into eight steps, and presents them in wizard form:

  • Source/Target is the specification of connection information.
  • Object Selection defines which objects in the source database to migrate.
  • Object Mapping indicates how the translation between datatypes will be accomplished.
  • Manual Editing is a pause in the action, during which you can inspect and modify the generated migration scripts before they are run.
  • Schema Creation runs the first set of scripts, which create new objects in the MySQL database.
  • Data Mapping presents options for moving the data itself between the source and target tables.
  • Bulk Transfer actually moves the data.
  • Summary presents a log of the actions of the run.