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?

L. Quastana · Accepted Answer · 2013-11-22 13:04:57Z

13

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';

edited Nov 22, 2013 at 13:04

answered Nov 22, 2013 at 12:55

L. Quastana

1,3461 gold badge13 silver badges35 bronze badges

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

2 Comments

Jeff Over a year ago

I get a syntax error unless I put a semicolon after the source command

ebyrob Over a year ago

It looks like this may not work in mysql 5.6 ? source works interactively, but doesn't seem to work when passed in the -e parameter.

Jason Heo · Accepted Answer · 2013-11-22 14:08:09Z

4

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

answered Nov 22, 2013 at 14:08

Jason Heo

10.3k2 gold badges39 silver badges72 bronze badges

1 Comment

Gabriel Glenn Over a year ago

And of course, in case of a sh script, use double quote in the sed part to pass the new value as variable. i.e. cat update.sql | sed "s/#domain#/$some_var/" | mysql -uusername -ppassword dbname

crash · Accepted Answer · 2023-01-20 10:20:27Z

3

I've found a better solution.

--init-command=name SQL Command to execute when connecting to MariaDB server.

mysql --init-command="SET @foo = 1; SET @bar = 2" -e "SELECT @foo, @bar, VERSION()"

Output:

+------+------+-------------------------------------+
| @foo | @bar | VERSION()                           |
+------+------+-------------------------------------+
|    1 |    2 | 10.6.3-MariaDB-1:10.6.3+maria~focal |
+------+------+-------------------------------------+

It also works with file redirection.

answered Jan 20, 2023 at 10:20

crash

7104 silver badges11 bronze badges

Comments

josef.van.niekerk · Accepted Answer · 2013-11-22 13:42:31Z

2

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.

answered Nov 22, 2013 at 13:42

josef.van.niekerk

12.2k20 gold badges104 silver badges161 bronze badges

Collectives™ on Stack Overflow

Pass MySQL variables to script from command line

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related