Backup MySQL Database using PowerShell Script

Performing regular database backup is very important to avoid data loss. In this article, we will create a PowerShell Script to backup MySQL databases. Basically, the backup is using mysqldump command. This is an executable file that stored in MySQL installation folder under bin folder. 

So let’s start scripting :

################################################################
#
# MysqlBackup.ps1
# Author: Ardian
# Updated: Nov 1, 2018
#
# Description:
# This script will query all MySQL databases and then create .sql backup files
# of all located databases.
#
################################################################
#Set up your parameter
$MYSQL_DIR = "C:\Program Files\MySQL\MySQL Server 5.6"
$BACKUP_FOLDER = "C:\MySQLBackup"
$dbuser = [your user]
$dbpass = [your password]
$BACKUPDATE = Get-Date -Format FileDate
$EMAILFROM = [Your Email From]
$EMAILRECIPIENT = [your email recipient]
$SMTPServer = [SMTP SERVER]
# Query and backup MySQL Databases
try {
	Set-Location "$MYSQL_DIR\bin"
		& .\mysql.exe -N -s -r -u $dbuser -p$dbpass -e 'show databases' | % {
		& .\mysqldump.exe -u $dbuser -p$dbpass --single-transaction $_ |
		Out-File "$BACKUP_FOLDER\${_}$BACKUPDATE.sql" -Encoding Ascii
	}
	Send-MailMessage -to $EMAILRECIPIENT -From $EMAILFROM -Subject "Backup MySQL Success" -Body "Backup MySQL Success" -SmtpServer $SMTPServer
}catch{
	Send-MailMessage -to $EMAILRECIPIENT -From $EMAILFROM -Subject "Backup MySQL Failed" -Body "Backup MySQL Failed" -SmtpServer $SMTPServer
}
# END OF SCRIPT

You can set a Task Scheduler to run the script above on daily basis. 

We hope this article can help you to backup your MySQL Databases using PowerShell command. If you liked this article, then please share with the others. You can also find us on Twitter and Facebook.