Home / server / Questions / 80914
Accepted
icelizard
Asked: 2009-11-04 04:11:32 +0800 CST

mysql create database and user script

  • 772

Question Rewritten:

HOMEDIR="ftpuser"
REMOTEIP="1.1.1.1"
MYSQLPASS="password"

Q1="DROP USER "$HOMEDIR"_shop;"
Q2="DROP DATABASE "$HOMEDIR"_shop;"
Q3="CREATE DATABASE IF NOT EXISTS "$HOMEDIR"_shop;"
Q4="GRANT ALL ON "$HOMEDIR"_shop TO '"$HOMEDIR"_shop'@'localhost' IDENTIFIED BY '$MYSQLPASS';"
Q5="GRANT ALL ON "$HOMEDIR"_shop TO '"$HOMEDIR"_shop'@'anotherip' IDENTIFIED BY '$MYSQLPASS';"
# Need to grant permissions from another server as well
Q6="FLUSH PRIVILEGES;"
SQL="${Q1}${Q2}${Q3}${Q4}${Q5}${Q6}"

echo $SQL
echo " "
ssh -p 8899 root@$REMOTEIP "mysql -u root -p "$SQL""

I then run:

/root/testing/migratesite.sh

And get:

bash: DROP: command not found
bash: CREATE: command not found
bash: GRANT: command not found
bash: GRANT: command not found
bash: FLUSH: command not found

What am I missing?

mysql

3 Answers

  1. You have already enclosed the $VARIABLES in quotes (which is good practice, and accounts for spaces in variables/filenames) however you are opening and closing them throughout the line - this works OK (although not recommended) for BASH variable assignment, but when you get to the SSH remote command, it fails as it tries to execute the variable, not quote enclosed string you have provided. The correct SSH command would be:

    ssh -p 8899 root@$REMOTEIP "mysql -u root -p $SQL"

    as the variable is already "quoted" by the quotes for passing the argument.

    • 1
  2. Best Answer

    You are missing quotes and a proper mysql client command line:

    ssh -p 8899 root@$REMOTEIP "mysql -u root -p -e \"$SQL\""

    You need to escape the quotes around the $SQL variable so they get passed to the remote shell, else they get interpreted by the local shell (that's why you get DROP: command not found, the semi colon is interpreted by the shell.) Also, to have the mysql client to execute a command you have to pass the -e command line option.

    • 1

  3. It may be easier to substitute perl and DBI for bash - no need to fork mysql, bit more control. Also t'internets is littered with tutorials and examples on "how to" - no point swimming agin the tide ;)

    • 0