Bash script to SSH to a server and connect to DB

Question

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...

Did your password have specials characters ? Can you show us what you get when you execute script ? — kranteg
– kranteg, Commented Jul 16, 2014 at 13:47
Sure thing Enter password: password ERROR 1049 (42000): Unknown database 'password' — Opentuned
– Opentuned, Commented Jul 16, 2014 at 15:18
First of all, try your mysql command outside of your bash script and outside of ssh. Then test it with the ssh command and then with the bash script. If you don't use root user, I think you have to specify a db name. — kranteg
– kranteg, Commented Jul 16, 2014 at 20:17

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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

Generate your keys

ssh-keygen -t rsa
Create .ssh on the server if it doesn't exists ssh b@B mkdir -p .ssh
Send them to the server

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

ssh b@B

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 16, 2014 at 11:24

Nikolay Ivanchev

1446 bronze badges

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

1 Comment

Opentuned Over a year ago

sorry, i should have mentioned that I have ssh keys to establish the SSH connection, the ssh connection is not the concern as far as I can see as I am able to get onto the boxes, it is the mysql connection that is the issue

Sylvain Leroux · Accepted Answer · 2014-07-18 10:31:55Z

1

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'"

edited Jul 18, 2014 at 10:31

answered Jul 16, 2014 at 21:55

Sylvain Leroux

52.4k8 gold badges114 silver badges136 bronze badges

2 Comments

kranteg Over a year ago

I'm agree with you that's what I asked for special characters in password.

Sylvain Leroux Over a year ago

@kranteg Yes, I've up-voted your comment about that yesterday. I would point out that some "special characters" might indeed be "unintentional" and "invisible". I've already have that kind of issue with users having copied-pasted their login/password from their webmail. For formating purpose, some invisible characters where added and sneaked in with the original text as the user copied it from his browser. This is highly speculative though. And without further feedback of the OP, I think we're stuck from now.

Collectives™ on Stack Overflow

Bash script to SSH to a server and connect to DB

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related