Use command-line MySQL for additional flexibility

by Vincent Danen, TechRepublic

The more popular ways of using MySQL are to use GUI front ends, such as phpMyAdmin or the MySQL GUI administration tools. While these tools are fantastic and make managing a MySQL database easier, the mysql command-line program works quite well also, and is more flexible.

You can customise the look of the mysql prompt, which may ease using it somewhat as the default prompt is simply mysql>, which is hardly informative. You can customise this to show the connected username, host, and current database using:

mysql> prompt mysql (u@h)::d >_ 
PROMPT set to 'mysql (u@h)::d >_'
mysql (root@localhost)::db1 >

To make this setting permanent, edit the ~/.my.cnf file and add:

[mysql] 
prompt=mysql (u@h)::d >_

Another nice and quick use for the mysql command-line client is that you can call and parse MySQL output from shell scripts without interactive sessions. For instance:

$ mysql -u me --password=secret wiki -e "select count 
(page_id) from page"

+----------------+ 
| count(page_id) |
+----------------+
|            131 |
+----------------+

If you wish to use something easier to parse, use a vertical format by appending the \G identifier at the end of the query:

$ mysql -u me --password=secret wiki -e "select count 
(page_id) from pageG"

*************************** 1. row 
***************************

count(page_id): 131

Since passing the password on the command-line could be picked up by other processes if the Linux kernel doesn't support process hiding, consider defining it in the ~/.my.cnf file:

[mysql] 
user = me 
password = secret

Make sure that ~/.my.cnf is mode 0600 so only the user can read (and write to) the file. With that setting, user and password requirements can be omitted, so the above could be executed as:

$ mysql wiki -e "select count(page_id) from page\G"

Of course, if you want to connect to MySQL as a different user, simply supply the -u option with the appropriate username and the -p option to prompt for a password. What is defined in ~/.my.cnf is just a default.