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

If I want run this bat file from local (client) computer to make backup in server computer, what should it to be?

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.