How to Build a Real‑Time MySQL Data Sync Platform with Alibaba Otter

Background

In daily cloud‑platform operations many fault‑diagnosis and data‑verification scenarios require real‑time access to production data. To provide all operators (including developers and analysts) with a unified, up‑to‑date query interface, the team built a data‑query and management system based on MySQL and the open‑source Otter middleware, achieving near‑real‑time (seconds) synchronization with the live environment.

Otter Architecture

Otter is an Alibaba‑provided distributed database synchronization system that parses MySQL binlog via Canal and replicates changes to target MySQL (or Oracle) instances. Its core components are:

db : source and target databases.

Canal : captures incremental binlog.

manager : configuration and monitoring.

zookeeper : coordinates nodes.

node : executes the actual data‑transfer tasks.

Manager and node run on the same physical host, forming the central hub of the query module.

Key Features

Pure Java implementation (higher resource consumption).

Canal‑based binlog capture.

Typical manager‑web + node architecture.

Zookeeper‑driven distributed scheduling.

Aria2 multi‑threaded transfer for low bandwidth dependency.

Problems Otter Solves

Heterogeneous source‑to‑target sync (MySQL → MySQL/Oracle).

In‑datacenter sync with sub‑millisecond RTT.

Cross‑datacenter replication for disaster recovery.

Bidirectional sync with loop‑avoidance and consistency algorithms.

File and image replication together with data.

Installation & Configuration

Prerequisites

Alibaba Otter binaries (https://github.com/alibaba/otter/releases)

MySQL 5.7 (http://dev.mysql.com/downloads/mysql/)

Zookeeper 3.4.8 (http://download.csdn.net/download/jxplus/9451794)

JDK 1.6+ (yum install java-1.6.0-openjdk.x86_64)

CentOS 7.1+ (https://www.centos.org/download/)

Step‑by‑step

Install Zookeeper, edit /zookeeper-3.4.8/conf/zoo.cfg to set tickTime, clientPort, dataDir, then start with ./zkServer.sh start and verify with ps -ef | grep zookeeper.

Create the Otter manager database in MySQL and load the schema:

mysql -uroot -pxxxx otter < otter-manager-schema.sql

Unpack the manager package, edit /otter/conf/otter.properties to set otter.domainName, otter.port, and the Zookeeper cluster address.

Start the manager with sh startup.sh and check /otter/logs/manager.log for a successful start.

Configure a node: write the manager’s IP:PORT to /node/conf/nid, edit /node/conf/otter.properties to set otter.manager.address (e.g., 192.168.165.200:1099), then start the node with sh startup.sh.

In the manager UI, add data sources (source and target), define a channel, create a pipeline, and map tables. Finally, start the channel.

Test the synchronization via the UI or command line, and optionally build a Kerberos security domain for cross‑datacenter traffic.

Query Platform Usage

Operators can connect with tools such as Navicat using the same credentials as the production MySQL instance. Frequently accessed tables should be materialised as views, and queries should always filter by primary key to avoid full‑table scans. Avoid SELECT * without conditions.

Conclusion

The described steps produce a production‑grade data‑synchronisation and query platform that supports real‑time fault‑diagnosis, data verification, and safe cross‑datacenter access for operations teams.