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