Three Approaches to Database Migration to the Cloud: A Scenario-Based Guide

Match Your Migration Approach to Your Business, Not the Other Way Around

When migrating a database to the cloud, the most difficult question is often not how to migrate, but which approach to use. Data import and export tools, OMA (OceanBase Migration Assessment) + OMS (OceanBase Migration Service), and Binlog ecosystem integration each have distinct strengths and apply to very different scenarios.
Teams often make one of two mistakes when selecting an approach. They either apply one approach to every system, which overengineers simple scenarios or provides insufficient safeguards for complex ones, or they rely solely on experience without matching the approach to the business characteristics.
This article helps you select the right migration approach based on your business scenario. It first establishes a decision framework, then explains the technical principles and procedures of the three approaches, and finally provides implementation recommendations for a hybrid strategy.

1. Decision framework: three scenarios and three choices

The following overview shows where each approach applies, followed by examples that clarify their boundaries.
The key is to match solution capabilities to the business requirements for downtime, data consistency, and real-time processing, instead of choosing the tool that appears to have the most features.

├── Core system migration (no downtime / no data errors / rollback required)
│   └── → OMA + OMS + data verification (enterprise migration solution)
├── Simple system migration (downtime window / simple schema / cost-sensitive)
│   └── → Data import and export tools (obloader/OBDumper / LOAD DATA)
├── Data integration and real-time synchronization (multi-source aggregation / real-time analytics / big data platform integration)
│   └── → Binlog ecosystem integration (OceanBase Binlog Service + message queue + stream processing)
└── Hybrid scenario (multiple types of systems)
    └── → Tiered strategy: OMS for core systems, data import and export tools for simple systems, and Binlog for analytics

Key distinction: Data import and export tools and OMS primarily address database migration, whereas the Binlog ecosystem primarily addresses continuous data integration. This is the most important distinction to understand when selecting an approach.

The three approaches complement rather than exclude one another. Many enterprises use two or even all three. The following examples demonstrate how to choose an approach for different scenarios.

Example 1: E-commerce promotion system → data import and export tools

An e-commerce company has a promotion rule management system with approximately 500GB of data and a simple schema consisting mainly of rule configuration tables. The system has the following characteristics:

• High tolerance for downtime: An 8-hour maintenance window is available on weekends.
• Moderate data consistency requirements: Static consistency as of the export time is sufficient.
• Low technical complexity: No complex stored procedures or cross-database transactions.
• Limited budget: The team wants to complete the migration at the lowest cost.
  Decision: Use data import and export tools and complete the migration in 6 hours over a weekend.
  Why not use OMS? A simpler approach is sufficient for this system. It has an adequate downtime window, a simple data structure, and no need for incremental synchronization. Introducing OMS would add unnecessary learning and configuration costs.

Example 2: Core banking system → OMA + OMS

A city commercial bank operates a core accounting system that processes two million transactions per day. The system has the following characteristics:

• No tolerance for downtime: The system must remain online 24/7, and any downtime must be reported to regulators.
• Extremely strict data consistency requirements: No discrepancies are acceptable, including discrepancies of a single cent.
• High technical complexity: More than 300 stored procedures and complex cross-table constraints.
• Strict compliance requirements: Financial regulations require a complete migration assessment report and rollback plan.
  Decision: Use OMA + OMS + data verification. After three months of assessment, adaptation, migration, and verification, switch over without downtime.
  Why not use data import and export tools? They cannot meet the zero-downtime requirement and do not provide risk assessment. For a core financial system, uncertainty about potential issues is itself an unacceptable risk.

Example 3: Internet data platform → Binlog ecosystem integration

An internet company’s data platform needs to integrate data from more than 10 business systems, including MySQL, PostgreSQL, and MongoDB, and provide real-time analytics. It has the following characteristics:

• Strict real-time requirements: Latency from the source database to the analytics platform must be measured in seconds.
• Extensive ecosystem integration: The solution must integrate with existing big data platforms such as Kafka, Flink, and Elasticsearch.
• Complex data flows: Data flows from multiple sources to multiple destinations, rather than only from system A to system B.
• Clear long-term plan: The goal is to build a unified data platform, with migration as only the first step.
  Decision: Use Binlog ecosystem integration with OceanBase Binlog Service, Kafka, and Flink to build a real-time data pipeline.
  Why not use OMS? OMS addresses migration from system A to system B, whereas a data platform requires a pipeline that continuously integrates data from multiple sources. The underlying requirement is data integration rather than database migration.

2. Migration with data import and export tools: simple but effective

When to use data import and export tools

Data import and export tools provide the simplest and most direct of the three approaches. The technology is mature, easy to adopt, and incurs almost no additional cost. It applies to scenarios with the following characteristics:

• An available downtime window: The business can tolerate an interruption lasting from several hours to several days.
• Moderate data volume: The data volume is typically below the terabyte level.
• Simple structure: No complex stored procedures or extensive cross-database dependencies.
• Cost sensitivity: The budget is limited and migration costs must be controlled.

Technical principles

The core process is straightforward: export data from the source → convert the format → import the data into the destination. The process is static and runs once, without incremental synchronization.
OBLoader and OBDumper are official OceanBase client tools for data import and export. They support formats including CSV, SQL, CUT, Parquet, ORC, and POS, and are suitable for bulk imports at the terabyte scale. For scenarios with higher performance requirements, you can use Direct Load to write data directly to data files and further improve import efficiency.
Typical usage:

./obloader -h <host> -P <port> -u <user> -p <password> \
  --tenant <tenant> --cluster <cluster> --database <database> \
  --table <table> --csv --file-path /path/to/data/ --external

Version information: Starting with OceanBase 4.2.1, the data import and export tools no longer have separate Enterprise Edition and Community Edition variants. The tools can import data from various storage media, including local disks, Apache Hadoop, Aliyun OSS, Amazon S3, Tencent COS, Huawei OBS, Azure Blob, and GCS.
OceanBase Database also supports other optional tools: mysqldump for native MySQL exports, LOAD DATA for SQL-level bulk imports, expdp/impdp for native Oracle imports and exports, and DataX, Alibaba’s open source tool for multi-source data synchronization.

Advantages and limitations of data import and export tools

Advantages:

• Simple and mature technology: Most DBAs already have the required skills, resulting in a low learning cost.
• Broad tool availability: Native database tools are available without additional investment.
• Flexibility and control: You can customize the export and import logic.
• Almost no additional cost: The main costs are labor and time.
  Limitations:
• Requires a downtime window: Writes must be frozen during export to avoid inconsistent data.
• No incremental synchronization: Data changes made after the export cannot be synchronized automatically to the destination.
• Difficulty processing very large tables: Tables larger than 100GB can cause timeouts or insufficient memory.
• Manual adaptation of complex objects: Stored procedures, triggers, partitioned tables, and other complex objects may require manual handling during migration.
  If the downtime window is sufficient and the system is simple, data import and export tools offer the best balance of cost and capability.

3. OMA + OMS + data verification: the standard approach for enterprise migration

When to use OMA + OMS

OMA + OMS provides the most comprehensive safeguards among the three approaches. It covers the entire process, from pre-migration assessment and migration execution to data verification and canary switchover.
Scenarios suited to OMA + OMS share one characteristic: the cost of failure is high. Specifically:

• No downtime is allowed: This applies to core transaction systems and 24/7 online services.
• No data errors are allowed: This applies to financial accounting and payment clearing.
• Risk assessment is required: This applies to financial regulation and audit compliance.
• Rollback safeguards are required: This applies to canary switchover scenarios where the system must be able to revert immediately if an issue occurs.

Core process: assess → execute → verify → switch over

OMA + OMS is not a single tool. It is a complete migration methodology with four stages:
1. Migration assessment (OMA): Supports source databases including MySQL, Oracle, PostgreSQL, TiDB, and DB2 LUW. It provides online assessment by connecting directly to the source database and offline assessment by uploading SQL files. The assessment report includes object compatibility results (PASS/CONVERT/FAILURE), SQL compatibility, a database profile, and recommended migration solutions.
2. Migration execution (OMS): Performs schema migration → full migration → incremental synchronization → full verification → reverse incremental synchronization. Supported links include MySQL / Oracle / TiDB → OceanBase in MySQL or Oracle mode, OceanBase → OceanBase across clusters or cloud providers, and OceanBase → Kafka / RocketMQ for data subscription.
3. Data verification (OMS): Provides three levels of verification: full verification through row-by-row comparisons between the source and destination, incremental verification of incremental data in real time, and business-level verification of key business metrics through SQL statements or scripts.
4. Canary switchover (OMS + application layer): Controls risk gradually through dual-write verification → allowlist-based traffic routing → percentage-based traffic routing (1% → 5% → 10% → 50%) → full switchover. Reverse incremental synchronization makes rollback available throughout the process.

Get OceanBase User Group’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Advantages and limitations of OMA + OMS

Advantages:

• Early risk identification: OMA identifies compatibility issues before migration, avoiding the discovery of unsupported behavior after data has been migrated.
• End-to-end safeguards: Covers assessment, migration, verification, and switchover.
• Zero-downtime switchover: Incremental and reverse incremental synchronization allow the business to migrate without downtime.
• Mature solution: Has been validated in core-system migrations.
  Limitations:
• Complex process: More complex than data import and export tools and requires more time and personnel.
• Learning curve: The team must learn how to use OMA and OMS.
• Potential overengineering: For a simple system with an adequate downtime window, the benefits may not justify the additional complexity.
  For a simple system with a sufficient downtime window, OMA + OMS may provide little additional benefit and can constitute overengineering.

4. Binlog ecosystem integration: integrating with an existing data ecosystem

When to use Binlog ecosystem integration

Binlog ecosystem integration is fundamentally a data integration solution, not a migration solution. Use it when data must continue to flow, rather than only being transferred once from system A to system B.
Typical scenarios have the following characteristics:

• Multi-source aggregation: Data from more than 10 business systems must be integrated, rather than migrated one-to-one.
• Continuous data flow: A data pipeline is required instead of a one-time transfer.
• Toolchain integration: The system must integrate with big data technologies such as Kafka, Flink, and Elasticsearch.
• Incremental migration: Microservices are migrated gradually instead of switching over all at once.

OMS data subscription vs. a self-managed Binlog pipeline

This is one of the most common implementation questions. Both approaches can push changes from OceanBase to downstream systems, but they serve different purposes:

Recommendations:

• If you only need to push OceanBase data to Kafka for downstream consumption, OMS data subscription is the most convenient choice. It is ready to use and requires less O&M.
• If you need a complete data pipeline for multi-source aggregation, complex transformation, and distribution to multiple destinations, use a self-managed Binlog pipeline.
• You can also combine the two: use OMS for stability on core links and self-managed pipelines for flexibility in complex scenarios.

Core features of OceanBase Binlog Service

OceanBase Binlog Service, formerly oblogproxy, converts transaction logs (CLOGs) from OceanBase Database into a MySQL-compatible Binlog format. It is primarily used for scenarios such as real-time data subscription.
In Binlog Service mode, CLOGs are converted into a MySQL-compatible Binlog format. This mode is fully compatible with MySQL ecosystem tools such as Canal and Debezium. Format conversion consumes additional computing resources, but these are dedicated Binlog Service resources and do not consume the resources of OceanBase Database itself. Full compatibility with the MySQL Binlog ecosystem makes this mode more suitable for scenarios that already use that ecosystem and require rapid integration.

Mainstream Binlog tools

Typical applications of Binlog ecosystem integration

Scenario 1: Multi-source aggregation for a data platform

MySQL (order system)       ─┐
PostgreSQL (user system)   ─┤→ OceanBase Binlog Service → Kafka → Flink → data lake → BI analytics
OceanBase (trading system) ─┘

Data from multiple business systems is aggregated in real time through Binlog into a unified data platform for real-time analytics and reporting.
Scenario 2: Real-time search index updates

OceanBase → OceanBase Binlog Service (Binlog mode) → Canal → Kafka → Elasticsearch (search index)

Changes to business data are synchronized to Elasticsearch in real time to keep search results current.
Scenario 3: Cache consistency

OceanBase → OceanBase Binlog Service (Binlog mode) → Flink → cache refresh service → Redis (hot data cache)

Database changes trigger cache updates in real time to avoid cache inconsistencies.

Advantages and limitations of Binlog ecosystem integration

Advantages:

• Low latency: Data changes can be delivered downstream with latency measured in seconds or even milliseconds.
• Mature ecosystem: The solution is built on the MySQL Binlog ecosystem, which provides a rich and mature toolchain.
• High flexibility: Supports multi-source aggregation, complex transformation, and distribution to multiple destinations.
• Transparent to the business: Continuous synchronization does not interrupt the business system.
• Strong ecosystem compatibility: Integrates directly with existing MySQL Binlog ecosystem tools.
  Limitations:
• High architectural complexity: Multiple components must be maintained, requiring strong technical capabilities from the team.
• Eventual consistency: The system provides eventual consistency, and transient inconsistencies may occur in intermediate states.
• Long debugging cycle: Building and debugging a Binlog pipeline can take substantial time.
• Not suitable for strong consistency requirements: OMS is more suitable for scenarios that require strong consistency.

5. Hybrid migration strategy and summary

In real-world enterprise environments, a tiered strategy is more common. Select the most appropriate approach based on the characteristics of each system:

Rollback strategies for each approach

Summary

Migrating a database to the cloud is fundamentally a matching exercise: align the capabilities of an approach with the characteristics of the business.
Data import and export tools, OMA + OMS, and Binlog ecosystem integration each serve a different purpose:

• Data import and export tools address whether the data can be migrated. They are suitable for simple scenarios where downtime is acceptable.
• OMA + OMS address how to migrate reliably. They are suitable for core systems where errors are unacceptable.
• Binlog ecosystem integration enables continuous data flow. It is suitable for data platforms and real-time analytics.

Ask three key questions when selecting an approach:

• How much downtime can the business tolerate?
  - Hours or days → data import and export tools.
  - Zero downtime or minutes → OMA + OMS.
  - No downtime because synchronization is continuous → Binlog.
• What level of data consistency is required?
  - Static consistency is sufficient → data import and export tools.
  - 100% strong consistency → OMA + OMS.
  - Eventual consistency → Binlog.
• Where will the data flow after migration?
  - One-time transfer to a new database → data import and export tools or OMS.
  - Continuous synchronization to downstream systems → Binlog.