CODEWITHSUNDEEP
sqlmysqlunixcommand-line
Matthew
Matthew

Reputation: 5825

How do you run a single query through mysql from the command line?

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;"

Upvotes: 247

Views: 242492

Answers (7)

RicHincapie
RicHincapie

Reputation: 3973

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.

Upvotes: 2

RC.
RC.

Reputation: 28207

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)

Upvotes: 375

cdmo
cdmo

Reputation: 1309

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.

Upvotes: 0

John Kugelman
John Kugelman

Reputation: 361605

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)

Upvotes: 40

dnagirl
dnagirl

Reputation: 20456

If it's a query you run often, you can store it in a file. Then any time you want to run it:

mysql < thefile

(with all the login and database flags of course)

Upvotes: 16

ʞɔıu
ʞɔıu

Reputation: 48416

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

Upvotes: 24

Oct
Oct

Reputation: 1525

echo "select * from users;" | mysql -uroot -p -hslavedb.mydomain.com mydb_production

Upvotes: 9

Related Questions