Automatic MySQL backup using batch File

Question

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

Now I have batch script(below given) and I edit it 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.

@ECHO OFF

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.
C:
CD \path-to\backup\

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

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 1

I came up with this:

@echo off
REM ********************************
REM **     BACKUP MYSQL           **
REM ********************************
REM ** CREATE DATE WITHOUT " " **
set DATETIME=%date:~-4%-%date:~-7,2%-%date:~-10,2%-%time:~-11,2%-%time:~-8,2%-%time:~-5,2%
set DATETIME=%DATETIME: =%
REM ** SET DATE && TIME **
set sqlfile=%TEMP%\%DATETIME%.sql
REM ** SET DROPBOX FILE AND DIR **
set dropboxfile=%USERPROFILE%\Dropbox\dayly\%DATETIME%.zip
REM ** DELETE FILES OLDER THAN 1 MONTH TO SAVE SPACE **
ForFiles /P "%USERPROFILE%\Dropbox\dayly" /S /M *.zip /D -30 /C "cmd /c del @file"
REM ** EXPORT .SQL FROM MYSQL = USER, PASSWORD, DATABASE, SQL FILE
"C:\wamp\bin\mysql\mysql8.0.31\bin\mysqldump.exe" --port=3306 --user=root --password=*yourpw* *yourdb* > %sqlfile%
REM ** ZIP AND COPY TO DROPBOX DIRECTORY **
"C:\Program Files\7-Zip\7z.exe" a %dropboxfile% %sqlfile%
REM ** DELETE THE .SQL FILE SAVE SPACE **
DEL %sqlfile%

Answer 2

For future reference and help, I am updating 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 also store the date in file name.

Then run this batch file regularly using Windows Task Schedule.

Answer 3

After creating the regular sql file using the methods mentioned above, we can use winrar and pass the name of the file to this batch file, the second parameter in my case if whether to use the DATE & TIME information to create the zip filename:

So say my batchfilename is makezip.bat, I would use something like:

makezip mysqlbackup.sql 

and if you run it on 2020-07-09 07:15am it will create zip file: mysqlbackup-20200907-0715.zip

@ECHO OFF
if "%~1"=="" goto BLANK

set useDMY=-agYYYYMMDD-HHMMSS
set useDMY2=-

if "%~2" NEQ "" goto dontUseDMY

goto carryon

:dontUseDMY
set useDMY=
set useDMY2=

:carryon
set flags=%~a1
set isFolder="%flags:~0,1%"
if %isFolder% EQU "d" goto zipFolder

:zipFile
winrar a %useDMY% %~n1%useDMY2%.rar %1
goto DONE

:zipFolder
winrar a -r %useDMY% %1%useDMY2% %1\*
GOTO DONE

:BLANK
    ECHO ERROR...PLEASE PROVIDE THE FOLDER TO RAR
    PAUSE
:DONE
    ECHO "ALL DONE"

I have also added some code to handle zipping whole folders if need be, we can in fact DRAG and DROP folders to this batch file to make a zip.

Also, can use winrar parameters to add passwords to the output zip file if needed.

Answer 4

Use the below code and enter

• The path of mysqldump.exe in [mysqldump path]
• Your Mysql username in [username]
• Your Mysql password in [password]
• Your Mysql database name in [database name]
• Full path where you want your dump in [full path where you want to save db]

@echo off
set currdate=%date:~4%
set filedate=%currdate:/=_%
[mysqldump path] -u [username] -p[password] [database name] > "[full path where you want to save db]_%filedate%.sql"

Answer 5

If you are using xampp server, use the following technique - it works for me:

@echo off 

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

"C:\xampp\mysql\bin\mysqldump.exe" -uroot -ppassword -hlocalhost dbname> D:\test.%TIMESTAMP%.sql 

pause

Answer 6

I had the same issue and realized that I was not specifying the MySQL port number in mysqldump command. Here is how I do auto-backup of mysql database using a windows .bat file. I am using Mysql server 8.0. Follow the steps below:

1. create backup.bat file in the C:\Program Files\MySQL\MySQL Server 8.0\bin directory(depends on your MySQL server install location and path to your mysqldump.exe).
2. paste the code below. for my case, MySQL server runs on port 3307..try 3306 if 3307 doesn't work.

3. schedule a job using system scheduler(I find this program easier to use than the default windows task scheduler) or windows task scheduler to run automatically in the background. C:\Users\User\ is my preferred backup location

   @echo off
   mysqldump -u root -pYOURPASSWORD -P3307 --opt YOURDATABASENAME> "C:\Users\User\NAMEFORYOURBACKUPFILE.sql"
   

Answer 7

In my case this command still generated an empty SQL file. So I ran this command directly in CDM and it gave the following error:

mysqldump throws: Unknown table 'COLUMN_STATISTICS' in information_schema..

So to solve I put the --column-statistics = 0 that disables a mysqldump function that gives this problem.

now my .bat file looks like this:

echo off 

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

"C:\Program Files\MySQL\MySQL Workbench 8.0 CE\mysqldump.exe" --column-statistics=0 -uroot -ppassowrd -hhostName -P3306 databaseName  > D:\databaseName.sql

this solved my problem and now it does the SQL file correctly

Answer 8

If you want to specify the port, You need this.

echo off 

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

"C:\Program Files\MySQL\MySQLWorkbench6.3CE\mysqldump.exe" -uroot -ppassword -hlocalhost -P3307 ecommerce  > C:\Users\User\Desktop\backmeup\destination_\ecommerce.%TIMESTAMP%.sql 

As we all know, -p stands for password while -P stands for port.

For some reason, if I use spaces like -u root -p password etc., it will still give an error.

And also, using --databases won't work for me. Instead I added:

echo off 

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

    "C:\Program Files\MySQL\MySQLWorkbench6.3CE\mysqldump.exe" -uroot -ppassword -hlocalhost -P3307 ecommerce  > C:\Users\User\Desktop\backmeup\destination_\ecommerce.%TIMESTAMP%.sql 
    "C:\Program Files\MySQL\MySQLWorkbench6.3CE\mysqldump.exe" -uroot -ppassword -hlocalhost -P3307 equipment_rent  > C:\Users\User\Desktop\backmeup\destination_\equipment_rent.%TIMESTAMP%.sql 

Answer 9

This works for me To take backup of Mysql database

@echo off 
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump" --user=root --
password=root --result-file="C:\Users\supriyam\Desktop\HotelBooking\db.sql" 
databasename(doctorsystem) 
echo Done!
pause
exit