Using mysqlreplicate to Set Up MySQL Replication: Introduction, Command Usage, and Source‑Code Analysis

1. Introduction to mysqlreplicate

The utility allows an administrator to configure and start replication from a master MySQL server to a slave server, supplying login information for the slave and connection details for the master, and optionally specifying a test database.

It reports mismatches in storage‑engine sets, default storage engine, and InnoDB type/version, and can treat these mismatches as warnings or errors using the --pedantic option. The -vv flag shows detailed discrepancies.

2. Usage

Most common command:

mysqlreplicate \
  --master=root:[email protected]:3306 \
  --slave=root:[email protected]:3306 \
  --rpl-user=rpl:rpl \
  -vv \
  --pedantic

Use # comments to indicate password usage warnings.

Specify a binary‑log position:

mysqlreplicate \
  --master=root@localhost:3306 \
  --slave=root@localhost:3307 \
  --rpl-user=rpl:rpl \
  --master-log-file=my_log.000001 \
  --master-log-pos=96

This starts replication from a specific log file and position.

3. Source‑Code Analysis

Step 1 – Check uniqueness of important parameters

Server‑id uniqueness : both master and slave must have server_id enabled and the values must differ. The check is performed by executing SHOW VARIABLES LIKE 'server_id' on each server.

UUID uniqueness : MySQL version must be ≥ 5.6.5, GTID_MODE must be ON, and server_uuid must be set on both servers and be different. The check runs SELECT @@GLOBAL.GTID_MODE and SHOW VARIABLES LIKE 'server_uuid'.

# Python snippet illustrating the checks
rpl = Replication(master, slave, rpl_options)
errors = rpl.check_server_ids()
for error in errors:
    print(error)
# ... further checks for UUIDs ...

Step 2 – Check InnoDB compatibility

Both servers must use the same InnoDB type (built‑in or plugin) and have identical plugin_version, plugin_type_version, and have_innodb values. The utility queries INFORMATION_SCHEMA.ENGINES and INFORMATION_SCHEMA.PLUGINS to obtain these details.

# Python snippet for InnoDB check
if master_innodb_stats != slave_innodb_stats:
    if not pedantic:
        errors.append("WARNING: Innodb settings differ between master and slave.")
    if pedantic:
        raise UtilRplError("Innodb settings differ between master and slave.")

Step 3 – Check storage‑engine consistency

The list of supported storage engines on the master must match that on the slave. The check runs

SELECT UPPER(engine), UPPER(support) FROM INFORMATION_SCHEMA.ENGINES ORDER BY engine

on both servers.

# Python snippet for storage‑engine check
errors = rpl.check_storage_engines(options)
for error in errors:
    print(error)

Step 4 – Check master binary logging

The master must have binary logging enabled ( log_bin = ON). The utility executes SHOW VARIABLES LIKE 'log_bin' and raises an error if it is OFF.

# Python snippet for binary‑log check
errors = rpl.check_master_binlog()
if errors:
    raise UtilError(errors[0])

Step 5 – Create and start replication

The process creates a replication user (if it does not exist), grants the REPLICATION SLAVE privilege, and configures the slave with CHANGE MASTER TO …. It then stops any running slave threads, issues START SLAVE, and monitors synchronization status, retrying the SQL thread if necessary.

# Python snippet for replication setup
if not rpl.setup(rpl_user, 10):
    raise UtilError("Cannot setup replication.")
# Start slave and monitor
res = slave.start()
while not synced and attempts < max_attempts:
    time.sleep(1)
    # check SHOW SLAVE STATUS ...

Finally, the article suggests testing the replication by creating a test database on the master and verifying its existence on the slave.

4. Step‑by‑Step Summary

1) Verify server_id uniqueness. 2) Verify server_uuid uniqueness and GTID mode. 3) Ensure InnoDB engine and version match. 4) Ensure storage‑engine sets match. 5) Ensure binary logging is enabled. 6) Create replication user and grant privileges. 7) Execute CHANGE MASTER TO … and START SLAVE. 8) Monitor and test replication.