CODEWITHSUNDEEP
mysqlbash
Lurch
Lurch

Reputation: 875

execute mysql on remote server via bash script

I need to execute a mysql command on a remote server but seem to be hitting problem when it comes to executing the actual mysql bit

#!/usr/bin/expect -f
spawn /usr/bin/ssh -t [email protected]
expect "password: "
sleep 1
send "password\r"
sleep 2
/usr/bin/mysql databasename -e "update device_log set status = 'Y' where device_id in ('1','2');"

basically I want to change the flag to Y on device id's 1&2

but the script outputs

invalid command name "/usr/bin/mysql"

Upvotes: 0

Views: 2403

Answers (2)

gertvdijk
gertvdijk

Reputation: 24844

Just append the mysql command to the ssh command to run it in one go, like this:

#!/usr/bin/expect -f
spawn /usr/bin/ssh -t [email protected] /usr/bin/mysql databasename -e "the query"
expect "password: "
sleep 1
send "password\r"

I'm not very much into expect, but I'm expecting that your attempt in the mysql line isn't actually valid syntax for expect to run a command.

Additionally:

  • You should use SSH keys for passwordless login instead of having a root password hardcoded in a script.
  • Consider running MySQL remotely e.g. mysql -h 10.0.0.2 -e "the query", or
  • Use port forwarding in SSH to connect to MySQL securely, e.g. run ssh -L 3307:localhost:3306 [email protected] in the background and then connect to TCP port 3307 on localhost mysql -h 127.0.0.1 -P 3307.

Upvotes: 2

dlaehnemann
dlaehnemann

Reputation: 701

It sounds like /usr/bin/mysql is not the the path to the mysql binary on that remote server. You could use just mysql instead, assuming that the binary is somewhere in that remote server's PATH. Otherwise you will have to go and find out where the binary is actually located and alter the absolute path accordingly.

Upvotes: 0

Related Questions