Unix SSH without password

Question

Hey all I'm completely new to Unix and I need to write up a "shell script" (?) to connect to another terminal and run a few SQL queries. How on earth do I do this? I've been browsing a few answers from this and other boards and if I have found my answer I don't understand it.

I am able to manually connect, enter password, etc, but I need to automate the process. I don't have access to Perl (as a few answers have suggested) and I am unable to edit the etc/shadow file. So I assume this has to be done strictly through Unix itself. This is what I am currently using:

X=`vUser='USER-NAME'
vPass='PASSWORD'
vTable='TABLENAME'

vHOST='HOST-NAME'

vPORT=4443

ssh root@vHost
expect {
    "root@the-host password:"{
        send -s "'vPass'\r"
    }
}

SQL_Query='select * from vTable limit 10'

mysql -p$vPASS -D$vTable -u$vUser P$vPort<<EOF 

$SQL_Query
EOF`
echo $X>Output.dat

Please explain all answers in full. I'm trying to learn.

Answer 1

There is a Linux command ssh-copy-id will do this for you, it is also available to Mac as a homebrew formula.

Answer 2

Try this:

1. Copy your SSH public key to your clipboard (output of cat ~/.ssh/id_rsa.pub). If you don't have an SSH key pair then generate it with this tutorial.
2. Paste your public key to your server's ~/.ssh/authorized_keys file. If it doesn't have one, create it with nano ~/.ssh/authorized_keys and paste it there.

3. In your computer, you can run the script in the server with the following command:

   ssh user@server_ip 'bash -s' < local_script.sh
   

   Or if you have a single command to run then this will do:

   ssh user@server_ip "echo Test | tee output.log"
   

4. If you don't like SSH asking you for the password all the time, use ssh-agent

For SQL-specific scripts, you can put all your SQL commands in a single file, say query.sql. You should copy query.sql to your server (scp query.sql user@server_ip:~/) and then run

ssh user@server_ip "mysql -uyourusername -pyourpassword < query.sql | tee output.log"

The output will be saved in output.log. Check this answer too.

Answer 3

Might not be a 100% relevant but I had to do the same on Linux.

First off you want to make a new user account on the other server that has SSH access and generate an SSH keypair,
even if you're going to do this as root,
keypairs are far superior over standard passwords because they're stronger,
and they allow you to log in automatically over SSH.

There's no real way of automating the password entry process (at least, not on Linux), hence the reason SSH keys are required to do this.

You can basically send a chain of commands as a parameter to the SSH tool.
Like so,
ssh user@host "ls; cat *; yes;"
Hope that helped.