Skip to content

mysql.md

Latest commit

 

History

History
169 lines (117 loc) · 8.4 KB

mysql.md

File metadata and controls

169 lines (117 loc) · 8.4 KB
Raw

Surreal-Sync for MySQL (trigger-based)

Start with binlog CDC. For most MySQL/MariaDB migrations, use surreal-sync from mysql-binlog sync — no triggers or audit tables, GTID-safe resume, and a long-lived follower.

This guide covers trigger-based sync (surreal-sync from mysql) when binlog is not an option: no replication privileges, managed MySQL without binlog access, cannot enable ROW-format binlog, or policy constraints on the source. MariaDB uses the same sub-command; see Surreal-Sync for MariaDB.

Optional transforms: pass --transforms-config with a TOML file. Omit the flag to leave rows unchanged. Details: How sync works.

Other strategies: For the legacy sequential-snapshot workflow (inconsistent monolithic snapshot plus a separate incremental replay from t1), see MySQL Legacy Full Sync.

How It Works

surreal-sync from mysql supports full, incremental, a combined sync, and an ad-hoc snapshot command.

Per-table triggers write to surreal_sync_changes for resumable, sequence-based checkpointing. The default interleaved-snapshot workflow copies tables in resumable chunks while consuming the trigger change stream; watermark reconciliation aligns each chunk with live changes (the log event wins), converging to a consistent image at the end position. Use sync for snapshot plus incremental handoff in one process.

See Full Sync Strategies for the consistency guarantee and strategy comparison.

Prerequisites

You need appropriate permissions to create triggers and tables in the MySQL database, because sync relies on database triggers to capture changes.

Every table you select for sync must have a usable primary key (single- or multi-column). Composite PKs become SurrealDB array record IDs (table:[k1, k2]) by default; join-table relations stay colon-flattened. Optional flatten_id / custom workers: How sync works — Record IDs. surreal-sync also writes a small surreal_sync_signal table on the source for watermark signalling.

TLS

Use --tls-mode when MariaDB is only reachable over an untrusted network (or when your server requires encrypted clients). The same flags work for sync, full, incremental, and ad-hoc snapshot:

  • --tls-mode disabled (default) — plain connection. Fine on a trusted network or VPN.
  • --tls-mode preferred — try TLS first; if the server cannot encrypt the connection (or the TLS handshake fails), surreal-sync falls back to a plain connection.
  • --tls-mode required — always use TLS; fail if encryption cannot be established.
  • --tls-ca <PATH> — trust this CA when checking the server certificate. Use this when you need to confirm you are talking to the intended server.
  • --tls-cert <PATH> / --tls-key <PATH> — present a client certificate when the server asks for one (both required together).

Encryption vs verification. With preferred or required, surreal-sync encrypts the session even when you omit --tls-ca. Without --tls-ca, it does not verify that the server certificate belongs to the host you expect — the connection is encrypted, but an attacker on the path could present another certificate. On untrusted networks, use --tls-mode required and pass the CA that signed your server via --tls-ca.

Connection string hostname. Put the hostname you expect the server to identify as in --connection-string (for example your MariaDB cloud endpoint, not only 127.0.0.1 when tunnelling). When you pass --tls-ca, that name is checked against the certificate.

Example with TLS on combined sync and snapshot signalling:

surreal-sync from mysql sync \
  --connection-string "$CONNECTION_STRING" \
  --database "myapp" \
  --tls-mode required \
  --tls-ca /path/to/ca.pem \
  ...

surreal-sync from mysql snapshot \
  --connection-string "$CONNECTION_STRING" \
  --database "myapp" \
  --tables "new_table" \
  --tls-mode required \
  --tls-ca /path/to/ca.pem

For binlog CDC (recommended for MariaDB), see Surreal-Sync for MySQL Binlog — TLS.

When --tls-mode disabled, --tls-ca, --tls-cert, and --tls-key are ignored.

Combined sync

The sync command runs the watermark snapshot and continues incremental sync from the handed-off end position in one process — no separate incremental replay pass is needed to reach consistency.

export CONNECTION_STRING="mysql://root:root@mysql:3306/myapp"
export SURREAL_ENDPOINT="ws://localhost:8000"

surreal-sync from mysql sync \
  --connection-string "$CONNECTION_STRING" \
  --database "myapp" \
  --surreal-endpoint "$SURREAL_ENDPOINT" \
  --surreal-username "root" \
  --surreal-password "root" \
  --to-namespace "production" \
  --to-database "migrated_data" \
  --tables "users,orders" \
  --chunk-size 1024 \
  --timeout 3600
  • --chunk-size <N>: rows read per keyset chunk during the snapshot phase (default 1024).
  • --timeout <SECONDS>: how long the incremental phase runs after the snapshot completes (default 3600).
  • --tables: comma-separated tables (empty means all tables).

Full Sync

Use full when you want a one-time snapshot without immediately continuing incremental sync — for example, to bulk-load data and run incremental on a schedule later. Interleaved-snapshot is the default (--chunk-size, default 1024).

export CONNECTION_STRING="mysql://root:root@mysql:3306/myapp"
export SURREAL_ENDPOINT="ws://localhost:8000"

surreal-sync from mysql full \
  --connection-string "$CONNECTION_STRING" \
  --database "myapp" \
  --surreal-endpoint "$SURREAL_ENDPOINT" \
  --surreal-username "root" \
  --surreal-password "root" \
  --to-namespace "production" \
  --to-database "migrated_data" \
  --checkpoint-dir ".surreal-sync-checkpoints"

Provide a checkpoint store (--checkpoint-dir or --checkpoints-surreal-table) so incremental can resume from the end position.

Example log output:

INFO surreal_sync::mysql: Emitted full sync start checkpoint (t1): mysql:sequence:0
INFO surreal_sync::mysql: Emitted full sync end checkpoint (t2): mysql:sequence:123

The (t1) / (t2) labels are log names for the bracketing positions. With interleaved-snapshot, both checkpoints record the same consistent end position — start incremental from either file.

Incremental Sync

Prefer sync when starting a new migration. If you already ran full, use incremental to continue live tracking from the end position (not a replay pass to fix inconsistency):

NUM=$(cat ./.surreal-sync-checkpoints/checkpoint_full_sync_end_*.json | jq -r '.checkpoint.MySQL.sequence')
CHECKPOINT="mysql:sequence:$NUM"

surreal-sync from mysql incremental \
  --connection-string "$CONNECTION_STRING" \
  --database "myapp" \
  --surreal-endpoint "$SURREAL_ENDPOINT" \
  --surreal-username "root" \
  --surreal-password "root" \
  --to-namespace "production" \
  --to-database "migrated_data" \
  --incremental-from "$CHECKPOINT" \
  --timeout 1m

--incremental-from is the end-position checkpoint; --timeout controls how long the run continues (useful for batched or scheduled runs).

While incremental sync is running, your application can continue writing to MySQL without downtime, as long as the source can serve the workload.

Ad-hoc Snapshots (Signalling)

While a sync is streaming, you can snapshot additional tables on the fly. The snapshot command inserts an execute-snapshot signal row into surreal_sync_signal; the running sync picks it up and snapshots the requested tables while streaming continues:

surreal-sync from mysql snapshot \
  --connection-string "$CONNECTION_STRING" \
  --database "myapp" \
  --tables "new_table,another_table"
  • --tables (required): comma-separated tables to snapshot.

Troubleshooting

Missing Triggers

If incremental sync is not capturing changes, ensure triggers were created during full or sync:

SHOW TRIGGERS LIKE 'surreal_sync_%';

Audit Table Growth

Interleaved-snapshot prunes consumed rows from surreal_sync_changes as the stream is applied, keeping retention bounded during snapshot and streaming. A long-lived standalone incremental process can still let the table grow — monitor size and prune synced rows if needed.

Data Type Support

See MySQL Data Types for data type mapping information.