Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Backup MySQL databases in separate gzipped sql files on Windows
@echo off
set dbUser=root
set dbPassword=password
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 (
set mm=%%i
set dd=%%j
set yy=%%k
)
:: get time
for /F "tokens=5-8 delims=:. " %%i in ('echo.^| time ^| find "current" ') do (
set hh=%%i
set mm=%%j
)
set dirName=%yy%%mm%%dd%_%hh%%mm%
:: switch to the "data" folder
pushd %mysqlDataDir%
:: iterate over the folder structure in the "data" folder to get the databases
for /d %%f in (*) do (
if not exist %backupDir%\%dirName%\ (
mkdir %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
del %backupDir%\%dirName%\%%f.sql
)
@sindresorhus

This comment has been minimized.

Copy link
Owner Author

commented Mar 14, 2011

Change the settings to your own, and then either double click it or set up a task with Task Scheduler.

It will create a folder for each backup containing the gzipped sql files.
Example: 20110314_1550

@michael-milette

This comment has been minimized.

Copy link

commented Oct 6, 2013

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:

 @echo off

 set dbUser=root
 set dbPassword=password
 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=1-3 delims=- " %%i in ('date /t') do (
      set yy=%%i
      set mon=%%j
      set dd=%%k
 )

 :: get time
 for /F "tokens=5-8 delims=:. " %%i in ('echo.^| time ^| find "current" ') do (
      set hh=%%i
      set min=%%j
 )

 echo dirName=%yy%%mon%%dd%_%hh%%min%

 :: switch to the "data" folder
 pushd %mysqlDataDir%

 :: iterate over the folder structure in the "data" folder to get the databases
 for /d %%f in (*) do (

 if not exist %backupDir%\%dirName%\ (
      mkdir %backupDir%\%dirName%
 )

 %mysqldump% --host="localhost" --user=%dbUser% --single-transaction --add-drop-table --databases %%f > %backupDir%\%dirName%\%%f.sql

 %zip% a -tzip %backupDir%\%dirName%\%%f.sql.gz %backupDir%\%dirName%\%%f.sql

 del %backupDir%\%dirName%\%%f.sql
 )
 popd
@adityasatrio

This comment has been minimized.

Copy link

commented Jul 1, 2015

Hai thx for the script, there are some trivial bugs from your @michael-milette script, here the script that i fixed.
https://gist.github.com/adityasatrio/507585a9c66d7f08fe09#file-mysqlbackup-bat

@carbfrze

This comment has been minimized.

Copy link

commented Sep 14, 2015

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!
Thanks
Marc

@Catiadr

This comment has been minimized.

Copy link

commented Oct 20, 2015

Likewise, I have several folders and databases below the data level and need the script to crawl them, backing each one up individually. I can try to hack the script, but this is obviously written more eloquently than any hack I could put together.

Thanks, Ray

@MrOlek

This comment has been minimized.

Copy link

commented Jul 13, 2016

Thanks, nice script! But why not use this simple tool MySQLBackupFTP (http://mysqlbackupftp.com/) for making scheduled MySQL database backups.

@Nap2016

This comment has been minimized.

Copy link

commented Aug 30, 2016

Thanks

@vforbox

This comment has been minimized.

Copy link

commented Jan 4, 2017

:: get data and time
set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%
set dirName=%date:~0,4%%date:~5,2%%date:~8,2%_%hour%-%time:~3,2%
@michael-milette

This comment has been minimized.

Copy link

commented Jul 15, 2017

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.
issue 2: The date won't format properly depending on the Windows date format settings.

I have resolved both of these issues in a similar batch file. Feel free to take a look:
https://github.com/michael-milette/batch/blob/master/mysqlbackup.cmd

It also has an option to just backup a single database instead of all of them.

@murilobaixou

This comment has been minimized.

Copy link

commented Feb 8, 2018

@michael-milette, loved your script. Saved me a ton of work! Do you have a script for restoring them too?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.