One of the things that surprised me at the MySQL Bootcamp was the focus on using the mysql CLI client. I used to only use this, since the GUI-based Query Browser was rather unstable on Linux. I just got tired of it crashing and losing my queries after a while. But once I started using Vista and gave it another try, it seemed to be rock-solid.
In the class, however, we only used the command-line client. It turns out there are a number of powerful shortcuts and customizations available that can make it more palatable for general use. Combined with the fact that this client is included with every distribution and works on every platform with or without a GUI makes it a tool worth getting familiar with.
One of my favorites customizations was changing the prompt displayed. As mentioned in a number of previous posts, I find highly customized prompts provide an efficient benefit in displaying essential information at a glance. In the case of MySQL, however, the default looks like:
1 | mysql> |
Somewhat… bland. Within the client, you can run \R followed by a wide variety of options to customize this display, however. They are all listed here. The better way to set this, however, is to create a file in your home directory called .my.cnf. In that, add a line containing [mysql]. This defines options belonging to the client group. Beneath that you can put many different session variables in place. These will be loaded every time you login. This page has more information on this file.
For the prompt, I played around with a few combinations, and found this to be best for me:
1 | prompt='\v [\D] \U [\d] #\c> ' |
It produces output like:
This lets you know what MySQL server version you are working with, the current datetime, the user you connected with and from where. It also shows the current database in use, and the command number since you connected.
This isn’t quite perfect. I’d like to just display the “5.0.45″ part of the version. But these are built in options, so using things like grep isn’t possible. I’d also like to have the date in ISO format for brevity’s sake. While there is a variable for year and month, there’s only one for names of days, not numbers! I have an idea to get around these limits, by creating variables that contain the content I want shown (via MySQL functions like date()) and calling those. I’ll update this page if I get that working.
Despite these rough edges, this can still make for a much more useful prompt. And it makes things much easier to keep track of when using TEE to record all your actions into an external file. You knew about that one, right? Well check it out too!
Additionally, the commands listed here can save a lot of time as well. These are also shown after running “help” at the prompt. A sampling:
- Access “SHOW STATUS;” with \s
- Clear out a command you have ruined beyond repair with \c
- Open your last command for editing in your default editor with \e
- Rotate your results on their side, displaying a separate line per cell with \G
No related posts.
Related posts brought to you by Yet Another Related Posts Plugin.
Most useful to me is that, like your shell, the mysql console maintains a history file (~/.mysql_history), which can be reverse searched using ctrl-r. Hit that key combination and begin typing the command you want to repeat. When you find something like it, you can hit ctrl-r again to find the next instance of something like it (sort of a ‘reverse search within the search results’). If you find what you want, hit enter. If you find a command you want to edit, hit the right arrow key (or end) and you can edit the command before entering.
Thanks for sharing that! I have been using the history, but haven’t gotten into the habit of using Ctrl + r as I do in Bash. It is a great feature to have available.