Effect of Floating IP (SIP) Switch on MySQL Connections and the Importance of TCP Keepalive

We frequently use a floating IP (SIP or VIP) to provide high‑availability for databases, allowing applications to always connect to the primary server. The article investigates whether an ongoing database connection is affected when the primary‑secondary role switches and the SIP moves.

The experiment sets up two virtual machines (IP x.x.x.37 and x.x.x.39) each running MySQL, and a third VM simulating an application that connects to the SIP (x.x.x.200). After loading data with sysbench, a long‑running SELECT query with a sleep is executed to keep the connection active.

When the SIP is moved from 37 to 39 by removing it from 37 and adding it to 39 (with ARP announcements), the application’s query output pauses for a long time before finally terminating. Network traces show that the old connection on the client continues to send TCP keepalive packets that receive no response; after the configured number of keepalive attempts, the client finally detects the failure and resets the connection.

On the server side, the original MySQL instance (37) enters FIN‑WAIT‑1 shortly after the SIP move, indicating it has recognized the broken connection, but the client does not notice until the TCP keepalive timeout (default hours) expires. Reducing the TCP keepalive interval on the client makes the failure detection happen within seconds.

**Tip:** Configure TCP keepalive for database connections and set the interval to a value acceptable for your workload; the default interval is often several hours, which is too long for high‑availability scenarios. Adjust keepalive at the connection level rather than globally at the OS level.