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

Posted on in Categories , , , , , , , last updated September 9, 2015

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.

Syntax

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

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

OR

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

Where,

  • -u : Specify mysql database user name
  • -p : Prompt for password
  • -e : Execute sql query
  • database : Specify database name

Examples

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:

[client]
# for local server use localhost
#host=localhost
host=10.0.1.100
user=vivek
password=myPassword
 
[mysql]
pager=/usr/bin/less

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin and a trainer for the Linux operating system/Unix shell scripting. He has worked with global clients and in various industries, including IT, education, defense and space research, and the nonprofit sector. Follow him on Twitter, Facebook, Google+.

21 comment

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

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

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

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

Leave a Comment