Scheduled backup of MySQL databases in Windows

For different reason I'm using MySQL as database. The one thing that i needed was a backup function. My prerequisities was that it needed to be scheduled and that the backup file should be copied to a remote location. I didn't want to install any third-party-backup-tool I wanted to use existing tools. In this post I'm going to explain how you can set up a scheduled backup using mysqldump and saving the file to dropbox.

Create a use with minimum permission

First we are going to create a user with minimum permission, the user should only be granted access for select and lock tables. Log in to mysql with root or equivalent user that has access to add and grant access to other users.

mysql> grant select, lock tables on foo.* to bar@localhost identified by 'backup';
mysql> flush privileges;

The database name is foo (.* means all tables in that database). the user name is bar on localhost and the password is backup.

Create a backup script

Since I'm doing this on Windows I choosed to make .bat file, mine looks like this:

@echo off
set mySqlPath=C:\Program Files\MySQL\MySQL Server 5.5
set dbUser=bar
set dbPassword=backup
set dbName=foo
set file=%dbName%.%DATE%_-_%time:~0,2%%time:~3,2%%time:~6,2%.sql
set path=C:\Data\Backup\%dbName%

echo Running dump for database %dbName% ^> ^%path%\%file%
"%mySqlPath%\bin\mysqldump.exe" -u %dbUser% -p%dbPassword% 
--result-file="%path%\%file%" %dbName%
echo Done!

This will output

C:\Data\Backup\Scripts>MySQL-backup.bat
Running dump for database dboskarorg > C:\Data\Backup\foo\foo.2012-01-23_-_221326.sql
Done!

and a file in C:\Data\Backup\foo\

Scheudeling

Now we want it to run every night, lets say at 23:30. For that we are going to use at. Linux has cron, windows has at.

C:\>at 23:30 /every:M,T,W,Th,F,S,Su C:\Data\Backup\Scripts\My
SQL-backup.bat
Added a new job with job ID = 1

Type at again to make sure it's scheduled

C:\>at
Status ID   Day                     Time          Command Line
-------------------------------------------------------------------------------
        1   Each M T W Th F S Su    23:30         C:\Data\Backup\Scripts\MySQL-b
ackup.bat

Backup to a remote location

For this I'm going to use Dropbox, the reason for this is that it's an awesome service and that it's free. Download the install file from dropbox.com and install it. If you are a new user you need to register otherwise fill in your login information and proceed and configure it the way you like.

The trick is to create a symbolic link so that you don't have to have your backups directly in your dropbox folder, in Windows we do that with mklink

C:\>mklink /D "C:\Users\Oskar\Dropbox\Backup" "C:\Data\Backup"

And we are done! Now lets play the waiting game until 23:30 to make sure it's working properly!