Automatically backup MySQL database with Batch file on Windows

Data backup is always very important in the way each system operates. In Linux you can easily automatically install the Crontab command, so what about Windows? We need to write code in Batch file and setup Task Schedule in Windows operating system. Very simple and fast.

Task Schedule is available in Windows operating system. You need to turn it on and map it to a Batch file. Our problem is running on Windows, maybe Windows 7, 10, Windows server and backing up data from MySQL.

The need to prepare:

1. Assuming we are using Xampp, you need to find the path of the mysqldump file

2. Prepare information about database: User, Password, Database Name

3. Prepare a database storage path. Database in this article will be saved as folder name of database, which will contain file mysql with format Year-month-date.sql

Doing:

1. Open the task schedule by either search command or in the Control panel.

2. Create Basic Task

3. Type name of task

4. Select auto-run time: daily, weekly, monthly, …

5. By default, the Task will work immediately after the setup completes. You can pause and end the task at any time by right-clicking and selecting Disable or Delete

In Batch file:

– rem: comment your code

– set date with format Year-month-day: %yyyy%-%mth%-%dd%

– create folder BACKUPPATH: md %BACKUPPATH%

– use MYSQLDUMPFILE to export your Data

@echo off 

CLS
ECHO Date format = %date%
rem echo %DATE% %TIME%

REM Breaking down the format
FOR /f “tokens=2 delims==” %%G in (‘wmic os get localdatetime /value’) do set datetime=%%G
ECHO dd = %datetime:~6,2%
ECHO mth = %datetime:~4,2%
ECHO yyyy = %datetime:~0,4%
ECHO/
REM Building a timestamp from variables
SET “dd=%datetime:~6,2%”
SET “mth=%datetime:~4,2%”
SET “yyyy=%datetime:~0,4%”
SET “Date=%yyyy%-%mth%-%dd%”
ECHO Backup data at Date: %Date%
ECHO/

rem —– Start set path and backup —–
set DATE=%Date%
set PATH=C:\dev\xampp\htdocs\your_folder\data-backup\
set MYSQLDUMPFILE=C:\dev\xampp\mysql\bin\mysqldump
set USER=root
set PASSWORD=

set DBNAME=YOUR_DATABASE_NAME

set BACKUPPATH=%PATH%\%DBNAME%

if not exist %BACKUPPATH% md %BACKUPPATH%

“%MYSQLDUMPFILE%” –user=”%USER%” –password=”%PASSWORD%” –result-file=”%BACKUPPATH%\%DATE%.sql” “%DBNAME%”

echo Done!
rem pause
exit

Delete old files:

Over time, the number of backup files will gradually increase. If you want to reduce storage space, the way to do it may be to delete old backup files. Leave only the latest files.

The following example will keep the 10 newest files and delete the other old files:

echo Delete old files…

for /f “skip=10 delims=” %%a in (‘dir /a-d /o-d /b /s %BACKUPPATH%’) do DEL “%%a”

echo Done!

exit

Setup time:

If you set it to run automatically at 1am every Sunday, you’ll see the backup file in folder C:\dev\xampp\htdocs\your_folder\data-backup\YOUR_DATABASE_NAME

2021-01-17.sql (created 01:00 am)

2021-01-24.sql (created 01:00 am)

2021-01-31.sql (created 01:00 am)