CODEWITHSUNDEEP
mysqlbashssh
Opentuned
Opentuned

Reputation: 1517

Bash script to SSH to a server and connect to DB

I want to test connectivity to DBs via a shell script that will SSH on to a number of boxes and then test the mysql connection. I have SSH keys working so I am not explicitly specifying user@server in the ssh command. The script is not working as i would expect and prompts me for a password but errors and uses the password as a DB name. Can some one please advise?

#!/bin/bash

SERVER_LIST=(172.10.1.1 172.10.1.2 172.10.1.3)
CONNECTION=172.0.0.10
USER="username"
PASS="password"

echo "Testing connectivity... "
for SERVER in ${SERVER_LIST[@]}
do
    echo "SSHing to $SERVER"
    ssh $SERVER "mysql -h $CONNECTION -u $USER -p$PASS"
done
echo "Finished."

After some reading around, should i have a expects command or similar on the password prompt? I have experimented with this to no avail...

Upvotes: 1

Views: 3076

Answers (2)

Sylvain Leroux
Sylvain Leroux

Reputation: 52040

Enter password: password ERROR 1049 (42000): Unknown database 'password'

From the error message, the mysql client erroneously understand password as the DB name. And if it asks for the password, that means it receive the -p option without a password. So, all behave like if the client has received the command mysql .... -p password (notice the space after p).

My guess is there is somehow a space before the password somewhere. Maybe in the following line:

PASS="password"

If you copied-pasted that password, maybe there is an invisible character before (zero-width space?). Try to remove, then hand-write that line.

In addition, try proper quoting (not that proper: this won't work if any of your variables holds a '). At the very least, this might help to narrow the problem:

ssh "$SERVER" "mysql -h '$CONNECTION' -u '$USER' -p'$PASS'"

Upvotes: 1

Nikolay Ivanchev
Nikolay Ivanchev

Reputation: 144

You must export you public key to authorized_keys in your .ssh folder in the server

  1. Generate your keys

    ssh-keygen -t rsa

  2. Create .ssh on the server if it doesn't exists ssh b@B mkdir -p .ssh

  3. Send them to the server

    cat .ssh/id_rsa.pub | ssh b@B 'cat >> .ssh/authorized_keys'

  4. Test your login

    ssh b@B

If anything goes wrong use ssh -v b@B to figure it out

Upvotes: 1

Related Questions