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
Subscribe to our free e-mail newsletter or RSS feed to get all updates.
You can Email this page to a friend.
Related Linux / UNIX FAQ:
- Howto: Use mysql or run mysql queries from shell script
- Configure sendmail as a smart host
- Mysql remove duplicate data or rows with DISTINCT
- Turn on or enable BIND DNS server logging to see all queries or for troubleshooting problem
- Connect to SQL Server from command prompt - list tables and database
Discussion on This FAQ
Leave a Reply
We encourage your comments, and suggestions. But please stay on topic, be polite, and avoid spam. Please do not use the comment form to ask for help / question. Ask your question on the excellent Linux tech support forum. Thank you very much for stopping by our site!
Tags: client host, database user, mysql command, mysql command line, mysql database, query database, select count, shell script, sql queries, sql query, sql statement, UNIX, unix command line, ~/my.cnf ~ Last updated on: May 4, 2008
May 5th, 2008 (2 weeks ago) at 12:01 am
Very useful.
Thank you