View source for Use command-line MySQL for additional flexibility

<i>by Vincent Danen, TechRepublic</i>

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:

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

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

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

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:

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

+----------------+ 
| count(page_id) |
+----------------+
|            131 |
+----------------+
</pre>

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

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

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

count(page_id): 131
</pre>

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:

<pre>
[mysql] 
user = me 
password = secret
</pre>

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:

<pre>
$ mysql wiki -e "select count(page_id) from page\G"
</pre>

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.



[[Category:Technet]]
[[category:Whitepapers]]