PowerShell to perform MySQL DB Backup and archiving with success/failure email alert

Hi Guys, this is another exciting post on PowerShell. You will find this post very much useful if you work with MySQL on windows platforms, as this post will tell you how to address the real-life requirement where one will ask you to provide solution for MySQL DB backups.

One of our environment uses MySQL on windows server and for this I have been tasked with below requirement.

1.       Solution to perform daily full backup of MySQL DB
2.       Solution to perform archive operations on these DB backups as a 2^nd step
3.       Solution to perform deletion of DB backup files once step 2 completes
4.       Solution to remove DB backup archives older than certain days
5.       *Solution to get email alert regarding success/failure of above actions

So, I have developed a PowerShell script which will address all these above requirements, i will discuss this script in this post and you can find the complete script at the end of this post. If you find these requirements useful or interesting, then you are at the right place as your goanna get the solution for these requirements.

Let’s Get Started.

This below portion of the script is the beginning of the script. This portion of the script is consisting of variables and their respective values which will be utilized during the execution of the script.

Here is the explanation on the above code

$mysqlpath = "C:\Program Files\MySQL\MySQL Server 5.5\bin"

To set the MySQl path to the MySQL installed bin folder

$backuppath = "C:\data\BlogDBBackup\"

 To specify where we need to store the backups

$7zippath = "C:\Program Files (x86)\7-Zip"

Path for 7zip installation (7zip will be needed for archival feature of this script)

$config = "C:\ADMIN\config.cnf"

Config file contains the mysql credential (MySQL backup will not work if credentials are directly placed inside script)
The config files contents simply looks like below.

$database = "blog"

Name of the database we want to backup

$errorLog = "C:\ADMIN\error_dump.log"

Logfile to log the errors if any

$mymail = "recipient@testing.com"

MailId to which we want alerts should be sent

$date = Get-Date

$timestamp=""+$date.day + $date.month + $date.year + "_" + $date.hour + $date.minute

To have the timestamp ready, to be used while uniquely renaming the DB backups

$backupfile = $backuppath + $database + "_" + $timestamp +".sql"

Backup file name generation

$backupzip = $backuppath + $database + "_" + $timestamp +".zip"

Backup archival file name generation

After all these variables are set, the next below portion of the script will be consist of actual operations like backup, archival, deletion and alerting.

For better understanding of the above code block please go through below.

try

{

#this block is performing backup

CD $mysqlpath

.\mysqldump.exe --defaults-extra-file=$config --log-error=$errorLog  --result-file=$backupfile  --databases $database /c

#this block is performing backup archiving

CD $7zippath

.\7z.exe a -tzip $backupzip $backupfile

 

#this block is performing delete operations for .sql file

Del $backupfile

 

#this block is performing delete operations for older archives

CD $backuppath

$oldbackups = gci *.zip*

for($i=0; $i -lt $oldbackups.count; $i++){

    if ($oldbackups[$i].CreationTime -lt $date.AddDays(-3)){

        $oldbackups[$i] | Remove-Item -Confirm:$false

    }

}

#this block sending email alert for successful operations

Send-MailMessage -to $mymail -From "eg@bkp.com" -Subject "Success suject" -Body "MSG body for success" -SmtpServer 127.0.0.1

}

catch

{

#this block sending email alert for failuer operations

Send-MailMessage -to $mymail -From "eg@bkp.com" -Subject "Failuer subject" -Body "MSG body for failuer" -SmtpServer 127.0.0.1

}

So above is the complete solution for the requirements discussed in this post. Below is the link for the complete script.

Script Link

Kindly Share your experience in comment section with my blog/post if it did help you !!

Stay Blessed!!  
Thank You!!