Senior LevelSQL & MySQL
How do GTID (Global Transaction Identifiers) and Semi-Synchronous Replication eliminate replication lag data loss?
❓ Interview Prompt: Explain MySQL Replication, Async vs Semi-Sync Replication, GTIDs (`gtid_mode=ON`), `rpl_semi_sync_master_wait_point`, and failover safety.
Standard Asynchronous Replication can lose data if a master crashes before binary logs reach read replicas. Semi-Synchronous Replication eliminates data loss:
Semi-Synchronous Mechanics:
•
Master waits for at least one replica to acknowledge receiving and writing the transaction to its Relay Log before committing to the client ('AFTER_SYNC').
•
GTID (Global Transaction Identifier): Assigns a unique identifier ('UUID:TRANSACTION_ID') to every transaction globally, simplifying automated failover (Orchestrator).
Code Implementation Example
sql
-- Enabling GTID mode and Semi-Synchronous Replication
SET GLOBAL gtid_mode = 'ON';
SET GLOBAL enforce_gtid_consistency = 'ON';
SET GLOBAL rpl_semi_sync_master_enabled = 1;💡Configuring GTID mode and Semi-Synchronous Replication in MySQL.
🎯 Interview Key Takeaway
Deploy Semi-Synchronous Replication with GTID to prevent data loss during master node failovers.
#MySQL#Replication#GTID#Semi-Sync#Senior
Back to all SQL & MySQL Questions→