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.
6 Answers
Try this:
mysql -h host -u root -proot -e "show databases;";
3 Comments
FreeSoftwareServers
For Scripting I'd look into using ~/my.cnf or MYSQL_PWD Variable >> see unix.stackexchange.com/questions/227648/…
Horsty
I think the last semicolon is not necessary
FRa
To me the right answer is the one from @King-Wzrd. This one lack the part of ssh command and doesn't give indication about quotes which are the real problem to solve the question
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.
1 Comment
mmv-ru
Good description why problem happens and quotes required unix.stackexchange.com/a/212298/18674
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"
1 Comment
Myke Carter
Thanks! The higher responses weren't working for me but yours did. Personally I prefer to use " -D databasename " before the " -e query " simply because it's more explicit - but both worked for me like a charm. I appreciate the help!
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.
1 Comment
Swapnil Gangrade
Hi @OZZIE the output is having special character like + and - +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ how to get ride of this in query
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/).
Comments
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";
sshcommand and not passed to mysql. Don't you get an error message from mysql?