Changing MySQL clients default prompt

Although graphical clients are preferred when accessing MySQL databases, for many users the mysql command line client is the first choice when connecting to the MySQL server. And many times this is the only option available when you are accessing remote machines through SSH.

The default mysql prompt is the simple:

mysql>

Nice and simple, but if you have multiple mysql client windows open it can be a little harder to know which window is connected to which user and database. Changing the prompt to display the database and user makes it easier to quickly tell which window is connected to which client and database.

Changing the mysql prompt

To change the prompt open your MySQL my.cnf (or my.ini if you are using Windows) and add the following line in the [mysql] section. If the file does not have one, create it by adding the following line.

[mysql]

After the above line add the following option.

prompt=\u@\h:[\d]>\_

The ‘\u’ and ‘\h’ display the username and hostname respectively. The ‘\d’ displays the default database selected. Restarting the client will display the changed prompt, the following is what it looks like on my machine. ‘wordpress’ is my default database.

codediesel@localhost:[wordpress]>

If you do not want to make changes to the my.ini file or are unable to do so, you can also change the prompt interactively as shown below.

shell> mysql --prompt="\u@\h:[\d]>\_" -uUSERNAME -p

List of options

The ‘prompt’ parameter supports many options, a complete list is shown in the following table.

Option Description
\c A counter that increments for each statement you issue
\D The full current date
\d The default database
\h The server host
\l The current delimiter (new in 5.0.25)
\m Minutes of the current time
\n A newline character
\O The current month in three-letter format (Jan, Feb, …)
\o The current month in numeric format
\P am/pm
\p The current TCP/IP port or socket file
\R The current time, in 24-hour military time (0–23)
\r The current time, standard 12-hour time (1–12)
\S Semicolon
\s Seconds of the current time
\t A tab character
\U Your full user_name@host_name account name
\u Your user name
\v The server version
\w The current day of the week in three-letter format (Mon, Tue, …)
\Y The current year, four digits
\y The current year, two digits
\_ A space
\ A space (a space follows the backslash)
\' Single quote
\" Double quote
\\ A literal “\” backslash character
\x x, for any ‘x’ not listed above