MySQL Semi-Synchronous Replication: Pros, Cons, and Setup Guide

MySQL 5.7 introduced ​semi-synchronous replication​ to enhance data durability while maintaining performance. This guide explains its core advantages, challenges, and practical setup steps using modern terminology.

Key Advantages

1. ​Improved Data Consistency​
   • Primary waits for ​at least one secondary​ to acknowledge receipt of binary logs before committing transactions.
   • ​AFTER_SYNC mode​ (default) ensures transactions are durable even if the primary crashes.
2. ​Performance Optimization​
   • Requires fewer acknowledgments than ​full synchronous replication, reducing latency.
   • Auto-fallback to asynchronous mode prevents primary blocking during secondary outages.
3. ​Flexible Failover​
   • Supports up to ​one secondary timeout​ before degrading, balancing safety and availability.

Major Drawbacks

• ​Increased Latency: Primary commits stall if secondaries are slow or networks lag.
• ​Risk of Data Loss: If all secondaries disconnect, primary reverts to asynchronous mode.
• ​Complex Configuration: Requires plugin installation and parameter tuning (e.g., rpl_semi_sync_primary_timeout).

Step-by-Step Configuration (Primary-Secondary Architecture)

1. Prerequisites

• Ensure primary-secondary replication is functional.
• Enable binary logging (log-bin) on the primary.
• MySQL version ≥ 5.7 with dynamic plugin support.

2. Install Plugins

​Primary:

INSTALL PLUGIN rpl_semi_sync_primary SONAME 'semisync_primary.so';
SET GLOBAL rpl_semi_sync_primary_enabled = 1;

‍

​Secondary:

INSTALL PLUGIN rpl_semi_sync_secondary SONAME 'semisync_secondary.so';
SET GLOBAL rpl_semi_sync_secondary_enabled = 1;

‍

3. Key Parameters (my.cnf)

​Primary:

[mysqld]
rpl_semi_sync_primary_enabled = 1
rpl_semi_sync_primary_timeout = 5000  # Timeout in milliseconds (default: 10000)
rpl_semi_sync_primary_wait_for_secondary_count = 1  # Wait for 1 secondary
rpl_semi_sync_primary_wait_point = AFTER_SYNC  # Commit after sync

‍

Secondary:

[mysqld]
rpl_semi_sync_secondary_enabled = 1

‍

4. Validate Status

​Primary:

SHOW STATUS LIKE 'Rpl_semi_sync_primary_status';  -- Should show 'ON'
SHOW STATUS LIKE 'Rpl_semi_sync_primary_clients';  -- Connected secondaries

‍

Secondary:

SHOW STATUS LIKE 'Rpl_semi_sync_secondary_status';  -- Should show 'ON'

‍

Best Practices

• ​Monitor Performance: Track Rpl_semi_sync_primary_no_times for timeouts.
• ​Adjust Timeouts: Increase rpl_semi_sync_primary_timeout in high-latency networks.
• ​Multi-Secondary Safety: Set wait_for_secondary_count ≥ 2 for critical workloads.

Conclusion

Semi-synchronous replication offers a practical compromise between durability and speed. Use it to enhance fault tolerance without sacrificing performance—especially in environments where occasional latency is preferable to data loss.

‍