Mysql Backup Script For Windows

MySqlBackup.bat

@echo off

 set dbUser=root
 set dbPassword=password
 set backupDir="F:\AdityaSatrioNugroho\testbackupDb-bat\"
 set mysqldump="C:\xampp\mysql\bin\mysqldump.exe"
 set mysqlDataDir="C:\xampp\mysql\data"
 set zip="C:\Program Files\7-Zip\7z.exe"

 :: get date
 for /F "tokens=2-4 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%
 set 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% --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
 )
 popd

how to restore this?

How to back up only one database?When I use this script it backup all database

Using the folder structure is not particulary clever. If you run InnoDB, your database does not have a folder.
Running "show databases" would be the correct way to enumerate databases.

Hi Mate.
Thank you for awesome batch file. I spent frikken hours trying to get something together and there you did it already. To answer question above how to restore this: I would go to mysql gui and open database and emply it out and then in sql area paste the backup and run it. I assume this is correct thinking. Re backing up only one db I do not see a problem if all db's are backed up. I.e. I see that they are easy to identify and get at in 7 zip folders.