How do you run a single query through mysql from the command line?
SqlMysqlUnixCommand LineSql Problem Overview
I'm looking to be able to run a single query on a remote server in a scripted task.
For example, intuitively, I would imagine it would go something like:
mysql -uroot -p -hslavedb.mydomain.com mydb_production "select * from users;"
Sql Solutions
Solution 1 - Sql
mysql -u <user> -p -e 'select * from schema.table'
(Note the use of single quotes rather than double quotes, to avoid the shell expanding the * into filenames)
Solution 2 - Sql
mysql -uroot -p -hslavedb.mydomain.com mydb_production -e "select * from users;"
From the usage printout:
> -e
, --execute=name
> Execute command and quit. (Disables --force
and history file)
Solution 3 - Sql
here's how you can do it with a cool shell trick:
mysql -uroot -p -hslavedb.mydomain.com mydb_production <<< 'select * from users'
'<<<' instructs the shell to take whatever follows it as stdin, similar to piping from echo.
use the -t flag to enable table-format output
Solution 4 - Sql
If it's a query you run often, you can store it in a file. Then any time you want to [run it][1]:
mysql < thefile
(with all the login and database flags of course) [1]: http://dev.mysql.com/doc/refman/5.0/en/batch-mode.html
Solution 5 - Sql
echo "select * from users;" | mysql -uroot -p -hslavedb.mydomain.com mydb_production
Solution 6 - Sql
From the mysql
man page:
You can execute SQL statements in a script file (batch file) like this:
shell> mysql db_name < script.sql > output.tab
Put the query in script.sql
and run it.
Solution 7 - Sql
As by the time of the question containerization wasn't that popular, this is how you pass a single query to a dockerized database cluster with Ansible, following @RC.'s answer:
ansible <host | group > -m shell -a "docker exec -it <container_name | container_id> mysql -u<your_user> -p<your_pass> <your_database> -e 'SELECT COUNT(*) FROM my_table;'"
If not using Ansible, just login to the server and use docker exec -it ...
part.
MySQL will issue a warning that passing credentials in plain text may be insecure, so be aware of your risks.