MySQL/MariaDB: Run SQL Queries From A Shell Prompt / Command Line

How do I specify or run MySQL or MariaDB SQL queries on the UNIX or Linux command line?

The mysql command line has option to execute the SQL statement and quit. This is also useful for running sql queries from a shell script or the bash prompt. [donotprint]
Tutorial details
Difficulty level Easy
Root privileges No
Requirements mysql
Est. reading time 2m


The syntax is as follows for both MariaDB and MySQL client:

mysql -u user -p -e 'Your SQL Query Here' database-name


mysql -u USER -p PASSWORD -h MYSQLSERVERNAME -e 'select * from foo...' database-name


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

Say hello to MYSQL configuration file

To avoid password prompt just create ~/.my.cnf file as follows:

# for local server use localhost

🐧 Get the latest tutorials on Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

🐧 21 comments so far... add one

CategoryList of Unix and Linux commands
Disk space analyzersdf ncdu pydf
File Managementcat cp mkdir tree
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network UtilitiesNetHogs dig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
21 comments… add one
  • Leopoldo Maestro May 5, 2008 @ 0:01

    Very useful.

    Thank you

  • Andrew McGregor Jul 27, 2009 @ 15:27

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

  • Barry Jul 6, 2010 @ 18:08

    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 Aug 19, 2010 @ 12:39

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

  • Gerald Mutisya Oct 22, 2010 @ 17:42

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

  • Shravan Nov 10, 2010 @ 9:15

    Helpful article. Thanks to Vivek Gite

  • Greg Nov 17, 2010 @ 0:29

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

  • guru Jan 18, 2011 @ 12:16

    Its very usefull thak you very much….

  • Shiva May 30, 2011 @ 10:32

    getting error 1045

  • vinod pardhi Aug 29, 2011 @ 13:34

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

  • Ryan Jun 14, 2012 @ 11:06

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

  • arun Aug 1, 2012 @ 7:06

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

  • Sean Sep 14, 2012 @ 11:19

    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 Mar 10, 2013 @ 4:54

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

  • T. Jun 6, 2013 @ 7:49

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

  • udit Feb 2, 2015 @ 10:41

    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 Jun 20, 2015 @ 2:17

    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

  • Troy Frech Sep 11, 2015 @ 14:13

    This does not work…
    mysql -uUSER -pPASSWORD -e “select * from this-test” dbname

    …because of the “-” in the query. Is there an escape character to allow for dashes in a query? The same query works fine if I log in to mysql and enter it at the mysql prompt but it doesn’t work from a Linus prompt.

    • 🐧 Vivek Gite Sep 11, 2015 @ 15:14

      Try single quotes and –?
      mysql -uUSER -pPASSWORD -e 'select * from this-test' dbname
      mysql -uUSER -pPASSWORD -e -- 'select * from this-test' dbname

  • raghu varma Mar 15, 2016 @ 10:08

    can we have the connection idle without closing after the query is executed?

  • sdag Mar 16, 2017 @ 19:53

    If I’m getting the count(*), is there any way to have it written to a variable in my shell script or does it have to go to a file and then be parsed?

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre> for code samples. Still have questions? Post it on our forum