psmysqlbackup.ps1

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