Migration to MySQL with SQLyog ver 4.1. - Page 2

Getting Started.

Now let's see what it looks like!

As our example, we will have a Microsoft Access database. It is the "Northwind" database distributed as a demo database with Microsoft Access.  On my system, it is located in the folder C:\Program Files\Microsoft Office\Office\Samples.  If it is not there, then search for the file "Northwind.mdb" or "Nwind.mdb.   It might be in slightly different places with the various versions of Microsoft Access and various Windows versions.  I suggest that you make a copy of it.  We will perform changes to that Northwind database in the process!

In the Access "relations view" (tools ... relationships from the menu), the Northwind database looks like the above image. It is quite a realistic example of a medium size database to use with an administrative system.

First of all: when importing from Microsoft Access there is one special thing we need do to before we can start importing. We need to unhide some system tables and let the import tool read them. It is peculiar to Access that this information is hidden to any human user by default and only readable to the system. These system tables contain information about indexes of all kinds and similar information.  This information is also called "metadata."  http://www.webyog.com/whitepaper/Preparing_Access__Migration.pdf explains how to prepare the Access database file for use with the SQLyog Migration Tool. If you only want to import data and no indexes or primary keys at all, you will not need to perform this step. However, then you must choose not to import indexes and foreign keys at the appropriate step of the migration wizard or an error will occur when the Migration Tool tries to read the index information.

However, in the case of Northwind-database you might find that the metadata tables are already unhidden.  This is because some Microsoft documentation uses this database as an example too and has opened it in advance.

If you have a Microsoft Office Suite installed on your system, you also have the ODBC-driver that the Migration Tool will need to connect to Microsoft Access.  However, when migrating/importing from other ODBC-compliant databases you might need to install the appropriate driver software separately.

Now let's start the Migration Tool wizard!

You start the Migration Tool from the SQLyog "powertools" menu. When launching the "Migration Tool Wizard" the first question is whether you want to create a new job file or edit an existing one. Since this is the first time you use the tool, choose to create a new one and press the "Next"-button. The connection dialogue appears.

This is the "standard" SQLyog connection dialogue. On the Server-tab you choose the MySQL-server parameters for the target MySQL server. If you want to use tunnelling or a proxy, enter the details on the Tunnel-tab. Refer to the SQLyog help-file for details on that. You can also choose a saved connection.  At the button, a drop-down box is added to the standard dialogue where you choose which MySQL database to import to.  It must be an existing database and the MySQL user you connect with must have adequate privileges.  However, it can be an empty database or it may contain data.  So if you do not have a MySQL-database that you want to import to, then create one before starting the Migration wizard.

The next dialogue is this one:

It is where you enter the ODBC-connection details for the source ODBC database. Pressing the "new" button or the "…" button will open the Windows ODBC-manager, which is where to configure an "instance" of an ODBC-driver to point to the Northwind database. It must of course be the ODBC-driver for Access you use for this.  However, if you have configured the ODBC-driver in advance, just choose the appropriate DSN. Enter user authentication details if needed and press "Next."  You will be presented with this:

If you prefer handwritten SQL, an editor window will open if you choose "Use a query ..." Here we will choose the "Copy tables…" option, so that we will see the rest of the GUI-elements of the wizard. The next image shows the table selection tab and the transform-dialogue.