Being that that this blog is “Proudly Powered by WordPress,” which uses a MySQL database, I wanted to find a way to schedule regular backups of MySQL databases. I am not that well versed with MySQL – all of the DB’s which I work with professionally are either SQL Server or Oracle – so I was looking to MySQL WorkBench to do the scheduling of regular backups, and to my dismay, you can’t schedule backups there.
I am not sure if this is something that Oracle has stripped out, or what, but it’s pretty crazy. So, I created this quick and easy PowerShell script to use the MySQLDump.exe to export the DB to a specified location, and cleanup any backups older than 1 month old.
Use the Task Scheduler to create a task to execute this script daily or weekly, and you’re set with your local backups of your MySQL DB.
Hope someone new to MySQL stumbles across this in less time than I took to realize that this functionality was not in MySQL WorkBench and to write the script. Enjoy!
############################################
# Author: Brad Payne
# Purpose: Backup MySQL databases
# Date: 5/1/2012
############################################
############################################
# VARIABLES WHICH NEED TO BE DEFINED
############################################
$mysqlpath = <PATH TO FOLDER CONTAINING MYSQLDUMP.EXE>
$backuppath =<BACKUP LOCATION>
$username = <DB USERNAME>
$password = <DB PASSWORD>
$database = <DB NAME>
$errorLog = <LOCATION OF ERROR LOG (INCLUDING FILE NAME)>
############################################
# END OF VARIABLES NEEDING DEFINED
############################################
$date = Get-Date
$timestamp = "" + $date.day + $date.month + $date.year + "_" + $date.hour + $date.minute
$backupfile = $backuppath + $database + "_" + $timestamp +".sql"
CD $mysqlpath
.\mysqldump.exe --user=$username --password=$password --log-error=$errorLog --result-file=$backupfile --databases $database
CD $backuppath
$oldbackups = gci *.sql*
for($i=0; $i -lt $oldbackups.count; $i++){
if ($oldbackups[$i].CreationTime -lt $date.AddMonths(-1)){
$oldbackups[$i] | Remove-Item -Confirm:$false
}
}
