15 Critical MySQL vs Oracle Migration Questions Every Bank Should Know

1. Why Choose MySQL to Replace Oracle?

MySQL’s rapid growth, extensive documentation, large ecosystem, strong InnoDB support for high‑concurrency OLTP, flexible logical replication, group replication for strong consistency, and mature open‑source middleware for data sharding make it a viable alternative to commercial databases in the banking sector.

2. Compatibility Issues Between MySQL and Oracle

Data types: MySQL lacks sequences, custom types, XML types, and pseudo‑columns.

Objects: No materialized views, package management, or synonyms.

Indexes: No bitmap, bitmap‑join, functional, or online rebuild indexes.

Triggers: No DDL, system, or time‑based triggers.

Advanced features: No external database links, object‑oriented extensions, or flashback queries.

Functions: Missing COSH, CHR, LAG, RANK, and several others.

3. Limitations of Middleware + MySQL Development vs Direct MySQL

Syntax restrictions on DDL/DML (e.g., CREATE TABLE … LIKE …, INSERT … SELECT … not supported).

Performance requires queries to include the same sharding key for both filtering and joins.

Unsupported features: foreign keys, temporary tables, triggers, distributed stored procedures, and custom functions.

4. Migrating Data from Oracle to MySQL

Migration is heterogeneous and relies on third‑party open‑source or commercial tools. For small datasets with acceptable downtime, a simple text export/import suffices. For minimal downtime, use Oracle GoldenGate (OGG) or similar tools to perform full‑plus‑incremental sync, then switch the application to MySQL after verification.

5. Why MySQL Discourages Stored Procedures, Triggers, and UDFs

Business logic should reside in application code for easier maintenance and debugging.

Nested triggers combined with stored procedures can cause deadlocks.

Middleware‑based distributed databases provide limited support for these objects.

Reducing database load improves protection and performance.

Portability across heterogeneous databases is poorer, increasing development cost.

6. When to Use Sharding and How to Choose Between Horizontal and Vertical Splitting

Consider sharding when a single table exceeds ~20 million rows or a database exceeds ~100 GB, or when CPU, memory, disk, or I/O become bottlenecks. Vertical splitting separates tables or schemas by functional modules and is simpler. Horizontal splitting partitions a table’s rows across identical tables based on a sharding rule and is more complex; choose vertical splitting for many tables with clear module boundaries, horizontal splitting for very large single tables.

7. MySQL Management Tools

Common tools include Navicat for MySQL, SQLyog, phpMyAdmin, and MySQL Workbench. The open‑source MySQL Workbench is recommended for most cases; SQLyog is suggested for paid environments.

8. Risks and Performance of Modifying Large MySQL Tables

Offline ALTER during low‑traffic windows is safe but may require long downtime for large tables.

Online schema change with pt‑online‑schema‑change locks the table for reads, creates an intermediate table, copies data, then swaps tables. It requires a primary key (no composite keys) and may lose data under high concurrency.

Distributed middleware solutions often do not support pt‑osc or gh‑ost; using MySQL 5.7+ native online DDL is preferred.

9. Principles of Using MySQL Partition Tables

MySQL partitions are implemented as separate sub‑tables; indexes are also partitioned, and there are no global indexes. Supported partition types are RANGE, LIST, HASH, and KEY. Use partitioning when data has clear range boundaries, queries do not cross partitions, modifications are infrequent, and query logic is simple. For large‑scale workloads, partitioning is less favored compared to sharding.

10. MySQL Architecture Selection

Architecture choices depend on system level (high, medium, low) and data volume (small vs large). Options range from centralized single‑node setups for small data to distributed three‑center architectures for large data. Thresholds: single table ≤ 20 M rows, single database ≤ 100 GB; adjust based on actual workload.

11. Ensuring Data Consistency in MySQL

Single‑node: enable sync_binlog=1 to force log writes to disk before commit.

Replication: use semi‑synchronous replication where the slave acknowledges receipt of the relay log before the master commits.

Group Replication (MGR): a transaction commits only after a majority (N/2 + 1) of members acknowledge, providing strong consistency and multi‑master capabilities.

12. Reducing MySQL Master‑Slave Lag

Upgrade slave hardware to match or exceed the master.

Use newer MySQL versions (e.g., 5.7 with parallel replication).

Design tables with short primary keys.

Deploy modern hardware such as PCI‑E SSDs.

Leverage application‑side caching to reduce load.

Avoid large transactions; prefer small, frequent batches during off‑peak periods.

13. Bidirectional Data Extraction Between Oracle and MySQL

Programmatic approach: use JDBC to connect to both databases, fetch result sets, and insert or batch‑load rows into the target. For MySQL‑to‑Oracle, export data via SELECT … INTO OUTFILE or CSV and import with sqlldr. For Oracle‑to‑MySQL, use the open‑source sqluldr2 tool or third‑party ETL/OGG solutions.

14. Performance Comparison: Standalone MySQL, Middleware + MySQL, and Oracle

Tested on a business scenario with three tables totaling 90 million rows. Findings:

Text import with middleware + MySQL outperforms single‑node imports.

MySQL 8.0 count queries are faster than Oracle; multi‑table joins are slower than Oracle 5.7.

MySQL 5.7 and 8.0 single‑node joins lag behind Oracle; hash joins in 8.0 help but require equality conditions and indexed join keys.

Conditional joins perform similarly when the join key matches the sharding key.

Middleware + MySQL on an 8c16G VM matches Oracle on a 32c64G server, provided queries respect sharding keys.

15. Resource Planning for MySQL Hosts and Master‑Slave Topology

Hardware: single‑node databases or middleware should have at least 16 CPU × 32 GB RAM; sharded nodes need ≥ 8 CPU × 16 GB RAM. Topology:

Single‑center deployment: 1 master + 2 slaves.

Dual‑center deployment: 1 master + 3 slaves.

Triple‑center deployment: 1 master + 4 slaves.

Conclusion

The article presents fifteen common MySQL‑related questions encountered during a bank’s migration from Oracle, offering solutions and configuration tips that reflect the author’s specific environment but can inspire other organizations undertaking similar database transformations.