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

=================================================================

DOWNLOAD

Once the process is completed the resulting output will be like below. SQL file is in the *.zip file. You can use the SQL file to restore the Database.