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
Featured Articles:
- 20 Linux System Monitoring Tools Every SysAdmin Should Know
- 20 Linux Server Hardening Security Tips
- My 10 UNIX Command Line Mistakes
- Linux: 20 Iptables Examples For New SysAdmins
- 25 PHP Security Best Practices For Sys Admins
- The Novice Guide To Buying A Linux Laptop
- 10 Greatest Open Source Software Of 2009
- Top 5 Email Client For Linux, Mac OS X, and Windows Users
- Top 20 OpenSSH Server Best Security Practices
- Top 10 Open Source Web-Based Project Management Software
- Top 5 Linux Video Editor Software
Facebook it - Tweet it - Print it -
{ 10 comments… read them below or add one }
Very useful.
Thank you
One correction is that in Linux then options file must be called:
~/.my.cnf
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!!
thanks for helpful working. ı looked for a long time this soul’s answer.thanks again…
Try using double quotes for the SQL statement – worked for me.
i.e. mysql -e “select * from users where fname=’Dave’” dbname
Helpful article. Thanks to Vivek Gite
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 :)
Its very usefull thak you very much….
getting error 1045
Thank you very much, it saved lot of time of mine………..