Batch File Example Code To Backup MySQL Database To A Particular Folder, Append Date and Time, Delete Backups Older Than X Days

In this post we will see how to make a batch file to take backup of MySQL database (single database) to a particular folder on the same computer. As you may already know, there are various variants of mysqldump command. But for this exercise we will use this command:

mysqldump --routines -u<username> -p<password> <database name> > <output sql file name>

Simple Batch File to backup MySQL Database:

If we simply want to output an sql file using sqldump command we can simply write a sqldump command similar to the one able in a text file and save it as a batch file. That will work.

Example:
Suppose, username = root, password = root, server = localhost, database name = dbdemo

Then

• Open Notepad or any plain text editor
• Type in the command:

mysqldump --routines -uroot -proot dbdemo > C:\mysqlbackup.sql

• Save as "mysqlbackup.bat"
• Run it 
• The backup should be exported to C Drive

Let's extend the above batch file to add more features!
MYSQLDUMP Batch command to save to a particular folder, append date and time, delete older mysqldump files, open backup folder
 
Suppose you want to create the batch file and give it to other users to use it. And you want the directory where it is save to be the same. And you also want delete MySQL backups that are older than "X" days. So,

• Let's check if a directory already exists. And if not, create one.
• Let's append date and time to MySQL backup files.
• Let's check if there are MySQL backups older than X days and delete them.
• Show the MySQL backup folder to the user on completion

Algorithm:

1. Check if a directory called C:\MYSQLBACKUPS already exits. 
1. If no: Create. And go to step 2
2. If yes: Continue to step 2
2. Define the date and time format
3. Execute mysqldump command
4. Check and delete files older than 60 days
5. Show the backup folder to the user
6. end

The code for the above mysqldump algorithm to check for folder, append date and time to sql file name and delete files older than 60 days is as given below.

In the batch file code below:
REM = Comments. Does not appear on the screen
ECHO. = New Line



@echo off
REM ----------------------------------------------------------------
REM Create a directory to save mysql backup files if not already exists REM ----------------------------------------------------------------

IF NOT EXIST "C:\MYSQLBACKUPS" mkdir C:\MYSQLBACKUPS

REM ----------------------------------------------------------------
REM append date and time to mysqldump files
REM ----------------------------------------------------------------

SET dt=%date:~-4%_%date:~3,2%_%date:~0,2%_%time:~0,2%_%time:~3,2%_%time:~6,2%


set bkupfilename=%dt%.sql

REM ----------------------------------------------------------------
REM Display some message on the screen about the backup
REM ----------------------------------------------------------------
ECHO Starting Backup of MySQL Database
ECHO Backup is going to save in C:\MYSQLBACKUPS\ folder.
ECHO Please wait ...

REM ----------------------------------------------------------------
REM mysqldump backup command. append date and time in filename
REM ----------------------------------------------------------------

"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe"  --routines -u root -proot dbdemo > C:\MYSQLBACKUPS\"mysqldb_%bkupfilename%"

REM ----------------------------------------------------------------
REM delete mysqldump backups older than 60 days
REM ----------------------------------------------------------------

ECHO.
ECHO Trying to find and delete backups older than 90 days if found.
ECHO And the result is:
forfiles /p C:\MYSQLBACKUPS /s /m *.* /d -3 /c "cmd /c del @file : date >= 60days"

ECHO.
ECHO Backup completed!
ECHO Backup saved in C:\MYSQLBACKUPS\
ECHO Thank You for backing up!
ECHO - Regards, Admin!
ECHO.
ECHO I am about to show you the backup files.

PAUSE

REM Show user the backup files
EXPLORER C:\MYSQLBACKUPS\
EXIT

REM Author: JK
REM Kohima, Nagaland
REM Modified: OCtober 7, 2016

Copy the above code to a notepad, and save it as "anyname.bat".
Run it.

Note: This is an example code. Review the code yourself and run it at your own risk. The author shall not be responsible for any issue caused by running this code if any.