Adding a custom MySQL prompt

September 1, 2018 by richardvk

Filed under Databases

Last modified September 1, 2018

I often work on multiple database instances at the same time. For example, i might be comparing table definitions between a development and production server, or if using a cluster like Percona XtraDB Cluster, you may have sessions open to each of three (or more) of the node servers at the same time to check their synchronisation statuses.

Whatever the use case, its extremely helpful to have a custom mysql prompt at the cli, and for me, at the very least, having the host name and the database name in the prompt has prevented me running commands on the wrong server!

To achieve this, simply add a configuration line to your ‘my.cnf’ file (e.g. /etc/mysql/my.cnf) like this:

[MYSQL]
prompt=mysql@my_host_name:[\d]>\_

You can find more details on the options that are configurable here.

You might wonder why i manually inserted ‘my-host_name’ as a string, instead of using the \h option. The \h option prints out the full host name of the server, including the domain name, which is not only not needed in my case, but can make the prompt horribly long. Indeed, I often just insert the term ‘production’ or ‘staging’ as i don’t always need the actual host name. The database name however \d is pretty important especially when typing out sql statements that include data from multiple databases.

Note that \_ is simply a white space added for readability.

Related Articles

                          Leave a Comment