MySQL Replication Guide

MygramDB supports real-time replication from MySQL using GTID-based binlog streaming with guaranteed data consistency.

What is replication?

Replication continuously propagates changes from MySQL to another process. To replace MySQL full-text search, MygramDB keeps reading the MySQL binlog and updating its search index.

Prerequisites

MySQL Server Requirements

Why ROW-format binlog

ROW format records which rows changed and how. MygramDB updates its index from the post-change row data, so STATEMENT format is not sufficient.

MygramDB requires:

• MySQL Version: 8.4+ / 9.x, or MariaDB 10.6+/11.x
• GTID Mode: Must be enabled for MySQL; MariaDB uses its native GTID format
• Binary Log Format: ROW format required
• Privileges: Replication user needs specific privileges

Enable GTID Mode

Check if GTID mode is enabled:

SHOW VARIABLES LIKE 'gtid_mode';

If GTID mode is OFF, enable it:

-- Enable GTID mode
SET GLOBAL enforce_gtid_consistency = ON;
SET GLOBAL gtid_mode = OFF_PERMISSIVE;
SET GLOBAL gtid_mode = ON_PERMISSIVE;
SET GLOBAL gtid_mode = ON;

MariaDB does not use MySQL's gtid_mode and enforce_gtid_consistency variables. Set a unique server_id, enable binary logging, and use binlog_format=ROW.

Persist the setting too

SET GLOBAL applies to the running MySQL but can revert after a restart. In production, also put gtid_mode=ON and enforce_gtid_consistency=ON into my.cnf or whatever manages your MySQL configuration.

On MariaDB

MariaDB is similar to MySQL but expresses GTIDs differently. MygramDB detects the server flavor automatically, so the same mysql config section works for both.

Configure Binary Log

Ensure binary logging is enabled with ROW format:

-- Check binary log format
SHOW VARIABLES LIKE 'binlog_format';

-- Set to ROW format (add to my.cnf and restart)
SET GLOBAL binlog_format = ROW;

Create Replication User

Why binlog_row_image=FULL is required

To update text columns, primary keys, and filter columns correctly on UPDATE and DELETE, MygramDB needs enough of the row in the binlog. MINIMAL can omit columns it depends on.

Create a user with replication privileges:

-- Create replication user
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'your_password';

-- Grant replication privileges (for binlog reading and GTID information)
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl_user'@'%';

-- Grant SELECT privilege for snapshot creation (REQUIRED)
-- Note: SELECT privilege on target tables is necessary for snapshot building
GRANT SELECT ON database_name.table_name TO 'repl_user'@'%';

-- Apply changes
FLUSH PRIVILEGES;

Important Notes:

1. REPLICATION CLIENT privilege is required: Necessary for retrieving GTID information
2. SELECT privilege is required: Necessary for reading table data during initial snapshot creation
3. No restart required: GRANT statements are applied online and take effect immediately
4. Principle of least privilege: When synchronizing multiple tables, grant SELECT privilege for each table individually

Security Considerations

• MySQL credentials are transmitted in plain text unless your MySQL server requires TLS. Place MygramDB close to MySQL on a trusted network, or terminate TLS/SSH tunnels in front of it when replicating across untrusted links.
• Snapshots created via DUMP SAVE include the MySQL host/user/password. Store dump files on encrypted storage with restrictive permissions (e.g., chmod 600) and rotate them like any other secret.

Manual Snapshot Synchronization

MygramDB supports manual snapshot synchronization to prevent unexpected load on the MySQL primary during startup.

Configuration

By default, MygramDB no longer automatically builds snapshots on startup:

replication:
  enable: true
  auto_initial_snapshot: false  # Default: false (safe by default)
  server_id: 12345
  start_from: "snapshot"

Manual SYNC Command

Use the SYNC command to manually trigger snapshot synchronization:

The production examples below assume api.admin_token is configured. If it is empty, AUTH is not required; non-loopback deployments should configure a token.

$ mygram-cli
127.0.0.1:11016> AUTH <token>
OK AUTHENTICATED
127.0.0.1:11016> SYNC app_db.articles
OK SYNC STARTED table=app_db.articles
127.0.0.1:11016> SYNC STATUS

Use SYNC articles instead in a single-database configuration.

See SYNC Command Guide for detailed usage.

Automatic startup snapshot

To restore automatic snapshot building on startup:

replication:
  auto_initial_snapshot: true
  start_from: "snapshot"

When automatic startup snapshots are enabled, use start_from: "snapshot" for both single-table and multi-table deployments.

Benefits of manual sync:

• Prevents unexpected MySQL load during server startup
• Allows operators to control when synchronization occurs
• Enables scheduled sync during off-peak hours
• Provides progress monitoring and cancellation

Multi-table and Multi-database Snapshot Consistency

Every table has an effective identity of <database>.<table>. If all configured tables are in one database, bare names such as articles remain accepted. When the configuration spans two or more databases, use qualified names in SYNC, SEARCH, and all client APIs.

When replication is enabled and more than one table is configured, the initial snapshot uses one START TRANSACTION WITH CONSISTENT SNAPSHOT across the configured tables and captures a shared GTID. This keeps related tables aligned at the same MySQL point-in-time before binlog streaming resumes.

Replication Start Options

Configure replication.start_from in your config file:

snapshot (Recommended)

Starts from GTID captured during initial snapshot build:

replication:
  start_from: "snapshot"

The diagram below traces that guarantee across the transaction boundary: the snapshot transaction opens and gtid_executed is captured in the same instant, table reads happen entirely inside that frozen view while new writes keep accumulating in the binlog, and streaming then resumes from precisely that captured GTID once the snapshot completes.

How it works:

• Uses START TRANSACTION WITH CONSISTENT SNAPSHOT for data consistency
• Captures @@global.gtid_executed at exact snapshot moment
• Guarantees no data loss between snapshot and binlog replication

When to use:

• Initial setup (recommended for most cases)
• When you need a consistent point-in-time view
• When starting from scratch

latest

Starts from current GTID position (ignores historical data):

replication:
  start_from: "latest"

How it works:

• MySQL uses SHOW BINARY LOG STATUS; MariaDB uses SELECT @@GLOBAL.gtid_binlog_pos
• Only captures changes after MygramDB starts

When to use:

• When you only need real-time changes
• When historical data is not important

gtid=UUID:txn

Starts from specific GTID position:

replication:
  start_from: "gtid=3E11FA47-71CA-11E1-9E33-C80AA9429562:100"

When to use:

• Manual recovery from specific point
• Testing or debugging

Supported Operations

DML Operations

MygramDB automatically handles:

• INSERT (WRITE_ROWS events)
  • Adds new documents to index and store
• UPDATE (UPDATE_ROWS events)
  • Updates document content and filters
  • Re-indexes text if changed
• DELETE (DELETE_ROWS events)
  • Removes document from index and store

DDL Operations

Consider a resync after a schema change

If you change a column referenced by text_source, primary_key, filters, or required_filters, fix the schema or the configuration and then rebuild with SYNC. A restart alone does not resume replication.

MygramDB handles these DDL operations:

TRUNCATE TABLE

Automatically clears index and document store for the target table:

TRUNCATE TABLE articles;

MygramDB will:

• Clear all documents from the table
• Clear all posting lists
• Reset document ID counter

DROP TABLE

Clears all data and logs an error:

DROP TABLE articles;

If the dropped table provides a configured primary key, text source, or filter column, MygramDB stops replication before advancing the GTID. Restore a compatible schema or update the configuration, then run SYNC to rebuild the table.

ALTER TABLE

Changes unrelated to configured columns can continue after a warning. An incompatible change to a configured primary key, text source, or filter column stops replication before advancing the GTID:

ALTER TABLE articles ADD COLUMN new_col VARCHAR(100);
ALTER TABLE articles MODIFY COLUMN content TEXT;

Recovery: Fix the schema or configuration, then run SYNC to rebuild the table. Do not assume that a restart alone resumes replication after an incompatible DDL change.

Supported Column Types

MygramDB can replicate these MySQL column types:

Integer Types

• TINYINT, SMALLINT, INT, MEDIUMINT, BIGINT (signed/unsigned)

String Types

• VARCHAR, CHAR, TEXT, ENUM, SET

Configured primary-key, text-source, and filter columns cannot use BINARY, VARBINARY, or BLOB. Character columns used by the configuration must have an utf8mb4, utf8/utf8mb3, or ascii collation.

Date/Time Types

• DATE, TIME, DATETIME, TIMESTAMP (with fractional seconds)

Numeric Types

• DECIMAL, FLOAT, DOUBLE

Special Types

• JSON, BIT, NULL

Replication Features

GTID Consistency

• Snapshot and binlog replication are coordinated via consistent snapshot transaction
• No data loss between snapshot and replication

GTID Position Tracking

• Atomic persistence with state file
• Automatic save on shutdown
• Resume on restart

Automatic Validation

• Checks GTID mode on startup
• Clear error messages if not configured

Automatic Reconnection

• Handles connection loss gracefully
• Exponential backoff retry (configurable)
• Continues from last GTID position

Multi-threaded Processing

• Thread pool architecture for efficient request handling
• Configurable queue size for performance tuning

Monitoring Replication

Check Replication Status

Use one authenticated CLI connection:

$ ./build/bin/mygram-cli
127.0.0.1:11016> AUTH <token>
OK AUTHENTICATED
127.0.0.1:11016> REPLICATION STATUS

Response:

OK REPLICATION
status: running
current_gtid: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-100
processed_events: 12345
queue_size: 0
END

Stop Replication

Stop binlog replication (index becomes read-only):

$ ./build/bin/mygram-cli
127.0.0.1:11016> AUTH <token>
OK AUTHENTICATED
127.0.0.1:11016> REPLICATION STOP

Start Replication

Resume binlog replication:

$ ./build/bin/mygram-cli
127.0.0.1:11016> AUTH <token>
OK AUTHENTICATED
127.0.0.1:11016> REPLICATION START

Troubleshooting

"GTID mode is not enabled on MySQL server"

Solution: Enable GTID mode on MySQL server:

SET GLOBAL enforce_gtid_consistency = ON;
SET GLOBAL gtid_mode = OFF_PERMISSIVE;
SET GLOBAL gtid_mode = ON_PERMISSIVE;
SET GLOBAL gtid_mode = ON;

Then restart MygramDB.

"Binary log format is not ROW"

Solution: Set binary log format to ROW:

SET GLOBAL binlog_format = ROW;

Or add to my.cnf and restart MySQL:

[mysqld]
binlog_format = ROW

"Replication lag is high"

Possible causes:

• High write volume on MySQL
• Insufficient MygramDB resources
• Network latency

Solutions:

• Increase replication.queue_size in config
• Add more MygramDB replicas or reduce upstream write pressure. build.parallelism is reserved / not yet enforced by the current snapshot loader.

Use replication_seconds_since_last_applied together with MySQL write activity and the reader state. The timestamp advances with the applied GTID, so a large value means no event has been applied recently; it does not by itself prove a lagging reader when the source is idle. /health/ready returns 503 when replication is unavailable, while INFO exposes the same overall readiness and data_initialized state.

"Lost connection to MySQL server during query"

MygramDB will automatically reconnect using the built-in retry schedule. The backoff fields below are accepted for forward compatibility but are not enforced today:

replication:
  reconnect_backoff_min_ms: 500
  reconnect_backoff_max_ms: 10000

"Schema mismatch after ALTER TABLE"

Solution: Rebuild snapshot after schema changes:

1. Stop MygramDB
2. Update config file to match new schema
3. Restart MygramDB
4. Run SYNC to rebuild the affected table

Recovering from an undecodable binlog event

If replication stops with error code 2017, the stream encountered an event this build cannot decode, such as an XA prepare event or a compressed MariaDB event. Changing the server setting that produced it does not remove the already-written event, so starting again from the old GTID stops at the same place.

Run SYNC for the table that is most important first, wait for it to complete with replication=STARTED, then run SYNC for every other replicated table. That first SYNC restarts the shared reader from the new snapshot marker and skips the undecodable interval. A table that you do not rebuild keeps its prior state for writes from that skipped interval.

For every other replication failure, SYNC uses the drained position rather than skipping an interval. See SYNC command for the command sequence.

Best Practices

1. Always use GTID mode for consistent replication
2. Use snapshot start mode for initial setup
3. Monitor replication lag regularly
4. Rebuild snapshot after significant schema changes
5. Test configuration before deploying to production
6. Keep state file for crash recovery
7. Use multiple replicas for high availability

Configuration Example

Complete replication configuration:

mysql:
  host: "127.0.0.1"
  port: 3306
  user: "repl_user"
  password: "your_password"
  database: "mydb"
  use_gtid: true
  binlog_format: "ROW"
  binlog_row_image: "FULL"

replication:
  enable: true
  server_id: 83917                # Unique value from 1 to 4294967295
  start_from: "snapshot"          # snapshot|latest|gtid=<UUID:txn>
  queue_size: 10000
  reconnect_backoff_min_ms: 500   # Reserved / not yet enforced
  reconnect_backoff_max_ms: 10000 # Reserved / not yet enforced

See Also

• SYNC Command Guide - Manual snapshot synchronization
• Configuration Guide - Full configuration reference
• Protocol Reference - REPLICATION commands