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.
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
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.
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.
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!
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.
content of this article will be:
Introduction (you just
Here we take the "easy approach" to migration to MySQL.
Methods used and
applied by the Migration Tool.
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.
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.
About the author.
let's see what it looks like!
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!
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.
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
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.
let's start the Migration Tool wizard!
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.
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:
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.
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
that this is done for each column independently. They should not be hard
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.
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.
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.
error handling and general notifications' option.
of email options: account information (above) and e-mail subject (below).
"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.
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:
addition, the table was imported with all 'metadata" as we requested and
as we can see in SQLyog OBJECTS-pane:
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.
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.
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
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.
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.
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.
feature, similar to the SQLyog/SJA "Notifications Service," has been
implemented with the new ODBC-import tool.
it in some more detail.
1) ODBC-import with SQLoyg
is now implemented in the code of the SJA.
the SJA ?
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."
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
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.
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.
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.
the SJA-based ODBC-import tool.
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
copy myaccs.mdb myaccs.bak y
(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
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.
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"
2) Import of DATA and METADATA.
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.
METADATA it reads and imports are
including of course unique indexes
what is normally associated with the term "METADATA."
when importing to MySQL version 5, it does not attempt to import
It does not
matter whether these database features are supported or not by the source
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.
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.
options too whether foreign keys and indexes should be imported. Simply uncheck
both if you want to import DATA only and NO METADATA.
Transformation and Filtering options.
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
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.
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
know your data so much yourself, that you know what you are doing when using
the transform option!
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
"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.
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.
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
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
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.
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!
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 ??
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.
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.
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
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!
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.
it is all implemented in a very intuitive GUI-tool. However if you prefer
handwritten XML-code, you can do that as well.
coexistence of MySQL and other databases
(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.
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
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
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.
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.
tell a truth too often so let's quote from the introduction!
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.
Questions? OK, I'll answer this one too! No, unfortunately it does not brew
About the author.
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!