One Click Automatic Backup All Your MySQL Database in zip format on Windows

It is not uncommon that web developers find themselves in a DBA role at some point in their career. A basic but crucial DBA responsibility is taking backups of the database. I often need to make a quick backup of MySQL database. For most database work I use phpMyAdmin. I do this because troubleshooting database issues are far outside of my skill-set and I need to get stuff done regardless of that. There are some situations when phpMyAdmin is not an ideal solution to take backups. Like migrating to a new system or taking a complete backup of your old projects databases or you may want to make the periodic backup of databases.

MySQL provides us the tool required to do this: mysqldump. As the name implies, this can make a dump of one database or even to all the databases. Based on the fact that we need to backup all the databases. We can use the following code to dump MySQL databases on the Windows command prompt.

Let's Find Out How to Backup MySQL Database in Windows

How to Use Windows MySQL Backup Script

1. Make sure your computer has a Zip compression application. I have used 7-zip best free open source file compression software.
2. Make sure to change the path (from line 4-9) based on your WAMP or XAMP setup.
3. Next, you will need to create a *.bat file (using notepad). Open up notepad and copy and paste the script below.
4. You will also require to changing the dbUser, dbPassword, backupDir, mysqldump, mysqlDataDir and zip file/app locations.
5. Save the file as mysqlbackup.bat or any-filename.bat
   
6. Before running this script make sure your MySQL is running.
7. This is a executable file. Now left click and "Run As Administrator".

@echo off

:: make sure to change the settings from line 4-9
set dbUser=root
set dbPassword=""
set backupDir="D:\Localhost_MySQL_Backup"
set mysqldump="D:\wamp\bin\mysql\mysql5.7.14\bin\mysqldump.exe"
set mysqlDataDir="D:\wamp\bin\mysql\mysql5.7.14\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
)

if %mm%==01 set Month="Jan"
if %mm%==02 set Month="Feb"
if %mm%==03 set Month="Mar"
if %mm%==04 set Month="Apr"
if %mm%==05 set Month="May"
if %mm%==06 set Month="Jun"
if %mm%==07 set Month="Jul"
if %mm%==08 set Month="Aug"
if %mm%==09 set Month="Sep"
if %mm%==10 set Month="Oct"
if %mm%==11 set Month="Nov"
if %mm%==12 set Month="Dec"

set dirName=%dd%_%Month%_%yy%
set fileSuffix=%dd%-%Month%-%yy%

:: remove echo here if you like
echo "dirName"="%dirName%"

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

:: create backup folder if it doesn't exist
if not exist %backupDir%\%dirName%\   mkdir %backupDir%\%dirName%

:: iterate over the folder structure in the "data" folder to get the databases
for /d %%f in (*) do (
      :: remove echo here if you like
      echo processing folder "%%f"

      %mysqldump% --host="localhost" --user=%dbUser% --password=%dbPassword% --single-transaction --add-drop-table --databases %%f > %backupDir%\%dirName%\%%f.sql
      %zip% a -tgzip %backupDir%\%dirName%\%fileSuffix%_%%f.sql.gz %backupDir%\%dirName%\%%f.sql
      del %backupDir%\%dirName%\%%f.sql
)
popd