How to create and fill database from shell script

TLDR

• Make sure MySQL is discoverable from your path
• Run:

to run createDatabase.sql as user root with password password

More detailed version

Recently I was working on a little personal project and I needed to create and fill MySQL database from command line or shell script. I remembered few years ago when I was fairly new to unix and databases I spent while figuring out how to run some sql code from command line, so for occasion I decide to write it out and hope I save someone few minutes of time.

I have created 2 scripts : createDatabase.sql and fillDatabase.sql

This script checks whether the database MyDB exists and creates it if not, drop possible existing tables and recreate a new ones.

Then the fill database just contains insert statements to put into database some initial data.

Run MySQL from command line

First of all make sure your MySQL executable is in your path. You can check by simply typing
mysql
And if it says something else than “command not found” you are good to go. Otherwise find out where is your MySQL installation and make it’s executable discoverable in from PATH variable.

To use MySQL from command line, type mysql -u<MySQLUsername> -p<MySQLUsername_Password>, no spaces!!! . Therefore, for user “root” and it’s password “password”, type
mysql -uroot -ppassword
You can add name of the database instance you want to use at the end of the command
mysql -uroot -ppassword DatabaseName

Create the shell script

Which will get you directly into the database you want to use. Now simple enough we can just redirect standard input from our .sql scripts into MySQL executable and it will work. Therefore just create bash script like this

So the script logs into MySQL as user “root” with password “password”, SQL code in createDatabase.sql and fillDatabase.sql, and any output produced goes to the outputCreate.sql and outputFill.sql

Now just don’t forget to add executable rights to create script by

And you are good to go