MySQL Migration
The wide spread of MySQL determines the demand for software products that enable to implement the transfer of information to MySQL databases from other data sources. These data sources can be different DBMS, local repository of documents, user data files, external data storages, etc. The migration to MySQL is usually required when taking a decision to change the data management system or to expand it by adding more MySQL servers, as well as when moving from one MySQL server version to another. Nevertheless, the migration to MySQL is a big problem for DBAs and developers, since it implies a set of issues and pitfalls caused by the significant inconsistencies between the different database management systems and data storage infrastructures.
Tools for Migrating to MySQL Database
Leaving aside issues related to the causes of migration, let’s review MySQL migration tools that may be required for the implementation of the migration process itself. There are several factors that need careful consideration when selecting the appropriate instrument for MySQL database migration.
One of the main stages of the migration procedure is transformation of source data schema and importing data stored in tables to the target MySQL database. This basically involves translating the source objects and their properties (such as fields or columns data types, indices, constraints) to MySQL complements, as well as source data transformation. EMS Data Pump for MySQL is a complex solution for converting databases and importing table data from an ADO-compatible source (e.g. MS Access, MS SQL Server, Oracle or any other database with ADO support) to MySQL databases. This migration tool has a user-friendly wizard interface that helps to implement data converting in a few steps.
EMS Data Pump for MySQL allows you to build the ADO connection string for source data provider connection, select tables, fields and indices for converting to MySQL equivalents, view and edit SQL script for generating target MySQL database, specify the engine type to be applied to the target tables (MyISAM, InnoDB, ISAM, Heap, Merge and others), set the correspondence between data types of the DB Provider being used and MySQL data types, edit target objects definitions, use special editor that can help to set a criterion for importing data in the form of SQL statements and much more. Additionally to the GUI version, the installation package of Data Pump for MySQL includes the console version which is intended for automation of MySQL migration process with the task scheduler.
Tools for Migrating to Another MySQL Version and Making Backup
To migrate from one version of MySQL database management system to another or to make snapshots of MySQL databases you can use this MySQL migration & backup tool - EMS DB Extract for MySQL. This database extract utility allows you to save metadata of all MySQL database objects as well as table data to a script file, thus this tool could be used to create MySQL database backups. The program enables you to select objects and data tables for extraction, dump data from several MySQL tables of different databases located at one host within one session, specify the version of MySQL server which the extracted metadata should be compatible with, transform table data to SQL script as INSERT statements, define constraints on extracted table data, insert statements for emptying tables before inserting extracted data, compress resulting script files and more. EMS DB Extract for MySQL scripts database objects in the correct order according to their dependencies, so that the subsequent recovery of MySQL objects from created script files also take place in the right order.
Tunneling
If due to the security policy accepted by your host provider you cannot connect to your MySQL server directly through TCP/IP (for example a firewall prevents this), you can use the SSH tunneling feature of EMS DB Extract for MySQL and EMS Data Pump for MySQL to fulfill your migration task. Moreover, SSH connection ensures privacy and safety of MySQL database data.
Conclusions on MySQL migration tools
While migration to MySQL is not an easy task, using the appropriate MySQL migration tools outlined in this paper allows considerably simplify the process of data transfer.
Both software products referred to above, can be used independently or as a part of the EMS SQL Management Studio for MySQL – a single work center that offers tools for performing the whole range of MySQL database management tasks such as managing database schema and objects, MySQL database design, migration, extraction, MySQL query building, data import, export and database comparison.