devxlogo

A Test Drive of the MySQL Migration Suite

A Test Drive of the MySQL Migration Suite

he MySQL Migration Suite facilitates the movement of data from a proprietary database application. This MySQL product suite consists of three tools:

The MySQL Migration Toolkit uses an eight-step process to ease the transition of data from a proprietary database (such as Microsoft Access, Microsoft SQL Server, or Oracle) to the MySQL open source database. The MySQL Query Browser adds color-coded editing and auto code-generating capabilities, which makes managing your databases, tables, and history much easier. Grouped with the MySQL Administrator, the suite also reduces the complexity of the DBA’s chores, such as backup, viewing server logs, and optimizing server performance.

What You Need:
* 32-bit Windows platform running the Java Runtime Environment Standard Edition 1.4.2
* MySQL version 4.1 and higher (OS independent)
* Oracle, Microsoft SQL Server, or Microsoft Access data

Generous Licensing

The most obvious advantage for migrating to MySQL is the end user licensing agreement. MySQL is released under a dual licensing model, providing both open- and closed-source product solutions:

  1. The open source license: if you have a product that is released under the General Public License (GPL) and you wish to include MySQL in the product, you are at liberty to do so with no financial obligation to the MySQL corporation.
  2. The commercial license: if you develop a product that includes the MySQL server in your product and you do not want to release the source code of your product you must use the MySQL commercial license.

A very interesting aspect of the MySQL Open Source license is that it allows you to use the MySQL database to power any application?GPL or not?so long as you don’t distribute copies of the MySQL engine you use. MySQL remains free for users who never copy or distribute the MySQL software, whether or not they release the source code, regardless of custom changes to the MySQL server.

The Migration Process

Migrating a database to MySQL starts with the MySQL Migration Toolkit for Windows. The MySQL Toolkit has not yet been ported to Mac or Linux. To install the toolkit, you also need to have version 1.4.2 of Sun’s J2SE Java Runtime Environment (JRE) running because the toolkit incorporates a Java agent. First, check whether you have a previous version of Java installed. If so, remove it and restart your computer. You then can download and install the current JRE.

Double click j2re-1_4_2_08-windows-i586-p.exe to install the JRE. I chose the Windows installation instead of the 52MB offline installation file download. I installed the entire Java package including source code and development tools. Though you are not prompted to restart your machine here, it is good Windows housekeeping to reboot.

Next, download the MySQL Migration Toolkit. It has a typical, straightforward Windows installer, but the start menu lacks an uninstall option. Of course, you can uninstall the MySQL Migration Toolkit from the control panel ?> add/remove programs, but programs should uninstall with as few clicks as they install.

To continue, you need permission to remotely administer your MySQL database. If you haven’t already, grant permissions to a user on the remote MySQL system from your Windows computer:

> GRANT ALL PRIVILEGES ON *.* TO root@'192.168.0.67' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;> FLUSH PRIVILEGES;

Alternatively, if you have the MySQL database installed on the same computer as the MySQL Migration Toolkit, use localhost as the database address, along with the necessary username and password.

Now you should be able to connect to MySQL through the toolkit’s GUI (see Figure 1). The user window is split into two distinct parts: the eight-step Migration Plan and the Wizard Interface. Currently, direct migration (moving data from your Windows box to a local or an external server) is the only available option. Work is continuing on an agent-based migration scheme that will allow the MySQL Migration Toolkit to transfer data between two remote servers.

Click to enlarge
Figure 1: Remote MySQL Connection

The next screen indicates whether or not your connection was successful (see Figure 2).

Click to enlarge
Figure 2: Remote MySQL Connection Confirmed

Continue to press Next until you complete the reverse engineering and migrate the database (see Figure 3).

Click to enlarge
Figure 3: Remote MySQL Connection Showing All Objects

Continue through the steps involving manual editing, schema creation, and data mapping until you get to step eight: the summary report. Once you make it to this step, chances are you’ve reverse engineered and migrated your database over to MySQL successfully. Review the summary report output (see Figure 4) and click Finish.

Click to enlarge
Figure 4: Step Eight: Summary Complete

The toolkit provides verbose details of each of the eight steps as you progress through the migration. To simplify matters even further, the MySQL homepage outlines this process in video presentations for both Oracle and Access migrations.

The MySQL Query Browser

Now that you have migrated the data to MySQL, it is time to install theMySQL Query Browser. Installation is as easy as download and double click. Consistent with the expectations of the average Windows end user, the interface utilizes drag-and-drop tools in a fully customizable file explorer fashion.

The MySQL Query Browser facilitates SQL editing with a wealth of rich features, including auto code completion, vertical and horizontal page split, image viewing, and file export to CVS, HTML, XML and Excel formats. Also included is a nice feature that functionally color codes edited changes. When you are ready to commit to an edit, you press the Apply Changes button at the bottom of the screen. Teamed with the MySQL Query Browser, the MySQL Migration Toolkit features an easy-to-navigate visual interface to the MySQL server, consistent with the drag-and-drop Windows style of GUI.

The MySQL Administrator

For visual administrative features, the MySQL Administrator provides visual feedback to the database administrator with functionality previously found only on the command line. Useful for file backups, log views, user admin, and server diagnostics, the MySQL Administrator is an extremely useful and visually verbose cross-platform diagnostics tool for the DBA. Like the MySQL Query Browser, the MySQL Administrator features an edit-changes-before-applying-changes style. The administrator can make and test changes before implementing them, greatly easing the pressure of live edits.

Room for Improvement

The MySQL Migration Toolkit provides all the functionality one has come to expect from a mature Windows program, but it currently is available only on Windows. Mac or Linux users are out of luck for the time being. While the MySQL Migration Toolkit is capable of transferring proprietary tables and data to a MySQL server, it cannot currently transfer Visual Basic for Applications (VBA) runtime code or queries bound to form controls. Oracle views, procedures, and sequences are also currently not supported. As an interim workaround, you can use MyODBC to link your Access front end with the MySQL server on the backend. Another alternative is to redesign your VB code and macros in Perl using Project: Axis (Not_Evil), a project whose goals include developing data-driven GUI apps under Linux.

Easy to Use, Easier on Your Budget

Despite its limitations, the MySQL Migration suite (beta) is a rapidly maturing project that provides an easy-to-use visual GUI for MySQL server migration, editing, and administration. Each of the three included applications is a standalone that provides its own visual working environment to the MySQL server.

Using an eight-step migration process, the MySQL Migration Toolkit is easy to install, visually pleasing, and intuitive. Because it incorporates a platform-independent Java agent and standards-based JDBC connectivity, the MySQL Migration Toolkit makes transferring data to and from the MySQL server practically effortless. Taking advantage of the free MySQL server license, you can (for example) migrate an Oracle database running on Solaris to a MySQL server running on BSD or Linux. Under the dual license agreement, migrating to the MySQL server has the potential to reduce the overhead costs associated with proprietary database servers. Compared with the command line, the MySQL Migration Suite makes migrating, querying, and administering a MySQL server a walk in the park.

Acknowledgements: Special thanks to Daniel Kasak, Bill White, Chad Auld, and Matt Comb

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist