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.

[MySQL Health Check]
About Maciej Dobrzanski

A MySQL consultant with the primary focus on systems, databases and application stacks performance and scalability. Expert on open source technologies such as Linux, BSD, Apache, nginx, MySQL, and many more. @linkedin

Comments

  1. Programs like tmux allow you to bring back database console session on other terminals.

  2. Just put mysql password into the ~/.my.cnf and you don’t have to hassle with this nonsense. :-/

    • But it wouldn’t be a very secure thing to do.

      • With chmod 600?

        • Jarek,

          There can be many different circumstances. You can store a database password as open text in .my.cnf if both you are allowed to do so and you do not consider it a security risk (which it is). However, in many cases there will be a security policy simply preventing such possibility. And even without any formal policy I would not recommend this to anyone.

          • Your password is already most likely in config/database.yml next to your .my.cnf anyway. In 99% of situations it doesn’t make your system any less secure.

      • You can also alias the mysql command, shortening the command line to a convenient mneumonic:

        alias sql ‘$MYSQL/bin/mysql –user test_user -p’

        Now you can simply type ‘sql’ at the command line to start a new session.

  3. Marcin Praczko says:

    Hi,

    I think that solution depends of the environment, where job must be done.
    1. Tmux / screen can help with detaching / attaching to console sessions from different terminals, but one you have them – you don’t have to connect multiple times over SSH with the same server.

    2. If one works on MySQL database as root account / one can put password to ~/.my.cnf. Risk related with security is the same (of course permissions 0600). If some intruder has root account to your database server – he can re-run mysql without asking for password (Intruder has already full root access – so is easy to read /root/.mycnf and is ready to restart mysql with skipping grand table). Having password in file spead up your work and allow others software working by using this passsword (innotop, mytop), etc.

    3. If one works on MySQL database server as not root user, ususally one connects via SSH from different server. Nice trick is setup SSH tunnels which allow you connect with database from your local machine.
    Modify file ~/.ssh/config and do something like that:

    # Hosts configuration – from linux host
    Host hostname
    User Your-SSH-login
    GatewayPorts no
    LocalForward 30000 localhost:3306

    Now you can create ~/.my.cnf on you local machine and connect with database via SSH-tunnel when you need. By running command: mysql -u user -p -h 127.0.0.1 -P 30000. When one will create extremly small script to overwrite ~/.my.cnf file every time when one connecting with different database servers, one will start working with minimum password prompts. And all passwords are kept in local machine, which one is working on all day. One should encrypt files / disk when one is traveling with ‘local host’

    • No need to overwrite my.cnf, just use an aliased mysql command with “–defaults-extra-file=” pointing to custom my.cnf. Also the best idea is just to go with 2) and use mysql client with .my.cnf as the user which mysql is running as (via sudo). Any intruder reading this file can also read/write your database. Just make sure this password isn’t used anywhere else. Concept of some sort of policy is bogus itself, as in most cases you need your password in framework configuration anyway.

      Also, if one really needs to work so much on production instances of database using command line client, then something probably isn’t quite right already.

  4. Hi, be careful with the fatal timeout ;-)
    http://www.mysqlplus.net/2011/07/07/fatal-timeout/

Speak Your Mind

*