Overview

For those deployments using MySQL Community Server for hosting the CentreStack database, the best practice is to do at least daily backups of the CentreStack database. This is recommended even if the CentreStack virtual machine is backed up using Veeam or some other mechanism of backing up the VM image because sometimes it is simpler and faster to restore the database backup rather than the entire virtual machine, in case of a problem specific to the database. 

MySQL Community Server includes the mysqldump.exe tool for exporting a MySQL database to a text file. This article describes a PowerShell script which can be used to automate mysqldump.exe such that backups are performed on a daily basis, which the backup data compressed into a .zip file which will greatly reduce hard drive space requirements for the backup files.

If you haven't already installed MySQL Community Server, see this link for more information about installation recommendations: Installing MySQL Community Server

Setup

1. Log in interactively to the CentreStack server and in a web browser navigate to this link: Install-MySQL
2. Click on the Download current folder icon:
   
3. The Install-MySQL.zip will be downloaded (typically to your Downloads directory in your user profile).
4. Extract the Install-MySQL.zip to an empty directory. You will see a handful of files in File Explorer:
   
5. Start an elevated PowerShell session (as Administrator).
6. Make sure scripts are allowed to run. Execute: 

   Set-ExecutionPolicy RemoteSigned

7. Change to the directory where the scripts were downloaded for example:

   cd $env:UserProfile\Downloads\Install-MySQL

8. Unblock the files in the directory:

   Get-ChildItem | Unblock-File

9. The script that is relevant to this article in the Backup-MySQL.ps1 script. To view the script's comment based help, execute the script like this:
   

   Get-Help .\Backup-MySQL.ps1 -Full

10. This text will be displayed:
    

        NAME
        C:\Users\JeffR\Downloads\Install-MySQL\Backup-MySQL.ps1
    
    SYNOPSIS
        Backs up the CentreStack MySQL database
    
    
    SYNTAX
        C:\Users\JeffR\Downloads\Install-MySQL\Backup-MySQL.ps1 [-BackupDir ] [-Database ] [-Days ]
        []
    
        C:\Users\JeffR\Downloads\Install-MySQL\Backup-MySQL.ps1 [-CreateTask] []
    
        C:\Users\JeffR\Downloads\Install-MySQL\Backup-MySQL.ps1 [-Config] []
    
    
    DESCRIPTION
        Backs up the CentreStack MySQL database
    
    
    PARAMETERS
        -CreateTask []
            Creates a Windows scheduled task where the command line of the scheduled task calls this PowerShell script
    
            Required?                    false
            Position?                    named
            Default value                False
            Accept pipeline input?       false
            Accept wildcard characters?  false
    
        -Config []
            Prompts the user to enter the password of the MySQL root user. Stores the password
            in encrypted format in %localappdata%\Backup-MySQL\config.json. Later, the script will read the password from
            this file in order to connect the MySQL server as root.
    
            Required?                    false
            Position?                    named
            Default value                False
            Accept pipeline input?       false
            Accept wildcard characters?  false
    
        -BackupDir 
            Specifies the directory where the mysqldump.exe .zip files will be stored. Defaults to C:\MySQLBackups.
    
            Required?                    false
            Position?                    named
            Default value                C:\MySQLBackups
            Accept pipeline input?       false
            Accept wildcard characters?  false
    
        -Database 
            The name of the database to back up. Defaults to csmain.
    
            Required?                    false
            Position?                    named
            Default value                csmain
            Accept pipeline input?       false
            Accept wildcard characters?  false
    
        -Days 
            mysqldump*.zip files in the BackupDir older then the number of Days specified will be removed. Defaults to 30
            days.
            Either a positive or negative value may be entered, it's always converted to negative
            meaning older than this number of days. Entering a zero for this parameter means remove all previous backup
            files.
    
            Required?                    false
            Position?                    named
            Default value                30
            Accept pipeline input?       false
            Accept wildcard characters?  false
    
        
            This cmdlet supports the common parameters: Verbose, Debug,
            ErrorAction, ErrorVariable, WarningAction, WarningVariable,
            OutBuffer, PipelineVariable, and OutVariable. For more information, see
            about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
    
    INPUTS
    
    OUTPUTS
    
    NOTES
    
    
            Author: Jeff Reed
            Name: Backup-MySQL.ps1
            Created: 2019-08-28
    
            Version History
            2018-08-28  1.0.0   Initial version
            2018-08-28  1.0.1   Document Database parameter
            2018-08-28  1.0.2   Change scheduled task Argument format to support script's command line parameters
    
            Requires -Version 5.1
            Requires -RunAsAdministrator
    
        -------------------------- EXAMPLE 1 --------------------------
    
        PS C:\>.\Backup-MySQL.ps1
    
    
    
    
    
    
        -------------------------- EXAMPLE 2 --------------------------
    
        PS C:\>.\Backup-MySQL.ps1 -CreateTask
    
    
    
    
    
    
        -------------------------- EXAMPLE 3 --------------------------
    
        PS C:\>.\Backup-MySQL.ps1 -Config
    
    
    
    
    
    
        -------------------------- EXAMPLE 4 --------------------------
    
        PS C:\>.\Backup-MySQL.ps1 -Database "csmain" -BackupDir "D:\MySQLBackups" -Days 14

11. In order to execute mysqldump.exe without hard coding a password on the command line, your user profile requires the file %localappdata%\Backup-MySQL\config.json which will contain the password of the MySQL root account. Execute this command to create the %localappdata%\Backup-MySQL\config.json file:
    

    .\Backup-MySQL.ps1 -Config

12. The script prompts for the root account's password:
    
    Enter the password for the root account, and press Enter. If you ran the Install-MySQL.ps1 script to install MySQL, then the root account password is saved in "%USERPROFILE%\Documents\mysql-init.txt"
13. Perform an initial backup by executing:
    

    .\Backup-MySQL.ps1

14. By default the script will save a .zip archive in the C:\MySQLBackups directory:
    
15. If you extract the file inside the .zip you will find a .sql text file that could be used to recreate the database including the schema and the data:
    

Running the Script as a Scheduled Task

1. The script has the capability of creating a scheduled task that executes the script each day. Copy the Backup-MySQL.ps1 and Common-Functions.ps1 scripts into a directory like C:\Tools:
   
2. Start an elevated PowerShell session (as Administrator).
3. Change to the C:\Tools directory:
   

   cd \tools

4. To create the scheduled task, execute:
   

   .\Backup-MySQL.ps1 -CreateTask

5. The run the Windows Task Scheduler to see the Backup CentreStack Database Daily task:
   
6. The task is configured to run daily at 3 am: