MySQLDump and VBScript: Dump all databases into separate files with compression

Last Updated on 2012-06-13.

'MySQL Backup Script
'run with "cscript mysql_backup.vbs"

'Configuration
server = "www"
user = "backup"
pw = "BackupPW"
mysql = "mysql.exe"
mysqldump = "mysqldump.exe"
backupdir = "e:mysqlbackupwww"

Set objShell = CreateObject("WScript.Shell")

'List databases
Set objWshScriptExec = objShell.Exec(mysql & " --host=" & server & " -u" & user & " -p" & pw & " -e""SHOW DATABASES""")
Set objStdOut = objWshScriptExec.StdOut

Do Until objStdOut.AtEndOfStream
db = objStdOut.ReadLine

If db <> "Database" Then
filename = db & "_dump.txt"
WScript.Echo "dumping database " & db & " nach " & backupdir & filename
objShell.Run mysqldump & " --routines=true --triggers=true --events=true --host=" & server & " -u" & user & " -p" & pw & " --result-file=" & backupdir & filename & " " & db, 3, true
objShell.Run("c:zip_file_and_delete.bat " & backupdir & filename & ".7z" & " " & backupdir & filename)
End If
Loop

 

zip_file_and_delete.bat contains:

%PROGRAMFILES%7-Zip7z a -t7z %1 %2
if errorlevel 1 goto :error
del %2
:error

 

Install 7-Zip to use compression.