Using PowerShell to backup your MySQL databases
A few months ago we had to find a way to backup our MySQL databases, both remotely and locally. Instead of trying to find a software that did something similar I decided to write a few lines of code that solved our problem. Why spend time and money evaluating software when can create your own solution to the problem with little to no cost, right ?
The snippet below is fairly simple. The $databases variable holds an array of all the databases you want to dump. You will need to specify the database name, the path you wish to save the backup and the backup filename. I took the liberty of commenting the code in order to make it a bit more clear.
Please keep in mind that this solution uses mysqldump. This executable is usually located under C:\Program Files\MySQL\MySQL Server 5.6\bin, assuming you have MySQL server installed. In addition to mysqldump, since all our Windows servers we have WinRAR installed, this snippet uses WinRAR in order to compress the database files. You can always modify the code either skip the compression or to use .NET’s ZipFile class or 7zip.
This solution still needs some tweaking. For example, creating a temporary file with the passwords and passing it as an argument to mysqldump to avoid the “Specifying a password on the command line should be considered insecure” message, as well as some code cleanup and error catching. Until then, you are free to modify this example to better suit your needs.
If you have any questions feel free to post them in the comment section below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 | function ZipAndDeleteFile([string] $file, [string] $saveLocation) { $command = [string]::Format("`"{0}`" a -ep -df `"$saveLocation`" `"$file`"", $winrarPath); iex "& $command"; } function GetCurrentDateTime { return Get-Date -Format yyyyMMdd_HHmmss; } Class DatabaseEntry { [string] $DatabaseName; [string] $DatabaseBackupPath; [string] $DatabaseFilename; DatabaseEntry([string] $databaseName, $databaseBackupPath, $databaseFilename) { $this.DatabaseName = $databaseName; $this.DatabaseBackupPath = $databaseBackupPath; $this.DatabaseFilename = $databaseFilename; } } $debugMode = $false; # Show errors from mysqldump $winrarPath = "C:\Program Files\WinRAR\WinRAR.exe"; # Path to WinRAR $logFilePath = "C:\Database Backups\"; # Folder where the log file will be created in $logFileName = "backup_log.log"; # Filename of the log file $logFileFullPath = [io.path]::combine($logFilePath, $logFileName); # The full path of the log file (ignore this, it will get populated from the 2 variables above) $mysqlDumpLocation = "C:\MySQL\mysqldump.exe"; # Path to the mysqldump executable. This is required in order to be able to dump the databases. <# The path where the database files will be stored. This is simply here to speed up the assigning of paths for the DatabaseEntry objects. You can set an entirely different path for each database. #> $databaseBackupLocation = "C:\Database Backups\MySQL"; $databaseIp = "<Your MySQL server IP>"; # Database IP or Hostname $databaseUsername = "<MySQL User>"; # Database username (it should have access to all the databases you need to backup) $databasePassword = "<MySQL password for user>"; # The password of the user <# Here is where you specify which databases you want to backup. The DatabaseEntry object takes three arguments: 1) The database name, this should match the name of the database you have in your MySQL server 2) The path where you want the database to be stored (without the filename) 3) The filename of backup file without the extension (a timestamp will be added automatically so you do need to worry about it) #> $databases = New-Object System.Collections.ArrayList; [void]$databases.Add([DatabaseEntry]::New("database1", [io.path]::combine($databaseBackupLocation, "database1_foldername"), "database1")); [void]$databases.Add([DatabaseEntry]::New("database2", [io.path]::combine($databaseBackupLocation, "database2_foldername"), "database2")); [void]$databases.Add([DatabaseEntry]::New("database3", [io.path]::combine($databaseBackupLocation, "database3_foldername"), "database3")); md -Force $logFilePath | Out-Null # Create the path for the log file Out-File $logFileFullPath -InputObject "Starting backup operation" -Append; # Iterate and process all the database entries foreach ($database in $databases.GetEnumerator()) { try { Out-File $logFileFullPath -InputObject ([string]::Format("Backing up {0}...",$database.DatabaseFilename)) -Append; $date = GetCurrentDateTime; md -Force $database.DatabaseBackupPath | Out-Null $saveFilePath = [string]::format("{0}\{1}_{2}.sql", $database.DatabaseBackupPath, $database.DatabaseFilename, $date); $command = [string]::format("`"{0}`" -u {1} -p{2} -h {3} --quick --default-character-set=utf8 --routines --events `"{4}`" > `"{5}`"", $mysqlDumpLocation, $databaseUsername, $databasePassword, $databaseIp, $database.DatabaseName, $saveFilePath); $mysqlDumpError; Invoke-Expression "& $command" -ErrorVariable mysqlDumpError; # Execute mysqldump with the required parameters for each database # If debug mode is on then you will see the errors mysqldump generates in the log file. if ($debugMode -eq $true) { Out-File $logFileFullPath -InputObject $mysqlDumpError -Append; } $zipFileLocation = [string]::format("{0}\{1}_{2}.zip", $database.DatabaseBackupPath, $database.DatabaseFilename, $date); ZipAndDeleteFile $saveFilePath $zipFileLocation; # Zip the file and delete the file that was zipped. $logEntry = [string]::Format("[{0}] Successfully backed up {1}", $date, $database.DatabaseName); Out-File $logFileFullPath -InputObject $logEntry -Append; } catch [Exception] { $exceptionMessage = $_.Exception.Message; $logEntry = [string]::Format("[{0}] Failed to backup up {1}. Reason: {2}", $date, $database.DatabaseName, $exceptionMessage); Out-File $logFileFullPath -InputObject $logEntry -Append; } } Out-File $logFileFullPath -InputObject "Backup operation completed" -Append; |
Too bad i gotta list all DB’s manually and change the script every single time a database is created or deleted
This should be used only in cases where you want to backup specific schemas in a database.
If you want to backup everything you could simply use “mysqldump -u root -p –all-databases”
Thanks for the Script, im currently modifying it for our environment. i did have one question though is there a license you have this under? I would like to complete my changes and release it under the MIT License but wanted to ask about that first.
Thank you for your comment Matt, I’m glad you found it useful.
Currently all the code I publish on this website has no restrictions as far as modifications go. This excludes some open source projects which are usually under the GPL license and is mentioned in the readme file or/and the license.txt file.
I will keep it in mind to make it a bit more clear now that you mention it.
As for this script, it falls under the same category as the other ones. In other words, feel free to modify it/use it for whatever purpose you would like (even commercial). No attribution is required.
Let me know if you have any additional questions.