From Oracle Database to MySQL

Most Oracle migrations head to PostgreSQL, but for web-scale, read-heavy, or LAMP-style applications MySQL is often the better-fitting open-source target: simpler to operate, ubiquitous in the application ecosystem, and lighter for straightforward transactional workloads. This guide is about choosing MySQL deliberately for the workloads it suits, and knowing where PostgreSQL would serve you better.

When MySQL is the right call

MySQL shines for high-volume, relatively simple OLTP and applications already built around the MySQL ecosystem (CMS, e-commerce, many SaaS backends). It’s weaker than PostgreSQL for complex analytical SQL, advanced datatypes, rich procedural logic, and strict standards compliance, if your Oracle estate leans on heavy PL/SQL, materialized views, or analytic functions, PostgreSQL is the safer landing. Pick MySQL for app simplicity and ecosystem fit, not for replicating Oracle’s advanced engine.

The ownership caveat worth naming

MySQL Community Edition is open source but owned by Oracle. If the point of leaving is escaping Oracle entirely, consider MariaDB (a community fork, near-drop-in) instead, same migration mechanics, no Oracle stewardship. We cover that path separately; the conversion work below applies to both.

What converts, and what doesn’t

• PL/SQL → MySQL stored procedures/functions/triggers. MySQL’s procedural language is less capable than Oracle’s; complex packages need redesign, sometimes pushed into the application tier.
• Datatypes. Oracle NUMBER → DECIMAL/INT, DATE/TIMESTAMP differences, VARCHAR2 → VARCHAR, CLOB/BLOB mapping; mind MySQL’s stricter/again-configurable SQL modes.
• Sequences → AUTO_INCREMENT (or MySQL 8 sequences via tables); ROWNUM patterns rewritten as LIMIT.
• Application SQL. Oracle hints, (+) outer joins, DUAL, and PL/SQL called from the app must be rewritten, usually the biggest hidden cost.

Tooling

AWS Schema Conversion Tool (SCT) assesses and converts schema and code to MySQL, flagging manual items; ora2pg can also target MySQL-style output. For data + low-downtime cutover, AWS DMS does a full load plus CDC replication until you switch over.

Start with the SCT assessment report and use it to confirm the fit decision, not just to plan the work: if the report shows a large share of complex PL/SQL that will not convert cleanly, that is a signal MySQL’s lighter procedural model may cost you more in redesign than PostgreSQL would. DMS maps Oracle types to MySQL types during the load, but review the mappings rather than trusting them blindly, especially around NUMBER precision and date/time columns.

Oracle behaviors to handle by hand

Several Oracle behaviors need deliberate handling on MySQL:

• NUMBER precision. Oracle NUMBER without precision is arbitrary; map it to DECIMAL where exactness matters and to an integer type only where you have confirmed the domain, or you will silently round or overflow.
• DATE carries time. Oracle DATE includes a time component, so map it to MySQL DATETIME rather than DATE unless the column is genuinely date-only.
• Empty string versus NULL. Oracle treats '' as NULL; MySQL stores a zero-length string. Normalize on load so IS NULL checks and constraints keep behaving as the application expects.
• SQL modes. MySQL’s behavior around zero dates, string truncation, and division depends on the active SQL mode. Set a strict mode explicitly and test, rather than inheriting whatever the server defaults to.

Application SQL changes

The application SQL is usually the largest hidden cost. Oracle idioms such as SELECT ... FROM DUAL, the (+) outer-join operator, optimizer hints, NVL, DECODE, and SYSDATE all have MySQL equivalents (NOW(), IFNULL/COALESCE, CASE), but each has to be found and rewritten across embedded queries, ORM mappings, reporting tools, and ETL. ROWNUM filtering becomes LIMIT. Inventory every place SQL is written and grep for the Oracle-specific constructs early, because these are exactly the queries that pass in Oracle and fail or return different results on MySQL.

The order to work in

1. Assess with SCT; triage objects and confirm MySQL is the right fit versus PostgreSQL.
2. Provision MySQL (primary + replicas), sized on vCPU, tuned (InnoDB buffer pool, etc.).
3. Convert schema, review action items, load into MySQL.
4. Migrate data with DMS full-load + CDC.
5. Convert procedures and application SQL in parallel.
6. Validate with row counts, data-diffs, an app regression suite, and performance comparison against the Oracle baseline.
7. Cut over in a window: quiesce writes, drain CDC, final diff, repoint connections, smoke-test, keep Oracle recoverable.

Earning trust in the new engine

The parallel-run phase is what earns trust in the new engine, and it is the step most often compressed under deadline pressure. Once DMS full-load plus CDC has MySQL tracking Oracle with low lag, point a copy of the application or a slice of read traffic at MySQL and diff results against Oracle over a representative period, including any month-end or batch cycles rather than a quiet afternoon. Compare row counts and checksums, run the application regression suite, and compare query latency against the Oracle baseline so a plan regression shows up before users do. Because MySQL’s procedural model is lighter than Oracle’s, this is also where you confirm that logic you pushed into the application tier still behaves correctly under load. Promote only when documented acceptance criteria for correctness and performance are met, then cut over in a window: quiesce writes, drain CDC to zero, run a final diff, repoint connections, smoke-test, and keep Oracle recoverable through hypercare so rollback is a repoint rather than a restore.

When MySQL is the answer

Oracle → MySQL is a strong, low-cost exit for simpler, high-volume transactional and web workloads; for complex procedural or analytical Oracle estates, prefer PostgreSQL. Watch the Oracle-ownership point (MariaDB if that matters), and budget for PL/SQL and application-SQL rewrites, the data movement is the easy part. Model the per-vCPU savings in the calculator above, and treat the figures as illustrative until you validate fit and performance against your workload.