Open Source

Use command-line MySQL for additional flexibility

The MySQL GUI and phpMyAdmin provide great administration tools for handling your database, but the mysql command-line program works quite well also and is more flexible.

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 customize 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 customize 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.

About

Vincent Danen works on the Red Hat Security Response Team and lives in Canada. He has been writing about and developing on Linux for over 10 years and is a veteran Mac user.

9 comments
Photogenic Memory
Photogenic Memory

I've been struggling to learn mySQl for sometime. I'm like totally intimidated by it. However, I'll cope and learn if I can just get past the gate keeper. This is my error: ]# mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) It's pretty self explanatory ( I think ). So no matter what I do I just can't get this !@#$%^ software to run on my box. These are my specs: #uname -a Linux Plutonium 2.6.9-67.0.7.EL #1 Sat Mar 15 06:19:12 EDT 2008 i686 athlon i386 GNU/Linux ========================== #cat /etc/redhat-release CentOS release 4.6 (Final) ========================= #rpm -q mysql mysql-4.1.20-3.RHEL4.1.el4_6.1 Perhaps I should just scrap the box and upgrade to CentOS 5.1. But I have a strong feeling this isn't going to help. The reason I'm asking for help is that I desired to install cacti or nagios on my own home network. It's just for fun and learning. No matter how much I decend into dependency hell finding abstract binaries to install a program that needs mySQL database programming to help it generate output; I run into this error. Can anyone please help me and speak to me in understandable instructions? I'm so lost.

garnerl
garnerl

Parsing mysql output from scripts is indeed handy, but the best option in my opinion is "-BN". This eliminates the column names and lines and changes +----+-----------+----------+ | id | FirstName | LastName | +----+-----------+----------+ | 1 | John | Doe | | 2 | Jane | Doe | +----+-----------+----------+ to: 1 John Doe 2 Jane Doe which is much easier to handle in a script.

vdanen
vdanen

Usually that error means mysqld isn't running. Did you actually start it? I.e.: "service mysqld start" would be the command on a Mandriva box (I assume CentOS would be the same although the service/initscript name may differ slightly). Oh, and you don't need MySQL to use nagios (cacti I think you might tho).

vdanen
vdanen

Looks like the system didn't escape the "\\" character in the original posting. Double-checked the original and those slashes are definitely there.

Photogenic Memory
Photogenic Memory

Yeah, it's simlar to the linux command PS1 menu that can be seen like this: #set | grep PS1 PS1='[\u@\h \W]\$ ' # This mean the same thing except the "W" mean present working directory.

Photogenic Memory
Photogenic Memory

I tried what you wrote and failed. I got the same error I had before but here's the catch. You have to do this first: yum install mysql-server Once this is done; then launch the mysql daemon like this: /etc/init.d/mysqld start Now I can launch the launch the database interface by simply typing: mysql And away we go! I can start to learn how to make and manipulate tables. I'm so glad you pointed my in some direction. Really! Even though it didn't help; it allowed my to think and search for the answers. THANK YOU! I found the answers here: https://www.centos.org/modules/newbb/viewtopic.php?forum=19&topic_id=2199&viewmode=flat

wratholix
wratholix

You must be new to this :) its important to understand atleast the basics of package(software) management on your linux machine so you can do what you like. There are a couple distro's that have easy package management. I believe Gentoo & Debian are the most convenient... Personally my choice goes to FreeBSD :)