MySQL Linux Client Timeout/Keepalive

Question

Is there a way to set a keepalive in the command-line MySQL client on Linux?

Our network recently moved to a VLAN setup, and our systems department no longer has control of the firewall. The powers-that-be decided to set a rule in their firewall to kill all connections after 30 minutes if no data has passed through (something about having to keep track of connections and limited resources). I have no control over this.

As a server admin and programmer, my problem is that this means I can not have a mysql command-line client running in the background while I do programming. If I don't send a query (or otherwise send data), when I try 35 minutes later, the client detects it no longer has a connection. I must reconnect every time.

In SSH, I am able to set a keepalive so that I can keep connections open throughout the day. Is there a similar configurable option for the mysql command-line client? I am having trouble finding anything, because "Mysql Keepalive" and similar searches all return results for a backend connection within a programming language.

Solution 1

You can set generic TCP keepalives, I think there is a kernel setting for that. But they're usually much less frequent (hours). There is a TCP Keepalive HOWTO which appears to have details.

Alternatively, why not just tunnel the MySQL connection over SSH, then you can use SSH keepalives?

$ ssh -L1234:127.0.0.1:3306 -o ServerAliveInterval=300 user@mysql-server
... (in another terminal)
$ mysql -u user -p -h 127.0.0.1 -P 1234

Third option would be to set up a tunnel (e.g., OpenVPN) to your server, and then that'll handle keepalive (and transparently reconnecting, etc.).

PS: The "our precious resources" argument for expiring established TCP connections in 30 minutes is BS. Unfortunately—I've been in their shoes before—some equipment is BS too. For example, BCP 142/RFC 5382 gives a minimum time of 2 hours 4 minutes (and that's only if the firewall isn't able to determine if the host is still up).

Solution 2

MySQL CLI client defaults with auto-connect, if a connection is lost during a session. Documentation: MySQL Tips & Controlling Automatic Reconnection Behavior.

The real solutions would be to work with the powers-that-be, and figure out a solution that works for development team-members.

Naturally that would take some time, so a temporary solution might need to be crafted. I'd recommend disabling MySQL's auto-connect (--skip-reconnect), and creating a simple bash-script to handle reconnection. Not a good idea, but might help.

#!/bin/bash

PROGRAM="/path/to/mysql"
ARGS="--skip-reconnect"

while [ 1 ]; do
 $PROGRAM $ARGS
 sleep 1
 echo -n "Lost Connection... Reconnect[Y]"
 read USER_INPUT
 case x$USER_INPUT in
  x)
   echo "Reconnecting..."
   ;;
  x[yY])
   echo "Reconnecting..."
   ;;
  *)
   echo "Stopping..."
   break
   ;;
 esac
done

Solution 3

You can use libkeepalive to preload a libkeepalive.so that will intercept socket() calls and automatically call setsockopt() with your preferred keepalive settings on the socket before returning it.

This does not require you to recompile the application (in this case MySQL), it's completely transparent.

Also see the TCP Keepalive HOWTO.

Comments

[Stephen Schrauger]

I had thought of using SSH tunneling as well, and I was planning on using that. It requires that I have a user on the server (I do, but isn't always the case). And I think it limits you to one sql connection (unless you forward multiple ports and specify them in the mysql command).

[Stephen Schrauger]

I really like the idea of changing the TCP keepalive in my kernel. Do you know where I can set that? This would solve the keepalive problem for any other service I use as well.

[derobert]

@dymutaos no, you can forward multiple connections over the same ssh connection. Just run another mysql client, connecting to the same port. And you don't need an account on the MySQL server, just an account outside your firewall. I'll have to look up setting TCP keepalive.

[derobert]

@dymutaos added link to a howto which explains how to set up keepalives. Its a little old, so the names of the /proc files may have changed a little...