Home Tech Database migration from Oracle to MySQL

Database migration from Oracle to MySQL

by Clare Louise

Oracle and MySQL are both very popular RDBMS having rich set of administration tools and programming APIs. However, Oracle has quite restrictive licensing policy and high total cost of ownership while MySQL goes with less restrictive license and it is open source. That is why many businesses and organizations consider migrating their databases from Oracle to MySQL.

However, the procedure of migrating database from Oracle to MySQL requires many efforts when doing it manually. In addition, there is high risk of data loss or corruption due to the human factor. It would be wise to use special software to automate the conversion process. 

One of such conversion tools is Oracle to MySQL developed by Intelligent Converters. Intelligent Converters is a software vendor specializing in solutions for database conversion, migration and synchronization since 2001. Their Oracle to MySQL converter has extremely high performance due to direct reading and writing data without any middleware libraries or components. It supports all versions of Oracle and MySQL including cloud solutions. The product provides command line version in order to automate and to schedule database conversion. Oracle to MySQL converter allows not only migrate data from Oracle into new database but also merge or synchronize it with existing MySQL database. 

Also, the tool allows to filter data via SELECT queries. With this option, it is possible to select particular columns and records or even transform the data before converting it into MySQL format. Here is a few examples of using this feature for different purposes.

  1. Filtering records: SELECT * FROM MyTable WHERE KeyColumn > 1000
  2. Select and rename individual columns: SELECT column1 AS Name, column2 as Phone FROM MyTable
  3. Skip NULL values: SELECT * FROM MyTable WHERE Address IS NOT NULL

Sometimes it is necessary to change column type in the resulting database. For this purpose, Oracle to MySQL converter provides feature called “custom column mapping”. The feature that allows changing name, type, default value and NULL-attribute for any column of table as well as exclude certain columns from conversion. 

If the target MySQL server does not allow remote connection, the program can export the data into SQL script. In this approach source database is exported into local file containing SQL-statements to create tables with all indexes and constraints and fill them with the data. Then database administrator can import the script file to MySQL server via one of the following tools:

  1. MySQL console client (psql) 

mysql -h host -u user –p –vvf database_name < script_file

  1. phpMyAdmin

(Note, that phpMyAdmin allows to import dump file into an existing MySQL database only, so the dump file should not contain ‘CREATE DATABASE’ statement.)

    • Click on the appropriate database name in the left frame. 
    • Click ‘SQL’ tab on the top of the screen.
    • Enter full path to the dump file in ‘Location of the textfile’ field or click ‘Browse’ button to locate it through the directory tree.
    • Click ‘Go’ button.