| Automating Database Backups Using batch files and Scheduled Tasks on Windows Platforms. |
| Description: A fairly detailed article describing how databases can be backed up automatically using mysqldump and batch files on Windows platforms. |
| Author: NeoThermic |
| Date: Wed Jan 07, 2004 11:22 am |
| Type: HowTo |
| Keywords: Windows, batch files, mysql, mysqldump, database, backup, automatic, command line, shell, script, scheduled |
| Category: Administrating |
 |
Thanks to bleak for his article. I'm going to follow the same structure.
Automating Database Backups Using Batch Files
Overview
This article is originally in response to bleak's article on how to setup an automatic, database backup using crond. Such backups, which save each backup to a different file, are covered here; but so, too, are
more simplistic automatic backups. The process described in this article uses batch scripts (on windows platforms, mind) in conjunction with mysqldump to create the automated backups. Therefore, I recommend that you read the previous article Backing Up a mySQL Database Via the Command line With mysqldump, so as to have a better understanding of the mysqldump commands used herein.
This article is fairly detailed; so be prepared for a bit of a read.
What You Will Need
This section makes it that bit harder to describe. If possible, optimum needs are:
Physical access to the server in question
Server needs to be running Windows.
However, even FTP access and VNC access to your server is good enough for what is required.
Using batch scripts to Automatically Backup a Database
Ok, so we all should be familiar with this:
| Code: | | mysqldump [options] -u [username] -p[password] [database] [table_name(s)] > [backup_file] |
For the sake of being consistent and causing as little confusion as possible, I will use user as the username, pass as the password, and phpbb as the database in all of the following examples. This information will be different (except, possibly, the
database name) for everybody. Make sure you use the correct information, as given to you by your hosting provider. The name you specify for the backup is arbitrary and may be whatever you want.
For a simple backup and overwrite job, the script would look like this:
BatchJob.bat:
| Code: |
mysqldump.exe --add-drop-table -u user -ppass phpbb > database_backup.sql
|
This file would have to be uploaded to the c:\mysql\bin directory.
(or wherever mysqldump.exe is located)
If you don't want the backup file(s) to be overwritten every time a new one is made, the procedure is a bit more complicated. For me, the job would look something like:
BatchJob2.bat:
| Code: |
FOR /F "tokens=1-4 DELIMS=/ " %%F IN ('date /T') DO (set v_date=%%F%%G%%H)
set fname=database_backup_%v_date%.sql
echo %fname%
mysqldump.exe --add-drop-table -u user -ppass phpbb > %fname%
|
Again, this file would have to be uploaded to the c:\mysql\bin directory.
(or wherever mysqldump.exe is located)
To understand what the hell is going on here, you need to quickly see that we are looping through the date variable (outputted via date /T), and then extracting the current month/year and placing it into v_date.
We then set fname to be database_backup_[date].sql
In this script, we echo the file name only for debug, you can comment that line out if you so wish by placing REM in front of it.
We then call mysqldump.exe with our params to get our nice new backup file.
If you want to include the time in the backup name, you only need to use this:
BatchJob3.bat:
| Code: |
FOR /F "tokens=1-4 DELIMS=/ " %%F IN ('date /T') DO (set v_date=%%F%%G%%H)
FOR /F "tokens=1-4 DELIMS=: " %%F IN ('time /T') DO (set v_time=%%F%%G%%H)
set fname=database_backup_%v_date%_%v_time%.sql
echo %v_time%
echo %fname%
mysqldump.exe --add-drop-table -u user -ppass phpbb > %fname%
|
Again, this file would have to be uploaded to the c:\mysql\bin directory.
(or wherever mysqldump.exe is located)
That’s about it, but lets say you wanted to run this script every day at 4am. How do you do that on windows? Simple. Use Scheduled Tasks applet. (Start -> Control panel -> Scheduled Tasks -> Add Scheduled Task)
On the main screen, Click next.
Then, when asked what application to run, click browse. Locate ourselves to the \mysql\bin directory, and select our batch file.
On the next screen that appears, Type in a name for the task (I used SQL_Backup) and select when you want this task to run. (I chose daily). Click Next.
Now enter a time for when the database should be backed up. I chose 4AM, as this is the time when my server is least busy (a database dump can eat a small size of CPU power).
Now select the 'Perform this task' options, I chose Every Day. The start date should be the current date you are doing this, and should be fine.
Click Next.
Note: this is for NT/2K/XP. I don't think that 9x OS'es ask for this
Now enter your username and password for which you will run the task under. Its best to run this from a administrator account so that it can access the needed files.
Click Next.
This screen tells you that it has finished making the task. Most users can click finish, those who want to see what else they can set can check the Advanced Properties checkbox.
That’s it. When the time you set rolls on by, the batch file shall be executed, and you shall have a nice new database backup file.
More Resources
As noted, the help command is always a good source of information and most commands have one. To view a help entry for a command, just type help <command> from the command line.
mySQL -http://www.mysql.com/documentation/mysql/bychapter/
End Of Tutorial
NeoThermic |
|
|
|
