psmysqlbackup.ps1

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