Intranets-Talk

I've been using Vim for about 2 years now, mostly because I can't figure out how to exit it

Automated FTP Backup of WAMP MySQL Databases on Windows Server 2003

On this one, I'll talk about setting up automated backups of one or more MySQL databases - on a Windows Server 2003 running WAMP server. This should work on your personal computer as well, on different versions of Windows, but I have only have set it up so far on WS 2003 - so I'll base my tut on this today.

I know that there is a lot of coverage of how to achieve the same on Linux/Unix using the funky Cron. And just like Cron, we want to end up with a "set it and forget it" kind of setup. So for those willing to get their Cron-like automated MySQL database backups in Windows, here we go.

Things I have available for this:

  • access to a Windows Server 2003 (with administrative rights), having WAMP installed;
  • access to a local or remote FTP server (for storage of the MYSQL dumps)
  • for this I assume you have WAMP installed on C drive, like this: C:\WAMP

There are 2 main steps. Creating the script and than creating a Windows Scheduled Tasks to run the script.

Create and save the script that will perform the backup:

We'll start off by creating a folder: C:\WAMP\sql_backup\, which will hold the script;

Inside that folder, create an empty text file: sql_backup.txt and have it renamed to sql_backup.bat

Right click on the file, choose "Edit", to open the file in Notepad++ (if you have it installed...). Now here's the bit of script that will do the work:

bash
@echo off

# variable to hold the datestamp of the backup
SET today=%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2%

# temporary FTP script to upload backup files
set SCRIPT_NAME=ftp_script.ftp

# MySQL database dump
"C:\wamp\bin\mysql\mysql5.1.33\bin\mysqldump" your_database_password -u your_database_user > "C:\wamp\sql_backup\%today%_name_of_database.sql"

# connect to FTP server, provide credentials and perform upload
echo open your-ftp-server.com>> %SCRIPT_NAME%
echo ftp-user-name>> %SCRIPT_NAME%
echo ftp-password>> %SCRIPT_NAME%
echo cd path/to/your/storage/folder>> %SCRIPT_NAME%
echo binary>> %SCRIPT_NAME%
echo put %today%_name_of_database.sql>> %SCRIPT_NAME%
echo disconnect>> %SCRIPT_NAME%
echo bye>> %SCRIPT_NAME%
ftp -s:%SCRIPT_NAME%

# always clean behind
@del %SCRIPT_NAME% /q
@del %today%_name_of_database.sql

What this does:

  • the script takes a dump from a database of your choice, saves it temporarily inside that folder, connects to your FTP server, uploads the sql file and in the end, cleans up the temporary files created.

Just make sure to adjust the scrip for your environment - path name to your MySQL "bin" folder, credentials for accessing your MySQL database, FTP credentials, and the FTP sever path where you want the MySQL files backed up.

Next, we'll use Windows Scheduled Tasks to have the sql_backup.bat script run automagically.

Creating a Windows Scheduled Tasks to run the backup script

First make sure that you are logged in to Windows Server 2003 as a user with administrative rights.

Than, go: Start Menu-->All Programs-->Accessories-->System Tools-->Scheduled Tasks

After: File-->New-->Scheduled Task

I'm going to name my new task "backup". Right-click onto it, and choose "Proprieties". We'll be mostly interested in the first three tabs on the Proprieties window: Task, Schedule and Settings.

Tab1

The Task tab:

Run option: point to your script - in our case mysql_backup.bat

Start in option: Point to the folder that holds the mysql_backup.bat

Run as: Write your Windows Server 2003 credentials in the form of: domain\username

Also, click the "Set password" button to input and store your password for this task. Otherwise, the task will attempt to run at the scheduled time and it will stop waiting for you to input a password. The task will not complete.

Make sure that "Enabled" is selected and "Run only if logged on" is un-selected (so the script will run at any time we'll specify on the next tab)

Tab2

The Schedule Tab:

On this screen, I chose my script to perform the MySQL backup weekly, every Saturday at 11:30 PM. Pretty much self-explanatory.

Tab3

The Settings tab:

On to the settings tab. Over here, I've decided to stop the task if running more than 72 hours.

You can imagine that if the task tries to run for a long time without succeeding, than there must be a problem. So we'll stop it. You can choose any length of time here that will make sense to you.

The rest of options in here are straight-forward and you can choose your own settings.

Inside the the fourth tab - Security (not pictured here), make sure that for the Administrator user, the "Full Control" box is checked.

What next?

After clicking OK on the newly created Task window, you can right click onto it again and choose "Run" to test it out. You should see a command prompt window popping up briefly, and executing the backup. Have a look on your FTP server and make sure that your database backup it's there. If there's issues, most likely they are related to typos (file paths, names, or passwords) - double check and scan your script lines for those.

And that's all for now. I know that lots of folks are not using database backup utilities at all, or they are using heavy third party applications running in the background as services or in the sys tray to do this job. This is just a simple straight-forward backup alternative, using tools that are already build into the system, and works.

I hope you find this useful, and as usual, if you have issues implementing it, you can drop me a line in the Comments - I will try to help if I can. Till next time, keep good.