Windows Batch File: How to Backup MySQL to WinRar passworded file and upload it to SkyDrive

Following is a batch file I’ve created to backup my MySQL database. It compresses the backup to a passworded WinRar file. Which is then uploaded by the same batch file to my free 25gb Microsoft SkyDrive account (you can use any cloud storage provider which can be mapped to drive letter). Batch script then automatically names created backup using the current date and time stamp. It then deletes all WinRar files older than 30 days on SkyDrive, thus rotating backups for me automatically. Batch file can be scheduled to run as often as needed.

This simple batch file replaced quite an expensive backup software which was running on my dedicated server and using lot of resources.
Let me know if you were able to improve it in any way.

Prerequisites for my batch file:
– Installed Forfiles (this is automatically available on Windows 2008 servers)
– Created D:\Backups\mysql\ folder (this is where uncompressed mysql exports are stored)
– Created D:\Backups\mysql\compressed folder on my local D drive (this is where compressed mysql exports are stored, before upload to Skydrive)
– I’ve installed Skydrive and mapped it to D:\Skydrive folder
– I have mysql bin folder installed in: C:\Program Files\MySQL\MySQL Server 5.5\bin
– Installed WinRar to: C:\Program Files\WinRAR\ (installation from http://www.rarlab.com/download.htm)

BATCH FILE:

:: ——————–MYSQL BACKUP———————–

:: Delete all files older than 30 days on Skydrive
Forfiles -p “D:\Skydrive” -s -m *.* -d -30 -c “Cmd /C del @FILE”

:: Delete all files in compressed folder
cd D:\Backups\mysql\compressed
del /s /q D:\Backups\mysql\compressed\*.*

:: Dump DB
cd “C:\Program Files\MySQL\MySQL Server 5.5\bin”
mysqldump -uroot -HereGoesYourMySqlRootPassword NameOfDBYouWantToDumpToFile >D:\Backups\mysql\mysql_db.sql

:: Set DATA stamp format
set datecode=%date:~-4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%

:: Set path to Winrar and rar compress databases
set path=”C:\Program Files\WinRAR\”;%path%

:: Compress databases
winrar a -HereGoesPasswordForRarFile “D:\Backups\mysql\compressed\%datecode%_mysql_db.rar” D:\Backups\mysql\mysql_db.sql

:: Copy compressed db over to Skydrive
xcopy “D:\Backups\mysql\compressed\*.rar” D:\Skydrive\Andy\MySQL

exit



Facebook Comments