MySQL command line color prompt

Question

I want to add colors to the MySQL command line color prompt.

I have so far in a script (database.sh):

mysql -uroot -hlocalhost -A --prompt="\u@\h:\d> "

I would like root to be red, @ to be blue, localhost to be green and database to be cyan:

root@localhost:database>

Is it possible to do this in my script?

Answer 1

Don't listen to people who say you can't. Here:

$ alias colormysql=$(echo -e 'mysql --prompt="\x1B[31m\\u\x1B[34m@\x1B[32m\\h\x1B[0m:\x1B[36m\\d>\x1B[0m "')

Then:

$ colormysql -hHOSTNAME -uUSERNAME -pPASSWORD ...

Answer 2

Walkthrough on setting up colorized mysql prompt.

Step 1. Understand how to login normally with a set prompt:

eric@dev ~ $ mysql --host=yourhost.com -u username --prompt="foobar> " -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 711
Server version: 5.6.19 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

foobar> 

Step 2. Understand how you can pipe an interpreted expression through echo to 'alias':

Which does exactly the same as step 1 above:

eric@dev ~ $ alias penguins=$(echo -e 'mysql --host=yourhost.com -u dev --prompt="foobar> " -p')
eric@dev ~ $ penguins
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 713
Server version: 5.6.19 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

foobar> exit
Bye
eric@dev ~ $

Step 3. Understand how echo -e evaluates the colorized expression:

This colors the "foobar>" prompt red:

alias penguins=$(echo -e 'mysql --host=yourhost.com -u dev --prompt="\x1B[31mfoobar>\x1B[0m " -p')
penguins

Like this:

Step 4. If you are confused as to what is going on here:

Look at the expression: \x1B[31mfoobar>\x1B[0m

It has three parts:

code               what it means:

\x1B[31m           Start colorizing, 31m is red.
foobar>            prompt text
\x1B[0m            Stop colorizing.

Step 4. Advanced, Lets make the prompt real nice:

eric@dev ~ $ alias penguins=$(echo -e 'mysql --host=yourhost.com -u dev --prompt="\x1B[31m\\u\x1B[34m@\x1B[32m\\v\x1B[0m:\x1B[36m\\d>\x1B[0m " -p')
eric@dev ~ $ penguins

If you are confused as to what this massive code does:

\x1B[31m\\u\x1B[34m@\x1B[32m\\v\x1B[0m:\x1B[36m\\d>\x1B[0m

Explanation:

Code         Note
\x1B[31m     Start colorizing red
\\u          escape the backslash for passage through echo, and print username
\x1B[34m     Start colorizing dark blue
@            literal at sign
\x1B[32m     Start colorizing green
\\v          escape the backslash for passage through echo, print server version
\x1B[0m      Stop colorizing
:            literal colon
\x1B[36m     Start colorizing cyan
\\d>         Backslash for passage through echo, print default db and >
\x1B[0m      Stop colorizing.

So wow. Much codes.

Answer 3

I wanted the prompt to be in the title of my terminals, which is essentially the same problem as wanting a colored prompt, just a different escape code. Came across this and wondered if I could do it without having to remember a special alias like the colormysql etc mentioned in the answers.

I've added the following to my .bashrc which does the trick on my machine:

export MYSQL_PS1=$(echo -e "\033]0;\u@\h [\d]\007\u@\h [\d]> ")

what this does is use echo -e to generate the raw escape characters (rather than the symbolic ones) to the MYSQL_PS1 variable. This should also work with colors.

Answer 4

It's a quite unfortunate answer, but you cannot.

Regarding the use of ANSI escape sequences, MySQL only allows the following:

You can use the escape sequences “\b”, “\t”, “\n”, “\r”, “\”, and “\s” in option values to represent the backspace, tab, newline, carriage return, backslash, and space characters.

Regarding cmjdmiller's answer, grc only works to display output from MySQL's shell through a "pager".

The best you could do is use rlwrap like this: rlwrap -a -p'GREEN' mysql -uroot -hlocalhost -A --prompt="\u@\h:\d> ". This will not give you fine-grained control however as it colorizes the entire prompt. Also be careful because it displays the password in cleartext.