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.

diff --git a/Docs/mysql.info b/Docs/mysql.info
index 7747201..dffacfd 100644
--- a/Docs/mysql.info
+++ b/Docs/mysql.info
@@ -20512,6 +20512,8 @@ sequences.
 `\D'        The full current date
 `\d'        The default database
 `\h'        The server host
+`\H'        Same as `\h' except that if the server host is localhost
+            the client's hostname will be used
 `\l'        The current delimiter (new in 5.1.12)
 `\m'        Minutes of the current time
 `\n'        A newline character
diff --git a/client/mysql.cc b/client/mysql.cc
index 5f360b8..b24fb14 100644
--- a/client/mysql.cc
+++ b/client/mysql.cc
@@ -39,6 +39,10 @@
 #include <signal.h>
 #include <violite.h>

+#ifdef SOLARIS
+extern "C" int gethostname(char *name, int namelen);
+#endif
+
 #if defined(USE_LIBEDIT_INTERFACE) && defined(HAVE_LOCALE_H)
 #include <locale.h>
 #endif
@@ -4657,12 +4661,27 @@ static const char* construct_prompt()
 case 'd':
 processed_prompt.append(current_db ? current_db : "(none)");
 break;
+      /* \H is the same as \h except if \h returns localhost in which case *
+       * we provide the client's hostname.                                 *
+       */
 case 'h':
+      case 'H':
 {
 const char *prompt;
+        char  myhostname[255];
 prompt= connected ? mysql_get_host_info(&mysql) : "not_connected";
-    if (strstr(prompt, "Localhost"))
-      processed_prompt.append("localhost");
+
+    if (strstr(prompt, "Localhost") || strstr(prompt, "localhost"))
+          if ( *c == 'h' ) {
+        processed_prompt.append("localhost");
+          }
+          else
+          {
+            if (gethostname(myhostname,sizeof(myhostname)) < 0)
+              processed_prompt.append( "gethostname(3) returned an error" );
+            else
+              processed_prompt.append( myhostname );
+          }
 else
 {
 const char *end=strcend(prompt,' ');

This gives the output as shown below:

$ hostname
my.computer.domain.com
$ client/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6892
Server version: 5.0.xx MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> prompt \h
PROMPT set to '\h '
localhost prompt \H
PROMPT set to 'prompt \H '
prompt my.computer.domain.com quit
Bye
$

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