psmysqlbackup.ps1

   1 [String]$configMysqlHost = "localhost"
   2 [String]$configMysqlUser = "backup"
   3 [String]$configMysqlPassword = "backup"
   4
   5 [String]$configMysqlCli = "C:\Program Files\MariaDB 10.5\bin\mysql.exe"
   6 [String]$configMysqldumpCli = "C:\Program Files\MariaDB 10.5\bin\mysqldump.exe"
   7
   8 [String]$configBackupDir = "backup"
   9 [Int32]$configRotate = 7
  10
  11 function Get-Databases() {
  12     $databaseString = (& $configMysqlCli --host=$configMysqlHost --user=$configMysqlUser --password=$configMysqlPassword --batch --skip-column-names -e "SHOW DATABASES;")
  13     $databases = $databaseString.split([Environment]::NewLine)
  14
  15     return $databases
  16 }
  17
  18 function Create-Backup([String]$database, [String]$target) {
  19     & $configMysqldumpCli --host=$configMysqlHost --user=$configMysqlUser --password=$configMysqlPassword --single-transaction --result-file=$target $database
  20 }
  21
  22 function Rotate-Backups($backupDir) {
  23     if($configRotate -le 0) {
  24                 return
  25         }
  26
  27         $keepBackupsCount = $configRotate
  28
  29         Get-ChildItem $backupDir -File | Where-Object {($_.Name -match "^backup-.+-\d{8,}-\d{6}\.sql$")} | Sort-Object -Descending |
  30         Foreach-Object {
  31                 if($keepBackupsCount -ge 0) {
  32                         $keepBackupsCount--
  33                 }
  34
  35                 if($keepBackupsCount -eq -1) {
  36                         Write-Output "Deleting backup $($_.FullName)"
  37                         Remove-Item -Force $_.FullName
  38                 }
  39         }
  40 }
  41
  42 $currDaytime = Get-Date -format "yyyyMMdd-HHmmss"
  43
  44 $databases = Get-Databases | Where-Object { $_ -ne "information_schema" -and $_ -ne "performance_schema"}
  45
  46 foreach($d in $databases) {
  47     $databaseBackupDir = Join-Path -Path $configBackupDir -ChildPath $d
  48
  49     if(!(Test-Path $databaseBackupDir)) {
  50         New-Item -ItemType directory -Path $databaseBackupDir -ErrorAction Stop | Out-Null
  51     }
  52
  53     $databaseBackupFile = Join-Path -Path $databaseBackupDir -ChildPath "backup-$d-$currDaytime.sql"
  54     Write-Output "Backing up $d to $databaseBackupFile..."
  55     Create-Backup $d $databaseBackupFile
  56     Rotate-Backups $databaseBackupDir
  57 }