MySQL Replication Guide

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

Prerequisites

MySQL Server Requirements

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.

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

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:

# Trigger snapshot sync for a table
mygram-cli SYNC articles

# In a multi-database configuration
mygram-cli SYNC app_db.articles

# Check sync progress
mygram-cli SYNC STATUS

See SYNC Command Guide for detailed usage.

Legacy Behavior

To restore automatic snapshot building on startup:

replication:
  auto_initial_snapshot: true   # Restore legacy behavior

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

In v1.7.0 and later, 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"

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:

• Uses SHOW BINARY LOG STATUS to get latest GTID
• 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

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;

MygramDB will:

• Clear all data
• Log error message
• Require manual restart/reconfiguration

ALTER TABLE

Logs a warning about potential schema inconsistency:

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

Important notes:

• Type changes (e.g., VARCHAR to TEXT) may cause replication issues
• Column additions/removals affecting text_source or filters require MygramDB restart
• Recommendation: Rebuild MygramDB snapshot after schema changes

Supported Column Types

MygramDB can replicate these MySQL column types:

Integer Types

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

String Types

• VARCHAR, CHAR, TEXT, BLOB, ENUM, SET

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 the CLI or TCP protocol:

# Using CLI
./build/bin/mygram-cli REPLICATION STATUS

# Using telnet
echo "REPLICATION STATUS" | nc localhost 11016

Response:

OK REPLICATION status=running gtid=3E11FA47-71CA-11E1-9E33-C80AA9429562:1-100

Stop Replication

Stop binlog replication (index becomes read-only):

./build/bin/mygram-cli REPLICATION STOP

Start Replication

Resume binlog replication:

./build/bin/mygram-cli 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.
• Add more MygramDB replicas

"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 (will rebuild snapshot)

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: 0                    # 0 = auto-generate
  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