Backup MySQL Database and Tables on Windows

Written by Brenton Ford on the 22nd Nov 2021

Exported on 13-Jan-2022 22:06:31

Grab this Attune Blueprint to perform MySQL backups for your safety and peace of mind. This script will provide a simple and automated method to backup your MySQL databases or tables. Use Attune for other convenient features such as historical logs and easy configuration by editing parameter values. Say goodbye to human error with this all-inclusive, foolproof backup Attune Blueprint for MySQL.

MySQL, the world's most popular open-source database has become an essential part in many web based applications. It offers performance that is proven and reliable with easy use for everyone to enjoy! MySQL power next generation apps including Facebook Twitter YouTube Yahoo!. Oracle drives innovation by delivering new capabilities which help developers create their own valuable solutions on top of this leading technology.

Blueprint Configuration

This Blueprint uses mysqldump and saves the file with a date and timestamp in the filename. This Blueprint doesn't cleanup old backup files. Please grab the Rotate Files Blueprint to automate the rotation and cleanup of the MySQL backups.

Testing

This Blueprint has been tested on Windows 10 with MySQL Server 8.0 and PowerShell 5.1.

Parameters

Name Type Script Reference Default Value Comment
MySQL Backup Directory Text mysqlBackupDirectory The directory location to save the backup.
MySQL Database Name Text mysqlDatabaseName The database name.
MySQL Program Location Text mysqlProgramLocation Set the value of the MySQL Program location, for example: "C:\Program Files\MySQL\MySQL Server 5.6"
MySQL Table Name Text mysqlTableName The table name. To backup the entire database, load an empty string into this parameter.
MySQL User Generic Credential mysqlUser The MySQL User Credentials
Windows Credential Windows OS Credential windowsCredential
Windows Node Windows Server windowsNode

1 - Create MySQL Backup - WIndows

This step will export a table backup if a table name is specified in the MySQL Table Name parameter. If a blank value is selected in the MySQL Table Name parameter then the database backup will be performed on the database specified in the MySQL Database Name parameter.

The backup will be compressed and deployed in the directory specified in the MySQL Backup Directory parameter. The backup file name will have the date and timestamp in the file name.

This step has the following parameters

Name Script Reference Default Value
MySQL Database Name {mysqlDatabaseName} None
MySQL Program Location {mysqlProgramLocation} None
MySQL Table Name {mysqlTableName} None
MySQL Backup Directory {mysqlBackupDirectory} None
MySQL User {mysqlUser.password} None
MySQL User {mysqlUser.user} None
The connection details have changed from the last step.

Login as user on node

  1. Connect via RDP 
    mstsc /admin /v:Windows Node
  2. Login as user {Windows Credential}
  3. Then open a command prompt
This is a PowerShell Script make sure you run it with powershell.exe Click start menu, enter "powershell" in the search bar, then select the powersehll program 
Write-Host "Creating backup..."

$fileDate = Get-Date -Format "yyyyMMdd-HHmm"

Set-Variable -Name 'fileName' `
-Value "{mysqlDatabaseName}_{mysqlTableName}_$fileDate" -PassThru

if ("{mysqlTableName}") {
    $backupItem = "Table: {mysqlTableName} from Datebase: {mysqlDatabaseName}"
} else {
    $backupItem = "Datebase: {mysqlDatabaseName}"
}

Write-Host "========================"

Write-Host "Backing up $backupItem to {mysqlBackupDirectory}"

Write-Host "Switching directory to MySQL bin..."

Set-Location -Path "{mysqlProgramLocation}\bin" -PassThru

Write-Host "========================"

Write-Host "Run mysqldump..."

./mysqldump.exe -h localhost -u {mysqlUser.user} -p{mysqlUser.password} `
{mysqlDatabaseName} {mysqlTableName} > `
"{mysqlBackupDirectory}\$fileName.sql"

Get-Item "{mysqlBackupDirectory}\$fileName.sql"

Write-Host "========================"

Write-Host "Compressing backup file..."

Compress-Archive -LiteralPath "{mysqlBackupDirectory}\$fileName.sql" `
-DestinationPath "{mysqlBackupDirectory}\$fileName.zip"

Remove-Item "{mysqlBackupDirectory}\$fileName.sql"

Get-Item "{mysqlBackupDirectory}\$fileName.zip"

Write-Host "========================"

Write-Host "Backup completed!"