MySQL Group Replication: Single-Primary vs Multi-Primary

This article explains the core differences between single-primary and multi-primary modes in MySQL Group Replication. It covers how each mode manages write operations, handles transaction conflicts, impacts application design, and behaves during node failures, allowing you to choose the best configuration for your database infrastructure.

What is Single-Primary Mode?

In single-primary mode, MySQL Group Replication designates only one member of the group as the primary node. This primary node is the only member that configures itself to accept write operations (read-write mode). All other members in the group are automatically configured as read-only.

If the primary node fails, the remaining members automatically elect a new primary. This election is based on factors such as the MySQL server version of the members and their UUIDs. Single-primary mode is the default configuration for MySQL Group Replication because it behaves similarly to traditional master-slave replication, making it highly compatible with existing applications.

What is Multi-Primary Mode?

In multi-primary mode, every member of the group can accept write operations simultaneously. There is no single primary node; instead, all nodes operate in read-write mode.

When a transaction is executed on one node, it must be agreed upon by the entire group before it commits. Because writes can occur on different nodes at the same time, MySQL Group Replication uses an optimistic replication model. Transactions are checked for conflicts against other concurrent transactions across the group, and any conflicting transactions are rolled back.

Key Differences

1. Write Allocation and Load Balancing

• Single-Primary: All writes must be routed to the single primary node. The secondary nodes can only handle read queries. This simplifies application routing but limits write scalability to a single machine.
• Multi-Primary: Write queries can be distributed across all nodes in the group. This allows applications to write to the closest or least busy node.

2. Conflict Detection and Resolution

• Single-Primary: Because only one node accepts writes, write conflicts between nodes are impossible. Transactions are executed sequentially on the primary, preventing concurrent modification conflicts.
• Multi-Primary: Concurrent writes can lead to conflicts if two nodes attempt to modify the same row at the same time. MySQL Group Replication resolves this using a “first-committer-wins” rule. The transaction that reaches consensus first is committed, while the conflicting transaction on the other node is aborted and rolled back.

3. Feature Limitations and Data Safety

• Single-Primary: Supports all standard MySQL features, including auto-increment locks, foreign keys with cascading actions, and Data Definition Language (DDL) statements without special restrictions.
• Multi-Primary: Has strict limitations to prevent data inconsistency.
  • Table locks and named locks are not supported.
  • Foreign keys with cascading deletes are not recommended, as they can cause undetected conflicts.
  • Concurrent DDL operations on different nodes are not supported and can lead to schema inconsistencies.

4. Application Complexity

• Single-Primary: Easy to integrate. The application only needs to distinguish between one read-write connection and multiple read-only connections.
• Multi-Primary: Requires careful application design. Developers must write application logic to handle transaction rollbacks caused by replication conflicts, and load balancers must be configured to distribute writes safely across nodes.

Summary of Differences