|set backupDir="C:\Documents and Settings\user\Desktop\backup\mysql"|
|set mysqldump="C:\Program Files\MySQL\MySQL Workbench 5.2 CE\mysqldump.exe"|
|set mysqlDataDir="C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data"|
|set zip="C:\Program Files\7-Zip\7z.exe"|
|:: get date|
|for /F "tokens=2-4 delims=/ " %%i in ('date /t') do (|
|:: get time|
|for /F "tokens=5-8 delims=:. " %%i in ('echo.^| time ^| find "current" ') do (|
|:: switch to the "data" folder|
|:: iterate over the folder structure in the "data" folder to get the databases|
|for /d %%f in (*) do (|
|if not exist %backupDir%\%dirName%\ (|
|%mysqldump% --host="localhost" --user=%dbUser% --password=%dbPassword% --single-transaction --add-drop-table --databases %%f > %backupDir%\%dirName%\%%f.sql|
|%zip% a -tgzip %backupDir%\%dirName%\%%f.sql.gz %backupDir%\%dirName%\%%f.sql|
Thanks., nice script sindresortus!
A couple of small corrections - It will actually create a directory called 20115014_1550 because you reused the %mm% variable twice (set mm=minutes and set mm=months). It's also missing the popd at the end.
Here is a revised version that includes these fixes for those using the ISO date format yyyy-mm-dd format instead of mm/yyyyy/dd format:
Hai thx for the script, there are some trivial bugs from your @michael-milette script, here the script that i fixed.
Thx for the script! Can you tell me if there is a way to easily edit the script to allow for multiple databases to be written to separate backup files? In my instance we are running on a Windows Server and we have multiple databases on this machine for development purposes and I am needing to separate out the database backups.
Any help would be greatly appreciated!
I have really enjoyed and made good use of this batch file. However it has a couple of issues:
Issue 1: The script won't backup databases that have a dash in the name of the database.
I have resolved both of these issues in a similar batch file. Feel free to take a look:
It also has an option to just backup a single database instead of all of them.