The mysql> command-line prompt is iconic, shown in countless documentation pages, forum posts, tutorials and manuals. It’s immediately identifiable – one look, and you immediately know the context in which commands are being executed. So it’s only with good reason that we would consider changing the prompt value to something else, and Daniël van Eeden provided a compelling suggestion to modify this to provide user, host and database context. Because the mysql prompt is user-configurable, this is easy to do dynamically:

mysql> prompt something>
PROMPT set to 'something> '
something> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

something>

Using the special character sequences defined at the bottom of one of the documentation pages, the prompt can supply with a number of different and useful information which provides context in the command line:

something> prompt \u@\h [\d]>
PROMPT set to '\u@\h [\d]> '
root@localhost [(none)]> prompt \h:\p [\d]>
PROMPT set to '\h:\p [\d]> '
localhost:3310 [(none)]> prompt \D>
PROMPT set to '\D> '
Mon Mar 23 17:09:58 2015>

The prompt can also be set as a command-line argument to the client, or via the configuration file read by the client at startup. Users who manage many servers and need information identifying to which instance a given command line client is connected might want to leverage the configuration file option, as this will affect all client instances started by default.

We actually changed the default prompt to “\u@\h [\d]> ” as Daniël proposed, but pulled it back out of 5.7 after using it internally for a few days and realizing a few issues. This post aims to document why we reverted to the original mysql> prompt, the options we’ve considered as alternatives, and a call for feedback.

Copying SQL

The mysql> prompt length aligns with the prompt length for continuation prompts, where queries span multiple lines. This makes it easy to copy SQL commands from terminals:

mysql> SELECT 1,
    -> NOW();

When using prompts with differing length, copy operations pick up extraneous characters, requiring users to manipulate the contents to transform it back into executable SQL:

root@localhost [(none)] > SELECT 1,
    -> NOW();

An option would be to make the buffer the continuation prompt to the same length as the original prompt. With larger and dynamic prompts, the results might be unsatisfactory – the example above takes up 25 characters of my 80-character-wide terminal.

(None)

MySQL allows users to connect without specifying a default database, and that’s not going to change anytime soon. This results in “[(none)]” being displayed in the prompt. When I first encountered the prompt change, I had to ask what the “none” meant – I had no idea what that meant. Informed users will only need to be told once (or never, if they are smarter than me), but it’s bound to confuse less-experienced users.

Misleading user name

The MySQL ACL system makes displaying user names an interesting exercise. With wildcards, anonymous users and proxy accounts, there’s any number of possible values for “user name” to display. Do you want to know the user name you supplied to the client, the user name portion of the account used to authenticate, or the user name portion of the account associated with the privileges you have? They are different:

shell> bin\mysql.exe -ublah -P3310
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
blah@localhost [(none)] > show grants;
+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.00 sec)

Similarly for proxy users:

pa@localhost [(none)] > SELECT USER(), CURRENT_USER(), @@session.proxy_user;
+--------------+----------------+----------------------+
| USER()       | CURRENT_USER() | @@session.proxy_user |
+--------------+----------------+----------------------+
| pa@localhost | pb@localhost   | 'pa'@'localhost'     |
+--------------+----------------+----------------------+
1 row in set (0.00 sec)

pa@localhost [(none)] > show grants;
+----------------------------------------+
| Grants for pb@localhost                |
+----------------------------------------+
| GRANT USAGE ON *.* TO 'pb'@'localhost' |
+----------------------------------------+
1 row in set (0.00 sec)

Like the [(none)] value, experienced MySQL users will understand the difference between USER(), CURRENT_USER() and @@session.proxy_user, but this can easily confuse users new to MySQL.

Server vs. client host

I’m used to seeing user@host define MySQL accounts, but that’s not what \u@\h actually provides. As noted above, the user is the user name supplied to the client, rather than the account. But the host is the server host, not the host of the account.

shell> ipconfig
...
Ethernet adapter Local Area Connection:

   Connection-specific DNS Suffix  . :
   IPv4 Address. . . . . . . . . . . : 192.168.2.64
...
shell> bin\mysql -utodd -h192.168.2.54
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
todd@192.168.2.54 [(none)] > select user();
+---------------+
| user()        |
+---------------+
| todd@GRAPHITE |
+---------------+
1 row in set (0.02 sec)

Note that using the \U option shows the full user name/host returned from USER():

todd@192.168.2.54 [(none)] > prompt \U >
PROMPT set to '\U > '
todd@GRAPHITE >

It makes sense to display the host a client is connected to, rather than the host component of the account used to connect. But it does allow for confusion about what is being displayed.

Other common use cases

Daniël’s suggestion works great for DBAs who manage multiple MySQL installations and need to ensure they are executing commands against the intended instance.  It’s less useful for single-host deployments, or for use cases like MySQL Sandbox, where multiple instances may all exist locally.  I frequently have 5+ MySQL instances running locally for various testing purposes, and the proposed default prompt wouldn’t help me distinguish between them at all.  Such use cases would be better served by a prompt value like “\h:\p > “:

 

root@localhost [(none)] > prompt \h:\p >
PROMPT set to '\h:\p > '
localhost:3310 >

Alternatives

The need Daniël identified is something we want to address, but the issues listed above gave us second thoughts about changing the default prompt. We’ve identified some possible alternatives, which I’m listing below:

Add prompt help

The definition of special sequences isn’t easy to find in the documentation, and even some experienced MySQL users are unfamiliar with the options that exist. Those that do know the possibilities for customizing prompts sometimes find it difficult to quickly recall the special sequences they might need. We’d like to add this information to the client, so that it is readily available:

mysql> prompt ?

*** PROMPT FORMAT OPTIONS ***
\d - The default database
\u - Your user name
\h - The server host
...

Define prompt macros

We know that \u@\h [\d]> reflects the needs of a common use case, as does \h:\p >. Another common use case likely involves outputting the date/time in the prompt for tracking purposes. Another common use case might be to simply include the default database.

We can provide macros which are easily remembered and cycled through, so that users don’t have to build their own prompts for these common use cases. For example:

mysql> prompt \1
PROMPT set to '\u@\h using \d> '
root@localhost using (none)> prompt \2
PROMPT set to '\h:\p> '
localhost:3310> prompt \3
PROMPT set to '\D> '
Fri Mar 20 14:08:59 2015> prompt
Returning to default PROMPT of mysql>
mysql>

Multi-line prompts

One possible solution is to leverage multi-line prompts, so that contextual information is provided on one line, while the familiar mysql> prompt is retained:

root@localhost [(none)] > prompt \u@\h [\d]\nmysql>
PROMPT set to '\u@\h [\d]\nmysql> '
root@localhost [(none)]
mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

root@localhost [(none)]
mysql>

This makes copying SQL easy, but still displays context information for a specific use case, and which may confuse less-experienced users.

Per-instance custom prompt definitions

As noted earlier, it’s easy enough to define a custom prompt dynamically, as a command-line option, or even globally within a configuration file. It could be useful if the custom prompts could be defined on a per-connection basis, and stored with other connection options in mysql_config_editor. Doing so would allow users to connect to a specific instance, and have a specific (non-default) prompt automatically associated.

Conclusion

Please let us know if you think the reasoning behind our decision to leave the prompt as-is is unsound in any way, or if you have particular affinity for any of the proposed alternatives. Thanks!