Decorate the mysql-client output in the console

Color and sound are those small pleasures that can decorate and facilitate the administrator's workday during constant work with the console. The output of color information is governed by the so-called escape sequences, which determine, among other things, the text color and the background color.

General view: \033[Xm , where X is the parameter value (digit). For example, echo -ne "\033[34mHELLO" will display “HELLO” in blue. A table of colors and other available parameters (underlining, flashing, etc.) can be obtained from the documentation man console_codes in the section "ECMA-48 Set Graphics Rendition". Typically, color support is integrated into the application itself, but mysql-client is not one of these programs.

On the Internet, there was more than once a question about decorating the console mysql, but nowhere was there a recipe. Only the general words “can concoct a wrapper” or “look in the source code”. Such a question on StackOverflow lived without an answer for more than 2 years! “Lived” was specifically used in the past tense, because the answer was found.
')
The grc utility will help us. It is available in most distros, and many people know about it. But how to wrap the output of mysql-client?



The grc utility (Generic Colorizer) is actually a grcat wrapper that runs the specified command and sends the output to grcat according to the configuration. We will need grcat directly, for which a config will be written, as well as a small mysql-client setting.

What is the problem just to send the output of mysql-client to grcat? Or just use grc mysql ...? That mysql-client recognizes the environment from which the command was invoked. If it is used interactively, a table layout is displayed. If the call was from a script, or the data stream was sent to another program, it removes the table layout from the output and uses tabulation. A simple example:

 $ mysql test -e "select * from test_table" +----+-------+ | id | value | +----+-------+ | 1 | a | +----+-------+ 

 $ mysql temp -e "select * from test_table" | cat - id value 1 a 

In the first case, we use the output in the console, and the table is displayed. In the second, we redirect the output via cat, and the table disappears in a magical way.

As the user suggested truezemez in the comments , table output can be saved using the option - --table . In this case, redirecting output to grc will also work.

And a black raincoat reading mysql documentation comes to the rescue. It turns out that in mysql-client you can set the parameter PAGER, which is responsible for displaying the results on the screen. We also will use them, having registered there grcat. Do not forget to specify the configuration file. Here is a snippet of the ~ / .my.cnf file:

 [mysql] pager = grcat ~/.grcat 

It is not necessary to set PAGER in the config, you can enter it each time with your hands, but this is an amateur

 mysql> pager grcat ~/.grcat PAGER set to 'grcat ~/.grcat' 

All that is left for us to do is to issue a config for grcat so that it recognizes the mysql tables in the output and applies a color scheme to them. Above, we indicated that we should look for it along the path ~ / .grcat. The config consists of groups of parameters, regexp and colors are required. Parameters are separated by a minus, and the pound is comments. The utility checks the match of the regular expression with the current line and applies the specified color to the result of the match. If the parameter count=stop is specified in the configuration, then if the regular expression matches, the program proceeds to the next line of the displayed text. In the absence of count=stop specified settings are applied sequentially. Such a configuration can be obtained:

 #        \G regexp=[*]+.+[*]+ count=stop colours=white - #  regexp=[+\-|]+ colours=red - #    regexp=[\w\.]+ colours=green - #   ( )   ' ' regexp=\([\w\d,']+\) colours=white - # regexp=\s[\d\.]+\s colours=yellow - #       \G regexp=\w+: colours=white - # regexp=\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2} colours=cyan - #IP regexp=(\d{1,3}\.){3}\d{1,3}(:\d{1,5})? colours=cyan - #,        ` ` regexp=`\w+` colours=yellow - #email regexp=[\w\.\-_]+@[\w\.\-_]+ colours=magenta 

The grcat utility itself is written on python, amateurs will be able to peep the code and write the config to fit their needs. For the rest, there is the man grc documentation and a good guide with examples .

Configuration files are available on github . The same link is indicated in the answer to the above question on StackOverflow

And some nice things about mysql-client:

Horizontal scrolling results
(and vertical too, in fact) from the tips of the nekt and Daedmen users :
pager = grcat ~/.grcat | less -RS
The -S flag activates horizontal scrolling, -R flips colors.

More interesting flags for less, thanks to wickedweasel
-F exits less if everything fits on one screen.
-i ignore case for search
-n removes line numbers
-X does not clear the screen after exiting less, which is useful in the console - you can see the results of the previous query

What else can you do with pager:
www.mysqlperformanceblog.com/2008/06/23/neat-tricks-for-the-mysql-command-line-pager
Setting a friendly greeting in mysql-client:
www.ultraquantix.com/blog/2008/12/making-the-mysql-prompt-more-useful