PeterHarding: New page: 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 fanta...

2008-04-25T05:02:48Z

New page: <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 fanta...

New page

<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]]

Use command-line MySQL for additional flexibility - Revision history

PeterHarding: New page: 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 fanta...