Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 2 down vote accepted

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).

share|improve this answer
    
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 Oct 2 '12 at 19:32
    
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. –  Stephen Schrauger Oct 2 '12 at 19:33
    
@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 Oct 2 '12 at 19:36
1  
@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... –  derobert Oct 2 '12 at 19:39

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
share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.