Knowing which database you’re working in is essential, especially when you’re working on multiple servers across development, staging, and production environments.  I recently started a new job, and switched from a mostly Postgres to a mostly MySQL environment.    I then spent half an hour looking at data in what I thought was a production server, but actually turned out to be my development environment, and told my boss some things that were obviously wrong.  Obvious to my boss at least, but not obvious to the new guy (me).  He just laughed after we figured it out, but I felt like an idiot.

I knew that Postgres provides psql prompt formatting that will tell you which server you’re working on, but I hadn’t had time to figure that out for MySQL.  So, I started Googling to determine how to put the database server in the prompt name.  This post by Ramesh Natarajan schools us on several ways to update the mysql prompt, and tells us how to insert the server name with the \h option in the prompt.

One method described in Ramesh’s post is to use the MYSQL_PS1 variable.  Just as the Bash PS1 environmental variable can be used to tweak your Bash shell prompt, the MYSQL_PS1 variable allows you to customize your mysql prompt.   And, it does have an option to put the server name in. So, you can get your username and the server name into the mysql prompt by setting MYSQL_PS1 to something like this:

export MYSQL_PS1='\u@\h> '

The mysql client just inserts whatever you passed on the command line for the hostname.   So, if you do something like this:

mysql -u joeuser -h dbhost

Your prompt will look like this:

joeuser@dbhost>

So far, so good.   But what if MySQL access control or your network firewall rules prevent you from logging in to MySQL remotely?   Usually what you need to do in that case is first use ssh into the server, and then use mysql on the server to log in to the database, using something like:

mysql -u joeuser -h localhost

and then your mysql prompt says

joeuser@localhost>

But where’s localhost? Is it the remote production server?  Is it your dev box?  It can get really hard to track if you have three or four windows open pointing to different databases, all with localhost in the prompt.  Really what you want is the database host name there.

Simon Mudd has a patch for the mysql client that allows you to put a “\H” in your MYSQL_PS1 format string.  The “\H” option will show the server hostname if “localhost” was passed to mysql.  You can read about it here: http://blog.wl0.org/2009/08/mysql-hostname-prompt-when-host-is-localhost/

This is a great solution, and hopefully the MySQL developers will include it in the mysql distribution.  While you wait for them, if you have the permissions and ability, you can install Simon’s patch yourself.   However, the system administrators at my job are plenty busy, and updating the mysql client on our production servers is pretty far down their list of things to do.

As a workaround, I wrote this script called “mysql_realhost.sh” which behaves like Simon Mudd’s patch.   If you pass in “-h localhost” to the mysql client, it will try to put the name of the host you’re on into the mysql prompt.

#!/bin/bash
#mysql_realhost.sh - Puts the real hostname in the mysql prompt
#Set REAL_MYSQL_PATH to the actual path of your mysql client
#Edit MYSQL_PS1 to add format the mysql prompt to your preference.

#set this to your MySQL path
REAL_MYSQL_PATH=/usr/bin/mysql

#look for hostname in mysql command line
HOSTNEXT=no
for ARG in $@; do
  if [ $ARG = "-h" ]; then
     HOSTNEXT=yes
  else
    #if hostnext==yes, this arg follows the -h and
    #is the hostname passed to mysql
    if [ $HOSTNEXT = "yes" ]; then
      CL_HOST=$ARG
      break;
    fi
  fi
done

#if we didn't get it from the mysql command line, try hostname
if [[ -z $CL_HOST || $CL_HOST = "localhost" || $CL_HOST = "127.0.0.1" ]]; then
   CL_HOST=`hostname -s`
fi

#finally, if all else fails, set to the mysql token "\h"
#and let mysql take its best guess
if [ -z $CL_HOST ]; then
   CL_HOST='\h';
fi

#Set up the prompt with our generated hostname.
#Tweak this prompt to have the format you want, just
#leave $CL_HOST where you want the hostname to be
export MYSQL_PS1="\u@$CL_HOST> "

#now execute mysql with the original arguments
$REAL_MYSQL_PATH $@

If you want to use the script yourself, you should modify line 7 to set the correct path to the mysql client, and set the MYSQL_PS1 formatting of the prompt on line 38 to your taste.

Now if you’re logging into dbhost with the hostname like this:

mysql_realhost.sh -u joeuser -h dbhost

Your prompt will look like this:

joeuser@dbhost>

Or if you log into it using localhost like this:

mysql_realhost.sh -u joeuser -h localhost

You’ll still have the correct hostname in your prompt, like this:

joeuser@dbhost>