MYSQL_PS1: 6 Examples to make your mysql> prompt like Angelina Jolie

by Ramesh Natarajan on February 22, 2010

Photo courtesey: Jessica Finson

Photo courtesy: Jessica Finson

A while back we tried to customize unix prompt to look like Angelina Jolie.

Oh boy, didn’t I fail miserably in that attempt? Well, that didn’t stop me from trying an extreme makeover for mysql> prompt.

Let us face it. The following mysql> prompt is boring. Nobody wants to see it. Let us change the default mysql> prompt to something functional and useful.

$ mysql -u root -pyour-password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.25-rc-community MySQL Community Server (GPL)

mysql>

1. Display username, hostname and current database name in the mysql prompt

The MYSQL_PS1 in this example displays the following three information in the prompt:

  • \u – Username
  • \h – Hostname
  • \d – Current mysql database
$ export MYSQL_PS1="\u@\h [\d]> "

$ mysql -u root -pyour-password -D sugarcrm

root@dev-db [sugarcrm]>

Now the mysql prompt doesn’t look that bad. does it? This prompt is more meaningful than the useless default “mysql>” prompt.

2. Change the mysql> prompt interactively

You can also change the mysql> prompt interactively from inside the mysql as shown below.

$ mysql -u root -pyour-password -D sugarcrm

mysql> prompt \u@\h [\d]>
PROMPT set to '\u@\h [\d]> '

root@dev-db [sugarcrm]>

3. Change the mysql> prompt from mysql command line

Instead of using the MYSQL_PS1 variable, you can also pass the prompt as an argument to the mysql command line as shown below.

$ mysql --prompt="\u@\h [\d]> " -u root -pyour-password -D sugarcrm

root@dev-db [sugarcrm]>

4. Display Current Time in the mysql> prompt

Use \D to display full date in the mysql prompt as shown below.

$ export MYSQL_PS1="\u@\h [\D]> "

$ mysql -u root -pyour-password -D sugarcrm

root@dev-db [Sat Dec 26 19:56:33 2009]>

5. Change the mysql> prompt using /etc/my.cnf or .my.cnf file

You can also use either the global /etc/my.cnf (or) your local ~/.my.cnf file to set the prompt as shown below.

$ vi ~/.my.cnf
[mysql]
prompt=\\u@\\h [\\d]>\\_

$ mysql -u root -pyour-password -D sugarcrm

root@dev-db [sugarcrm]>

6. Customize mysql> prompt any way you want it

Use the following variables and customize the mysql prompt as you see fit. These variables are somewhat similar to the Unix PS1 variables (but not exactly the same).

Generic variables:

  • \S displays semicolon
  • \’ displays single quote
  • \” displays double quote
  • \v displays server version
  • \p displays port
  • \\ displays backslash
  • \n displays newline
  • \t displays tab
  • \ displays space (there is a space after \ )
  • \d displays default database
  • \h displays default host
  • \_ displays space (there is a underscore after \ )
  • \c displays a mysql statement counter. keeps increasing as you type commands.
  • \u displays username
  • \U displays username@hostname accountname

Date related variables:

  • \D displays full current date (as shown in the above example)
  • \w displays 3 letter day of the week (e.g. Mon)
  • \y displays the two digit year
  • \Y displays the four digit year
  • \o displays month in number
  • \O displays 3 letter month (e.g. Jan)
  • \R displays current time in 24 HR format
  • \r displays current time in 12 hour format
  • \m displays the minutes
  • \s displays the seconds
  • \P displays AM or PM

Note: You can go back to the regular boring mysql> prompt at anytime by simply typing prompt in the mysql> prompt as shown below.

root@dev-db [sugarcrm]> prompt
Returning to default PROMPT of mysql>
mysql>

Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 8 comments… read them below or add one }

1 Raghu February 22, 2010 at 3:59 am

Nice Post.

2 David February 22, 2010 at 7:50 am

I’m still looking for Angelina…. :-) I think I’m gonna insert her picture as an ASCII art..

thx for the tips.

3 Sebastian Kusnier February 22, 2010 at 8:50 am

Only Vim + dbext is better :)
One tool to rule them all – like Sarah Michelle Geller :)
http://www.vim.org/scripts/script.php?script_id=356

4 Shantanu Oak February 24, 2010 at 2:30 am

You can also create an alias and save it in the bashrc file.
This will allow me to simply type “mysql_new” to login.
Show Warnings and tee are the other 2 options I do use apart from “prompt”.

vi ~/.bashrc
alias mysql_new=’mysql -uroot -pPassWd –prompt=”(\r:\m)\_mysql>” –show-warnings –tee=”/home/sqltee.txt”‘

5 Ramesh Natarajan February 26, 2010 at 5:03 pm

@Raghu,

Thanks for the comments. I’m very glad that you found this post helpful.

@David,

If you get angelina on an ASCII art, share it with us. :)

@Sebastin,

Vim+dbext looks great. Thanks for sharing it with us.

I can’t stop thinking about Daphne and scooby doo, whenever I hear about Sarah Michelle Geller. :)

@Shantanu,

Thanks for sharing your mysql_new alias. It’s very good.

6 Mannoj July 31, 2012 at 1:07 am

Well I was looking for unix bash history to *** for mysql password.
Eg: If I issue -
mysql -uroot -psecuritydemon -h192.168.90.888

then in unix prompt if I use history | grep -i mysql -> I get the password entry too.. Instead I would like to see for the history grep result as below

mysql -uroot -p***** -h192.168.90.888

Any way to achieve this?

7 Mannoj October 12, 2013 at 2:31 pm

To avoid the pwd in command line or history use mysql_config_editor here.

8 Sanjay Sengupta April 3, 2014 at 8:19 pm

Very useful & concise article, thanks a lot:-)

Leave a Comment

Previous post:

Next post: