Using Batch Files to Create MySQL Databases

Creating MySQL Tables and Stored Procedures with a Text File

Sep 26, 2008

Use a batch file to create MySQL Databases, Mark Alexander Bain

A batch file provides an historical record when creating MySQL databases and the objects in them (tables, views and stored procedures)

There's nothing that a Windows or Linux batch file can do that can't be done by hand by a programmer, or to put that another way - there's nothing that a programmer can do by hand that can't be done with a windows or Linux batch file.

This is especially true when it comes to creating MySQL databases. For example, with a batch file it's possible to

create a new database
create tables
create stored procedures and functions
populate the tables with data
run queries

There are also some very good reasons for always creating a MySQL database from a batch file:

the batch file provides an historical record of what's been done
a batch file means that databases are reproducible - either at another time or in another place

All that the database developer has to do is:

store all of the SQL commands in a text file
use the mysql command to run the SQL commands in the file

This file could be something like:

on Linux - /home/bainm/sql/create_database.sql
on Windows - c:\sql\create_database.sql

SQL Code for Creating a MySQL Database

The first lines of the batch file will need to contain the commands for creating the database itself:

drop database if exists suite101_data;

create database suite101_data;

use suite101_data;

In this example the SQL code:

deletes the database if it already exists - this is particularly useful during the development stage, but is to be avoided in a live system (obviously)
it then creates the database
and then it moves the user into the newly created (but empty) database

SQL Code for Creating Tables in a MySQL Database

The next job for the batch file is to create the tables for the database:

create table user (id int auto_increment, firstname varchar(25), surname varchar(25), username varchar(25), office_id int, primary key (id));

create table office (id int auto_increment, name varchar(25), manger_id int default 0, address_id int, primary key (id));

create table address (id int auto_increment, address1 varchar(255), address2 varchar(255), town varchar(50), postcode varchar(10), primary key (id));

It's worth noting that these tables will be created using auto_increment in the id field - this automatically inserts a new index number for any new records.

Having created the tables, the batch file can then be used to populate them:

insert into address (address1, address2, town, postcode)

values

('Database House','1, The High St','Query Ville','SQL1 1SQL');

insert into office (name, address_id) values ('Head', 1);

insert into office (name, address_id) values ('Accounts', 1);

insert into office (name, address_id) values ('IT', 1);

SQL Code for Creating a Stored Procedure in a MySQL Database

As well as creating the database itself and the tables in the database, the batch file can also be used to create any stored procedures:

delimiter //

create procedure new_user (in fname varchar(25), in sname varchar(25), in uname varchar(25), in office_name varchar(25))

begin

declare o_id int;

select id into o_id from office

where name = office_name;

insert into user (firstname, surname, username, office_id)

values

(fname, sname, uname, o_id);

end

delimiter ;

In this example the stored procedure defined in the batch file does what all stored procedures should do - it handles any of the more complex operations required by the database; in this case it:

accepts a user's first name, surname, username and office
obtains the office's id
creates a new entry in the user table

As always when creating a stored procedure the delimiter needs to be changed since the default delimiter (a semicolon) is used as part of the stored procedure definition.

Now new users can be created very simply:

call new_user ('Fred','Smith','smithf','IT');

And finally the batch file can be used to output the results of the work done so far:

select * from user;

Running a Batch file on a MySQL Database

Normally all the above SQL would be entered by hand after logging on to MySQL:

mysql -ubainm -pnot_saying mysql

The mysql command is still used, but his time it's used to read the SQL from the batch file; so on windows the command will be:

mysql -ubainm -pnot_saying mysql < c:\sql\create_database.sql

and on Linux it would be:

mysql -ubainm -pnot_saying mysql < /home/bainm/sql/create_database.sql

or:

cat /home/bainm/sql/create_database.sql | mysql -ubainm -pnot_saying mysql

In all cases the command will expect:

the database to be on localhost (i.e. the current pc)
a username to be entered with the -u flag (and the user must have appropriate create privileges on the database)
the user's password to be entered with the -p flag

If the database is located on a remote server then the -h flag should used, for example:

mysql -h192.168.1.3 -ubainm -pnot_saying mysql < c:\sql\create_database.sql

Conclusion

A database developer may use the mysql command to create objects line by line on a MySQL database, but it's so much more useful to use that same mysql command to load a batch file; a batch file that can be stored for posterity or to be used elsewhere.