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 # MySQL host
  12 $configMysqlHost = "localhost"
  13 # Port of MySQL host
  14 $configMysqlPort = 3306
  15 # MySQL user using to connect to MySQL
  16 $configMysqlUser = "backup"
  17 # Password for MySQL user
  18 $configMysqlPassword = "backup"
  19
  20 # Path to MySQL CLI program
  21 $configMysqlCli = "C:\Program Files\MariaDB 10.5\bin\mysql.exe"
  22 # Path to mysqldump CLI program
  23 $configMysqldumpCli = "C:\Program Files\MariaDB 10.5\bin\mysqldump.exe"
  24
  25 # Directory where to store the backups
  26 $configBackupDir = "backup"
  27 # Number of backups to keep, set to 0 to keep all backups
  28 $configRotate = 7
  29
  30 # Databases to backup, leave empty to backup all databases
  31 $configDbBackup = @()
  32 # If $configDbBackup is empty, don't backup the databases defined here
  33 $configDbExclude = @("test")
  34 # If $configDbBackup is empty, don't backup the databases matching these patterns
  35 $configDbExcludePattern = @()
  36
  37 # End of config
  38
  39 function Get-Databases() {
  40     $databaseString = (& $configMysqlCli --host=$configMysqlHost --port=$configMysqlPort --user=$configMysqlUser --password=$configMysqlPassword --batch --skip-column-names -e "SHOW DATABASES;")
  41
  42     if($LastExitCode -ne 0) {
  43         throw "MySQL CLI exited with Exit code $LastExitCode"
  44     }
  45
  46     $databases = $databaseString.split([Environment]::NewLine)
  47
  48     return $databases
  49 }
  50
  51 function Create-Backup([String]$database, [String]$target) {
  52     & $configMysqldumpCli --host=$configMysqlHost --port=$configMysqlPort --user=$configMysqlUser --password=$configMysqlPassword --single-transaction --result-file=$target $database
  53
  54     if($LastExitCode -ne 0) {
  55         throw "mysqldump exited with Exit code $LastExitCode"
  56     }
  57 }
  58
  59 function Rotate-Backups($backupDir) {
  60     if($configRotate -le 0) {
  61         return
  62     }
  63
  64     $keepBackupsCount = $configRotate
  65
  66     Get-ChildItem $backupDir -File | Where-Object {($_.Name -match "^backup-.+-\d{8,}-\d{6}\.sql$")} | Sort-Object -Descending |
  67     Foreach-Object {
  68         if($keepBackupsCount -ge 0) {
  69             $keepBackupsCount--
  70         }
  71
  72         if($keepBackupsCount -eq -1) {
  73             Write-Output "Deleting backup $($_.FullName)"
  74             Remove-Item -Force $_.FullName
  75         }
  76     }
  77 }
  78
  79 $defaultDbExclude = @("information_schema", "performance_schema")
  80
  81 $currDaytime = Get-Date -format "yyyyMMdd-HHmmss"
  82
  83 # Get a list of all databases
  84 try {
  85     $databases = Get-Databases | Where-Object {!($_ -in $defaultDbExclude)}
  86 }
  87 catch {
  88     Write-Output "Failed to get list of databases"
  89     Write-Output $_
  90     exit 1
  91 }
  92
  93 # Create a list of databases to backup
  94
  95 $databasesToBackup = @()
  96
  97 if($configDbBackup -and $configDbBackup.count -gt 0) {
  98     foreach($cDb in $configDbBackup) {
  99         if($cDb -in $databases) {
 100             $databasesToBackup += $cDb
 101         }
 102         else {
 103             Write-Warning "Not backing up database $cDb, because it does not exist"
 104         }
 105     }
 106 }
 107 else {
 108     :excludeOuter
 109     foreach($rDb in $databases) {
 110         if($rDb -in $configDbExclude) {
 111             continue;
 112         }
 113
 114         foreach($cPattern in $configDbExcludePattern) {
 115             if($rDb -match $cPattern) {
 116                 continue excludeOuter;
 117             }
 118         }
 119
 120         $databasesToBackup += $rDb
 121     }
 122 }
 123
 124 # Iterate over the list of databases and back them up and rotate the backups
 125 foreach($d in $databasesToBackup) {
 126     $databaseBackupDir = Join-Path -Path $configBackupDir -ChildPath $d
 127
 128     if(!(Test-Path $databaseBackupDir)) {
 129         try {
 130             New-Item -ItemType directory -Path "$databaseBackupDir" -ErrorAction Stop | Out-Null
 131         }
 132         catch {
 133             Write-Output "Failed to create directory $databaseBackupDir"
 134             Write-Output $_
 135             exit 1
 136         }
 137     }
 138
 139     $databaseBackupFile = Join-Path -Path $databaseBackupDir -ChildPath "backup-$d-$currDaytime.sql"
 140     Write-Output "Backing up $d to $databaseBackupFile..."
 141
 142     try {
 143         Create-Backup $d $databaseBackupFile
 144         Rotate-Backups $databaseBackupDir
 145     }
 146     catch {
 147         Write-Output "Could not backup database $d to $databaseBackupFile"
 148         Write-Output $_
 149     }
 150 }