Seamless Oracle-MySQL Sync: Step-by-Step Integration Moving data between an enterprise Oracle database and an agile MySQL instance is a common requirement for modern data architectures. Whether you are offloading reporting workloads, building real-time dashboards, or migrating microservices, establishing a reliable synchronization pipeline is critical.
This guide provides a clear, step-by-step framework to implement a seamless, low-latency sync between Oracle and MySQL. Architectural Approach: Why CDC?
Traditional batch-based replication (like scheduled SQL dumps) stresses the source database and introduces significant data lag. For a truly seamless integration, Change Data Capture (CDC) is the industry standard.
CDC monitors the Oracle redo logs or archive logs in real time. Whenever a INSERT, UPDATE, or DELETE occurs, the change is captured immediately and streamed to MySQL without impacting Oracle’s transactional performance. Key Components Source: Oracle Database (OLTP Production)
Capture Engine: Debezium, Oracle GoldenGate, or AWS Database Migration Service (DMS)
Streaming Platform (Optional but Recommended): Apache Kafka (for decoupled, scalable routing) Target: MySQL Database (Replica/Downstream) Step 1: Prepare the Source Oracle Database
Oracle keeps transactions secure in its redo logs. To allow an external tool to read these changes, you must enable supplemental logging and put the database in ARCHIVELOG mode. 1. Enable ARCHIVELOG Mode
Connect to your Oracle instance as SYSDBA and execute the following commands:
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; Use code with caution. 2. Enable Supplemental Logging
This ensures that the redo logs contain both the new and old values of modified rows, which is necessary for the sync tool to update MySQL correctly.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; Use code with caution. 3. Create a Dedicated Sync User
Create a user with the specific privileges required to read the log miner or replication data.
CREATE USER cdc_user IDENTIFIED BY password; GRANT CONNECT, RESOURCE TO cdc_user; GRANT SELECT ANY TRANSACTION TO cdc_user; GRANT EXECUTE ON DBMS_LOGMNR TO cdc_user; Use code with caution. Step 2: Establish the Data Mapping Strategy
Oracle and MySQL handle data types differently. Before configuring your sync tool, you must map your schemas accurately to prevent data truncation or errors. Oracle Data Type Recommended MySQL Target NUMBER(p, s) DECIMAL(p, s) VARCHAR2(n) VARCHAR(n) DATE DATETIME or TIMESTAMP CLOB TEXT or LONGTEXT BLOB BLOB or LONGBLOB
Tip: Pre-create your target schema in MySQL using these mapped data types before turning on the replication engine.
Step 3: Configure the Integration Tool (Using Debezium/Kafka)
While commercial tools like Oracle GoldenGate are highly effective, open-source architectures using Debezium and Apache Kafka offer incredible flexibility without licensing bottlenecks. 1. Deploy the Oracle Debezium Connector
Configure a JSON payload for your Kafka Connect instance to target your Oracle source:
{ “name”: “oracle-source-connector”, “config”: { “connector.class”: “io.debezium.connector.oracle.OracleConnector”, “database.hostname”: “oracle-host”, “database.port”: “1521”, “database.user”: “cdc_user”, “database.password”: “password”, “database.dbname”: “ORCL”, “database.pdb.name”: “ORCLPDB”, “database.server.name”: “oracle_server”, “schema.include.list”: “PRODUCTION_SCHEMA”, “table.include.list”: “PRODUCTION_SCHEMA.ORDERS”, “database.history.kafka.bootstrap.servers”: “kafka:9092”, “database.history.kafka.topic”: “schema-changes.oracle.orders” } } Use code with caution. 2. Deploy the MySQL JDBC Sink Connector
Once Debezium streams Oracle changes into a Kafka topic, a JDBC Sink connector consumes those messages and writes them to MySQL.
{ “name”: “mysql-sink-connector”, “config”: { “connector.class”: “io.confluent.connect.jdbc.JdbcSinkConnector”, “tasks.max”: “1”, “topics”: “oracle_server.PRODUCTION_SCHEMA.ORDERS”, “connection.url”: “jdbc:mysql://mysql-host:3306/target_db”, “connection.user”: “mysql_user”, “connection.password”: “mysql_password”, “insert.mode”: “upsert”, “pk.mode”: “record_key”, “auto.create”: “true”, “auto.evolve”: “true” } } Use code with caution.
Note: Setting insert.mode to upsert ensures that updates and inserts coming from Oracle are handled correctly in MySQL without causing duplicate key errors. Step 4: Validate Data Consistency
A replication pipeline is only as good as its accuracy. Once the initial sync is complete and real-time changes are flowing, implement validation routines.
Row Count Audits: Run scheduled scripts to compare row counts between source tables and target tables during low-traffic windows.
Checksum Verification: Use open-source tooling or custom scripts to calculate cryptographic hashes of matching rows on both sides to catch data drift.
Lag Monitoring: Monitor the offset lag in Kafka or your chosen replication engine. High lag indicates that your MySQL target cannot keep up with Oracle’s transaction volume, requiring tuning of MySQL write buffers (innodb_buffer_pool_size). Conclusion
Setting up a seamless Oracle-to-MySQL synchronization pipeline requires careful planning around logging infrastructure and data mapping. By leveraging Change Data Capture (CDC), you eliminate batch processing windows, reduce source database strain, and unlock real-time data availability for downstream applications.
If you are setting up this pipeline, I can help you tailor the configuration. Let me know:
Which replication tool you plan to use (Debezium, AWS DMS, GoldenGate, etc.) The volume of data/transactions you need to sync daily
If you have any complex Oracle-specific data types (like spatial data or custom objects)
Leave a Reply