In our home workshop, we have a shared PC workstation running XAMPP to serve up some local PHP applications inside of our home network. These apps are mostly convenience tools, nothing critical, but at the same time the MySQL database holds some data we want to backup – just in case. Backing up the MySQL database is easy with mysqldump, but having a copy of the data on the same machine really wasn’t sufficient, it needed to be pushed off site to another machine. So, my goal was to:

1. Run mysqldump to get an export of the database.
2. Load the resulting dump file to an off site server.
3. Schedule this to happen every night.

The solution was to write a batch file, and use PSCP to upload the file to our off site storage. The mysqldump.exe is a very handy tool to create a backup file of the database. You should read up on the many options, which I won’t go into here. One option you should definitely consider is to compress the dump output file. In my case, the output file is less than 50kb, so I didn’t bother.

XAMPP is really a great development tool, and a decent micro web server for home use. It comes with PHP 5.3 and MySQL already installed, plus more goodies. Installing it is a snap, normally as simple as unzipping it into the root of C:\ (Windows of course, but there are versions for Mac OS X and Linux). But this post is not about using XAMPP, but about backing up a database.

First, create a temp directory somewhere, I created one as C:\temp. Next, if you don’t already have it, install PSCP, an SCP client (command line secure file copy). We’ll use this to copy the files to a remote Linux server (such as a web host). You can get PSCP from the PuTTY download page (look for pscp.exe). The install is simple, just save the pscp.exe executable to a new folder in Program Files, I put it where I already have PuTTY installed: C:\Program Files\Putty\pscp.exe. If you are running a 64bit machine, you’ll probably put it in C:\Program Files (x86)\Putty. You’ll need to have SSH access to your remote server to use PSCP. If you don’t, contact your remote host administrator and ask for permission.

On your off site server create a directory to hold the backup file. Depending on your host (we’re using a web host, and placing the folder outside of the public_html directory), you can use PuTTY and SSH in, or just use the C-Panel to login and create the new directory. You’ll definitely want to place this outside of the web root!

Now, create a batch file. I placed this at the root of the application I wanted to backup, but it doesn’t matter where it goes (well, mostly doesn’t matter). Another good place might be in the C:\temp directory. If you haven’t done this before, create a new text file, and change the name to backup_db.bat. In this batch file, we’ll add a few commands:

@ECHO OFF
REM Backup database using mysqldump.exe.
REM Then send the backup file to remote web host using pscp for offsite storage.

REM To run this script, you need:
REM 1) To have pscp.exe installed
REM 2) Assumes you are using MySQL as part of an XAMPP install
REM 3) A temp directory c:\temp
REM 4) A target directory on your web host for the backup file storage

REM Set local variables here, this way you can easily update references in one place.
SET localdir=c:\temp
SET mysqldumppath=C:\xampp\mysql\bin\mysqldump.exe
SET mysqluser=root
SET mysqlpassword=<your_root_password>
SET mysqldatabasename=<your_database_name>

REM Uncomment the line below if you want to accumulate a new database backup file each night.
REM SET backupfilename=backup_%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%.sql

REM Otherwise, this version of the backupfilename just gets over written each night.
REM (Comment out the line below if you use date-unique backup file names.)
SET backupfilename=db_backup.sql

REM If the path has spaces, use double quotes for the path.
SET pscppath="C:\Program Files\Putty\pscp.exe"
SET remotehost=<your_remote_server>.com
SET remoteuser=<ssh_user_name>
SET remotepassword=<ssh_user_password>
SET remotepath=<remote_absolute_path>

@REM Dump database.
%mysqldumppath% -u%mysqluser% -p%mysqlpassword% --opt --databases %mysqldatabasename% > %localdir%\%backupfilename%

@REM Upload new file using pscp to remote server
%pscppath% -pw %remotepassword% %localdir%\%backupfilename% %remoteuser%@%remotehost%:%remotepath%

You’ll need to replace any <your_name_references> with your actual names and paths. If the string has a space in it, wrap it in double quotes.

After making your reference changes, test the batch file by double clicking it, you should see a command line window briefly appear as PSCP connects to the remote server and uploads the file. You can verify this by checking that your C:\temp directory has a copy of the database dump file, and then check that your remote server has the same copy of the dump file. Almost there!

Finally, we can schedule this as a Windows task. Open Start > All Programs > Accessories > System Tools > Task Scheduler. Depending on which version of Windows you are using, you’ll see a slightly different Task Scheduler.

Creating a new task is fairly simple, give the task a name, select something to do, and set a repeating schedule, such as every night. On an XP machine, you’ll need to select an application to run. Just choose any application; after you create the task you can then edit the task and change the path to the full path and file name of your new batch file: C:\temp\backup_db.bat. On Windows 7 the wizard appears to let you define a file to run.

What Do You Think?