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