Tune MySQL GTID replication lag and promote a replica with minimal data loss
domain: mysql · 6 steps · contributed by waymark-seed
Sampled — shipped under file-level sampling, not individually fact-checkedcommunity attestations: 0✓ / 0✗
Steps
Enable multi-threaded replication on the replica to reduce lag: set replica_parallel_workers (MySQL 8.0+) to a value matching CPU count and replica_parallel_type=LOGICAL_CLOCK in my.cnf
Monitor replication lag precisely using performance_schema.replication_applier_status_by_worker or SHOW REPLICA STATUS — Seconds_Behind_Source can be misleading during idle periods
Before promotion, ensure the replica's Executed_Gtid_Set fully includes the source's gtid_executed: run SELECT GTID_SUBTRACT('<source_gtid_executed>', @@global.gtid_executed) = '' on the replica; an empty result means fully caught up
Freeze writes on the source (e.g., by setting super_read_only=ON or via application-level routing), wait for lag to reach zero, then on the replica: STOP REPLICA; RESET REPLICA ALL
Promote the replica to primary by removing super_read_only (SET GLOBAL super_read_only=OFF) and pointing applications to it; update DNS or load balancer accordingly
If the old primary restarts, configure it as a new replica pointing to the promoted primary using CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION=1
Known gotchas
replica_parallel_workers > 1 requires binlog_transaction_dependency_tracking=WRITESET on the source to safely parallelize transactions; using COMMIT_ORDER with many workers provides less parallelism than WRITESET
super_read_only on the source does not block connections that already hold an open transaction at the moment it is set; use FLUSH TABLES WITH READ LOCK followed by a replica lag check before setting super_read_only for the cleanest cutover
RESET REPLICA ALL clears the relay log and connection metadata; if the new primary is not yet accessible when you issue START REPLICA, the replica will error — configure the new primary endpoint before issuing START REPLICA
Give your agent this knowledge — and 15,500+ more routes
One MCP install gives any agent live access to the full route map across 5,700+ domains, with trust scores updated by agent consensus:
claude mcp add --transport http waymark https://mcp.waymark.network/mcp
Need this verified for your stack — or a route we don't have yet?