When your mysql server crashes and won't restart

This just happened to me: mysql went down for no apparent reason and I thought it was because I was missing with the .ini file. No matter how I tried, it wouldn't restart.

I checked the error logs and found:

InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11

This was an indicator that a mysql process was already running it, but I couldn't find it on top, or using

ps aux | grep mysql 

So I tried this:

# lsof -i:3306

COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  24416 mysql   10u  IPv4 173905      0t0  TCP localhost:mysql (LISTEN)

# sudo kill 24416
# sudo /etc/init.d/mysql start

That's all it took.

In this case.

How to build a reverse ssh mysql tunnel

Suppose you need to connect to a mysql database on a server (the "DB" server) to which you have been firewalled out of. Not a problem if you can connect to a server (the "TUNNEL" server) that has been let through the firewall. No problem at all. It's a little bit of a problem. But you're stalwart.


Here's what you do:

1. ssh into the DB server:
   

   ssh YOU@DBSERVER.com
   

2. use the screen utility to allow you to detach your terminal, and still keep the tunnel open:
   

   screen
   

3. Open the tunnel to the TUNNEL server. Let's say you want to connect to the db server on port 3306. Pick an arbitrary port for your TUNNEL server (3316, for instance):
   

   ssh -R 3316:localhost:3306 YOU@TUNNELSERVER.com
   

4. You are now on the TUNNEL server. You want to keep the connection open, at that point, and can do a cheesy keepalive:
   

   while true ; do echo keepalive ; sleep 60 ; done
   

5. Now detach by pressing ctrl-A, then d. This returns you to the DB server but the tunnel to the TUNNEL server remains open.

That's it! Your reverse tunnel is open. If you want to connect to the DB server directly from the TUNNEL server, then your command is:

mysql -h 127.0.0.1 -u USERNAME -p -P 3316

If you are on another server, you will have to tunnel into the TUNNEL server first:

ssh -L 3308:127.0.0.1:3316 YOU@TUNNELSERVER.com

After that, you can connect with the command:

mysql -h 127.0.0.1 -u USERNAME -p -P 3308