1

I don't have remote access to a MySQL server, so I am trying to do it via an SSH session.

It partly works, but not correctly.

sshpass -p $password ssh user@$IP /bin/bash << EOF
mysql -N -uroot -ppassword test -e "select id from client where user ='$user'"
EOF

This will show the result of the select statement, but I'd like to be able to use that result in another echo statement.

eg:

The user ID is: xxxxx

I tried to assign the output to a variable using:

sshpass -p $password ssh user@$IP /bin/bash << EOF
res=$(mysql -N -uroot -ppassword test -e "select id from client where user ='$user'")
echo $res
EOF

But that results in:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/run/mysql/mysql.sock' (2)

If I quote EOF like 'EOF' then I can get the result into res but I lose the ability to use $user

Is there anyway to do this so I can use my variable in the heredoc and get the result of the MySQL query to a new variable ?

oguz ismail
  • 1
  • 16
  • 47
  • 69
Tom
  • 1,436
  • 24
  • 50
  • 1
    This might help: [How to assign a heredoc value to a variable in Bash?](https://stackoverflow.com/q/1167746/3776858) or [Can a bash heredoc put its result directly into a variable?](https://stackoverflow.com/q/28565623/3776858) – Cyrus Jun 11 '20 at 08:48

1 Answers1

1

If I quote EOF like 'EOF' then I can get the result in to res but I lose the ability to use $user

You can pass $user to bash as a positional parameter and still have the quoted EOF and its advantages. E.g:

sshpass -p "$password" ssh "user@$IP" /bin/bash -s "$user" << 'EOF'
res=$(mysql -N -uroot -ppassword test -e "select id from client where user ='$1'")
echo $res
EOF

Bash manual describes the -s option as follows.

If the -s option is present, or if no arguments remain after option processing, then commands are read from the standard input. This option allows the positional parameters to be set when invoking an interactive shell or when reading input through a pipe.

oguz ismail
  • 1
  • 16
  • 47
  • 69