Business Requirement

Both the source and target schemas on the same MySQL instance contain a table named student. Any INSERT, UPDATE, or DELETE executed against source.student must be reflected in target.student with sub-second latency.

Candidate Approaches

1. Shell Script

Quick PoC with bash + mysqldump was abandoned because the team lacked shell expertise and the approach would only support batch, not real-time, synchronization.

2. DataX

Alibaba’s offline batch tool supports heterogeneous sources, but:

• Distribution size ≈ 771 MB
• Requires Python runtime
• Designed for cross-DB engines, overkill for two schemas in the same MySQL instance

3. Otter – Chosen Solution

Otter is a Java-based, distributed, near-real-time replication system that leverages Canal for binlog parsing and ZooKeeper for cluster coordination.

3.1 Architecture Overveiw

1. Canal tails the MySQL binary log and publishes row events.
2. Otter Manager (web console) pushes channel definitions to Node(s).
3. Each Node applies the captured events to the destination schema and reports progress back to Manager.
4. ZooKeeper keeps Manager and Nodes in sync, enabling horizontal scaling.

Environment Specification

Setup Steps

1. Enable Binary Logging

Add the following to my.cnf (Linux) or my.ini (Windows, saved as GB2312):

[mysqld]
log-bin=/var/lib/mysql/mysql-bin
binlog-format=ROW
server-id=201609   # unique across the topology

Verify with:

SHOW MASTER STATUS;

2. Prepare Otter System Tables

Import otter-manager-schema.sql. If you hit

ERROR 1067 (42000): Invalid default value for 'GMT_CREATE'

set explicit defaults:

ALTER TABLE `otter_system`.`ALARM_RULE`
  MODIFY `GMT_CREATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;

3. Configure ZooKeeper

# conf/zoo.cfg
tickTime=2000
dataDir=/opt/zookeeper/data
clientPort=2181

4. Configure Otter Manager

# conf/otter.properties
otter.domainName = 127.0.0.1
otter.port = 8080
otter.database.driver = com.mysql.jdbc.Driver
otter.database.url = jdbc:mysql://127.0.0.1:3306/otter_system
otter.database.username = otter
otter.database.password = otter_pwd

5. Register Node

# conf/otter.properties on each node
otter.nodeId = 1
otter.manager.address = 127.0.0.1:1099

Define a Channel in Manager UI

1. Create two Data Sources pointing to the same MySQL instance but different schemas.
2. Create a Data Media Pair mapping source.student → target.student.
3. Enible row mode and field overwrite if you want identical replicas.
4. Start the channel; initial load plus streaming replication begins immediately.

Common Pitfalls

• Duplicate server-id prevents MySQL startup.
• Wrong binlog format (STATEMENT) causes Otter to miss row images.
• Missing time-zone tables in MySQL leads to timestamp drift warnings.