Smart code for developers

Sw33tCode header image 2

Building Mysql Database contents from batch script in Windows

April 5th, 2008 · 2 Comments

I needed a way to build my database quickly on a project I was working on and I was constantly making changes to the database that supplied content to my application. So I came up with an easy batch script that would help facilitate the changes in my database environment.

Here is a mocked version of the batch script that I wrote to build the database:

@echo off
REM: Command File Created To Create MySQL Experiments Database
REM: Date Generated: 4/5/2008
REM: Usage: CommandFilename [Host] [Username] [Password]if '%1' == '' goto usage
if '%2' == '' goto usage
if '%3' == '' goto usage
if '%1' == '/?' goto usage
if '%1' == '-?' goto usage
if '%1' == '?' goto usage
if '%1' == '/help' goto usage
echo experiments.build.database START
mysql -h %1 -u %2 -p%3 > "experiments.build.database.sql"
if %ERRORLEVEL% NEQ 0 goto errors
echo experiments.build.database END
echo experiments.build.tables START
mysql -h %1 -u %2 -p%3 "experiments" > "experiments.build.tables.sql"
if %ERRORLEVEL% NEQ 0 goto errors
echo experiments.build.tables END
echo building stored procedures START
mysql -h %1 -u %2 -p%3 "experiments" > "sprocs\uspAddUser.sql"
if %ERRORLEVEL% NEQ 0 goto errors
echo uspAddUser
echo building stored procedures END
echo experiments.insertdata.messenger START
mysql -h %1 -u %2 -p%3 "experiments" > "experiments.insertdata.sql"
if %ERRORLEVEL% NEQ 0 goto errors
echo experiments.insertdata.messenger END
echo Finished Building Database 
goto finish
REM: How to use screen
echo Usage:    MyScript Server Database
echo Host:     MySQL Server
echo Database: the name of the target database
echo Username: username to be used on target database
echo Password: password to be used on target database
echo Example: BuildDatabase.cmd "localhost" "root" "password"
goto done
REM: error handler
echo WARNING! Error(s) were detected!
echo --------------------------------
echo Please evaluate the situation and, if needed,
echo restart this command file. You may need to
echo supply command parameters when executing
echo this command file.
goto done
REM: finished execution
echo Script execution is complete!
@echo on

This script will call the sql files that contain the commands to build the database, tables, stored procedures, and even create some test data. To call this command file all you have to do is supply the host, username, and password parameters. Like this:

c:\BuildDatabase.cmd "localhost" "root" "password"

This script example will call your database: experiments. But if you wanted to dynamically name your database you can just add another parameter onto your batch script and replace all the mysql calls above with this code:

mysql -h %1 -u %2 -p%3 %4  > "sql_code.sql"

Then your call to the command file will be like this:

c:\BuildDatabase.cmd "localhost" "root" "password" "database_name"

If you wish to try out the code in action please download the zip file below. You will need MySql Version 5 or above to test this. Please let me know if you have any questions.

Sample Mysql Batch Script

Tags: Mysql · Windows Batch Script

2 responses so far ↓

  • 1 JR // Apr 15, 2008 at 3:02 pm

    Nice man! I was looking for something like this awhile back, but now I’m using this code in my database creation scripts with vs2005. Thanks!

  • 2 Lacy // Oct 28, 2008 at 6:21 pm

    Good for people to know.

Leave a Comment