Skip to content

Instantly share code, notes, and snippets.

@sindresorhus
Created March 14, 2011 14:50
  • Star 27 You must be signed in to star a gist
  • Fork 14 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save sindresorhus/869240 to your computer and use it in GitHub Desktop.
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
Copy link
Author

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
Copy link

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
Copy link

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
Copy link

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
Copy link

Catiadr 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
Copy link

MrOlek 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
Copy link

Nap2016 commented Aug 30, 2016

Thanks

@vforbox
Copy link

vforbox 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
Copy link

michael-milette 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.

@muriloinflue
Copy link

@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