MySQL Restart – Troubleshooting Long DB Restart Times

I stumbled upon this question when I myself had encountered this error. I could finally figure out the configuration.

1. I didn't touch anything in /etc/mysql/my.cnf which already has bind_address = 127.0.0.1. So only localhost can connect.
2. I use OpenSSH server. So in its config file /etc/ssh/sshd_config I changed from no to yes the param responsible for TCP forwarding, thus AllowTcpForwarding yes.

3. Finally I have the following entered in MySQL WorkBench.

   • SSH Hostname: 192.168.0.8:22 (my SSH server listens to port 22)
   • SSH Username: sshuser
   • SSH Key File: *C:\Users\windowsuser\.ssh\id_rsa* (should be private key, even though it says public)
   • MySQL Hostname: 127.0.0.1 (this should not be changed, since MySQL server by default is bound to localhost only which I didn't change)
   • MySQL Server Port: 3306 (also default)
   • Username: root

The only remaining thing for you is to configure correctly your SSH server to work with keys rather than passwords. Hope this will help someone.

Rather than killing the process, it would be safer if you did it within MySQL:

$ mysqladmin processlist -u root -p
Enter password: 
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| Id  | User | Host      | db                | Command | Time | State | Info             |
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| 174 | root | localhost | example           | Sleep   | 297  |       |                  |
| 407 | root | localhost |                   | Query   | 0    |       | show processlist |
+-----+------+-----------+-------------------+---------+------+-------+------------------+

The query with id 174 is the one blocking deletion of the 'example' database, so before you kill any processes first let MySQL try to terminate the query:

$ mysqladmin kill 174

Run the processlist command above again to confirm that it was killed.

If this doesn't work, then you could perhaps look at killing the errant process, but before that you might try restarting the MySQL server.

You can also run commands like 'SHOW FULL PROCESSLIST' and 'KILL 174' in the MySQL shell, for example if you only have the MySQL client installed. The main point is to avoid killing the process using 'kill' in the shell unless absolutely necessary.

Generally speaking you can use either mysql or mysqladmin. You shouldn't need to be running commands like this that often though; once you start killing queries regularly something is definitely wrong and you'd be better off fixing that problem (killing the query process is just treating the symptom).