mysql hostname prompt when host is localhost

I manage several mysql servers and often on these servers for security reasons the SUPER account is not allowed external network access, so access is made to localhost. When connecting to several hosts at the same time, for example from different ssh sessions, this can be inconvenient as the \h prompt only ever shows localhost and not the hostname of the server to which I’m connected.

The following small patch against 5.1.36 which can also be found here adds a new \H option which behaves the same as \h except in this case the hostname is shown.

This gives the output as shown below:

As this uses the FQDN it may be necessary to add another option to show only the host’s short name but for now this patch is useful for me. I expect the patch should work against most other versions of MySQL. Perhaps this is useful enough to warrant putting in the main MySQL sources?

Published by

Simon J Mudd

Born in England, I now live in Spain, but spent a few years living in the Netherlands. I previously worked in banking (financial markets) both in IT and as a broker, but IT has always had a stronger influence. Now working at as a Senior Database Administrator. Other interests include photography, and travel. Simon is married, with two children and lives in Madrid.

7 thoughts on “mysql hostname prompt when host is localhost”

  1. $ export MYSQL_PS1=”hostname> ”
    $ mysql> quit

    Add to your .bashrc file (or similar).

    1. Sorry for the delayed reply. Being away from the keyboard prevented me replying earlier.
      I wasn’t aware of this option which indeed does allow the problem to be solved differently, though not cleanly if you connect to different db hosts from the same server which I do frequently. So a useful thing to know and for some cases it does provide a good solution.

  2. I have asked that \H be @@global.hostname for several reasons. I agree that making the hostname variable available in the command-line client makes perfect sense. One person within sun said it could be a security risk, however, I don’t see how. I agree that setting MYSQL_PS1 could help, but why should I be forced to set an environment variable for something so simple? I hope MySQL accepts your patch modified to use the appropriate internal variable.


  3. I don’t see how @@global.hostname can be a security risk as the client already can retrieve that information from the server with a simple SELECT @@hostname. It would certainly be nice to get this fixed.

    Our current systems have a fixed prompt showing the server’s hostname, but this of course is wrong if we connect to a mysqld on a different server.

    Although an aside I also wish there was a way to enable and disable the input using a command such as ‘set echo on’ or similar which is often used in Oracle and Sybase scripting. With mysql the only way to do this is with a “silly” -vvv option to mysql and then this behaviour is fixed during the runtime of the mysql execution.

  4. One additional note, however… When multiple instances are running on the same host, I would want code to make sure it does a look up on the bind address if specified because at some places, multiple IP addresses are assigned to a box rather than worrying about multiple port numbers.

Leave a Reply