Click an Ad

If you find this blog helpful, please support me by clicking an ad!

Monday, March 31, 2014

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.

4 comments:

  1. While old and for Linux - we did have this whitepaper:
    http://www.veeam.com/wp-hot-backup-of-mysql-on-a-linux-vm.html

    ReplyDelete
  2. You have outdone yourself this time. It is probably the best, most short step by step guide that I have ever seen. DR-Site

    ReplyDelete