Customizing MySQL Prompt

Summary: This tutorial will teach you how to customize the MySQL prompt to streamline your interactions with the database.

First, connect to the MySQL server using the mysql program:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Once you enter a valid password for the root account, mysql displays the mysql> command prompt like this:

mysql>Code language: SQL (Structured Query Language) (sql)

If you change the current database to classicmodels, the mysql shows the same command prompt:

use classicmodels;Code language: SQL (Structured Query Language) (sql)

It shows the same command prompt as before:

mysql>Code language: SQL (Structured Query Language) (sql)

There are two main issues.

• First, the mysql> does not show the currently logged-in user.
• Second, it does not display the current database.

Setting the MySQL prompt using the –prompt option

To make current user account and database information visible in the mysql> prompt, you can connect to the MySQL server with the --prompt option:

mysql -u root -p --prompt="\u@\h [\d]>"Code language: SQL (Structured Query Language) (sql)

Here’s the breakdown of the prompt option "\u@\h [\d]>":

• \u: the user used to connect to the MySQL server.
• @: literal character @.
• \h: the host that connects to the MySQL server.
• \d: the current database. If no database is selected, it is none.

Once you enter the valid password for the root account, you’ll see the following:

root@localhost [(none)]>Code language: SQL (Structured Query Language) (sql)

If you change the current database to the sample database classicmodels, you’ll see the database name in the square brackets []:

use classicmodels;Code language: SQL (Structured Query Language) (sql)

It’ll show the following:

root@localhost [classicmodels]>Code language: SQL (Structured Query Language) (sql)

Now, the mysql prompt displays the current user account and database.

Setting the MySQL prompt using the environment variable

You can also customize the mysql prompt by setting the MYSQL_PS1 environment variable to a prompt string. For example:

On Linux:

export MYSQL_PS1="(\u@\h) [\d]> "Code language: SQL (Structured Query Language) (sql)

On Windows:

setx MYSQL_PS1 "(\u@\h) [\d]> "
SUCCESS: Specified value was saved.Code language: SQL (Structured Query Language) (sql)

Since you have modified the environment variable, it is necessary to restart the terminal (or command prompt) on Windows for the changes to take effect.

Setting the MySQL prompt using the MySQL configuration file

You can set the prompt option in the [mysql] group of any MySQL configuration file such as my.ini on Windows or my.cnf in a Unix-based system:

[mysql]
prompt=(\\u@\\h) [\\d]>\\_Code language: SQL (Structured Query Language) (sql)

Note that in the configuration file, you need to double the backslashes (\\).

Setting the prompt interactively

The mysql program allows you to change the prompt interactively using the prompt or \R command:

mysql> prompt (\u@\h) [\d]>\_
PROMPT set to '(\u@\h) [\d]>\_'
(root@localhost) [(none)]>Code language: SQL (Structured Query Language) (sql)

To reset the prompt, you enter the prompt command only:

(root@localhost) [(none)]> prompt
Returning to default PROMPT of mysql>
mysql>Code language: SQL (Structured Query Language) (sql)

Summary

• To customize the MySQL prompt, you can utilize the environment variable MYSQL_PS1, the --prompt option, adjust the configuration file, or employ the prompt feature within the MySQL program.