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][/donotprint]

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

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

Posted by: SXI ADMIN

The author is the creator of SXI LLC and a seasoned sysadmin, DevOps engineer, and a trainer for the Linux operating system/Unix shell scripting. Get the latest tutorials on SysAdmin, Linux/Unix and open source topics via RSS/XML feed or weekly email newsletter.