MySQL Run SQL Queries From A Shell Prompt / Command Line

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


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

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

# for local server
Tweet itFacebook itG+ itDownload PDF versionFound an error/typo on this page?

{ 16 comments… read them below or add one }

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

Very useful.

Thank you


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

One correction is that in Linux then options file must be called:


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


4 damdaray August 19, 2010 at 12:39 pm

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


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


6 Shravan November 10, 2010 at 9:15 am

Helpful article. Thanks to Vivek Gite


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

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


8 guru January 18, 2011 at 12:16 pm

Its very usefull thak you very much….


9 Shiva May 30, 2011 at 10:32 am

getting error 1045


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

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


11 Ryan June 14, 2012 at 11:06 am

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


12 arun August 1, 2012 at 7:06 am

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


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.


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';


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.


16 udit February 2, 2015 at 10:41 am

i used the history command to see the previously executed commands how can i remove all the contents without actually deleting the file or without using rm command


Leave a Comment

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

Previous Faq:

Next Faq: