Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this community
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?
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 ...
$(foo) executes the result of foo, in this case echo's output. The -e to echo expands the \x1b into an escape char. The \x1b[31m is a terminal control code that sets the current color to red. And so forth. \x1b[0m returns the color to the default terminal color.
Apr 1, 2014 at 17:34
mysql --prompt="`echo -e '\033[01;32m\\d\033[01;34m >\033[00m '`"
Jun 30, 2014 at 8:01
\001 and \002 around the sequences for proper readline support. $ alias colormysql=$(echo -e 'mysql --prompt="\001\x1B[31m\002\\u\001\x1B[34m\002@\001\x1B[32m\002\\h\001\x1B[0m\002:\001\x1B[36m\002\\d>\001\x1B[0m\002 "') There appears to be some kind of rendering issue with copy-and-pasting that from the browser, where the \002:\001 portion of the command is being put into the clipboard as \002:\<200c><200b>001 (where <200c> and <200b> are non-printing characters, probably the CR/NL from word-wrapping).
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.
readline wouldn't be confused about the line length and editing multiple lines in mysql command line would be possible? (try to write multiple lines, then press and hold backspace)
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.
Just right-click on the title bar, select defaults you will get access to do everything in terms of color, font & background. Restart the command line to see the changes.
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.
