Thomas’ Developer Blog

April 18, 2008

mySQL batch files (Run commands from file) – Basic Example

Filed under: sql — Tags: , , , , — sanzon @ 6:55 pm

Once you developed your database in mysql there will be times when you will have to make changes.  The problem is that the database may be constantly being used.  While it is a good idea to prevent access to users for a breif period (1-5 minutes at most) it isn’t a good idea to make a mistake by manually entering changes since it can cause issues during the process which will increase down time.  Which down time is never a good thing.

The process for doing this that I use is to create two databases on your developer unit.  The first is the original database being worked on, and the second is a backup that will be used to test the batch file.

Once you have the database setup correctly, go ahead and create the batch file to test on the backup.  You also want to do a mysqldump to backup the database before you do anything.

A batch file simply a text file that holds mysql commands.  All you do is type in the commands in notepad, or your prefered text editor, and save it as .sql or technically whatever you want.

To run a batch file just type in the mysql client:

source c:\myfile.sql

you can change the directory/filename as needed.  This way you will quickly go through the command and reconfigure the database without causing unneeded downtime.  You can even do it while the database is running, long as it doesn’t effect any current tables.

So give it a try:

Open notepad and tye:
select * from TABLE;

Save as: c:\myfirstbatch.sql

Open sql client, and access database you want to change and type:
source c:\myfirstbatch.sql

It’s a common practice and is very useful.  Also you can run these commands through an actual batch file by accessing the mysql.exe though command prompt.

For more information visit:
http://dev.mysql.com/doc/refman/5.0/en/batch-mode.html

 

About these ads

3 Comments »

  1. plz help me out because i cant understand how it work in back end

    Comment by Ruchi — May 12, 2009 @ 11:52 am

  2. Realy it benifited me i would like to advice the biggeners to follow this website………

    Comment by naman jha — July 26, 2010 @ 12:00 pm

  3. I need some of basic example for batch file in sql so that i can able to create an batch file also edit.I want to run it in command prompt.

    Thank you
    vivek

    Comment by vivekanand vivek — July 27, 2010 @ 8:01 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: