Put the database hostname in your MySQL prompt

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>

3 thoughts on “Put the database hostname in your MySQL prompt

  1. stratzoomer

    How about using variables while setting the MYSQL_PS1 env variable on the host machine?
    The below should work. Note the backward single quote enclosing the hostname -s
    export MYSQL_PS1=”\u@`hostname -s`”

    Reply
    1. lreeder Post author

      stratzoomer, your approach works fine for local connections, and that’s effectively what my script does with a few extra steps if no hostname is specified by the user. However, if someone is using the “-h” argument to connect to mysql, you want to honor (ignoring localhost and 127.0.0.1) it so that the remote hostname is in your mysql prompt, not the local hostname obtained by “hostname -s”.

      Reply
  2. Steve Buzonas

    This works assuming that when you are connecting to localhost it isn’t a port forwarded connection to a remote machine or socket to a remote server. I have a script that hits amazon to get all of my production database servers and use socat to create a unix socket for each one with the tunneling proper tunneling configuration. My bash configuration finds all of these sockets to create aliases which results in every production host is localhost. I end up doing something similar but I do a “select @@hostname” first, it would be nice if Oracle made it easier to list the hostname from the server perspective in the prompt.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *