Data Loader

 

Step-by-Step Guide to Transfer Data from Oracle to MySQL Using MySQL Workbench

 

Author: Hassan Shareef,
Database Administrator
Since 16 years

 

In this guide, I will show step-by-step how to transfer data from Oracle to MySQL using MySQL Workbench tool. As you must be already knowing that MySQL Workbench is a free tool which can be downloaded from MySQL website. The Workbench is specially made for managing MySQL databases through graphical user interface. It also comes with Migration Wizard for importing and exporting data from MySQL. We will be using this tool for our example. So let's start!

 

Prerequisites

Step 1: Install Oracle ODBC Driver

  1. Download the Oracle ODBC driver

  2. Install the driver:

    •  Oracle Instant Client: Extract the package and configure the driver via the ODBC Data Source Administrator.

dataloader

Step 2: Configure ODBC Data Source (DSN)

  1. Open the ODBC Data Source Administrator:

    • Windows: Search for "ODBC Data Sources" in the Start menu.

    • macOS/Linux: Use iodbc or unixODBC utilities.

  2. Create a System DSN for Oracle:

    • Go to the System DSN tab and click Add.

      odbc administrator

    • Select the installed Oracle ODBC driver.

    • Configure connection details:

      • Data Source Name: A custom name (e.g., Oracle_DSN).

      • Host/IP: Oracle server address.

      • Port: 1521 (default).

      • Service Name/SID: Oracle database service name.

      • Username/Password: Oracle credentials.

    • Test the connection and save the DSN.

Step 3: Set Up MySQL Workbench for ODBC Migration

  1. Open MySQL Workbench and navigate to Database > Migration Wizard.

    mysql workbench

  2. Source Database:

    • Click Start Migration Button

      start migration

    • Select ODBC as the source type.

    • Enter the DSN name (Oracle_DSN) and Oracle credentials.

  3. Target Database:

    • Configure MySQL connection details (host, port, username, password).

  4. Test both connections before proceeding.

Step 4: Schema Conversion

  1. Fetch Source Schema:

    • Select the Oracle schema(s) to migrate. We will use the the sample HR  demo schema for the purpose of this guide.

    • MySQL Workbench will auto-convert Oracle objects to MySQL syntax. Adjustments may be needed:

      • Data Types: Map Oracle NUMBER to MySQL INT or DECIMAL, VARCHAR2 to VARCHAR, etc.

      • Reserved Keywords: Escape MySQL keywords with backticks (e.g., `order`).

  2. Manual Adjustments:

    • Modify indexes, triggers, or constraints as needed.

    • Handle unsupported features (e.g., Oracle-specific functions).

Step 5: Data Migration

  1. Select Tables:

    • Choose tables to migrate. Use filters to exclude unnecessary data.

  2. Data Mapping:

    • Map Oracle columns to MySQL columns, adjusting data types if required.

    • For large datasets, enable batch processing (e.g., 10,000 rows per batch).

  3. Import Modes:

    • Append: Add new records to MySQL.

    • Replace: Overwrite existing data.

    • Use the Truncate Table option if starting fresh.

  4. Start the migration and monitor logs for errors.

Step 6: Verify Data Integrity

  1. Compare row counts between Oracle and MySQL:

    -- Oracle
    SELECT COUNT(*) FROM employees;
    -- MySQL
    SELECT COUNT(*) FROM employees;

  2. Validate data consistency for critical fields (e.g., dates, unique IDs).

  3. Use tools like OracleSync2MySQL for automated data comparison

 

Tips

  1. ODBC vs. JDBC: ODBC is more resource-efficient and supports cross-platform compatibility 4.

  2. Architecture Mismatch: Ensure the ODBC driver and application (e.g., MySQL Workbench) are both 32-bit or 64-bit 8.

  3. Performance:

    • Disable foreign key checks during migration:

SET FOREIGN_KEY_CHECKS = 0;
-- Run migration
SET FOREIGN_KEY_CHECKS = 1;

 

  1. Tools Use Data Loader for a GUI-driven migration with advanced mapping features.

Troubleshooting