BASH: SSH into server, query MYSQL Funny stuff

Question

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 [email protected]  << 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"

Please check if you are using password that has privileges on localhost..you can check by show grants for root@'localhost' and your password in script should be same as password by this command. — Zafar Malik
– Zafar Malik, Commented Mar 7, 2014 at 7:29

glglgl · Accepted Answer · 2014-03-07 19:45:51Z

1

You might want to

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

This way:

example=$(ssh [email protected] 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 [email protected] '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 [email protected]  << 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 [email protected] '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 [email protected] '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.

edited Mar 7, 2014 at 19:45

answered Mar 7, 2014 at 8:05

glglgl

91.5k13 gold badges157 silver badges230 bronze badges

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

3 Comments

JHAWN Over a year ago

I am adapting my script to this answer but it apparently has a few rough edges. For a bash noob I'm pretty stuck in escaping everything. Could you address this issue?

glglgl Over a year ago

@JHAWN Which of the 3?

JHAWN Over a year ago

The last one would be the best, though all three work. (Ignoring the escaping issues)

Raul Andres · Accepted Answer · 2014-03-07 08:42:40Z

1

You should escape the substitutions that has to be done at remote host:

Try:

 ssh [email protected]  << EOFMARK
    example=\$(mysql -h localhost -u root -p$MYSQL_PASSWORD myDatabase -e "SELECT now();")  
    echo "Variable is \$example"
  EOFMARK

You can follow @glglgl advise, and capture standard output of your ssh command into a local variable. But I would use a local mysql client and I would grant privileges to access database. If you have a firewall that prevents you from remote accesing 3306, I would ssh forward port.

example=$(mysql -h SERVER.com -u root -p myDatabase -e "SELECT now();")

That's the right approach. No ssh, and interactive password, no accesible via ps -ef| grep mysql

edited Mar 7, 2014 at 8:42

answered Mar 7, 2014 at 7:27

Raul Andres

3,81617 silver badges24 bronze badges

1 Comment

JHAWN Over a year ago

This works perfectly from within the scope of the SSH heredoc, however I need access to the variable from outside of the heredoc as well. Could you advise?

Collectives™ on Stack Overflow

BASH: SSH into server, query MYSQL Funny stuff

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related