MySQL to OceanBase Migration: Evaluation Tools, Migration Process, Monitoring, and Automated Fault Diagnosis

Authors : Ti Qie, Cong, and Typhoon – senior database engineers at Ctrip focusing on database automation and distributed database research.

1. Introduction MySQL has served Ctrip well for years, but evolving business models (OLTP/OLAP convergence, high slow‑query costs, and cumbersome sharding) demand a more scalable solution. OceanBase (OB), an open‑source distributed database compatible with most MySQL syntax, offers horizontal scalability, strong consistency, and high availability, prompting a phased migration from MySQL to OB.

2. Assessment Tool To ensure a smooth migration, Ctrip extended the official OceanBase Migration Assessment (OMA) tool. The enhanced tool adds checks for middleware version compatibility, low‑impact performance capture, non‑middleware account compatibility, and automatic table‑partition recommendations. Data collection uses MySQL General Log, Locust for replay, and produces a detailed assessment report.

3. Migration Process After a successful assessment, a one‑click automated migration is triggered, consisting of:

Pre‑migration configuration validation.

Creation of tenant‑aware OB accounts (MySQL accounts are mapped to OB accounts with tenant information).

Data consistency verification using Canal and primary‑key based result‑set comparison.

Temporary suspension of DDL on the source MySQL during migration.

Establishment of a CDC‑based reverse‑sync link for fast rollback.

When synchronization completes with no lag, a cut‑over task switches traffic to OB within a minute, with the reverse link retained for at least two weeks for safety.

4. OceanBase Monitoring Monitoring a distributed database requires a global view and alert aggregation. Ctrip deploys an Agent on each server to collect metrics, which are reported to Hickwall and visualized on a monitoring dashboard. Alerts are generated based on threshold breaches, and email notifications are sent for critical events.

5. Automated Fault Diagnosis With increasing MySQL‑to‑OB migrations, real‑time fault detection becomes critical. Ctrip built a performance data warehouse (ClickHouse) that ingests metrics every 10 seconds, stores structured logs, and runs automated analyses to match anomalies (CPU spikes, I/O saturation, QPS changes) with root causes at the SQL, table, or system level. The system generates diagnostic reports and emails them to DBAs.

6. Migration Issues & Practices

.NET MySQL connector incompatibility – missing charset index; Ctrip patched OB source to add the required charset.

Druid SQL parser errors – missing handling for OceanBase index options; a PR was submitted and merged.

Read‑write splitting – OBProxy was extended with enable_weak_read and weak_read_user_list to make read‑write separation transparent to applications.

Large IN‑list query range causing stack overflow – identified optimizer memory‑leak, reported to community, and fixed in newer OB releases.

Execution‑plan mismatches – OB’s optimizer may choose sub‑optimal plans; Ctrip uses outline hints to force stable plans.

7. Future Outlook OceanBase 4.0 introduces a unified single‑node‑plus‑distributed architecture, higher MySQL compatibility (charset, constraints, functions, stored procedures), and richer monitoring/diagnostic tooling. Ctrip plans to further automate link‑style diagnostics, improve DR capabilities, and continue contributing patches upstream.