Tooling Around: MySQL's Client Config File

There are several good GUI options out there for working with MySQL. For the CLI fans or those forced to use a machine with no GUI it’s possible to have a good experience using the default MySQL1 client.

The mysql command accepts a variety of options. They can be passed in the initial call, set on the mysql terminal or permanently added in a ~/.my.cnf file. There are two in particular that are extremely useful.

Prompt

As you can see, the default prompt lacks any useful information.

mysql>

The prompt can show time, user and server info. You can see the full list of options on MySQL’s commands page. I prefer the current time, the name of the server and the current database I’m working with. Adding a newline provides a nice divide between queries and ensures that they all start at the same place on the screen.

Note: The pipe below indicates the cursor.

mysql> prompt \R:\m\_\h:\d\n)\_ 
PROMPT set to '\R:\m\_\h:\d\n)\_'
10:32 localhost:companyco_test
) |

Knowing that you’re running SQL commands on the correct server and database can help prevent painful mistakes.

Pager

By default mysql doesn’t do any paging. Query data flies by as fast as it can and you’re forced to scroll back if you missed something. Most people I’ve come accross use less(418) as their default pager. I recommend less with the inSFX options passed in.

mysql> pager less -inSFX
PAGER set to 'less -inSFX'

Config File

Working in the mysql terminal is a great way to find settings that work for you. Once that’s done the settings can be added to a ~/.my.cnf file. Put them under [mysql] and any time mysql runs it will use your preferred options.

[mysql]
pager=less -inSFX
prompt=\R:\m\_\h:\d\n)\_

Enjoy!

  1. Version 5.5.25a