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 |
21 comment
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’;
Very useful, especially with double quote (if you want to pass variables from bash to
mysql, for example). Thank you very much.
i used the history command to see the previously executed commands how can i remove all the contents without actually deleting the file or without using rm command
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
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.
Try single quotes and –?
mysql -uUSER -pPASSWORD -e 'select * from this-test' dbnameOR
mysql -uUSER -pPASSWORD -e -- 'select * from this-test' dbnamecan we have the connection idle without closing after the query is executed?
If I’m getting the count(*), is there any way to have it written to a variable in my shell script or does it have to go to a file and then be parsed?