Search code examples
mysqlbashsshheredoccommand-substitution

BASH: SSH into server, query MYSQL Funny stuff

I am having some trouble with a script of mine that is supposed to SSHs into my server, run a mysql query, and return a specific value.

My problem is that I am getting an Access Denied error when I use command substitution [ $() and `` ] - but without command substitution I don't get the result saved to a variable.

I have full privileges, and the password is correct. 

  ssh root@SERVER.com  << EOFMARK
    example=$(mysql -h localhost -u root -p$MYSQL_PASSWORD myDatabase -e "SELECT now();")  
    echo "Variable is $example"
  EOFMARK

echo "My goal is to read the variable here: $example"
Solution

  • You might want to

    • immediately execute the command and
    • give it the query it is supposed to execute.

    This way:

    example=$(ssh root@SERVER.com mysql -h localhost -u root -p"$MYSQL_PASSWORD" myDatabase -e '"SELECT now();"')
echo "My goal is to read the variable here: $example"

    would be the way to go.

    If you really need it at both places, you either might want to have a script on the server, or you could try

    example=$(ssh root@SERVER.com 'example=$(mysql -h localhost -u root -p"$MYSQL_PASSWORD" myDatabase -e "SELECT now();"); echo "Variable is $example")
echo "My goal is to read the variable here: $example"

    In order to stick on the heredoc, you could as well try

    example=$(ssh root@SERVER.com  << EOFMARK
    example=$(mysql -h localhost -u root -p$MYSQL_PASSWORD myDatabase -e "SELECT now();")  
    echo "Variable is $example"
EOFMARK)

echo "My goal is to read the variable here: $example"

    in order to have the variable on the client side as well.

    Due to whatever, I can't get my head around the heredoc example.

    But the others should look like

    example=$(ssh root@SERVER.com 'mysql -NB -h localhost -u root -p"$MYSQL_PASSWORD" myDatabase -e "SELECT now();"')
echo "My goal is to read the variable here: $example"

    which gives exactly the output into the local variable, or

    example=$(ssh root@SERVER.com 'example=$(mysql -NB -h localhost -u root -p"$MYSQL_PASSWORD" myDatabase -e "SELECT now();"); echo "Variable is $example")
echo "My goal is to read the variable here: $example"

    but you should be aware that here, the local output looks like (e. g.)

    My goal is to read the variable here: Variable is 2014-03-07 20:42:23

    as the sub string Variable is gets output as well.