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
- Log in interactively to the CentreStack server and in a web browser navigate to this link: Install-MySQL
- Click on the Download current folder icon:
- The Install-MySQL.zip will be downloaded (typically to your Downloads directory in your user profile).
- Extract the Install-MySQL.zip to an empty directory. You will see a handful of files in File Explorer:
- Start an elevated PowerShell session (as Administrator).
- Make sure scripts are allowed to run. Execute:
Set-ExecutionPolicy RemoteSigned
- Change to the directory where the scripts were downloaded for example:
cd $env:UserProfile\Downloads\Install-MySQL
- Unblock the files in the directory:
Get-ChildItem | Unblock-File
- 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 - 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 - 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 - 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" - Perform an initial backup by executing:
.\Backup-MySQL.ps1 - By default the script will save a .zip archive in the C:\MySQLBackups directory:
- 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
- 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:
- Start an elevated PowerShell session (as Administrator).
- Change to the C:\Tools directory:
cd \tools - To create the scheduled task, execute:
.\Backup-MySQL.ps1 -CreateTask - The run the Windows Task Scheduler to see the Backup CentreStack Database Daily task:
- The task is configured to run daily at 3 am:
Comments
4 comments
Great article, also I would mention that it is easy to back up using Robocopy or third-party tools like GoodSync and Gs Richcopy 360
Hi Jeff,
Exactly what i am looking for :-), expect one part is still not working:
When is run the script i receive an error that my windows username ia not authorized to connect to mysql db, while the username is hardcoded “root”. see attached file.
random team name generator all my googling outputs brought to me to article like yours where you described how to change the defualt save location setting to another specifc location, but i have not a specificic location in mind now. I just want word opens or when I brows for files, takes me to the most recent folder I used my very last time, can you help me with this?
nbi online registration login all my googling outputs brought to me to article like yours where you described how to change the defualt save location setting to another specifc location, but i have not a specificic location in mind now. I just want word opens or when I brows for files, takes me to the most recent folder I used my very last time, can you help me with this?
Please sign in to leave a comment.