When I first started using Mysql, I relied heavily on the Phpmyadmin in-browser control panel for database administration.
Eventually the feeling of reliance on the GUI became became so unbearable that I switched to using the command line mysql client to communicate with the database server.
Here is how I use the the Mysql cli with Vim to accomplish tasks such as user creation, and search-replace operations.
Why use the Mysql cli
Using the command line interface offers offers several benefits. Visually there are less distractions when compared with.
I find that I pay much more attention to each keypress and am inclined to pay attention to every character of output the cli displays.
The cli experience is also snappier being ‘closer’ to the databse, and feels more interactive.
Though I don’t mind using Phpmyadmin, it always feels good to eliminate a possible point-of-failure, even with such ubiquitous tool where bugs are unlikely.
A few months ago when I wrote this post, I thought this might be a stretch to suggest,
but it looks like increased security is indeed a legitimate reason to choose the CLI.
Check out this article on LinuxSecurity.com
MySql Vi Config
To set the mysql pager to vim( or less, nano etc.) Simply enter the following command (including the minus sign ):
\P vi -
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
Vim will open each query result in a temporary file which you can then navigate/search/edit like any other text file.
It is also possible to use the \e flag to enter editing mode and then use vim to structure your query before executing it.
edit (\e) Edit command with $EDITOR.
I use the “HELP” query to obtain the proper syntax of unfamiliar commands. It’s usually quicker than switching tabs to search the online documentation. It’s also great to have the results in vim so that the text can be quickly manipulated in-place to form the next query.
To view the same results using Phpmyadmin, I had to select the “Full texts” option under “Options”.
PhpMyAdmin is a great choice for those uninterested in becoming comfortable on the command line.
However, for the rest of us, choosing to use the cli version will eventually lead to more efficient database use.
Also vim users enjoy the added benefit of stacking skills by using vim as the primary pager.
It’s a great way to maintain the familiarity with the editor’s options and abilities, which comes only from frequent use.