Romulus Romulus - 1 month ago 5
MySQL Question

recursively input .sql files as new databases

I have a folder with alot of database files as .sql files.

I want to input them into mysql with new databases as the name of the file.

So if a file is name conference_2016.sql, I want the script to take that file and input its contents into a new database named: conference_2016.

So far, this inputs a single file into a single already existing database

mysql -u username -p database_name < file.sql


What I want is a line that can make a mysql databse from a .sh script. Because I need to make the database before adding the file to it, but cant figure how to from a script.

Answer

I think you can use this script:

#!/bin/bash

databases=$(ls *.sql)

for database in ${databases};do
    name=${database%.*}
    mysql -u root -ppassword -e "CREATE DATABASE IF NOT EXISTS ${name} DEFAULT CHARACTER SET utf8;"
    mysql -u root -ppassword < ${database}
done

And this is my directory structure:

toplevel
├── one.sql
├── test.sh
├── three.sql
└── two.sql

The test.sql is the above bash script. And the *.sql are the sql files you want to import.

Comments