- The first thing that needs to be done is that a user needs to be created within the MySQL instance that can be used for backups. I accomplish this on the server in MySQL Workbench. Under Server Administration, click on Security. Add a user account, give it a nice password, and add it to the BackupAdmin role.
- The MySQL server is not a domain-joined computer, so I created a local Windows account (MySQLWinBackup), and assigned the account the "Log on as batch" right.
- I created a local folder named C:\DB_Backup. This folder should be shared to "Everyone", and then the NTFS permissions modified so that the local Windows backup account has full permissions.
- The following Powershell script file needs to be created. I store my scripts in C:\PS:
#--------------------- BEGIN SCRIPT -------------------------------
#Name of Server
$ServerName = "WebServer"
#The Path to the mysqldump.exe program. This is the program that backs up the MySQL database natively.
$PathToMySQLDump = "C:\MySQL\bin\mysqldump.exe"
#Credentials of the MySQL user account
$Username = "MySQLBackupAccount"
$Password = "MySQLBackupAccountPassword"
#Get today's date, to be used for naming purposes
$Date = (get-date).ToString('MM-dd-yyyy')
#Where to store the backup files
$LocalBackupPath = "C:\DB_Backup"
#Backup all of the Databases
cmd /c " `"$PathToMySQLDump`" --routines --events --user=$UserName --password=$Password --all-databases > $LocalBackupPath\$ServerName-AllDataBasesBackup-$Date.sql "
#--------------------- END SCRIPT -------------------------------
- Now, I set that Powershell script to execute daily at 5:10PM in Task Scheduler.
- I run the scheduled task and find out how long it's going to take. This information will be used later on.
- Over on my backup server, I create another script. This script handles moving the backup files created earlier off-SAN and off-site:
#--------------------- BEGIN SCRIPT -------------------------------
#Name of Server
$ServerName = "WebServer"
#Path to the location where the MySQL server put the backups
$RemoteBackupPath = "C:\DB_Backup"
#Off-SAN copy location
$OffSANCopyLocation = "\\OnSiteNASDevice\MySQLBackup"
#Off-Site copy location
$OffSiteCopyLocation = "\\DR_NAS_Device\MySQLBackup"
#Body of the notification email
$Body = "."
#Map Y drive to the MySQL server's backup location:
net use Y: \\WebServer\DB_Backup /user:"MySQLWinBackup" MySQLWinBackupPassword
#Get a file count for success/failure test
$FileTest = (get-childitem Y:\ | measure).count
#Success/Failure Test - If the file count does not come back as expected, send an email, THEN EXIT THE SCRIPT
If ($FileTest -ne 1){
$Body = "The correct number of files is not present on the MySQL Server to be distributed off-site and off-SAN."
Send-Mailmessage -from "helpdesk@contoso.com" -to "itreporting@contoso.com" -subject "PS Report - MySQL Backup Status - FAILURE - WEBSERVER" -smtpserver contosomailserver -body $body
exit
}
#Copy the files from the MySQL Server to the off-site and off-SAN locations
copy-item -Path "Y:\*.*" -Destination "$OffSiteCopyLocation"
copy-item -Path "Y:\*.*" -Destination "$OffSANCopyLocation"
#Delete the backup files stored locally on the MySQL server
remove-item "Y:\*.*"
#For off-site location, get the creationtime and delete anything older than 7 days
$Files = (Get-childitem $OffSiteCopyLocation)
Foreach ($file in $files){
$FileAge = ((get-date) - ($file.creationtime)).totaldays
If ($FileAge -gt 7){
remove-item $File.FullName -Force
} #End If
} #End Foreach
#For off-SAN location, get the creationtime and delete anything older than 7 days
$Files = (Get-childitem $OffSANCopyLocation)
Foreach ($file in $files){
$FileAge = ((get-date) - ($file.creationtime)).totaldays
If ($FileAge -gt 7){
remove-item $File.FullName -Force
} #End If
} #End Foreach
#Send a success notification email
Send-Mailmessage -from "helpdesk@contoso.com" -to "itreporting@contoso.com" -subject "PS Report - MySQL Backup Status - SUCCESS - WEBSERVER" -smtpserver contosomailserver -body $body
#Unmap the MySQL Backup drive
net use Y: /delete
#--------------------- END SCRIPT -------------------------------
- I save this script in the C:\PS folder on my backup server, then set a scheduled task to run the script at an appropriate time. How I determine what's appropriate? I look at the run time of the backup on the server itself, and then pick what's comfortable to me. In my case, the backup takes about 2 minutes to run, so I set this task to execute 10 minutes after the start of the MySQL backup script.
The resulting workflow is:
- MySQL Server backs up databases to its C drive.
- Backup server checks for the existence of the new backup file. If not ok, send failure email alert.
- Backup server copies that file to 2 other locations, then deleted the originals on the MySQL Server.
- Backup server deletes backup files older than 7 days at the 2 other locations.
- Backup Server sends success email alert.
