Devart Blog

Data migration from MySQL to other DBMS

Posted by on February 5th, 2009

Contents

  • Introduction
  • About Data Export feature
  • How to move data using Data Export
  • Summary

Introduction

This article describes how to migrate data from MySQL database to any other database, for example, Oracle, Microsoft SQL Server, IBM DB2, or PostgreSQL one, using Data Export feature of dbForge for MySQL products. To illustrate it, we will move data from table actor and the query result from sakila database that exist on MySQL server to the schema SCOTT on Oracle server. To see the screenshots in full size, just click them.


About Data Export Feature

Data Export feature is part of dbForge Studio for MySQL and dbForge Fusion for MySQL products. It represents a customizable tool for data exporting from MySQL database to various formats. You can also migrate data from MySQL database to any other DBMS using installed ODBC driver. You can move data either from the whole table (view) or collected from several tables that is the result of the query execution. Data export is simple due to convenient Data Export Wizard.

How to move data using Data Export

To move the data, you need to follow the steps, shown in Data Migration Plan picture. Let us examine each step in detail.

1. Table/Query

Right-click table actor in the Database Explorer window and select Export Data->To Other Format from the menu (see Figure 1) or execute the query and after that right-click the grid in the Data window and select Export Data (see Figure 2).

When Data Export Wizard (see Figure 3) opens, select ODBC format to export data.

Figure 3: Data Export Wizard

Figure 3: Data Export Wizard

2. Table Name

If required, you can enter another name for the moved table, which can differ from the tables name on the Source server. For a query result, let us change the default tables name expdata to result from query.

3. Column selection

Now let us select the following export settings:

For a query result to export data only from address_id, address, phone columns.
For the table actor to export data from all the columns.

To do it, you should unselect check boxes next to unnecessary columns (see Figure 4).

Figure 4: Exported columns list

Figure 4: Columns to export list

4. Column names

Data Export Wizard allows you to enter different names for exported columns just the same way as you did for the tables name. For example, let us change the name of actor_id column from the table actor into id(see Figure 5).

Figure 5: Changing a column's name

Figure 5: Changing a column’s name

5. Setting ODBC

The following steps are the most important in data export. Let us set ODBC driver to connect to DBMS server (in our case with Oracle one).
Do the following:
Go to ODBC data provider options page of Data Export Wizard and select Use a connection string radio button (Figure 6), then press the Build button at the right.

Figure 5: Connection string

Figure 6: Connection string

When the Select Data Source window opens, go to the Machine Data Source tab and press the New button (see Figure 7).

Figure

Figure 7: Machine Data Source tab

In the opened Create New Data Source window press the Next button (see Figure 8).

Figure 8: Create New Data Source window

Figure 8: Create New Data Source window

Now you should select a required driver to connect to the server. Select Microsoft ODBC for Oracle and press the Next button. Then press Finish (see Figure 9).

Figure 9: Selecting a driver

Figure 9: Selecting a driver

Figure 10: Finishing the process

You can see the Microsoft ODBC for Oracle Setup window where you should specify the servers name, for example, ora920 and the user name, for example, scott. To identify data source on the server, let us enter a name (for example, Migration to Oracle) in the Data Source Name field. Description field is not required, leave it empty. Press OK to save settings (see Figure 11).

Figire 9:

Figire 11: Specifying the server and user’s names

We are back to the Select Data Source window where the line Migration to Oracle is appeared. Press OK to finish data sourcecreation.

Figure

Figure 12: Select Data Source window

We can see the Microsoft ODBC for Oracle Connect window with User and Server fields populated (see Figure 14). Let us enter a password tiger into the corresponding field and press OK.

Figure

Figure 13: Microsoft ODBC for Oracle Connect window

You can press the Test Connection button to test the connection to the Target server (see Figure 14).

Figure

Figure 14: Testing the server connection

Now we are ready to export the data, i.e., send DDL, DML queries to the Target server. Press the Export button to start data export.

6. Table creation

When you press the Export button, Data Export searches for a table with the actor name on the server. If no table with this name exists, it is created based on the syntax of the Target server. If the table is found, Data Export checks if the table has required columns.

7. Data transferring

When the table is created or the structure of the existing one is checked, data is moved from one server to another ( in our case from MySQL to Oracle server). We can see the operation progress on the progress bar (Figure 15).

Figure

Figure 15: Data export progress

When the export is finished, Data Export Wizard notifies about successful data export.

8. Summary

As a result, we have tables actor and result from query in schema SCOTT on Oracle server. The tables contain data exported from the table actor and from the query result from the database sakila based on MySQL server. To check the result of data export from MySQL to Oracle, we can use Devart OraDeveloper Studio program.

Leave a Reply