My MySQL command prompt

Earlier this week we had a discussion with fellow DBAs about our mysql prompts, and at the end of the day it showed up, that a lot of us hit the same problem.

The problem is, that when you set up your mysql prompt then ‘\h’ will be resolved to ‘localhost’ when you connect locally – instead the name of your host as you expect it. It always bugs me, and once I spent a good afternoon figuring out how to workaround this.

Well, the workaround is not a big deal, because you can insert any text into your mysql prompt, and after you realise that you can do it, then it is easy: just put the hostname into your prompt with your chosen provisioning tool and that will do.

For example in my puppet installations I use the following setting in the template file from I generate the /etc/my.cnf file:

prompt = "\u@<%= @fqdn %> (\h) [\d]> "

It says: “username@hostname (connected_host) [database_name]” which is really informative. Let me show you this on live servers!

[banyek@db-dev.bfc /home/banyek]# mysql -s (localhost) [(none)]> \q
[banyek@db-dev.bfc /home/banyek]# mysql -s -h ( [(none)]> \q
[banyek@db-dev.bfc /home/banyek]#

You can see that looking at this prompt I am able to say that which username I used, which host I am running the mysql command, what server will I run my commands and in which database I am.

There is also one more thing I want to show you, and that is colorising!

This is not my idea, so here’s the link the superuser article where I’ve read about that, but the idea is simple, use the ANSI escape sequences to colorise your mysql prompt. This could not be done inside a ‘prompt’ setting, but you can to this as creating an alias and echo the whole mysql command line out – where you can use the terminal control sequences.

For example here is an alias which you could check out:
$ alias colormysql=$(echo -e 'mysql --prompt="\x1B[31m\\u\x1B[34m@\x1B[32m\\h\x1B[0m:\x1B[36m\\d>\x1B[0m "')

But I am not really sure if this is generally a good idea. What do you think about that?