Mysql Prompts

You can change the prompt on the MySQL command-line tool to show user name, time, current database or many other useful facts.

mysql> prompt \R:\m:\s>
PROMPT set to '\R:\m:\s>'
11:46:16>show tables;
+----------------------+
| Tables_in_wordpress |
+----------------------+
| wp_categories |
| wp_comments |
| wp_linkcategories |
+---------------------+
9 rows in set (0.00 sec)

11:46:24>

If you specify the prompt command with no argument, mysql resets the prompt to the default of mysql>.

The prompt command reconfigures the default mysql> prompt. The string for defining the prompt can contain the following special sequences:

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

You can set the prompt in several ways:

• Use an environment variable. You can set the MYSQL_PS1 environment variable to a prompt string. For example:

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

• Use a command-line option. You can set the --prompt option on the command line to mysql. For example:

  shell> mysql --prompt="(\u@\h) [\d]> "
  (user@host) [database]>
  

• Use an option file. You can set the prompt option in the [mysql] group of any MySQL option file, such as /etc/my.cnf or the .my.cnf file in your home directory. For example:

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

  In this example, note that the backslashes are doubled. If you set the prompt using the prompt option in an option file, it is advisable to double the backslashes when using the special prompt options. There is some overlap in the set of allowable prompt options and the set of special escape sequences that are recognized in option files. (These sequences are listed in Section 4.2.3.2, “Using Option Files”.) The overlap may cause you problems if you use single backslashes. For example, \s is interpreted as a space rather than as the current seconds value. The following example shows how to define a prompt within an option file to include the current time in HH:MM:SS> format:

  [mysql]
  prompt="\\r:\\m:\\s> "
  

• Set the prompt interactively. You can change your prompt interactively by using the prompt (or \R) command. For example:

  mysql> prompt (\u@\h) [\d]>\_
  PROMPT set to '(\u@\h) [\d]>\_'
  (user@host) [database]>
  (user@host) [database]> prompt
  Returning to default PROMPT of mysql>
  mysql>

Details are here half way down the page.