Blog

Tip: how to save 10 minutes a day?

For any person actively working with MySQL databases on the command line level, logging in, logging out for a few moments, and then logging in again, all repeated many times, not only eventually becomes annoying, especially with passwords that aren't easy to type, but it also can take a lot of time over the course of a day. For a long time I've been relaying on something that allows me to avoid most of this effort while working. The solution is of course not to log out unless you actually want to.

Linux, BSD and Solaris all allow suspending a running task and resuming its execution at a later time. This can be used to temporarily exit MySQL client without having it to leave the database.

When inside MySQL, you can press Ctrl+Z to suspend the client program and return to the system shell. You will be able to bring the database client back at any time by running fg command (fg as in foreground).

garfield ~ # mysql -p test
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 93595
Server version: 5.5.21-log
..
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> SELECT 'In MySQL';
+----------+
| In MySQL |
+----------+
| In MySQL |
+----------+
1 row in set (0.00 sec)

mysql> (press Ctrl+Z)
[1]+  Stopped                 mysql -p test
garfield ~ # echo 'Back to shell'
Back to shell
garfield ~ # fg
mysql -p test

mysql> SELECT 'In MySQL again';
+----------------+
| In MySQL again |
+----------------+
| In MySQL again |
+----------------+
1 row in set (0.00 sec)

Of course, MySQL may choose to drop an idle connection, but in such case the client shall reconnect automatically during the next query:

garfield ~ # fg
mysql -p test

mysql> SELECT 'Have I been disconnected?';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    93818
Current database: *** NONE ***

+---------------------------+
| Have I been disconnected? |
+---------------------------+
| Have I been disconnected? |
+---------------------------+
1 row in set (0.11 sec)

The only restriction for using this method is that a suspended task can only be brought back inside the same terminal in which it was started. Specifically, you can't bring back a program started in another SSH connection.

Take care of your MySQL performance.

MySQL audits available from only €99 per server. Learn More
blog comments powered by Disqus