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