Loading...
X

How to change the command line prompt for MySQL (MariaDB)

The MySQL (MariaDB) client, when connected to a DBMS server, displays the database selected to use and the name of the DBMS (such as MariaDB or MySQL) at the default command prompt.

Perhaps you want to add to this information the username, the hostname on which the MySQL (MariaDB) server is running, the port number, the current date and time, or, for example, a counter of executed SQL queries. All this is possible – you can customize the command line prompt of the MySQL (MariaDB) client to your taste and your needs.

You can change the command line prompt in MySQL and MariaDB for the duration of a single session or for each session. In any case, this setting is easy to change and disable.

Before moving on to ways to customize the MySQL (MariaDB) command line prompt, let's take a look at the conventions used to indicate the format.

Option Description
\C The current connection identifier
\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
\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 Print an asterisk (*) if the current session is inside a transaction block
\t A tab character
\U

Your full user_name@host_name account name

\u Your user name
\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. How to change the MySQL (MariaDB) command line prompt using an environment variable

Use an environment variable. You can set the MYSQL_PS1 environment variable to your desired command line prompt format. For example:

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

As you can see in the screenshot, the following command line prompt is used by default:

MariaDB [(none)]>

After exporting the MYSQL_PS1 variable and reconnecting to the MariaDB server, the command line prompt changed to:

(root@localhost) [(none)]>

In order not to enter the variable export command every time, you can save it in a .bashrc file (for the Bash shell).

All files starting with a dot, including the .bashrc file, are considered hidden by default in Linux. That is, these files are not displayed unless the “show hidden files” option is enabled. In this case, you can easily open this file on the command line:

gedit ~/.bashrc

If you are using a different shell, then the filename will be different.

See also: How to find out which shell is in use in Linux

Or you can create a shortcut to connect to the database server with something like the following content.

#!/bin/bash

export MYSQL_PS1="(\u@\h) [\d]> "
mariadb -u root -p

2. How to change the MySQL (MariaDB) command line prompt in the command line options

Use command line option. You can set the --prompt option on the command line for mysql/mariadb. For example:

mysql --prompt="(\u@\h) [\d]> " -u root -p

3. How to change the MySQL (MariaDB) command line prompt in the configuration file

See also: How to determine the location and name of the MySQL (MariaDB) configuration file. How to find the group name for MySQL and MariaDB configuration files

Open the configuration file /etc/my.cnf:

sudo gedit /etc/my.cnf

And add the command line prompt format to the [mysql] group:

[mysql]

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

Note that in this example, the backslash is doubled. If you set the command line prompt format with the prompt setting in an options file, it is advisable to double the backslash if it occurs. There is some overlap between the set of allowed prompt options and the set of special escape sequences that are recognized in the option files. This overlap (match) can cause problems if you use single backslashes. For example, \s is interpreted as a space and not as the current value of seconds. The following example shows how to specify a prompt in an options file to include the current time in the format hh:mm:ss>:

[mysql]

prompt="\\r:\\m:\\s> "

Note: You can also use a different group name to specify client settings. In my tests, the MariaDB client correctly handled settings from the [mysql] group, but you can also use any of the following names for MariaDB:

[client]

[mariadb-client]

[client-mariadb]

[client-server]

IMHO, the most universal of these names is [client], although, as I noted just above, the group name [mysql] is also understood by both DBMS.

4. How to change the MySQL (MariaDB) command line prompt interactively

Set the prompt interactively. You can change the prompt interactively using the “prompt” (or \R) command. For example:

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

Will output:

PROMPT set to '(\u@\h) [\d]>\_'
(user@host) [database]>

Calling the “prompt” command without specifying a value will return the command line prompt to its original form:

(user@host) [database]> prompt
Returning to default PROMPT of mysql>
mysql>


2 observations on “How to change the command line prompt for MySQL (MariaDB)
  1. Stein V H Haugan

    THANK YOU! Why didn't I google this earlier ;-). I have three machines with basically the same database, and it's really irritating to have to scan upward in the terminal history to see "uh, which machine am I on again?". Great, readable article 🙂

     
    Reply

Leave Your Observation

Your email address will not be published. Required fields are marked *