MySQL Run SQL Queries From A Shell Prompt / Command Line

by on May 4, 2008 · 15 comments· LAST UPDATED May 4, 2008

in

Q. How do I specify MySQL SQL queries on the UNIX / Linux command line?

A. mysql command line has option to execute the SQL statement and quit. This is also useful for running sql queries from a shell script. Following is the syntax:

mysql -u user -p -e 'SQL Query' database

Where,
-u : Specify mysql database user name
-p : Prompt for password
-e : Execute sql query
database : Specify database name
To list all database, enter:
$ mysql -u vivek -p -e 'show databases;'
To list count all rows, enter:
$ mysql -u vivek -p -e 'SELECT COUNT(*) FROM quotes' cbzquotes
Sample output:

Enter password:
+----------+
| count(*) |
+----------+
|      471 |
+----------+

Tell mysql to display output a page at a time, by using more or less pager:
$ mysql --pager=/usr/bin/less -u vivek -p -e 'SELECT COUNT(*) FROM quotes' cbzquotes
Redirect out to a file:
$ mysql -u vivek -p 'PassWord' -e 'SELECT COUNT(*) FROM quotes' cbzquotes > sql.output.txt
To avoid password prompt just create ~/my.cnf file:

[client]
# for local server
#host=localhost
host=10.0.1.100
user=vivek
password=myPassword
[mysql]
pager=/usr/bin/less
TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 15 comments… read them below or add one }

1 Leopoldo Maestro May 5, 2008 at 12:01 am

Very useful.

Thank you

Reply

2 Andrew McGregor July 27, 2009 at 3:27 pm

One correction is that in Linux then options file must be called:
~/.my.cnf

Reply

3 Barry July 6, 2010 at 6:08 pm

Very helpful, but any idea how to get a statement line:

select * from users where fname=’Dave’

to work? I just cannot find a way to eacape the quote characters and it’s driving me nuts!!

Reply

4 damdaray August 19, 2010 at 12:39 pm

thanks for helpful working. ı looked for a long time this soul’s answer.thanks again…

Reply

5 Gerald Mutisya October 22, 2010 at 5:42 pm

Try using double quotes for the SQL statement – worked for me.
i.e. mysql -e “select * from users where fname=’Dave’” dbname

Reply

6 Shravan November 10, 2010 at 9:15 am

Helpful article. Thanks to Vivek Gite

Reply

7 Greg November 17, 2010 at 12:29 am

I wish I had read these comments earlier, that double quote requirement wasted about 30 minutes of my time today..lol

Oh well… and thanks for the article… I’m using it in a perl script via a system call :)

Reply

8 guru January 18, 2011 at 12:16 pm

Its very usefull thak you very much….

Reply

9 Shiva May 30, 2011 at 10:32 am

getting error 1045

Reply

10 vinod pardhi August 29, 2011 at 1:34 pm

Thank you very much, it saved lot of time of mine………..

Reply

11 Ryan June 14, 2012 at 11:06 am

Great post thanks for sharing. Should really be updated to include Andrew McGregor’s correction.

Reply

12 arun August 1, 2012 at 7:06 am

why “where class” is not working properly? please explain

Reply

13 Sean September 14, 2012 at 11:19 am

This post saved my butt. I had to edit about 1500 records between 2 databases and this made it happen in a hour instead of about 2 weeks.

Reply

14 Ambiorix Rodriguez March 10, 2013 at 4:54 am

This could help to avoid password prompt
mysql –user=dbuser –password=’mypassword’ asterisk -e ‘SELECT * FROM cdr';

Reply

15 T. June 6, 2013 at 7:49 am

Very useful, especially with double quote (if you want to pass variables from bash to
mysql, for example). Thank you very much.

Reply

Leave a Comment

Tagged as: , , , , , , , , , , , , ,

Previous Faq:

Next Faq: