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