X-Git-Url: https://git.p6c8.net/psmysqlbackup.git/blobdiff_plain/694ded57142ef77ca04d8306843230943ab4b0c9..dc12c9e1658cd18f9dee590b5b47a195b8620060:/psmysqlbackup.ps1 diff --git a/psmysqlbackup.ps1 b/psmysqlbackup.ps1 index f67619b..bbfc075 100644 --- a/psmysqlbackup.ps1 +++ b/psmysqlbackup.ps1 @@ -1,21 +1,162 @@ +# PSMySQLBackup +# PowerShell script for backing up MySQL / MariaDB databases on Windows +# +# Author: Patrick Canterino +# WWW: https://www.patrick-canterino.de/ +# https://github.com/pcanterino/psmysqlbackup +# License: 2-Clause BSD License + # Config +# MySQL host $configMysqlHost = "localhost" +# Port of MySQL host $configMysqlPort = 3306 +# MySQL user using to connect to MySQL $configMysqlUser = "backup" +# Password for MySQL user $configMysqlPassword = "backup" +# Path to MySQL CLI program $configMysqlCli = "C:\Program Files\MariaDB 10.5\bin\mysql.exe" +# Path to mysqldump CLI program $configMysqldumpCli = "C:\Program Files\MariaDB 10.5\bin\mysqldump.exe" +# Directory where to store the backups $configBackupDir = "backup" +# Number of backups to keep, set to 0 to keep all backups $configRotate = 7 +# Directory where to store the logfiles +$configLogDir = "log" +# Number of logfiles to keep, set to 0 to keep all logfiles +$configLogRotate = 7 + +# Databases to backup, leave empty to backup all databases $configDbBackup = @() -$configDbExclusions = @("test") +# If $configDbBackup is empty, don't backup the databases defined here +$configDbExclude = @("test") +# If $configDbBackup is empty, don't backup the databases matching these patterns +$configDbExcludePattern = @() # End of config +<# +.Synopsis + Write-Log writes a message to a specified log file with the current time stamp. +.DESCRIPTION + The Write-Log function is designed to add logging capability to other scripts. + In addition to writing output and/or verbose you can write to a log file for + later debugging. +.NOTES + Created by: Jason Wasser @wasserja + Modified: 11/24/2015 09:30:19 AM + + Changelog: + * Code simplification and clarification - thanks to @juneb_get_help + * Added documentation. + * Renamed LogPath parameter to Path to keep it standard - thanks to @JeffHicks + * Revised the Force switch to work as it should - thanks to @JeffHicks + + To Do: + * Add error handling if trying to create a log file in a inaccessible location. + * Add ability to write $Message to $Verbose or $Error pipelines to eliminate + duplicates. +.PARAMETER Message + Message is the content that you wish to add to the log file. +.PARAMETER Path + The path to the log file to which you would like to write. By default the function will + create the path and file if it does not exist. +.PARAMETER Level + Specify the criticality of the log information being written to the log (i.e. Error, Warning, Informational) +.PARAMETER NoClobber + Use NoClobber if you do not wish to overwrite an existing file. +.EXAMPLE + Write-Log -Message 'Log message' + Writes the message to c:\Logs\PowerShellLog.log. +.EXAMPLE + Write-Log -Message 'Restarting Server.' -Path c:\Logs\Scriptoutput.log + Writes the content to the specified log file and creates the path and file specified. +.EXAMPLE + Write-Log -Message 'Folder does not exist.' -Path c:\Logs\Script.log -Level Error + Writes the message to the specified log file as an error message, and writes the message to the error pipeline. +.LINK + https://gallery.technet.microsoft.com/scriptcenter/Write-Log-PowerShell-999c32d0 +#> +function Write-Log +{ + [CmdletBinding()] + Param + ( + [Parameter(Mandatory=$true, + ValueFromPipelineByPropertyName=$true)] + [ValidateNotNullOrEmpty()] + [Alias("LogContent")] + [string]$Message, + + [Parameter(Mandatory=$false)] + [Alias('LogPath')] + [string]$Path='C:\Logs\PowerShellLog.log', + + [Parameter(Mandatory=$false)] + [ValidateSet("Error","Warn","Info")] + [string]$Level="Info", + + [Parameter(Mandatory=$false)] + [switch]$NoClobber + ) + + Begin + { + # Set VerbosePreference to Continue so that verbose messages are displayed. + $VerbosePreference = 'Continue' + } + Process + { + + # If the file already exists and NoClobber was specified, do not write to the log. + if ((Test-Path $Path) -AND $NoClobber) { + Write-Error "Log file $Path already exists, and you specified NoClobber. Either delete the file or specify a different name." + Return + } + + # If attempting to write to a log file in a folder/path that doesn't exist create the file including the path. + elseif (!(Test-Path $Path)) { + Write-Verbose "Creating $Path." + $NewLogFile = New-Item $Path -Force -ItemType File + } + + else { + # Nothing to see here yet. + } + + # Format Date for our Log File + $FormattedDate = Get-Date -Format "yyyy-MM-dd HH:mm:ss" + + # Write message to error, warning, or verbose pipeline and specify $LevelText + switch ($Level) { + 'Error' { + Write-Error $Message + $LevelText = 'ERROR:' + } + 'Warn' { + Write-Warning $Message + $LevelText = 'WARNING:' + } + 'Info' { + Write-Verbose $Message + $LevelText = 'INFO:' + } + } + + # Write log entry to $Path + "$FormattedDate $LevelText $Message" | Out-File -FilePath $Path -Append + } + End + { + } +} + function Get-Databases() { $databaseString = (& $configMysqlCli --host=$configMysqlHost --port=$configMysqlPort --user=$configMysqlUser --password=$configMysqlPassword --batch --skip-column-names -e "SHOW DATABASES;") @@ -35,66 +176,133 @@ function Create-Backup([String]$database, [String]$target) { throw "mysqldump exited with Exit code $LastExitCode" } } +function Invoke-FileRotation { + Param ( + $Dir, + $MaxFiles, + [Parameter(Mandatory=$false)] + $Pattern, + [Parameter(Mandatory=$false)] + $LogFile + ) -function Rotate-Backups($backupDir) { - if($configRotate -le 0) { + if($MaxFiles -le 0) { return } - - $keepBackupsCount = $configRotate - Get-ChildItem $backupDir -File | Where-Object {($_.Name -match "^backup-.+-\d{8,}-\d{6}\.sql$")} | Sort-Object -Descending | + $keepFilesCount = $MaxFiles + + Get-ChildItem $Dir -File | Where-Object {($null -eq $Pattern -or $_.Name -match $Pattern)} | Sort-Object -Descending | Foreach-Object { - if($keepBackupsCount -ge 0) { - $keepBackupsCount-- + if($keepFilesCount -ge 0) { + $keepFilesCount-- } - - if($keepBackupsCount -eq -1) { - Write-Output "Deleting backup $($_.FullName)" + + if($keepFilesCount -eq -1) { + Write-Output "Deleting file $($_.FullName)" + + if($null -ne $LogFile) { + Write-Log "Deleting file $($_.FullName)" -Path $LogFile + } + Remove-Item -Force $_.FullName } } } -$defaultExclusions = @("information_schema", "performance_schema") +$defaultDbExclude = @("information_schema", "performance_schema") $currDaytime = Get-Date -format "yyyyMMdd-HHmmss" +$logFile = "$configLogDir\log-$currDaytime.log" + +$startTime = Get-Date -format "yyyy-MM-dd HH:mm:ss" +Write-Log "Started at $startTime" -Path $logFile + +# Get a list of all databases try { - $databases = Get-Databases | Where-Object {!($_ -in $defaultExclusions -or $_ -in $configDbExclusions)} + $databases = Get-Databases | Where-Object {!($_ -in $defaultDbExclude)} } catch { + Write-Log "Failed to get list of databases" -Path $logFile -Level Error + Write-Log $_ -Path $logFile -Level Error + Write-Log "Exiting" -Path $logFile -Level Error + Write-Output "Failed to get list of databases" Write-Output $_ + exit 1 } +# Create a list of databases to backup + $databasesToBackup = @() if($configDbBackup -and $configDbBackup.count -gt 0) { - $databasesToBackup = $configDbBackup + foreach($cDb in $configDbBackup) { + if($cDb -in $databases) { + $databasesToBackup += $cDb + } + else { + Write-Log "Not backing up database $cDb, because it does not exist" -Path $logFile -Level Warn + Write-Warning "Not backing up database $cDb, because it does not exist" + } + } } else { - $databasesToBackup = $databases + :excludeOuter + foreach($rDb in $databases) { + if($rDb -in $configDbExclude) { + continue; + } + + foreach($cPattern in $configDbExcludePattern) { + if($rDb -match $cPattern) { + continue excludeOuter; + } + } + + $databasesToBackup += $rDb + } } +# Iterate over the list of databases and back them up and rotate the backups foreach($d in $databasesToBackup) { $databaseBackupDir = Join-Path -Path $configBackupDir -ChildPath $d if(!(Test-Path $databaseBackupDir)) { - New-Item -ItemType directory -Path $databaseBackupDir -ErrorAction Stop | Out-Null + try { + New-Item -ItemType directory -Path "$databaseBackupDir" -ErrorAction Stop | Out-Null + } + catch { + Write-Log "Failed to create directory $databaseBackupDir" -Path $logFile -Level Error + Write-Log $_ -Path $logFile -Level Error + Write-Log "Exiting" -Path $logFile -Level Error + + Write-Output "Failed to create directory $databaseBackupDir" + Write-Output $_ + + exit 1 + } } $databaseBackupFile = Join-Path -Path $databaseBackupDir -ChildPath "backup-$d-$currDaytime.sql" + + Write-Log "Backing up $d to $databaseBackupFile..." -Path $logFile Write-Output "Backing up $d to $databaseBackupFile..." try { Create-Backup $d $databaseBackupFile + Invoke-FileRotation -Dir $databaseBackupDir -MaxFiles $configRotate -Pattern "^backup-.+-\d{8,}-\d{6}\.sql$" -LogFile $logFile } catch { + Write-Log "Could not backup database $d to $databaseBackupFile" -Path $logFile -Level Error + Write-Log $_ -Path $logFile -Level Error + Write-Output "Could not backup database $d to $databaseBackupFile" Write-Output $_ } - - Rotate-Backups $databaseBackupDir -} \ No newline at end of file +} + +$endTime = Get-Date -format "yyyy-MM-dd HH:mm:ss" +Write-Log "Ended at $endTime" -Path $logFile \ No newline at end of file