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 $configMysqlHost = "localhost"
  12 $configMysqlPort = 3306
  13 $configMysqlUser = "backup"
  14 $configMysqlPassword = "backup"
  15
  16 $configMysqlCli = "C:\Program Files\MariaDB 10.5\bin\mysql.exe"
  17 $configMysqldumpCli = "C:\Program Files\MariaDB 10.5\bin\mysqldump.exe"
  18
  19 $configBackupDir = "backup"
  20 $configRotate = 7
  21
  22 $configDbBackup = @()
  23 $configDbExclude = @("test")
  24 $configDbExcludePattern = @()
  25
  26 # End of config
  27
  28 function Get-Databases() {
  29     $databaseString = (& $configMysqlCli --host=$configMysqlHost --port=$configMysqlPort --user=$configMysqlUser --password=$configMysqlPassword --batch --skip-column-names -e "SHOW DATABASES;")
  30
  31     if($LastExitCode -ne 0) {
  32         throw "MySQL CLI exited with Exit code $LastExitCode"
  33     }
  34
  35     $databases = $databaseString.split([Environment]::NewLine)
  36
  37     return $databases
  38 }
  39
  40 function Create-Backup([String]$database, [String]$target) {
  41     & $configMysqldumpCli --host=$configMysqlHost --port=$configMysqlPort --user=$configMysqlUser --password=$configMysqlPassword --single-transaction --result-file=$target $database
  42
  43     if($LastExitCode -ne 0) {
  44         throw "mysqldump exited with Exit code $LastExitCode"
  45     }
  46 }
  47
  48 function Rotate-Backups($backupDir) {
  49     if($configRotate -le 0) {
  50         return
  51     }
  52
  53     $keepBackupsCount = $configRotate
  54
  55     Get-ChildItem $backupDir -File | Where-Object {($_.Name -match "^backup-.+-\d{8,}-\d{6}\.sql$")} | Sort-Object -Descending |
  56     Foreach-Object {
  57         if($keepBackupsCount -ge 0) {
  58             $keepBackupsCount--
  59         }
  60
  61         if($keepBackupsCount -eq -1) {
  62             Write-Output "Deleting backup $($_.FullName)"
  63             Remove-Item -Force $_.FullName
  64         }
  65     }
  66 }
  67
  68 $defaultDbExclude = @("information_schema", "performance_schema")
  69
  70 $currDaytime = Get-Date -format "yyyyMMdd-HHmmss"
  71
  72 try {
  73     $databases = Get-Databases | Where-Object {!($_ -in $defaultDbExclude)}
  74 }
  75 catch {
  76     Write-Output "Failed to get list of databases"
  77     Write-Output $_
  78     exit 1
  79 }
  80
  81 $databasesToBackup = @()
  82
  83 if($configDbBackup -and $configDbBackup.count -gt 0) {
  84     foreach($cDb in $configDbBackup) {
  85         if($cDb -in $databases) {
  86             $databasesToBackup += $cDb
  87         }
  88         else {
  89             Write-Warning "Not backing up database $cDb, because it does not exist"
  90         }
  91     }
  92 }
  93 else {
  94     :excludeOuter foreach($rDb in $databases) {
  95         if($rDb -in $configDbExclude) {
  96             continue;
  97         }
  98
  99         foreach($cPattern in $configDbExcludePattern) {
 100             if($rDb -match $cPattern) {
 101                 continue excludeOuter;
 102             }
 103         }
 104
 105         $databasesToBackup += $rDb
 106     }
 107 }
 108
 109 foreach($d in $databasesToBackup) {
 110     $databaseBackupDir = Join-Path -Path $configBackupDir -ChildPath $d
 111
 112     if(!(Test-Path $databaseBackupDir)) {
 113         New-Item -ItemType directory -Path $databaseBackupDir -ErrorAction Stop | Out-Null
 114     }
 115
 116     $databaseBackupFile = Join-Path -Path $databaseBackupDir -ChildPath "backup-$d-$currDaytime.sql"
 117     Write-Output "Backing up $d to $databaseBackupFile..."
 118
 119     try {
 120         Create-Backup $d $databaseBackupFile
 121         Rotate-Backups $databaseBackupDir
 122     }
 123     catch {
 124         Write-Output "Could not backup database $d to $databaseBackupFile"
 125         Write-Output $_
 126     }
 127 }