Run Remote Mysql Command with Shell Script

Question

I am trying to run an ssh command in my shell script which will automatically create a database, user, password etc on a remote server:

password=`date +%s|sha256sum|base64|head -c 32`
read -p "Enter staging folder name... e.g. xxxxxxxx:   " stagingdirectory
echo $stagingdirectory
read -p "Give the name for the database (this will be used in mysql)" dbname
echo $dbname
read -p "Give the name of the user for the database (this will be used in mysql)" dbuser
echo $dbuser

sqlstatement="mysql -uXXXXXXX -pXXXXXXXX -hXXXXXXXX -e "
sqlstatement+='"CREATE DATABASE IF NOT EXISTS $dbname;CREATE USER $dbuser@'%' IDENTIFIED BY '$password';GRANT ALL PRIVILEGES ON $dbname.* TO $dbuser@'%';FLUSH PRIVILEGES;"'

echo $sqlstatement
ssh -A [email protected] -e "$sqlstatement"

When I try and run this command, I get this error:

This is what gets returned (I have replaced actual values with XXXX):

Bad escape character 'mysql -udbadmin -pXXXXX -hXXXXX -e"CREATE DATABASE IF NOT EXISTS $dbname;CREATE USER $dbuser@% IDENTIFIED BY XXXXXX;GRANT ALL PRIVILEGES ON $dbname.* TO $dbuser@%;FLUSH PRIVILEGES;"'.

I think this is due to my sql statement and strings escaping.

Post the value of $(echo $sqlstatement), the problem is in there. — Nic3500
– Nic3500, Commented Dec 14, 2017 at 18:02

Jithin Scaria · Accepted Answer · 2017-12-14 18:28:47Z

2

Try changing the line,

ssh -A [email protected] -e "$sqlstatement"

to

ssh -A [email protected] "$sqlstatement"

From the manual of ssh, see below

-e escape_char

Sets the escape character for sessions with a pty (default: ‘~’). The escape character is only recognized at the beginning of a line. The escape character followed by a dot (‘.’) closes the connection; followed by control-Z suspends the connection; and followed by itself sends the escape char- acter once. Setting the character to “none” disables any escapes and makes the session fully transparent.

Here in your example, ssh is not getting a valid escape character, and you don't need one either

edited Dec 14, 2017 at 18:28

answered Dec 14, 2017 at 18:23

Jithin Scaria

1,3191 gold badge16 silver badges27 bronze badges

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

Collectives™ on Stack Overflow

Run Remote Mysql Command with Shell Script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related