Master-slave MySQL setups fail in predictable ways. Applications hang for 30-60 seconds during failover whilst your monitoring shows everything normal. The problem isn't your alerting thresholds - it's the detection methods themselves.
The 30-Second MySQL Failover Blind Spot
Most MySQL replication monitoring relies on SHOW SLAVE STATUS output. This command reports lag based on averaging windows that smooth out temporary spikes. During a master failover, your slave continues reporting "normal" replication status for 15-30 seconds after the master connection has actually failed at the socket level.
Applications trying to write to the master during this window receive connection timeouts or hang indefinitely. By the time SHOW SLAVE STATUS reflects the problem, you've already lost user sessions and potentially corrupted transactions.
Traditional Monitoring: SHOW SLAVE STATUS Limitations
The standard approach queries SHOW SLAVE STATUS every 30-60 seconds and alerts on SecondsBehindMaster values. This works for steady-state lag monitoring but fails during failover scenarios.
Why Binary Log Position Reporting Lags Behind Reality
Binary log position monitoring (ExecMasterLog_Pos) suffers from similar averaging effects. The slave's SQL thread can continue processing queued events from memory even after the I/O thread has lost connection to the master. This creates a false sense of replication health during the critical failover window.
The MySQL documentation mentions this behaviour but doesn't provide alternatives for real-time detection.
Socket-Level Detection Through /proc/net/tcp Analysis
The /proc/net/tcp file reveals active socket connections with their current states. MySQL replication maintains dedicated connections visible in this file - one for the I/O thread and one for the SQL thread.
Reading MySQL Connection States During Failover
During normal operation, MySQL replication connections appear as ESTABLISHED in /proc/net/tcp. When a master fails, these connections immediately transition to CLOSE_WAIT or disappear entirely - well before SHOW SLAVE STATUS updates.
# Find MySQL replication socket states
grep :3306 /proc/net/tcp | awk '{print $4}' | sort | uniq -cIdentifying Replication Thread Behavior Patterns
The I/O thread socket typically fails first, followed by the SQL thread socket. This pattern provides a clear signal that replication has broken, regardless of what the MySQL status commands report.
Comparative Analysis: Four Detection Methods Tested
We tested these four approaches during controlled master failovers across different MySQL versions and configurations:
Method 1: SHOW SLAVE STATUS (Standard Approach)
- Detection time: 25-35 seconds after actual failure
- Reliability: High for steady-state, poor during transitions
- Resource overhead: Medium (requires MySQL connection and query execution)
- False positives: Low
Method 2: Binary Log Position Monitoring
- Detection time: 20-30 seconds after actual failure
- Reliability: Slightly better than SHOW SLAVE STATUS
- Resource overhead: Medium (requires parsing log positions)
- False positives: Medium (position can advance from memory cache)
Method 3: Application-Level Health Checks
- Detection time: 10-15 seconds (depends on check frequency)
- Reliability: Good for detecting application impact
- Resource overhead: High (additional test connections)
- False positives: High (application timeouts have many causes)
Method 4: Socket State Analysis
- Detection time: 2-5 seconds after actual failure
- Reliability: Excellent for connection-level failures
- Resource overhead: Minimal (reading /proc files)
- False positives: Very low (socket states are definitive)
Implementation: Building Early Warning Detection
Socket-based monitoring requires parsing /proc/net/tcp and correlating connection states with known MySQL processes.
Parsing /proc/net/tcp for MySQL Connections
Identify MySQL connections by matching local and remote ports, then track state changes over time. The connection state field uses hexadecimal values: 01 for ESTABLISHED, 08 for CLOSE_WAIT.
Correlating Socket States with Replication Events
Compare socket state changes with MySQL process IDs to distinguish replication connections from regular client connections. This prevents false alerts from normal client disconnections.
Server Scout's agent combines socket analysis with traditional MySQL metrics to provide both immediate failover detection and long-term replication health trends. The bash-based approach adds minimal overhead whilst delivering faster alerts than database-query methods.
Socket-level monitoring catches the problems that matter most - actual connection failures that break applications. Traditional MySQL monitoring tools focus on database-internal metrics that lag behind network reality. For production environments where 30 seconds of downtime costs real money, the difference matters.
This approach works alongside existing MySQL monitoring rather than replacing it. Use socket analysis for immediate failover detection and SHOW SLAVE STATUS for understanding longer-term replication health patterns. The combination provides both fast alerts and detailed diagnostics.
FAQ
Does socket monitoring work with MySQL Galera clusters?
Partially. Galera uses different connection patterns than traditional master-slave replication, but socket analysis still catches node communication failures faster than cluster status commands.
Will this approach detect read replica lag in RDS or other managed MySQL services?
No. Managed database services don't expose host-level /proc filesystem access. This technique only works on servers where you control the operating system.
How often should socket state checking run to catch brief connection interruptions?
Every 5-10 seconds provides good detection speed without excessive overhead. More frequent polling (every 1-2 seconds) catches transient failures but increases CPU usage on busy systems.