psmysqlbackup.ps1

   1 # PSMySQLBackup
   2 # PowerShell script for backing up MySQL / MariaDB databases on Windows
   3 #
   4 # Author: Patrick Canterino <patrick@patrick-canterino.de>
   5 # WWW: https://www.patrick-canterino.de/
   6 #      https://github.com/pcanterino/psmysqlbackup
   7 # License: 2-Clause BSD License
   8
   9 # Config
  10
  11 # MySQL host
  12 $configMysqlHost = "localhost"
  13 # Port of MySQL host
  14 $configMysqlPort = 3306
  15 # MySQL user using to connect to MySQL
  16 $configMysqlUser = "backup"
  17 # Password for MySQL user
  18 $configMysqlPassword = "backup"
  19
  20 # Path to MySQL CLI program
  21 $configMysqlCli = "C:\Program Files\MariaDB 10.5\bin\mysql.exe"
  22 # Path to mysqldump CLI program
  23 $configMysqldumpCli = "C:\Program Files\MariaDB 10.5\bin\mysqldump.exe"
  24
  25 # Directory where to store the backups
  26 $configBackupDir = "backup"
  27 # Number of backups to keep, set to 0 to keep all backups
  28 $configRotate = 7
  29
  30 $configLogDir = "log"
  31 $configLogRotate = 7
  32
  33 # Databases to backup, leave empty to backup all databases
  34 $configDbBackup = @()
  35 # If $configDbBackup is empty, don't backup the databases defined here
  36 $configDbExclude = @("test")
  37 # If $configDbBackup is empty, don't backup the databases matching these patterns
  38 $configDbExcludePattern = @()
  39
  40 # End of config
  41
  42 <#
  43 .Synopsis
  44    Write-Log writes a message to a specified log file with the current time stamp.
  45 .DESCRIPTION
  46    The Write-Log function is designed to add logging capability to other scripts.
  47    In addition to writing output and/or verbose you can write to a log file for
  48    later debugging.
  49 .NOTES
  50    Created by: Jason Wasser @wasserja
  51    Modified: 11/24/2015 09:30:19 AM
  52
  53    Changelog:
  54     * Code simplification and clarification - thanks to @juneb_get_help
  55     * Added documentation.
  56     * Renamed LogPath parameter to Path to keep it standard - thanks to @JeffHicks
  57     * Revised the Force switch to work as it should - thanks to @JeffHicks
  58
  59    To Do:
  60     * Add error handling if trying to create a log file in a inaccessible location.
  61     * Add ability to write $Message to $Verbose or $Error pipelines to eliminate
  62       duplicates.
  63 .PARAMETER Message
  64    Message is the content that you wish to add to the log file.
  65 .PARAMETER Path
  66    The path to the log file to which you would like to write. By default the function will
  67    create the path and file if it does not exist.
  68 .PARAMETER Level
  69    Specify the criticality of the log information being written to the log (i.e. Error, Warning, Informational)
  70 .PARAMETER NoClobber
  71    Use NoClobber if you do not wish to overwrite an existing file.
  72 .EXAMPLE
  73    Write-Log -Message 'Log message'
  74    Writes the message to c:\Logs\PowerShellLog.log.
  75 .EXAMPLE
  76    Write-Log -Message 'Restarting Server.' -Path c:\Logs\Scriptoutput.log
  77    Writes the content to the specified log file and creates the path and file specified.
  78 .EXAMPLE
  79    Write-Log -Message 'Folder does not exist.' -Path c:\Logs\Script.log -Level Error
  80    Writes the message to the specified log file as an error message, and writes the message to the error pipeline.
  81 .LINK
  82    https://gallery.technet.microsoft.com/scriptcenter/Write-Log-PowerShell-999c32d0
  83 #>
  84 function Write-Log
  85 {
  86     [CmdletBinding()]
  87     Param
  88     (
  89         [Parameter(Mandatory=$true,
  90                    ValueFromPipelineByPropertyName=$true)]
  91         [ValidateNotNullOrEmpty()]
  92         [Alias("LogContent")]
  93         [string]$Message,
  94
  95         [Parameter(Mandatory=$false)]
  96         [Alias('LogPath')]
  97         [string]$Path='C:\Logs\PowerShellLog.log',
  98
  99         [Parameter(Mandatory=$false)]
 100         [ValidateSet("Error","Warn","Info")]
 101         [string]$Level="Info",
 102
 103         [Parameter(Mandatory=$false)]
 104         [switch]$NoClobber
 105     )
 106
 107     Begin
 108     {
 109         # Set VerbosePreference to Continue so that verbose messages are displayed.
 110         $VerbosePreference = 'Continue'
 111     }
 112     Process
 113     {
 114
 115         # If the file already exists and NoClobber was specified, do not write to the log.
 116         if ((Test-Path $Path) -AND $NoClobber) {
 117             Write-Error "Log file $Path already exists, and you specified NoClobber. Either delete the file or specify a different name."
 118             Return
 119             }
 120
 121         # If attempting to write to a log file in a folder/path that doesn't exist create the file including the path.
 122         elseif (!(Test-Path $Path)) {
 123             Write-Verbose "Creating $Path."
 124             $NewLogFile = New-Item $Path -Force -ItemType File
 125             }
 126
 127         else {
 128             # Nothing to see here yet.
 129             }
 130
 131         # Format Date for our Log File
 132         $FormattedDate = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
 133
 134         # Write message to error, warning, or verbose pipeline and specify $LevelText
 135         switch ($Level) {
 136             'Error' {
 137                 Write-Error $Message
 138                 $LevelText = 'ERROR:'
 139                 }
 140             'Warn' {
 141                 Write-Warning $Message
 142                 $LevelText = 'WARNING:'
 143                 }
 144             'Info' {
 145                 Write-Verbose $Message
 146                 $LevelText = 'INFO:'
 147                 }
 148             }
 149
 150         # Write log entry to $Path
 151         "$FormattedDate $LevelText $Message" | Out-File -FilePath $Path -Append
 152     }
 153     End
 154     {
 155     }
 156 }
 157
 158 function Get-Databases() {
 159     $databaseString = (& $configMysqlCli --host=$configMysqlHost --port=$configMysqlPort --user=$configMysqlUser --password=$configMysqlPassword --batch --skip-column-names -e "SHOW DATABASES;")
 160
 161     if($LastExitCode -ne 0) {
 162         throw "MySQL CLI exited with Exit code $LastExitCode"
 163     }
 164
 165     $databases = $databaseString.split([Environment]::NewLine)
 166
 167     return $databases
 168 }
 169
 170 function Create-Backup([String]$database, [String]$target) {
 171     & $configMysqldumpCli --host=$configMysqlHost --port=$configMysqlPort --user=$configMysqlUser --password=$configMysqlPassword --single-transaction --result-file=$target $database
 172
 173     if($LastExitCode -ne 0) {
 174         throw "mysqldump exited with Exit code $LastExitCode"
 175     }
 176 }
 177
 178 function Rotate-Backups($backupDir) {
 179     if($configRotate -le 0) {
 180         return
 181     }
 182
 183     $keepBackupsCount = $configRotate
 184
 185     Get-ChildItem $backupDir -File | Where-Object {($_.Name -match "^backup-.+-\d{8,}-\d{6}\.sql$")} | Sort-Object -Descending |
 186     Foreach-Object {
 187         if($keepBackupsCount -ge 0) {
 188             $keepBackupsCount--
 189         }
 190
 191         if($keepBackupsCount -eq -1) {
 192             Write-Output "Deleting backup $($_.FullName)"
 193             #Write-Log "Deleting backup $($_.FullName)"
 194
 195             Remove-Item -Force $_.FullName
 196         }
 197     }
 198 }
 199
 200 $defaultDbExclude = @("information_schema", "performance_schema")
 201
 202 $currDaytime = Get-Date -format "yyyyMMdd-HHmmss"
 203
 204 $logFile = "$configLogDir\log-$currDaytime.log"
 205
 206 $startTime = Get-Date -format "yyyy-MM-dd HH:mm:ss"
 207 Write-Log "Started at $startTime" -Path $logFile
 208
 209 # Get a list of all databases
 210 try {
 211     $databases = Get-Databases | Where-Object {!($_ -in $defaultDbExclude)}
 212 }
 213 catch {
 214     Write-Log "Failed to get list of databases" -Path $logFile -Level Error
 215     Write-Log $_ -Path $logFile -Level Error
 216     Write-Log "Exiting" -Path $logFile -Level Error
 217
 218     Write-Output "Failed to get list of databases"
 219     Write-Output $_
 220
 221     exit 1
 222 }
 223
 224 # Create a list of databases to backup
 225
 226 $databasesToBackup = @()
 227
 228 if($configDbBackup -and $configDbBackup.count -gt 0) {
 229     foreach($cDb in $configDbBackup) {
 230         if($cDb -in $databases) {
 231             $databasesToBackup += $cDb
 232         }
 233         else {
 234             Write-Log "Not backing up database $cDb, because it does not exist" -Path $logFile -Level Warn
 235             Write-Warning "Not backing up database $cDb, because it does not exist"
 236         }
 237     }
 238 }
 239 else {
 240     :excludeOuter
 241     foreach($rDb in $databases) {
 242         if($rDb -in $configDbExclude) {
 243             continue;
 244         }
 245
 246         foreach($cPattern in $configDbExcludePattern) {
 247             if($rDb -match $cPattern) {
 248                 continue excludeOuter;
 249             }
 250         }
 251
 252         $databasesToBackup += $rDb
 253     }
 254 }
 255
 256 # Iterate over the list of databases and back them up and rotate the backups
 257 foreach($d in $databasesToBackup) {
 258     $databaseBackupDir = Join-Path -Path $configBackupDir -ChildPath $d
 259
 260     if(!(Test-Path $databaseBackupDir)) {
 261         try {
 262             New-Item -ItemType directory -Path "$databaseBackupDir" -ErrorAction Stop | Out-Null
 263         }
 264         catch {
 265             Write-Log "Failed to create directory $databaseBackupDir" -Path $logFile -Level Error
 266             Write-Log $_ -Path $logFile -Level Error
 267             Write-Log "Exiting" -Path $logFile -Level Error
 268
 269             Write-Output "Failed to create directory $databaseBackupDir"
 270             Write-Output $_
 271
 272             exit 1
 273         }
 274     }
 275
 276     $databaseBackupFile = Join-Path -Path $databaseBackupDir -ChildPath "backup-$d-$currDaytime.sql"
 277
 278     Write-Log "Backing up $d to $databaseBackupFile..." -Path $logFile
 279     Write-Output "Backing up $d to $databaseBackupFile..."
 280
 281     try {
 282         Create-Backup $d $databaseBackupFile
 283         Rotate-Backups $databaseBackupDir
 284     }
 285     catch {
 286         Write-Log "Could not backup database $d to $databaseBackupFile" -Path $logFile -Level Error
 287         Write-Log $_ -Path $logFile -Level Error
 288
 289         Write-Output "Could not backup database $d to $databaseBackupFile"
 290         Write-Output $_
 291     }
 292 }
 293
 294 $endTime = Get-Date -format "yyyy-MM-dd HH:mm:ss"
 295 Write-Log "Ended at $endTime" -Path $logFile