Bash script to Import Multiple Databses using Loop

Question

I have a folder with many DB.sql.gz files, i want to import them all into a new installation of MySQL Server in one go.

I thought using bash script would be a good idea.

So Far, I have this file as an executable.

#!/bin/sh
for filename in *.gz; 
do
tempfile="${z##*/}";
database_name = "${tempfile%.*}";
echo database_name;
mysql -pPASS -u USER;
CREATE DATABASE database_name;
unzip z | mysql -pPASS -uUSER database_name;
done

Whats going wrong is that when taking the 'filename' in the for loop, i cant string manipulate to to remove the extension, I want to do this because the database files are named as the database. ( just want to remove the .sql.gz to get the db name )

Help appreciated

To execute a query direectly using mysql, use mysql -e 'query'` — Barmar
– Barmar, Commented Oct 23, 2014 at 6:08

Barmar · Accepted Answer · 2014-10-23 07:40:32Z

1

#!/bin/sh
for filename in *.gz; 
do
    tempfile="${filename##*/}"
    database_name="${tempfile%%.*}"
    echo $database_name
    mysql -pPASS -u USER -e "CREATE DATABASE $database_name"
    gzcat $filename | mysql -pPASS -uUSER $database_name
done

$z should be $filename.
No spaces around = in assignments
You were missing $ before many variable uses.
To execute the CREATE DATABASE command, use the -e option to mysql
Use gzcat to expand a .gz file to stdout.
${tempfile%.*} should be ${tempfile%%.*}. % removes the shortest match of the pattern, %% removes the longest match. This will remove all the suffixes.

edited Oct 23, 2014 at 7:40

answered Oct 23, 2014 at 6:11

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

5 Comments

Deano Over a year ago

Brilliant: there's just one small blip. Unknown database 'xdb.sql' the string needs not to have the .sql part

Barmar Over a year ago

It should be %%.*, not %.*, so you remove all the suffixes, not just the last one.

Deano Over a year ago

Nearly there, it's not creating the Databases, it's creating one called 'database_name' and failing :(

Barmar Over a year ago

That's another place where you were missing $. Note also that I changed to double quotes there, since variables aren't expanded inside single quotes.

Deano Over a year ago

Perfect ! I changed the gzcat to gunzip using --stdout and that got over the gzcat issue i had, but thats the winner !

Collectives™ on Stack Overflow

Bash script to Import Multiple Databses using Loop

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related