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).
- \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>