Binlog Archiving

Enforcing GTID Consistency in Multi-Primary Clusters

The moment more than one node in a Group Replication cluster accepts writes, the comfortable assumption behind file-and-position archiving — that a single server owns a single, totally ordered stream of transactions — stops being true. Each write-accepting member mints its own Global Transaction Identifiers (GTIDs), and the recoverable history for point-in-time recovery (PITR) becomes the union of every member’s gtid_executed, not any one node’s binary log. Get the enforcement configuration wrong and the group does not fail loudly at write time; it fails silently at recovery time, when you discover the archive covers member A’s transactions and member B’s transactions but not the interleaving that actually committed. This page resolves that exact gap: how to make enforce_gtid_consistency=ON and the multi-primary safety checks reject anything that would fracture continuity, and how to merge and verify a gap-free GTID set across concurrent write nodes before it reaches your archive.

Visual Overview

Multi-primary GTID consistency and archiving pipelineThree primaries (A green, B blue, C amber) each feed their gtid_executed set into a single union built with GTID_SUBTRACT. The union hits an enforcement gate: with the gates off, unsafe DDL/DML is rejected at commit (red); with the gates on, the union is diffed against the archive manifest and one contiguous recoverable set is archived (green).Primary Agtid_executedPrimary Bgtid_executedPrimary Cgtid_executedUnion setGTID_SUBTRACTenforce_gtid_consistency +update-everywhere checksenforcementgates ON?YesNoDiff union vsarchive manifestArchive onecontiguousrecoverable setReject unsafeDDL/DML at commit

Context & Prerequisites

This scenario is the multi-primary specialization of the pipeline built in GTID Tracking & Enforcement: the same extract-normalize-diff-verify gates apply, but the source of truth is now several nodes instead of one. It presumes the event and lifecycle model from MySQL Binary Log Architecture & GTID Fundamentals, and it only holds under row-based logging — a statement-format event carrying NOW() or UUID() will certify on one member and replay differently on another, so the format trade-offs in ROW vs STATEMENT vs MIXED formats are a hard dependency here, not a preference. Minimum environment: MySQL 8.0.22+ (for SHOW BINARY LOG STATUS and stable Group Replication semantics), gtid_mode=ON, enforce_gtid_consistency=ON, and binlog_format=ROW on every member. Because multiple members write concurrently, the retention math is stricter too — the oldest un-archived transaction on any node bounds the whole cluster’s safe purge, which is why binlog retention boundaries must be computed against the union set, not per node.

Step-by-Step Implementation

Each step is annotated with its PITR relevance — why the setting either preserves or destroys the ability to replay every member to an arbitrary point.

1. Turn on multi-primary enforcement on every member

enforce_gtid_consistency=ON alone is necessary but not sufficient in multi-primary mode. You also need the group to reject operations that are safe on a single writer but unsafe when several members write the same rows concurrently:

-- MySQL 8.0.22+ : run on EVERY member, identically
SET PERSIST gtid_mode = ON;
SET PERSIST enforce_gtid_consistency = ON;
SET PERSIST binlog_format = ROW;
SET PERSIST group_replication_single_primary_mode = OFF;
SET PERSIST group_replication_enforce_update_everywhere_checks = ON;

PITR relevance: group_replication_enforce_update_everywhere_checks=ON blocks cascading foreign keys, SERIALIZABLE isolation, and other constructs whose replay order is not deterministic across writers. Every member must carry an identical configuration; a single node with binlog_format=MIXED or the checks disabled becomes a source of events your archive cannot replay in the order they certified.

2. Cap transaction size so the certifier cannot stall the archive

Large transactions inflate the certification write set and can back up the applier queue on remote members, delaying when their GTIDs become archivable:

-- MySQL 8.0+ : default is 150000000 (≈143 MiB); tune to your write profile
SET PERSIST group_replication_transaction_size_limit = 150000000;

PITR relevance: a stalled applier queue widens the window in which a committed transaction exists on the originating member but has not yet propagated and been archived from the others. Bounding transaction size keeps the union frontier advancing evenly, so the archiver never has to wait on one oversized payload.

3. Read each member’s executed set from the group

gtid_executed on a healthy member already reflects certified transactions from the whole group, but during recovery or a partition individual members diverge. Query every member and treat the collection as the source:

-- MySQL 8.0.22+ : per-member frontier
SELECT @@GLOBAL.gtid_executed AS executed, @@GLOBAL.gtid_purged AS purged;

-- MySQL 8.0+ : certification and applier health per member
SELECT
    MEMBER_ID,
    COUNT_TRANSACTIONS_CHECKED,
    COUNT_CONFLICTS_DETECTED,
    COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE,
    COUNT_TRANSACTIONS_REMOTE_APPLIED
FROM performance_schema.replication_group_member_stats;

PITR relevance: COUNT_CONFLICTS_DETECTED rising means the certifier is rolling transactions back — those never receive a durable GTID and must never appear as gaps in your manifest. A non-zero COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE marks GTIDs that are committed somewhere but not yet locally durable, so archiving that member now would capture a set that is still moving.

4. Merge the members into one recoverable set

The archive’s recoverable window is the union of every member’s executed set, minus anything already purged everywhere. Delegate the interval arithmetic to MySQL, which handles multi-interval UUIDs (uuid:1-5:8-9) correctly:

-- MySQL 8.0+ : union of two members, then subtract the purged frontier
SELECT GTID_SUBTRACT(
         GTID_SUBTRACT('<union_of_all_executed>', ''),
         '<gtid_purged_common_to_all>'
       ) AS recoverable_set;
Merging three members' GTID intervals into one recoverable set and diffing the archive gapMember A (1-6, green), B (4-9, blue) and C (8-12, amber) overlap and collapse into a contiguous union of 1-12. The 1-3 prefix is purged and subtracted; 4-9 is already archived; 10-12 is the missing range GTID_SUBTRACT(recoverable, archived) flags for the next extraction.123456789101112Member AMember BMember C1–64–98–12Unionpurged 1–3subtracted outarchived 4–9already in manifestgap 10–12extract nextGTID_SUBTRACT(recoverable, archived) = 10–12overlapping member intervals collapse into one contiguous recoverable set

For the offline case — reconciling a captured set against an archive manifest when the live group is gone — fold the members client-side, then re-validate against the server before any apply:

# Python 3.10+
from dataclasses import dataclass


@dataclass(frozen=True, slots=True)
class GTIDInterval:
    """One contiguous interval of transactions from a single source UUID."""
    source_uuid: str
    start: int
    end: int

    def merges_with(self, other: "GTIDInterval") -> bool:
        return (
            self.source_uuid == other.source_uuid
            and other.start <= self.end + 1
            and other.end >= self.start - 1
        )


def union_member_sets(members: dict[str, list[GTIDInterval]]) -> list[GTIDInterval]:
    """Collapse every member's intervals into one merged, sorted recoverable set."""
    flat = [iv for intervals in members.values() for iv in intervals]
    flat.sort(key=lambda iv: (iv.source_uuid, iv.start))
    merged: list[GTIDInterval] = []
    for iv in flat:
        if merged and (last := merged[-1]).merges_with(iv):
            merged[-1] = GTIDInterval(last.source_uuid, last.start, max(last.end, iv.end))
        else:
            merged.append(iv)
    return merged

PITR relevance: the union is the only set that describes what actually committed cluster-wide. Archiving per member and hoping the pieces line up later is exactly how an interleaved gap survives undetected into a recovery.

5. Diff the union against the archive and bound the extraction

Compute what the archive is missing, then extract strictly within the verified window so no partially propagated event is captured:

-- MySQL 8.0+ : transactions the archive still owes, cluster-wide
SELECT GTID_SUBTRACT('<recoverable_set>', '<archived_set>') AS missing_ranges;
# Bound mysqlbinlog to the certified GTID window; never capture beyond it.
mysqlbinlog --read-from-remote-server --host=member-a.internal \
  --include-gtids='<recoverable_set>' \
  --verify-binlog-checksum --raw --result-file=/archive/ mysql-bin.000123

PITR relevance: a non-empty missing_ranges is the authoritative gap list. Passing --include-gtids with the merged set — and refusing to archive anything outside it — guarantees the archiver never trusts a transaction that has not yet certified across the group.

Configuration Reference

A minimal, copy-pasteable multi-primary block for every member’s my.cnf. It must be byte-identical across every member:

[mysqld]
server_id                                          = 101   # unique per member
log_bin                                            = mysql-bin
binlog_format                                      = ROW
gtid_mode                                          = ON
enforce_gtid_consistency                           = ON
sync_binlog                                        = 1
plugin_load_add                                    = group_replication.so
group_replication_single_primary_mode             = OFF
group_replication_enforce_update_everywhere_checks = ON
group_replication_transaction_size_limit           = 150000000
# group_replication_consistency = BEFORE_ON_PRIMARY_FAILOVER  # tune read/write guarantees
VariableTypeDefaultRecommendedPITR impact
enforce_gtid_consistencyenumOFFONRejects statements that cannot get one atomic GTID, preventing non-deterministic replay across writers
group_replication_single_primary_modeboolONOFF (multi-primary)Enables concurrent writers; recoverable history becomes the union of all members
group_replication_enforce_update_everywhere_checksboolOFFONBlocks constructs (cascading FKs, SERIALIZABLE) whose cross-writer replay order is undefined
group_replication_transaction_size_limitint150000000tuned to write profileCaps write-set size so the certifier and applier queues do not stall the archive frontier
binlog_formatenumROWROWDeterministic, byte-reproducible replay required for multi-writer PITR
binlog_transaction_dependency_trackingenumWRITESETWRITESETParallel apply by row hash; removed in MySQL 8.4 where WRITESET is the built-in default

Verification Checklist

Gotchas & Version-Specific Caveats

Enforcement errors are the system working, not breaking. ERROR 1786 (ER_GTID_UNSAFE_CREATE_SELECT) on CREATE TABLE ... SELECT, ERROR 1787 on a CREATE/DROP TEMPORARY TABLE inside a transaction, and ERROR 1785 (ER_GTID_UNSAFE_NON_TRANSACTIONAL_TABLE) on mixed transactional/non-transactional writes are all enforce_gtid_consistency refusing to mint a GTID it cannot replay deterministically. The fix is always to rewrite the statement (split CREATE ... SELECT into CREATE then INSERT ... SELECT; move temp-table DDL to autocommit; isolate MyISAM writes) — never to relax enforcement to make the error disappear. The full classifier and remediation table lives in the GTID Tracking & Enforcement pipeline.

binlog_transaction_dependency_tracking is gone in MySQL 8.4. It was deprecated in 8.0.35/8.2.0 and removed in 8.4.0, where WRITESET behavior is the built-in default. A multi-primary config carried forward from 8.0 that still sets it will fail to start on 8.4 — delete the line on upgrade rather than porting it.

BINLOG_GTID_POS does not exist in Oracle MySQL. It is a MariaDB function; on MySQL, map a byte offset to its GTID by inspecting the stream with mysqlbinlog --start-position and reading the Gtid_log_event entries, or trust gtid_executed directly. Automation copied from MariaDB examples silently breaks here.

SHOW MASTER STATUS and RESET MASTER were renamed. As of MySQL 8.2.0 use SHOW BINARY LOG STATUS; on 8.4 RESET MASTER becomes RESET BINARY LOGS AND GTIDS. Multi-node automation that shells the old names breaks unevenly across members mid-upgrade — pin statements to the server version.

A gtid_purged mismatch on a re-provisioned member is a trap. When you rebuild a member from backup, re-derive gtid_purged on it from the backup’s captured coordinates before it rejoins, or the group’s distributed recovery will either reject the first transaction or skip it as already-applied. If enforcement hard-stops a member and no clean rejoin exists, degrade deterministically per fallback routing strategies rather than forcing an unsafe write. See the mysqlbinlog GTID options reference for exact --include-gtids/--exclude-gtids behavior.

Frequently Asked Questions

Whose gtid_executed do I archive from in a multi-primary cluster?

None of them in isolation — you archive the union. In a healthy Group Replication cluster every member’s gtid_executed converges on the same set, but during a partition, a rebuild, or an applier backlog they diverge. Read gtid_executed from every member, fold them with GTID_SUBTRACT/set arithmetic, and treat that merged set as the recoverable history. Archiving from a single node and assuming it speaks for the group is how an interleaved gap survives into recovery.

Do I need enforce_gtid_consistency if Group Replication already certifies transactions?

Yes. Certification detects write conflicts between members; it does not guarantee each transaction can be assigned one deterministic GTID. enforce_gtid_consistency=ON is the separate gate that rejects CREATE TABLE ... SELECT, temp-table DDL in transactions, and mixed-engine writes — statements that would replay differently regardless of whether they conflicted. The two mechanisms cover different failure modes and are both required.

Why does group_replication_enforce_update_everywhere_checks matter for archiving?

Because it forbids operations whose replay order is undefined across concurrent writers — cascading foreign keys, SERIALIZABLE isolation, and similar constructs. With multiple members writing, a transaction that is safe on a single primary can produce a different result depending on apply order. Enabling the checks keeps every archived transaction replayable in the order it certified; leaving them off lets non-deterministic events into a log you later trust for PITR.

A member shows COUNT_CONFLICTS_DETECTED climbing — is my archive missing transactions?

No, and that is the point. A conflicting transaction is rolled back and never receives a durable GTID, so it must be absent from your manifest, not recorded as a gap. Rising conflicts signal application-level write contention across primaries, not archive corruption. Investigate the workload (hot rows written on multiple members), but do not chase the rolled-back transactions as missing — they were never committed cluster-wide.

Back to GTID Tracking & Enforcement.