Mysql – Best Approach to Migrating Oracle to MySQL – Run on EC2 in AWS

amazon ec2migrationMySQLoracleschema

I'm trying to migrate an Oracle schema as well as all of the data within the DB to a MySQL database. I want to then make an EC2 app on AWS to kick off whatever I've written for this. Are there any approaches that one might say is better than another (for instance, use Ruby/Rails instead of Perl)? Is there a tool that I might be able to wrap into an app and place it on my EC2 instance?

Full disclosure, I'm pretty much a complete newbie when it comes to AWS, so if there is something really basic that I'm missing (you could use some type of Linux-based MySQL Workbench-like tool) I would definitely appreciate that.

I realize this is a bit specific, thank you in advance!

Best Answer

There are two tasks here. The migration to MySQL and setting up AWS. Setting up AWS is off topic here - try Superuser or Stackoverflow.

You are in for a tough job. MySQL is a much simpler database and does not have as many features as Oracle. Also many things in Oracle are non-standard making it difficult to migrate to / from.

The process is basically as follows:

  • Dump Oracle database schema / DDL. You will end up with a file full of CREATE statements
  • Update the DDL to allow for types that Oracle does not support. Read the MySQL manual. Use InnoDB as the backend
  • Dump Oracle functions
  • Rewrite the Oracle functions. This is probably going to be the most time consuming step. You will have to look at the Oracle and MySQL documentation
  • Run your converted DDL and functions against your new MySQL database
  • Dump the data from Oracle
  • Restore the data into MySQL. You may have to temporarily disable some contraints
Related Question