MainSourcesMysql

MySQL Setup

Prerequisites

  • MySQL 5.7 or later, self hosted or managed (Amazon RDS, Azure Database for MySQL).
  • Binary logging enabled with ROW format and a retention period of at least 3 days recommended.
  • A user with SELECT, REPLICATION SLAVE, and REPLICATION CLIENT privileges.
  • Network connectivity from the Supermetal agent to the MySQL server (default port 3306).

Replication modes

Supermetal detects the replication mode at startup.

  • GTID mode. When gtid_mode=ON, position tracking uses Global Transaction Identifiers. Recommended where possible, since it survives failover to a different server.
  • File+position mode. Tracks the binlog file name and byte offset. Use when GTID cannot be enabled on the server.

Setup

Supermetal requires a dedicated MySQL user with permissions to read data and access the binary log.

PermissionPurpose
SELECTRead data from tables during snapshot and CDC
REPLICATION SLAVEAccess the binary log for CDC
REPLICATION CLIENTQuery server status and replication position

Self hosted MySQL setup

Enable binary logging

Edit your MySQL configuration file (my.cnf or my.ini):

[mysqld]
# Server identification (must be unique in your infrastructure)
server-id = 223344

# Binary logging (required)
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
binlog_row_metadata = FULL

# GTID (recommended, not required)
gtid_mode = ON
enforce_gtid_consistency = ON

# Binary log retention (3 days)
binlog_expire_logs_seconds = 259200

Configuration parameters

  • server-id: must be unique for each server in your replication topology. Any non zero value between 1 and 4294967295.
  • log_bin: base name for binary log files.
  • binlog_format: must be ROW.
  • binlog_row_image: must be FULL to capture complete before and after row images.
  • binlog_row_metadata: must be FULL to include column metadata in binary log events.
  • gtid_mode and enforce_gtid_consistency: recommended for failover support. If you cannot enable GTID (for example, other CDC tools on the same server depend on non GTID mode), Supermetal uses file+position tracking instead.
  • binlog_expire_logs_seconds: how long binary logs are kept before automatic deletion.

Create a dedicated user for replication

Connect to MySQL as a privileged user and run:

CREATE USER 'supermetal_user'@'%' IDENTIFIED BY 'strong-password';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'supermetal_user'@'%';
FLUSH PRIVILEGES;

Script variables

Replace supermetal_user and strong-password with your own values and store the password securely.

Verify configuration

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'binlog_row_image';
SHOW VARIABLES LIKE 'binlog_row_metadata';
SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'gtid_mode';

Required values:

  • log_bin: ON
  • binlog_format: ROW
  • binlog_row_image: FULL
  • binlog_row_metadata: FULL
  • server_id: a non zero value

GTID values (if enabled):

  • gtid_mode: ON
  • enforce_gtid_consistency: ON

If gtid_mode is OFF, Supermetal uses file+position replication automatically.

Amazon RDS MySQL setup

Create a parameter group

  1. In the AWS RDS console, navigate to "Parameter groups"
  2. Click "Create parameter group"
  3. Select the MySQL engine and version that matches your RDS instance
  4. Enter a name and description (e.g., "supermetal-cdc-params")
  5. Click "Create"
  6. Select the newly created parameter group and modify the following parameters:
    • binlog_format: ROW
    • binlog_row_image: FULL
    • binlog_row_metadata: FULL
    • gtid-mode: ON (recommended, not required)
    • enforce_gtid_consistency: ON (only if gtid-mode is ON)

RDS limitations

In RDS, server-id is automatically assigned and cannot be modified.

Apply the parameter group to your RDS instance

  1. Navigate to your RDS instances and select your MySQL instance
  2. Click "Modify"
  3. Under "Additional configuration", select the parameter group you created
  4. Choose "Apply immediately" or schedule for your next maintenance window
  5. Click "Continue" and confirm the changes

Set the binary log retention period

Connect to your RDS MySQL instance and run:

CALL mysql.rds_set_configuration('binlog retention hours', 72);

RDS specific

Amazon RDS uses binlog retention hours instead of the standard binlog_expire_logs_seconds parameter. The default value is 0 (immediate removal), so this step is essential.

Create a user for replication

CREATE USER 'supermetal_user'@'%' IDENTIFIED BY 'strong-password';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'supermetal_user'@'%';
FLUSH PRIVILEGES;

Script variables

Replace supermetal_user and strong-password with your own values and store the password securely.

Azure Database for MySQL setup

Configure server parameters

  1. In the Azure portal, navigate to your MySQL server resource
  2. Select "Server parameters" under the Settings section
  3. Find and set the following parameters:
    • binlog_format: ROW
    • binlog_row_image: FULL
    • binlog_row_metadata: FULL
    • gtid_mode: ON (recommended, not required)
    • enforce_gtid_consistency: ON (only if gtid_mode is ON)
  4. Click "Save" to apply the changes
  5. Restart your Azure Database for MySQL server if prompted

Azure limitations

Some parameters might be read only in Azure Database for MySQL, particularly in Flexible Server configurations. The server-id is automatically assigned by Azure and cannot be modified.

Create a user for replication

Connect to your Azure MySQL instance and run:

CREATE USER 'supermetal_user'@'%' IDENTIFIED BY 'strong-password';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'supermetal_user'@'%';
FLUSH PRIVILEGES;

Script variables

Replace supermetal_user and strong-password with your own values and store the password securely.

Network security

Ensure your Azure MySQL server's firewall rules allow connections from the Supermetal agent.

Last updated on

On this page

PrerequisitesReplication modesSetup