Migration to MySQL with SQLyog ver 4.1. - Page 5

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!