CODEWITHSUNDEEP
mysqlsshremote-access
lk121
lk121

Reputation: 1325

Run MySQL query on remote machine through ssh in command line

I am trying to run MySQL query on remote machine with this command:

ssh [email protected] "mysql -uroot -proot -e \"use test";""

I am not able to use that database.

Please suggest a working command.

Upvotes: 37

Views: 82458

Answers (6)

Myke Carter
Myke Carter

Reputation: 376

Running this from my Host environment against MySQL within my Homestead VM produced a nice result... although I did have to set the root password from within the VM first in order for it to work.

ssh [email protected] mysql -h localhost -u root -p -e "'SELECT * FROM user;' mysql";

Upvotes: 1

ling
ling

Reputation: 10019

This worked for me after a few tests (basically same answer as @King-Wzrd):

ssh -t kom "mysql -uroot -p -e 'show databases;'"
ssh -t kom "mysql -uroot -p < /home/ling/websites/jin_test/.deploy/tmp.sql"

The "trick" was the quotes around the command.

The -t option allows for prompting password interactively via the remote shell.

The kom here is just a ssh config identifier defined in my ~/.ssh/config file (see more here: https://www.cyberciti.biz/faq/create-ssh-config-file-on-linux-unix/).

Upvotes: 3

OZZIE
OZZIE

Reputation: 7338

This ended up working for me in a bash script:

query='USE [database]; SELECT ...'   
mysql='mysql -u [username] -p[password] -e '"'""$query""'"
ssh [username]@[server] -t "$mysql"

If you want to make it more safe then add a prompt for the password instead of storing it somewhere potentially unsafe.

Upvotes: 3

King-Wzrd
King-Wzrd

Reputation: 171

Try this:

ssh root@host "mysql database -e 'query to run on table_name; more queries to run;'"

Same can be done with user@host if that user has permission to execute SQL queries let alone launch mysql in general. Using -e is the same as --execute, which will run whatever you put within the trailing quotes (single or double) and quit. The standard output format would be the same as you would see using --batch.

Upvotes: 17

kun tang
kun tang

Reputation: 532

Try this:

mysql -h host -u root -proot -e "show databases;";

Upvotes: 50

Vicky T
Vicky T

Reputation: 1633

MySql seems to have a special command line syntax which includes the database.

mysql -u user -p -e 'SQL Query' database

This documentation is rather old but I got it to work

http://www.cyberciti.biz/faq/run-sql-query-directly-on-the-command-line/

Final working command with ssh:

ssh user@host "mysql -u user -e 'show tables;' databasename"

Upvotes: 6

Related Questions