2

I have the unfortunate situation, that I don't have the permission to access the MYSQL database from outside the server. But SSH is possible. Therefore I try to run a simple SQL statement from a bash file, that creates a SSH connection, connects to the MYSQL DB and run the SQL statement.

The syntax is pretty straight forward but I'm not able to use them combined in one bash file, but on the command line each individual is working

that the snippets I'm using:

1) establish the SSH connection:

$:sshpass -p my_password ssh my_user@my_server

2) connect to the MYSQL DB:

my_server>mysql -h rdbms -u db_user -D db_name -p db_password

3) run the SQL statement

mysql>SELECT * FROM table

... as said. all good when running on command line.

But when I combine them into a bash file:

#!/usr/bin/
sshpass -p my_password ssh my_user@my_server
mysql -h rdbms -u db_user -D db_name -p db_password
SELECT * FROM table

It stops right after the first line (establishing the SSH connection). Any ideas how I can combine these?

Improve this question

1 Answer 1

Reset to default
1

To run a command on a remote server via ssh, you need to list the command as arguments on the same command-line.

For example:

sshpass -p my_password ssh my_user@my_server date

That will run date on the remote server, and return the output of that command.

You can run the mysql client this way too. Just put the mysql command on the same command-line, as arguments to your ssh.

sshpass -p my_password ssh my_user@my_server mysql ...arguments...

You can use \ at the end of a line to continue a long command on the following line. It works as if you had written the full command on one very long line, but it's easier to post in Stack Overflow so readers don't have to scroll horizontally to read it. :-)

Also note that the remote command must be in quotes so it appears like a single argument to ssh. When it runs on the remote server, it will be expanded to multiple arguments.

sshpass -p my_password ssh my_user@my_server \
 "mysql ...arguments..."

The mysql client has an option -e that you can use to execute an SQL statement.

sshpass -p my_password ssh my_user@my_server \
 "mysql -h rdbms -u db_user -D db_name -pdb_password -e 'SELECT * FROM table'"

A couple of tips about the password:

  1. There must be no space between -p and the password. If you use -p with a space after it, you will be prompted for the password interactively. The word following the space is not taken as the password unless you stick it against the -p.

  2. I don't like to put passwords in plaintext on the command-line. It's not safe to do that, because anyone who can access your shell history can view the password. It's better to use an option file or a login file. But you'll have to put these on the remote server where the mysql client runs.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

yes totally upvote for the password topics! I'm actual following your guidelines to that already. For demonstration purposes I try to keep it short in the question ;-)
Okay, that's good to hear. I hope other readers will also read the tips and follow them too!
but I'm still struggling with the actual answer. Yes the 'date' argument is working fine. And the mysql statement with the '-e my_query' is working as well. But both (ssh + mysql combined) is not working. It shows the same content as 'mysql --help' but not executing the query. Everything needs to be in "" after the ssh server.
Okay, I have edited the above answer to show the quotes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.