Pass MySQL variables to script from command line

Question

I have a MySQL update script I'd like to run from the command line, but I want to be able to pass a stage domain variable to the script.

I know this won't work, but it's the best way I can describe what I'm trying to do:

$ -uroot -hlocalhost mydatabase  --execute "SET @domain = 'mydomain.dev' " < ./sql/update_domain.sql

Inside the script, I'm using the @domain variable, to update some configuration variables in a config table, using commands like this:

UPDATE my_cfg SET value = @domain WHERE name = 'DOMAIN';

Basically I want to prefix the SET @domain on the update_domain.sql file.

Any ideas how I can rectify my approach?

Answer 1

In your BATCH File :

mysql -e "set @domain=PARAMVALUE;source ./sql/update_domain.sql"

And in you SQL file :

UPDATE my_cfg SET value = @domain WHERE name = 'DOMAIN';

Answer 2

you can do that with sed like this:

echo "UPDATE my_cfg SET value = '#domain#' WHERE name = 'DOMAIN'" | sed 's/#domain#/mydomain.dev/' | mysql -uusername -ppassword dbname

or update.sql has UPDATE:

cat update.sql | sed 's/#domain#/mydomain.dev/' | mysql -uusername -ppassword dbname

Answer 3

This works for me:

system("(echo \"SET @domain = 'newstore.personera.abc';\"; cat sql/set_domain.sql) > /tmp/_tmp.sql")
system("mysql -uroot -hlocalhost newstore.personera.dev < /tmp/_tmp.sql")
system("rm /tmp/_tmp.sql")

...calling with system() from Capistrano.