The Geek Diary

CONCEPTS | BASICS | HOWTO

How to Migrate from Oracle to MySQL

By

The task of migrating a complete schema from one database to another is not always simple. Apart from the physical aspect from getting the data from one machine to another, or even a different geographical location, some of the more complex tasks are converting between different data types, stored procedures and simply incompatible differences that may occur between the two databases.

MySQL Migration Toolkit was the package of choice originally, but this has been superseded by MySQL Workbench. At this point, however, the migration capabilities of MySQL Workbench are still being developed into a complete migration solution for even the major commercial databases.

Exporting Data from Oracle

Exporting the existing data from Oracle is often one of the cheapest forms of migration, but it can involve more manual input and can be slower than using a migration tool. One of the most popular tools for exporting data from Oracle is Oracle SQL Developer. The Oracle SQL Developer tool is capable of exporting data from Oracle tables in numerous formats, like Excel, SQL insert statements, SQL loader format, HTML, XML, PDF, TEXT, Fixed text, etc.

You can use SQL*Plus to select and format your data and then spool it to a file. This example spools out a CSV (comma separated values) file that could either be imported via LOAD DATA INFILE or by creating a CSV table for immediate access.

set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on 
spool oradata.txt 
select col1 || ',' || col2 || ',' || col3 
  from tab1 
  where col2 = 'XYZ'; 
spool off

You can also use the “set colsep” command if you don’t want to put the commas in by hand. This saves a lot of typing. Example:

set colsep ',' 
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on 
spool oradata.txt 
select col1, col2, col3 
  from tab1 where col2 = 'XYZ'; 
spool off

Using PL/SQL

PL/SQL’s UTL_FILE package can also be used to unload data. Example:

declare 
  fp utl_file.file_type; 
begin 
  fp := utl_file.fopen('c:\oradata','tab1.txt','w'); 
  utl_file.putf(fp, '%s, %sn', 'TextField', 55); 
  utl_file.fclose(fp); 
end; 
/

Importing Data into MySQL

Once the data has been exported from Oracle into a flat file, then it can be loaded into MySQL. This step continues with the expectation that an identical schema structure was created in the MySQL database to accept the data. The most common method to read a flat file into MySQL is using the LOAD DATA INFILE command. This is of the form:

LOAD DATA INFILE 'data.txt'   INTO TABLE tbl_name   FIELDS TERMINATED BY ','  ENCLOSED BY '"' 
   LINES TERMINATED BY '\r\n' 
   IGNORE 1 LINES;

This will read the exported data from the ‘data.txt’ file into the table ‘tbl_name’ using the specified separators.

Note: There is also the LOAD XML INFILE if the exported data is in XML format.

Another method that may be even quicker for the load is to use the CSV storage engine capabilities. This requires the table to again be created with the appropriate schema and the storage engine specified as CSV. For example:

CREATE TABLE mytable (
   name varchar(50)  NOT NULL,
   address varchar(100) NOT NULL,
   zipcode CHAR(5) NOT NULL
) ENGINE=CSV;

Once the table is created, a .csv file will appear in the data directory corresponding to the table ready to store data. Copy the exported CSV file over the existing ‘mytable.csv’ data file and execute a FLUSH TABLES to update the table information. Now you have access to the data and can use an INSERT INTO new_table … SELECT to read data quickly from the CSV table into a more appropriate table for your production system.

Migration Tools

There are a host of third-party tools, some of which are open source. For example:

http://kettle.pentaho.com/
http://www.convert-in.com/ora2sql.htm
http://www.ispirer.com/products/oracle-to-mysql-migration

You May Also Like