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?

Tags: , , , , ,

7 Responses to “mysql hostname prompt when host is localhost”

  1. Anonymous says:

    $ export MYSQL_PS1=”hostname> ”
    $ mysql
    host.name.com> quit

    Add to your .bashrc file (or similar).

    • sjmudd says:

      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. KB says:

    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.

    KB

  3. Simon Mudd says:

    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. [...] 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/ [...]

  5. KB says:

    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.

  6. Kolbe says:

    This is great. I guess I’d never looked closely enough to notice that this was not already happened when using “\h”. I created https://mariadb.atlassian.net/browse/MDEV-505 in an effort to get this included in MariaDB.

    Kolbe

Leave a Reply