MySQL Replication

MySQL replicates by shipping binary log events from a source to replicas, which apply them through a relay log.

Source configuration

server_id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

CREATE USER 'repl'@'10.20.1.%' IDENTIFIED BY 'password' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.20.1.%';

Replica setup

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'primary.internal',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'password',
  SOURCE_SSL = 1,
  SOURCE_AUTO_POSITION = 1;      -- GTID-based; no file and position needed
 
START REPLICA;
SHOW REPLICA STATUS\G

Take the initial copy with a tool that records the GTID set — mysqldump --source-data=2 --single-transaction, or a physical copy from Percona XtraBackup or MySQL Enterprise Backup, which is far faster for large datasets.

GTID

A GTID is source_uuid:transaction_id, assigned when a transaction commits and preserved wherever it is applied. Its value is operational:

• A replica repointed at a different source works out what it is missing from its own gtid_executed set. Without GTIDs you must supply an exact binlog file and byte offset from the new source — error-prone during a failover.
• Errant transactions become visible: a transaction executed directly on a replica has a GTID that the source does not have, and that difference is detectable rather than silent.

SELECT @@global.gtid_executed;    -- what this server has applied
SELECT @@global.gtid_purged;      -- what it no longer has binlogs for

Semi-synchronous replication

With the semi-sync plugin, a commit waits until at least one replica has acknowledged receipt of the event before returning to the client.

rpl_semi_sync_source_enabled = 1
rpl_semi_sync_source_timeout = 1000        # ms before falling back to async
rpl_semi_sync_source_wait_for_replica_count = 1

It narrows the loss window; it is not synchronous replication and not consensus. On timeout the source silently reverts to asynchronous, so monitor Rpl_semi_sync_source_status — a value of 0 means you no longer have the guarantee you configured.

Parallel apply

A single-threaded applier is the usual cause of replica lag on a write-heavy source.

replica_parallel_workers = 8
replica_parallel_type = LOGICAL_CLOCK
binlog_transaction_dependency_tracking = WRITESET   # more parallelism than COMMIT_ORDER
replica_preserve_commit_order = ON                  # keeps replica commit order identical

WRITESET dependency tracking lets the replica parallelise transactions that touched disjoint rows, which typically improves apply throughput substantially over COMMIT_ORDER.

Monitoring

SHOW REPLICA STATUS\G

Read these fields together:

• Replica_IO_Running / Replica_SQL_Running — both must be Yes.
• Seconds_Behind_Source — misleading on its own: it is zero when the IO thread has stalled, because the applier has nothing queued. Compare GTID sets or use performance_schema.replication_applier_status_by_worker for a real measure.
• Last_Error — why it stopped.

SELECT * FROM performance_schema.replication_connection_status\G
SELECT * FROM performance_schema.replication_applier_status_by_worker\G