≡ Menu

MySQL Run SQL Queries From A Shell Prompt / Command Line

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
Tweet itFacebook itGoogle+ itPDF itFound an error/typo on this page?

{ 17 comments… add one }

  • Leopoldo Maestro May 5, 2008, 12:01 am

    Very useful.

    Thank you

  • Andrew McGregor July 27, 2009, 3:27 pm

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

  • Barry July 6, 2010, 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!!

  • damdaray August 19, 2010, 12:39 pm

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

  • Gerald Mutisya October 22, 2010, 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

  • Shravan November 10, 2010, 9:15 am

    Helpful article. Thanks to Vivek Gite

  • Greg November 17, 2010, 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 :)

  • guru January 18, 2011, 12:16 pm

    Its very usefull thak you very much….

  • Shiva May 30, 2011, 10:32 am

    getting error 1045

  • vinod pardhi August 29, 2011, 1:34 pm

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

  • Ryan June 14, 2012, 11:06 am

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

  • arun August 1, 2012, 7:06 am

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

  • Sean September 14, 2012, 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.

  • Ambiorix Rodriguez March 10, 2013, 4:54 am

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

  • T. June 6, 2013, 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.

  • udit February 2, 2015, 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

  • trungvan June 20, 2015, 2:17 am

    work for me (centos 6):
    mysql -u $rootusr -p$rootpw -e “INSERT INTO db_site (name, domain) VALUES (‘$domain’, ‘$domain’)” db_session;

    note: remove chracter: “`” in sql string

Leave a Comment