214

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;"
Matthew
  • 5,435
  • 6
  • 25
  • 29

7 Answers7

332
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)

Neilski
  • 85
  • 10
RC.
  • 27,409
  • 9
  • 73
  • 93
  • 3
    C:\Program Files (x86)\MySQL\MySQL Server 5.7\bin>mysql.exe -u root -p -e "my query"---->ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) – Dr.jacky Mar 12 '17 at 11:38
  • 3
    This should be the first result in a google search (for "mysql exec sql from command line") and not the huge mysql site! – Guillaume Bois Apr 04 '17 at 17:48
  • 1
    I don't know why but double quotes was not working for me, had to turn them into single quotes – Developerium Apr 27 '17 at 08:19
  • 4
    Also, if you want to strip the header and table format you can use `mysql -u -p -B --disable-column-names -e 'select * from schema.table'` – dvlcube Oct 11 '17 at 18:40
  • 2
    @tinybyte Assuming Linux: Insinde doublequotes the asterisk `*` gets expanded to all files in the current directory, while this expansion does not happen in singlequotes, thats the reason why. Doublequotes will work for quesries without the `*`. – NobbZ Apr 03 '18 at 07:39
  • 1
    This worked for me, except I'm new at this, and didn't realize if you want to pass the actual password you need to use -p1234 not -p 1234. Very poor security practice I'm sure, but useful for dev. – Michael Feb 05 '19 at 17:03
  • 5
    @Dr.jacky I'm sure you don't really have the need any more, but for the future, if your root user has no password, dont pass -p option as blank, just dont pass it at all i.e. `mysql.exe -u root -e "my query"` – solidau Jun 25 '19 at 16:29
  • Great answer but the double quote thing is a clear concern, given that the question is tagged with Unix. I've submitted an edit to address that... – Neilski Oct 10 '21 at 18:53
  • Can I avoid the `SyntaxError: Unexpected identifier at` because of - probably - js mode which is the default starting mode? – Timo May 17 '22 at 16:15
  • This just prints out the usage for me – Koops128 Sep 01 '23 at 21:04
38
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)

Soviut
  • 88,194
  • 49
  • 192
  • 260
John Kugelman
  • 349,597
  • 67
  • 533
  • 578
21

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

ʞɔıu
  • 47,148
  • 35
  • 106
  • 149
16

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)

dnagirl
  • 20,196
  • 13
  • 80
  • 123
8
echo "select * from users;" | mysql -uroot -p -hslavedb.mydomain.com mydb_production
Oct
  • 1,505
  • 13
  • 18
1

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.

RicHincapie
  • 3,275
  • 1
  • 18
  • 30
0

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.

cdmo
  • 1,239
  • 2
  • 14
  • 31