create mysql database from variable in bash

Question

I'm trying to write a bash script that will create a database using a variable for the new name but not having any luck with the syntax. I know almost next to nothing about bash. Keeps telling me there's a syntax error at line 1 after I enter the db password.

setup.sh

#!/bin/bash

db="mytest"

scl enable rh-mariadb 'mysql -u user -p -e "set @dbname=$db; \. setup_db.sql"'

setup_db.sql

SET @query = CONCAT('CREATE DATABASE `', @dbname, '`');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREAPRE stmt;

If I hardcode the db name where I have $db in the bash then I receive this error:

Unknown column 'mytest' in 'field list'

That error doesn't come from the CREATE DATABASE statement. You probably have some other statement in your setup_db.sql file that is malformed. — Bill Karwin
– Bill Karwin, Commented Aug 18, 2021 at 18:00
You should also look at the mysqladmin tool. You can create a schema more easily. — Bill Karwin
– Bill Karwin, Commented Aug 18, 2021 at 18:01
Second one doesn't use a variable. I saw the first one but no it hasn't helped. — Phaelax
– Phaelax, Commented Aug 18, 2021 at 18:27

Barmar · Accepted Answer · 2021-08-18 18:37:10Z

1

You need to put the DB name in quotes in quotes, otherwise it's treated as a column name.

scl enable rh-mariadb 'mysql -u user -p -e "set @dbname=\"$db\"; \. setup_db.sql"'

answered Aug 18, 2021 at 18:37

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

1 Comment

Phaelax Over a year ago

That fixed the error, but the variable is being received in the sql script as empty.

Collectives™ on Stack Overflow

create mysql database from variable in bash

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related