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.

OptionDescription
\cA counter that increments for each statement you issue
\DThe full current date
\dThe default database
\hThe server host
\lThe current delimiter (new in 5.0.25)
\mMinutes of the current time
\nA newline character
\OThe current month in three-letter format (Jan, Feb, …)
\oThe current month in numeric format
\Pam/pm
\pThe current TCP/IP port or socket file
\RThe current time, in 24-hour military time (0–23)
\rThe current time, standard 12-hour time (1–12)
\SSemicolon
\sSeconds of the current time
\tA tab character
\UYour full user_name@host_name account name
\uYour user name
\vThe server version
\wThe current day of the week in three-letter format (Mon, Tue, …)
\YThe current year, four digits
\yThe current year, two digits
\_A space
\ A space (a space follows the backslash)
\'Single quote
\"Double quote
\\A literal “\” backslash character
\xx, for any ‘x’ not listed above
Tags: