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
You should follow me on twitter here or grab rss feed to keep track of new changes.
Featured Articles:
- 30 Handy Bash Shell Aliases For Linux / Unix / Mac OS X
- Top 30 Nmap Command Examples For Sys/Network Admins
- 25 PHP Security Best Practices For Sys Admins
- 20 Linux System Monitoring Tools Every SysAdmin Should Know
- 20 Linux Server Hardening Security Tips
- Linux: 20 Iptables Examples For New SysAdmins
- Top 20 OpenSSH Server Best Security Practices
- Top 20 Nginx WebServer Best Security Practices
- 20 Examples: Make Sure Unix / Linux Configuration Files Are Free From Syntax Errors
- 15 Greatest Open Source Terminal Applications Of 2012
- My 10 UNIX Command Line Mistakes
- Top 10 Open Source Web-Based Project Management Software
- Top 5 Email Client For Linux, Mac OS X, and Windows Users
- The Novice Guide To Buying A Linux Laptop
{ 14 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………..
Great post thanks for sharing. Should really be updated to include Andrew McGregor’s correction.
why “where class” is not working properly? please explain
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.
This could help to avoid password prompt
mysql –user=dbuser –password=’mypassword’ asterisk -e ‘SELECT * FROM cdr’;