Search code examples
mysqlbashsshheredoc

Expand selected variables within here-document while invoking Bash through SSH

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 ?

Solution

  • 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.