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