More and more people are moving non-important data out of Oracle to MySQL for lower Oracle license fee. For DBAs, we need to know how to migrate data from Oracle to MySQL quickly. I will introduce you how to migrate data with free SQLULDR2 utility.
We can export Oracle data to text file, then load it with MySQL "LOAD DATA" command, SQLULDR2 can escape the special character in the column value for MySQL data load. Just run the following SQLULDR2 comman.
sqluldr2 ... escape=0x5c quote=0x22 null=0x5cN field=0x2c record=0x0a
Then you can load the data in MySQL with following SQL command.
LOAD DATA LOCAL INFILE '...' INTO TABLE ...
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
ESCAPED BY '\\' LINES TERMINATED BY '\n';
We can also export Oracle data to insert SQLs files, and then run it in MySQL. SQLULDR2 can escape the special character in the column value for MySQL SQL syntax. Just run the following SQLULDR2 comman.
sqluldr2 ... escape=0x5c quote=0x27 null=null field=0x2c format=mysql table=table_name
Then you can run insert SQLs in MySQL as following.
mysql> source insert_sql_files
$ sqluldr2 ... | mysql database_name
We are planing to migrate one billion rows from Oracle to MySQL with SQLULDR2. If you have data migration jobs to do, try SQLULDR2, it will save you!