Migration to MySQL with SQLyog ver 4.1.

Wednesday Sep 21st 2005 by Peter Laursen
Share:

With the release of Webyog's flagship product, SQLyog Enterprise version 4.1, a new ODBC "Power Tool" is introduced that replaces the old "ODBC import tool." Not just a new name for an old tool, the migration tool has been completely re-written and offers a wide range of new possibilities.

Introduction:

With the release of Webyog's flagship product, SQLyog Enterprise version 4.1, a new ODBC "Power Tool" is introduced that replaces the old "ODBC import tool."  Starting with SQLyog version 4.2 it was re-named "SQLyog Migration Tool," so this is the term that I will use in this text.  This is not just a new name for something old that comes in a new bottle--the Migration Tool has been completely rewritten. It is all completely new code, but – more important to users – it offers a wide range of new possibilities. The term "ODBC-import" to me means, "Fetching some external data into MySQL."  "Migration" is much more than that – it imports not only data but also indexes ("ordinary" indexes, Primary Keys, Foreign Keys – all that used to be called "metadata"), and it provides a large set of options for (almost) any situation.

It is equally well fit for use with a Migration Process considered as a one-time event (no matter how long that process might take!) and in situations with "permanent coexistence" (or "permanent migration" if you like) of more database systems within an organisation.

In my opinion the SQLyog ODBC-import tool is the most complete, versatile, intuitive and flexible import tool that exists and that you can imagine. All the options, that the tool offers, are available from the SQLyog GUI. There is no need to edit some 'strange format' file to transform or filter data during import. It lets you easily import any subset ("vertically" or "horizontally") of data and metadata (all sorts of indexes). It can be scheduled, run from external applications, and run in batch-mode in combination with any other OS/system command, executable file and any other job type of the SQLyog Job Agent (SJA).  The other job types offered by the SJA are, MySQL to MySQL synchronization, High Performance Scheduled Backups and "Notifications Service" – the last one is a very unique facility that lets you schedule and execute any SQL with your databases and have formatted result-sets of these queries delivered to your mailbox.

The XML-format of the job file control lets you integrate the full functionality of the ODBC-import tool (and any other job type of the SJA) into your own applications, if that is what you want.

If all of that talk is nonsense to you, you can simply use Migration Tool from the SQLyog GUI, and it is almost as simple as using an Office-suite application.  The wizards will guide you so you will not have to worry about what a complicated thing you actually are doing!

SQLyog has developed terrifically over that last year or so, and it continues to do so.  Before this article is out you will probably see that full support for MySQL version 5 features (Stored Procedures, Triggers and Views) is available, and in just a few weeks more you will have localised versions.  Check the Webyog website frequently at www.webyog.com.  Moreover, do not forget that the Migration Tool is only a "small corner" (though big in itself!) of the complete set of functionalities you will find in SQLyog Enterprise.  I recommend that you download the fully functional demo of SQLyog Enterprise from www.webyog.com if you do not have it, so that you can experiment with it on your own system.  This article should give you ideas enough for many hours of experimenting and testing for yourself.

The content of this article will be:

  • Introduction (you just read it!).
  • Getting Started.  Here we take the "easy approach" to migration to MySQL.
  • Methods used and applied by the Migration Tool.
  • What's New.
    • ODBC-import with SQLyog is now implemented in the code of the SJA (SQLyog Job Agent).
    • It will now import not only DATA but also METADATA.
    • It offers a wide range of general options for the import.
    • It offers various options to transform data during import and options for filtering of which data should be imported.
    • It implements a "trigger" or "write-back" functionality.
    • A notifications' feature similar to the SQLyog/SJA "Notifications Service" has been implemented with the new Migration Tool.
  • What to use the Migration Tool for?
    • Migration to MySQL (as the name of it says!).
    • (More or less) permanent coexistence of MySQL and other databases within an organisation.
  • MySQL and MySQL – a note on how to use the Migration Tool across different MySQL versions.
  • Conclusion.
  • About the author.

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!

Click for larger image

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.

From the transform-dialogue you choose which tables and columns to import, and you can change the name of each table and column and the data type "proposed" by the ODBC-driver if you like.  Of course, the data type must be valid with the data!  But you can replace, for instance, a "proposal" of the ODBC-driver to create a char(40) data type in the MySQL-database with a varchar(60) if you like. Here I have chosen to replace the "proposal" of the ODBC-driver to create an INT-column with my own idea of what the data type with MySQL should be: a BIGINT.  Also, note that the InnoDB table type is chosen.  You need to do that if you want to import foreign keys.

It is in the "DDL/DML"  that you select or deselect the import of metadata.The other options here are not relevant this time since we are importing to an empty MySQL database.  If there were already data in the database that we were importing to, it would be here that you should decide if you would drop the data and it also would be here that you should decide what to do in case of a conflict with the data already in the MySQL database and the newly imported data.

  Note that this is done for each column independently.  They should not be hard to understand.

Click for larger image

To get an idea of the trigger option you could choose to delete one column from a table in the source and you could spot a string type variable in the source and enter a constant string like 'a string' (note it must be surrounded by quotes!) as a trigger value for update.  Open the respective Northwind tables after import and you will find that one column of data has been deleted and one has had its data overwritten with 'a string'.  In the example below, I 'reset' the region-information about customers to some default value "N/A."    Also, note that you can parse the SQL (the UPDATE) that the trigger consists of, by pressing the "Parse"-button.

Note: triggers are only fired against the rows that are imported. So, that lead us to the WHERE clause, which is used for the selection of rows for import.

Here you enter an SQL WHERE statement that will select the rows to be imported.  The keyword WHERE is inserted by the program automatically so do not write it!  It looks like the picture above.  For table "employees" I chose only to import employees who have a 'lastname' ending on 'son' (Hansson, Peterson etc.) Moreover, as I said just above, the WHERE also controls (indirectly) where trigger functionality should be applied.  If no WHERE is specified, all rows will be imported (in SQL no WHERE means EVERYWHERE J), and specified triggers will execute on all rows.  In addition, here you can parse the validity of the SQL-statement before proceeding to next step. Note that with Microsoft Access the use of the Windows' wildcard '*' is legal in an SQL-statement.  Most ODBC-databases would reject this because the corresponding standard SQL wildcard is '%'.

We are now ready to finish the last details: configuration of error handling, email-settings for notification (account-info and the e-mail subject) and finally to decide if the job-description that we just accomplished should be executed immediately from the GUI, saved as an XML jobfile or scheduled as a task with the Windows scheduler. It should all speak for itself.

Above: error handling and general notifications' option.

Configuration of email options: account information (above) and e-mail subject (below).

Press "Next" and the import will start immediately. If you choose to save instead, you can open the saved file from the first dialogue in the wizard at a later point of time, step through the wizard, change things as you like and again have the option to execute, schedule or save.  If you choose to schedule and later want to change the point(s) of time that the job is executed, you can do that directly from "Scheduled Tasks" in the Windows' Control Panel.

Finally, let us have a look at the MySQL database created as a result of what we just did.  In the DATA-pane of SQLyog we see that all the data of the table "products" is there:

In addition, the table was imported with all 'metadata" as we requested and as we can see in SQLyog OBJECTS-pane:

However, no data from table "employees" was imported because there is no employee named "Hansson" or "Peterson" or the like.  You can check yourself that the "employees" table is empty. 

Methods supported and applied by the Migration Tool.

Though the ODBC-standard is about 15 years old, the ODBC-implementation with many database systems has some non-standard issues (for instance, non-standard data-types used for internal purposes, non-standard formats of temporary tables). All of these issues have been sorted out. The ODBC-import tool will import what it should import and no more than that. "Workarounds" for known bugs have been implemented with some commonly used ODBC-drivers.

The new Migration Tool uses ODBC-connections only. JDBC and other (proprietary) connection methods are not supported and there are no plans of supporting any other connection method.

The Migration Tool has been extensively tested with many different database systems: personal/office-suite type databases like Microsoft Access and Dbase as well as common Database Server systems like MS SQL Server, Oracle, Sybase etc.

What's New

We will now delve more deeply into what was briefly introduced with the Northwind-example above. If you had no difficulty in understanding what you have been reading up to now, I propose that you read on.  If not, then try out some rather simple examples for yourself before continuing from here.

An overview

Let's have a more detailed look at what is new with the SQLyog Migration Tool.

  • ODBC-import with SQLyog is now implemented in the code of the SJA (SQLyog Job Agent) (sja.exe and not sqlyog.exe in terms of filenames).
  • It will now import not only DATA but also METADATA.
  • It offers a wide range of general options for the import.
  • It offers various options to transform data during import and options for filtering of which should be imported.
  • It implements a "trigger" or "write-back" functionality.
  • A notifications feature, similar to the SQLyog/SJA "Notifications Service," has been implemented with the new ODBC-import tool.

Let's have it in some more detail.

1) ODBC-import with SQLoyg is now implemented in the code of the SJA.

What is the SJA ?

As you might know, SJA is a standalone command-line MySQL client available for WINDOWS and LINUX (the Windows version, however, can be started from inside the SQLyog GUI as well as we have just seen above). The SJA will run on Windows and Linux, but will connect to any MySQL server running on any OS. That is the meaning of the term "MySQL client."

The ODBC-import tool connects to a MySQL server using the native MySQL C-api (compiled into SJA executable). That MySQL is the "target" of the import. The "source" of the import is any ODBC-compliant database. It uses the Windows standard ODBC-management, so any DSN types such as "user-DSN," "system-DSN" and "file-DSN" are all possible. If Windows can access and accepts the ODBC-driver, SJA can and does too.

The ODBC-import tool has the same advanced connectivity features as the SQLyog GUI itself and the other tools implemented with the Windows' version of the SJA: different tunnelling options and the ability to work behind proxies. The different tunnelling options allow you to connect to your MySQL server even if your ISP/Admin disallows remote connections.

The OBDC-import is only available is the Windows version of the SJA as of now. I cannot tell if it will ever be coded into the Linux-version. I don't think it is "just around the corner." I do not know much about ODBC-implementation on LINUX but I believe that to connect to an ODBC-source from Linux would probably take a completely rewritten version of the code of the Migration Tool. That is opposite to the situation when SQLyog (and the SJA) connects to MySQL, because both MySQL-server and –client implementations on different platforms are very similar.

With the release of SQLyog 4.1, SJA now executes 4 different types of <jobtypes>. Besides the new one with SQLyog 4.1 <importjob> there are the three "old" ones as well: <backupjob>, <notifyjob> and <syncjob>. That I use this XML-like terminology to describe the job types ( or <jobtypes> to stay in XML) is no coincidence, since SJA reads its parameters from an XML-file.

Executing the SJA-based ODBC-import tool.

That the ODBC-import is now implemented in the code of the SJA means that you will not have to start the SQLyog program to do an ODBC-import because the SJA can be run from command-line and more important:

  • The SJA itself can be scheduled using the Windows Scheduler (or any other (third-party) scheduler running as a service with the OS).
  • The SJA can be run in batch-mode from a Windows .bat-file. That .bat-file of course can contain any legal OS/System command and any other executable that can run in batch mode, and passing of a parameter to the executables follows the standard of the OS. For instance if you want to import from an Access database to MySQL using the SQLyog/SJA trigger functionality (that we will describe later) you might want to make a copy of the database(.mdb)-file. The windows .bat file will then be something like
    copy myaccs.mdb myaccs.bak –y
    sja importjob.xml

    (where the –y parameter answers "yes" to the question whether a previous myaccs.bak should be overwritten)

  • There can also be several instances of the SJA in one .bat file. This way you can import from many various sources into a MySQL-database from one .bat file containing more SJA-instances. That the .bat-file itself can be scheduled. Moreover, it may contain other SJA-jobtypes as well – of which each has its own jobfile. For instance, importing (using Migration Tool) from an Oracle Database into MySQL ver 4.1, importing from a MySQL 3.23 server (also using Migration Tool), and importing from another MySQL 4.1 server (where I would generally recommend use of the Synchronization Tool and not the Migration Tool – simply because the checksum's algorithm of the Synchronization Tool is more efficient than using ODBC) could be done as one batch job. Therefore, if you happen to own a factory producing cars and another producing apple juice and even one more – and even if each of those uses different databases – you will be able to transfer all relevant financial data from all of those different places to one central MySQL database when you want to!
  • The SJA can be launched form an external program capable of launching an external program, no matter if it is a scripting language such as PHP or a compiled application written in C or Delphi. That gives you even more advanced options than the batch-mode ability – depending on what you are able to do from that other programming language.

The SJA reads its parameters from an XML-file, readable and editable by humans as well as other programs. The "wizards" built into SQLyog let you create a job-file for all job types. You can save it or execute it immediately, as you like. You only need to have a little bit of experience with mark-up languages to be able to understand or edit an SJA job file.

You can also open any existing job file from the appropriate GUI-"wizard", change the parameters as needed and save or execute it. Personally, I prefer to use the GUI for "small" tasks and the command line for "longer-running" tasks

2) Import of DATA and METADATA.

The import of METADATA and not only DATA is a major achievement of the new ODBC-import tool. The term "METADATA" means "DATA about DATA." It is the data stored in the database itself about the organisation of data--table definitions, information about all sorts of indexes and that sort of thing. How these METADATA are organized and stored differ with various databases, but any database engine will need to store METADATA some way. The new ODBC-import tool reads them all.

The METADATA it reads and imports are

  • (ordinary) indexes, including – of course – unique indexes
  • primary keys
  • foreign keys

That is what is normally associated with the term "METADATA."

Note that when importing to MySQL version 5, it does not attempt to import

  • Stored Procedures
  • Triggers
  • Views

It does not matter whether these database features are supported or not by the source ODBC-database.

As far as Stored Procedures and Triggers are concerned, that is OK with me! Except for the most trivial examples, I do not think I would ever trust an automatic import/conversion tool anyway, unless it had proven very reliable! However, as far as Views are concerned I think that could be implemented safely whenever the ODBC-driver allows for it. So that is one of my wishes for the future.

3) General options for the import

If you are familiar with the SQLyog/SJA MySQL-to-MySQL Synchronization tool, then you also know what to expect to find here. Basically, the same options are implemented with both tools. There are options to choose whether to append imported data to an existing database or table or whether to delete old data before the start of the import. There are also options for what to do in case of errors – cancel the job or simply go on with the next row of data. In addition, in case you have chosen to append data to an existing database, there are options for what then to do in case a Primary Key or Unique Index conflict occurs during import: keep the old data or replace the data with the one from the ODBC-source.

There are options too whether foreign keys and indexes should be imported. Simply uncheck both if you want to import DATA only and NO METADATA.

4) Transformation and Filtering options.

Transformation:

The transformation option lets you transform the names of tables and columns and the column-definition data type for each column of data during import. This is very practical for many reasons – the most obvious is the simple one that field-length definitions with similar data types on various databases differ. This is most obvious with the VARCHAR-type but applies to other types as well. The ODBC-driver used for the import will "propose" a conversion of the data-type, but it might not be the one you want! If the ODBC-driver "proposes" a char(40) as the data type to create with MySQL simply change it to a varchar(60) if you like.

You can do a little experiment yourself to understand the usefulness of this facility. If you have a MySQL and a MS Access database available, try to EXPORT from Access to MySQL (using the MyODBC-driver from MySQL) and compare the result with the one you get when IMPORTing into MySQL (using the Microsoft ODBC driver for Access and SQLyog, but without making use of the transform option). If you never did that comparison before you will probably be surprised how much ODBC-drivers disagree on how a data type from one database is best created with another database!

With the transformation option of the new SQLyog import tool it is YOU that is in control – not a piece of software that you cannot configure. There is no need to edit any strangely formatted file to do any transform of data types or any renaming of tables or columns. It is all available from the GUI-wizard and from an easily understandable XML-file as well, if you prefer.

Be aware that data may be truncated by MySQL when importing, if you specify a too short data type. In case there is a conflict with the transform option you have chosen and the data, the MySQL Server is in control. Data might be truncated or converted (if MySQL considers it safe) or an error may arise – the rules of MySQL decide!  In some cases, what happens could depend on the MySQL Server version.

You should know your data so much yourself, that you know what you are doing when using the transform option!

Filtering:

The filtering options available with the new ODBC-import Tool allows for "horizontal" as well as "vertical filtering." Together, they constitute what I would call a "grid filtering."

What I call "horizontal filtering" is simply an ability to select/deselect tables and columns for the import. That is done from a checkbox in the GUI-wizard.

What I call "vertical filtering" is an ability to select/deselect rows for the import. The selection of rows to import is done by specifying a WHERE-clause to execute against each row on the source. It is identical to the implementation of the SQL_WHERE with the SQLyog/SJA Synchronization Tool – except for one difference. This difference is that the WHERE-clause of the Migration Tool is executed with the ODBC-source. Therefore, the syntax must follow the syntactic rules of the SQL-"dialect" of the source-database (that most likely is not MySQL) and the ODBC-driver used for import. With the Synchronization Tool, both source and target are identical – or "almost identical" - MySQL-versions, so here you will not have to take the differences of various SQL-"dialects into account.  We have seen one example already: that Microsoft Access allowed us to use the '*' –character as a wildcard in the WHERE-statement.

5) Trigger options

The "trigger" functionality is the ability to perform changes to the ODBC-database from where data is imported. The changes take place with each row of the source after data has been saved in the MySQL-database. The option is available and configurable for each individual column.

The trigger functionality simply sends an SQL-statement to the source database. The statement will be executed on the row that has just been imported to MySQL. The two SQL types of statements available as of now are DELETE and UPDATE.

The DELETE trigger option simply deletes that row of data from the source after it has been saved to the MySQL database. Not so much more to write about this one, I believe!

The UPDATE trigger option lets you perform UPDATES to the source database on the row in operation. You simple enter the "last half part" of a normal SQL UPDATE-statement.

Note that what you enter as an UPDATE-trigger sent to the source database must follow the syntactic rules of the SQL-"dialect" of the source database, as was the case with WHERE-clause. Thus, not only constants but also functions and expressions are possible - actually, any expression that is legal with the syntax of the SQL-dialect of the ODBC-source and the ODBC-driver used for the import will work. With MS-Access you can enter "date()" and the current date will be written to the source – provided that the date()-function is valid with the column-definition. In addition, you might increment a "mycolumn"-column by entering "mycolumn+1" as the UPDATE-trigger to execute against the "mycolumn"-column.

Note that this is a trigger-functionality of the SJA itself. It has nothing to do with any (native) trigger functionality that might exist with the database imported from – or with the trigger functionality of MySQL version 5.  It is something of its own. And very unique actually!

6) Notifications

A notifications' feature, similar to the SQLyog/SJA "Notifications Service," has been implemented with the new Migration Tool. It lets you send an email notification informing you of the result of the ODBC-import. The message is configurable--you can choose to either always send a message or only to send a message in case of an error. This notifications' feature is particularly useful when used with scheduled imports. In addition, it can be very useful with imports that are started manually. For instance, you can set up a "rule" with the mail-client receiving the message, telling that mail-client that these messages should all be moved to a certain folder in the mailbox folder system. The messages gathered there provide you with complete logging of all ODBC-imports (and other SJA jobs as well, if you want).

What to use the ODBC-import tool for ??

The SQLyog Migration Tool is equally well suited for these two situations:

  • Migration to MySQl.
  • (more or less) permanent coexistence of MySQL and other databases within an organisation.

At first sight, "migration" and "permanent coexistence" may seem as different as things can be. Migration is in principle a one-time thing. Permanent coexistence the contrary – it is permanent! However, in real life I believe it is not two discrete situations but rather a continuous scale with an unlimited number of "intermediates" possible. Because migration takes time to complete, it should be planned. A "small scale" pilot-project will often be done; some subsystems may be implemented and tested before proceeding to the next subsystem etc. In addition, the organisation must be trained in the use of new or modified applications, maintenance procedures etc.

Migration to MySQL:

A general allegation about the MySQL database is that it is "effective but not very advanced." You will hear that often among Systems Administrators. For that reason, you will also often hear that MySQL is "fine for use with the web – but it does not have the features that we need for our LAN-based business-critical applications."

It is true that with older versions of MySQL a lot of organisations and companies missed features such as appropriate localization features (Time Zone and Charset management for instance), not to mention features such as Stored Procedures, Triggers and Views. That is history! MySQL has implemented all that, although some of it is still only available with MySQL version 5, which is still in Beta. Many organisations of all kinds have realized that and have started a migration process from some other database to MySQL. The main reason for this are the low cost of ownership of MySQL (various licensing and certification models are available – even a completely free one).

Some - and not so many - years ago migration would involve exporting data to a .csv(text-)file and importing to the new database – one table at a time. This simply is not acceptable any more – people are too busy and it is too expensive for that kind of work. Organisations and companies need migration methods that are more efficient!

The SQLyog Migration Tool has everything you need for a successful migration to MySQL - no matter whether you intend to accomplish it over a weekend (!) or it is a year-long process. With The SQLyog Migration Tool you can import your data to MySQL (that should be obvious since that is basically what the tool is for!), but you can also create "test cases" with subsets of data, and perform the migration stepwise using the filtering options. And if should you happen to have a lot of old data in your old system that is not relevant, you can use filtering to "clean" the data to import as you like.

Moreover, it is all implemented in a very intuitive GUI-tool. However if you prefer handwritten XML-code, you can do that as well.

Permanent coexistence of MySQL and other databases

For the (more or less) permanent coexistence of MySQL with other database systems, the trigger –functionality is particularly useful. In combination with the use of WHERE-filtering of data, it lets you perform an "incremental import." The principles of this are described in the SQLyog help-file, and a more extensive case-study is planned for another article (that I yet don't know where will appear!), so I shall not go further into this here.

Instead, let us sketch out a few simple and very realistic situations where "permanent coexistence" would occur

  • You depend on one or more applications that run on one database system only (and not on MySQL). Application(s) that are so special in their nature, that there is no realistic alternative – at least for some time or at a price that you are willing to pay.
  • You use MySQL for web (would typically be with some remote web hosting provider) and have no actual plans of migrating the database servers serving your LAN to MySQL.
  • You want to start a Migration Process with the least critical or the least complicated applications. When that is accomplished, you want to wait for some time while deciding if more applications should be migrated. In addition, you want to be sure that you and your staff can manage MySQL before migrating the more difficult and business-critical applications.
  • You sometimes have employees working "disconnected" (could be from their home address or while travelling). While disconnected they use "personal"/office-suite databases like Microsoft Access for working with data. You want to import that data to your MySQL database at intervals and/or when possible, no matter if it is new data or data that has been changed.

The SQLyog Migration Tool has all the options needed to handle these and similar situations. Once the appropriate job-files are built, you can execute them again and again, using exactly the general options, transformation options, filtering options and triggers to ensure the correct flow of data from practically any database in the world to your MySQL server. In addition, you have the option to combine ODBC-import with synchronization of MySQL servers, run maintenance scripts using the SJA "Notification Services" and performing automated backups. It is all implemented with the SJA.

MySQL and MySQL – a note on use across different MySQL versions.

Although the Migration Tool was primarily designed to import data from other databases into MySQL, you can also connect to one MySQL database with the MySQL ODBC-driver from the Migration Tool. When this type of connection is done, this MySQL-database is just an "ODBC-source" like was it any other ODBC-compliant database. That is very practical since there are quite a lot of differences between the various MySQL-versions. Especially versions up to 4.0.x vary quite a lot compared to later versions (4.1.x and 5.0.x). User management, implementation of DATE and TIME variable types, implementation of the concat-ws() function, management of charset/collation and other localization issues are examples of this. Those differences between MySQL-versions might prevent you from successfully using the SJA Synchronization Tool and the "copy to other host" functionality of SQLyog if the MySQL versions are "too different." Even importing a dump from a MySQL version 5.0.x server into a MySQL 4.0.x server is not possible unless you edit the dump-file in an editor. However, with one MySQL-server connected through ODBC as the "source" and another MySQL-server connected with the native MySQL C-api (as compiled into SJA code) as the "target" you can overcome these incompatibility-issues across MySQL-versions.

Conclusion

You cannot tell a truth too often so let's quote from the introduction!

The SQLyog ODBC-import tool is the most complete, versatile, intuitive and flexible import tool that exists and that you can imagine. All the options that the tool offers are available from the SQLyog GUI. There is no need to edit some 'strange format' file to transform or filter data during import. It lets you easily import any subset ("vertically" or "horizontally") of data and metadata. It can be scheduled, can be run from external applications, can be run in batch-mode in combination with any other OS/system command, executable file and any other job type of the SJA. The XML-format of the jobfile control lets you integrate the full functionality of the ODBC-import tool into your own applications if that is what you want.

More Questions? OK, I'll answer this one too! No, unfortunately it does not brew coffee!

About the author.

Peter Laursen is a Dane and a Mechanical Engineer.  He specializes in Quality Assurance, Environmental Management, and various IT-issues.  He has experience with some very different types of database systems, primarily (in the chronological order that he used them): DATAFLEX, IBM AS-400, Microsoft Access and MySQL.  He is a MySQL and SQLyog devotee!

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved