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

One Click Autometic Backup All Your MySQL DataBase 800x300 - 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

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.

output-mysql-backup-using-windows-batch-file

About This Author

My name is Parameshwar Roy (P. Roy), and I am a web developer. This is my personal blog to record my own thoughts. Though I am not a natural writer, I love to share my experiences. Hope my experiences will be useful to you…read more about me

1 Comment

You can post comments in this post.


  • Great!! This is a fully informative tutorial about to create MySQL database backup. It is massively helpful. This was so easy to follow and exactly what I was looking for.

    Thanks.

    php programmer 3 months ago Reply


Leave A Reply