L.P. L.P. - 6 months ago 11
MySQL Question

Unix SSH without password

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

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.

Comments