Automated Backup of a MySQL Database

I have a couple MySQL databases out here in the wilderness. I use Veeam Backup and Replication to back up my virtual machines, but Veeam doesn't have any way to quiet MySQL database activity down so that it can get a proper backup, that I'm aware of. So, I got my hands dirty and created the following process to automatically back up MySQL databases.

• 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:

1. MySQL Server backs up databases to its C drive.
2. Backup server checks for the existence of the new backup file. If not ok, send failure email alert.
3. Backup server copies that file to 2 other locations, then deleted the originals on the MySQL Server.
4. Backup server deletes backup files older than 7 days at the 2 other locations.
5. Backup Server sends success email alert.