Customizing Mysql Prompt For multiple Servers

If you are using mysql regularly on multiple servers its really useful to have the promt in mysql to show something more than mysql>. It would be useful if it showed the username,hostname, current database and things like that.
So how to do this.

One method is on login into a client you can set the session variable prompt using the command below. This is only present for a connection and you have to do it each time you connect.

prompt (u@h) [d]>_

Another method is you can set the environment variable in bash and when you start a client then it will take this value from the environment variable and start the client with that as the prompt. This can be made permanent by putting it in the .bashrc file in home folder

export MYSQL_PS1="(u@h) [d]> "

The best way according to me is doing it by inserting in the my.cnf file. This is the configuration file for mysql. You can find it as /etc/my.cnf in Linux and Mac and my.ini in the installation folder in windows. You can insert the client prompt in this and this is permanent and it works for multiple host showing the username,hostname and database. So find this file if it doesn’t exists create a new one and add the following lines. If the file exists and has some data,then find [mysql] in that and insert only the prompt line the file. Do not create multiple [mysql] as it might create problems.

prompt=(\u@\h) [\d]>\_

Here is the entire list of Commands you can choose from. Use the ones that suits you and tell me about it.

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 username
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

, , , , , , , , , , ,

  1. #1 by Usdbot on January 22, 2010 - 6:33 am

    Interesting post, Thanks

  2. #2 by readbud on June 13, 2010 - 2:22 pm

    Seriously, thanks for sharing. It helps me alot personally, do me a favour, keep up the good blog!

(will not be published)