Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
    
    
I saw it but it does not work – EscoMaji Mar 5 '12 at 19:06
    
Yeah I think it's possible the same way a in your regular shell prompt. Your best bet is probably some wrapper or a more advanced MySQL-client. – micke Mar 5 '12 at 19:12
1  
Does your setup support ANSI escape codes? If it does then you can use them in the following way: --prompt=" ^[[1;33mDTHIS PART IS IN YELLOW COLOUR^[[0m \u@\h:\d> " – Hennes Mar 24 '13 at 22:53

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 ...
share|improve this answer
    
Can you give some more explanation as to why this works, but you can't just pass ANSI codes? – Bryan Agee Nov 5 '13 at 21:14
    
$(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. – Irongaze.com Apr 1 '14 at 17:34
    
Looks nice, but when using home/end, the cursour end of at the wrong position :-( – Puggan Se Jun 30 '14 at 7:59
3  
Also adding escape codes enclosing the color codes make this work properly (navigating the history will work without make your prompt a mess) like this: $ 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 "') – David Santamaria Jan 28 '15 at 16:06
1  
@CyprianGuerra - Look at @david-santamaria's comment above, using \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). – dossy Dec 2 '15 at 17:45

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:

enter image description here

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

enter image description here

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.

share|improve this answer
    
Any idea on how to escape terminal codes correctly so that 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) – Cyprian Guerra Dec 1 '15 at 15:25
    
This is a completely separate tangent question that should be asked as a separate stackoverflow question. Of course it's possible with access to a turing complete programming language, but the question is how, I don't know without putting a few hours into it. – Eric Leschinski Dec 1 '15 at 15:31

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.

share|improve this answer

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.

share|improve this answer
    
This is out of date. There are a host of possible values for the prompt option. See here: dev.mysql.com/doc/refman/5.6/en/mysql-commands.html – Irongaze.com Apr 1 '14 at 17:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.