Mohammad Faizan Khan Mohammad Faizan Khan - 1 year ago 66
SQL Question

Automatic MySQL backup using batch File

I am trying to make auto backup of mysql database; searched many links got many references but didn't even single option work for me(accept paid softwares). Finally I am trying this link.

Now I have batch script(below given) and i edit according to my credentials but it is creating empty SQL file. I don't know why? I am new to MYSQL and it's auto backup mechanism very poor.


set TIMESTAMP=%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%

REM Export all databases into file C:\path\backup\databases.[year][month][day].sql
"C:\wamp\bin\mysql\mysql5.6.12\bin\mysqldump.exe" –-user=root –-password=xyz --all-databases --result-file="D:\dbbackup.%TIMESTAMP%.sql"

REM Change working directory to the location of the DB dump file.
CD \path-to\backup\

REM Compress DB dump file into CAB file (use "EXPAND" to decompress).
MAKECAB "databases.%TIMESTAMP%.sql" ""

REM Delete uncompressed DB dump file.
DEL /q /f "databases.%TIMESTAMP%.sql"

I run this batch file it create backup but empty sql file.

Answer Source

For future reference and help I am going to update my answer! Just update the below batch file according to your configuration:

echo off 
start "" "C:\wamp\bin\mysql\mysql5.6.12\bin\mysqldump.exe(your mysqldump address)" --user root --password=(provide here) databaseNameHere --result-file="D:\where you want path with SqlFileName.sql" --database databaseNameHere

you can use

.%date:~10,4%-%date:~7,2%-%date:~4,2% Blockquote

in your backup file name it will store the date also in file name.

Then run this batch file regularly using windows task schedule.