How to Safely Perform Reverse Incremental Migration from MySQL to OceanBase

1. Background

After migrating MySQL to OceanBase, besides forward incremental data, sometimes reverse incremental is needed to ensure new data on OceanBase is written back to MySQL for quick rollback.

Forward switch step includes disabling triggers and foreign keys on the source to avoid double updates when reverse sync is enabled.

This article outlines the main steps and precautions for reverse incremental when migrating MySQL to OceanBase.

2. Terminology

Before the main text we explain two terms.

Forward Switch

In the migration chain, stopping MySQL writes and building non‑table objects, then connecting to OceanBase and starting business is called forward switch.

Reverse Incremental

After traffic moves from MySQL to OceanBase, writing new OceanBase data back to MySQL is called reverse incremental.

3. Solution Overview

Before operation, clarify the pre‑conditions, data migration flow and rollback plan for reverse incremental.

3.1 Pre‑conditions

Cases not supporting reverse incremental: multi‑table aggregation, many‑to‑one schema mapping.

Disable MySQL triggers (backup then delete after forward switch).

Disable MySQL foreign keys (prepare drop/create statements, delete after forward switch).

Enable binlog on MySQL and log archive on OceanBase.

DDL synchronization scope limits – avoid long‑running or non‑compliant DDL.

3.2 General Data Migration Process

Lock OceanBase business users.

Run MySQL‑to‑OceanBase link to incremental sync stage.

Stop business on the day of formal switch.

Lock MySQL business users and kill related connections.

Wait for data catch‑up.

Perform full data verification.

Execute forward switch.

Import non‑table objects on OceanBase.

Backup and delete MySQL triggers.

Backup and delete MySQL foreign keys.

Start reverse incremental.

Unlock OceanBase business users.

Connect business to OceanBase and start it.

3.3 Rollback Plan (Switch back to MySQL)

Stop business writes.

Lock OceanBase users and kill connections.

Wait for data catch‑up.

Stop reverse incremental.

Run data verification link to ensure consistency.

Create triggers on MySQL.

Create foreign keys on MySQL.

Unlock MySQL business users.

Connect business to MySQL and start it.

4. OMS Execute Forward Switch

Forward switch consists of Step 1 to Step 7.

5. Import Non‑Table Objects

Use DBCat to convert stored procedures, functions, triggers to OceanBase‑compatible DDL.

Create non‑table objects (e.g., triggers) on OceanBase.

6. Handling Triggers and Foreign Keys

6.1 Triggers

Export triggers with mysqldump, backup, delete on MySQL, then import on OceanBase.

mysqldump -h10.186.65.14 -P3306 -uoms_mysql -p'xxxxxxxxxx' --single-transaction --set-gtid-purged=OFF --no-data --no-create-info --triggers --databases company_db > /tmp/company_db_triggers.sql

Query, generate DROP statements, execute, verify count is zero.

SELECT TRIGGER_SCHEMA,TRIGGER_NAME FROM information_schema.triggers WHERE trigger_schema='company_db';

6.2 Foreign Keys

Generate DROP and CREATE statements for foreign keys using information_schema.

SELECT CONCAT('ALTER TABLE ',table_name,' DROP FOREIGN KEY ',constraint_name,';') AS drop_statement FROM information_schema.table_constraints WHERE constraint_type='FOREIGN KEY' AND table_schema='company_db';

SELECT CONCAT('ALTER TABLE ',tc.table_name,' ADD CONSTRAINT ',tc.constraint_name,' FOREIGN KEY (',kcu.column_name,') REFERENCES ',kcu.referenced_table_name,' (',kcu.referenced_column_name,');') AS create_statement FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name=kcu.constraint_name AND tc.table_schema=kcu.table_schema WHERE tc.constraint_type='FOREIGN KEY' AND tc.table_schema='company_db';

6.3 OMS Mark Non‑Table Object Import Completed

7. Start Reverse Incremental

7.1 OMS Click “Confirm Start Reverse Incremental”

7.2 Validate Reverse Incremental and Foreign Key Constraints

Insert data on OceanBase, verify sync to MySQL, and test foreign‑key enforcement.

INSERT INTO departments (dept_name, location) VALUES ('Training','Houston');

INSERT INTO employees (first_name, last_name, dept_id, hire_date) VALUES ('Alice','Green',11,'2025-09-05');

Verify data on MySQL, then attempt violating foreign key to see error.

INSERT INTO employees (first_name, last_name, dept_id, hire_date) VALUES ('Bob','White',999,'2025-09-05');

8. Stop Reverse Incremental and Rollback to MySQL

Business stops writing to OceanBase.

Lock OceanBase users and kill connections.

Wait for data catch‑up.

Pause reverse incremental.

Run data verification link.

9. Create Triggers and Foreign Keys on MySQL

9.1 Create Foreign Keys

ALTER TABLE employees ADD CONSTRAINT employees_ibfk_1 FOREIGN KEY (dept_id) REFERENCES departments (dept_id);

ALTER TABLE projects ADD CONSTRAINT projects_ibfk_1 FOREIGN KEY (emp_id) REFERENCES employees (emp_id);

9.2 Create Triggers

mysql -h10.186.65.14 -P3306 -uoms_mysql -p'$pwd' company_db < company_db_triggers.sql

Verify import by querying information_schema.triggers.

SELECT TRIGGER_SCHEMA,TRIGGER_NAME FROM information_schema.triggers WHERE trigger_schema='company_db';

10. Unlock MySQL Business Users

Connect to MySQL, start business, and verify traffic.

11. Reference

Data migration task details: https://www.oceanbase.com/docs/enterprise-oms-doc-cn-1000000003095422