Quick and Dirty MySQL Backup Script on Windows

I’ve wrote a small backup script to backup a remote MySQL database.

Requires mysqldump.exe which is part of most/all standard MySQL installations.

@echo off
:: variables
set drive=D:\Backup\DB

for /f “tokens=2-4 delims=/ ” %%g in (‘date /t’) do (
set yy=%date:~-4%
set mm=%date:~-7,2%
set dd=%date:~-10,2%
)
set today=%dd%-%mm%-%yy%

:: Switch to mysqldump.exe directory
cd C:\wamp\mysql\bin\

echo ### Backing up Blog…
mysqldump –opt -Q -h mysql1.hostingcompany.ie -u database_user database_name > %drive%\database_name%today%.sql

echo Backup Complete!
@pause

Thats it, save it as a .bat file and run it.

It will create the backup file with todays date

It will also prompt for a password, if you want to include the password in the file (alarm bells!)

mysqldump –opt -Q -h mysql1.hostingcompany.ie -u database_user --password database_password database_name > %drive%\database_name%today%.sql

I should also append this script to correctly check for existing files, and incorporate some compression. This script is for a relatively unimportant database, if I had a more mission critical database, I’d be using a proper backup & recovery system from my database provider.

Further Reading.
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html