Backup MySQL database using a batch file

Problem

I understand the earlier tutorial on how to back up a database, but now I want to incorporate it as part of a batch file to be run automatically AND I want to keep previous copies of the backups.

Solution

database-backup.bat

@echo off
REM net user administrator /active:yes
REM -----------------------------------------------------------
REM MySQL Database backup
REM -----------------------------------------------------------
 
 
REM ------
REM set up the TODAY variable
REM -----
for /f "tokens=2-4 delims=/ " %%a in ('date /T') do set year=%%c
for /f "tokens=2-4 delims=/ " %%a in ('date /T') do set month=%%b
for /f "tokens=2-4 delims=/ " %%a in ('date /T') do set day=%%a
set TODAY=%year%%month%%day%
REM -----
 
 
REM -----
REM backup my databases
REM -----
mysqldump flex > flex.sql
move flex.sql w:\backup-database\flex-%TODAY%.sql
 
mysqldump yii > yii.sql
move yii.sql w:\backup-database\yii-%TODAY%.sql
REM ------
 
 
REM -----
REM delete all the old backups
REM ----- 
forfiles -p "w:\backup-database" /m *.sql /d -90 /c "cmd /c del @path"
REM -----
 
exit

You can review the Windows batch commands here.

The first section is setting up a variable called TODAY. It will hold the current date in YYYYMMDD format. I will be using this variable later to modify the file name(s) of the backups.

REM ------
REM set up the TODAY variable
REM -----
for /f "tokens=2-4 delims=/ " %%a in ('date /T') do set year=%%c
for /f "tokens=2-4 delims=/ " %%a in ('date /T') do set month=%%b
for /f "tokens=2-4 delims=/ " %%a in ('date /T') do set day=%%a
set TODAY=%year%%month%%day%
REM -----

The next section is performing the actual MySQL backup and renaming the file to use the TODAY variable I created earlier. mysqldump is able to execute in a batch file without passing the username or password (-u and -p flags) because I set the username and password in the mysql.ini file for mysqldump. See an earlier tutorial of how this is done.

REM -----
REM backup my databases
REM -----
mysqldump flex > flex.sql
move flex.sql w:\backup-database\flex-%TODAY%.sql
 
mysqldump yii > yii.sql
move yii.sql w:\backup-database\yii-%TODAY%.sql
REM ------

The last section does some tidy up by removing any backups that are more than 90 days old.

REM -----
REM delete all the old backups
REM ----- 
forfiles -p "w:\backup-database" /m *.sql /d -90 /c "cmd /c del @path"
REM -----

If you want to set up the batch file to run automatically as a cron then I strongly recommend FireDaemon.

Tags: 

Comments

Hi Brent,

I have used your script to take a back up file.
where the script is creating a file but not writing any content to it.
Please help me out

By Divya C (not verified)

Hi,

Make sure that you look at the first tutorial and click on the link(s) for options and my.ini. mysqldump.exe still requires username, password and host to be supplied, however because I have already stored these in the my.ini file, mysqldump.exe will use those by default.

Once you get that sorted, the rest of the script should work as expected.

This tutorial should help with the my.ini file as it pertains to mysqldump.exe. http://www.brentknigge.com/?q=node/211

Cheers,

By Brent

If I want to run batch from client computer to make backup of mysql database running on server computer, what should the batch to be?
The server computer using XAMPP to run database server.

By maman (not verified)

This is not usually the way things are done, however wanting to execute commands on a server from a local machine can be done in several ways.

One technique is to SSH into the server and run your batch file.
Another is to with RPC or REST
A variant of REST would be a web interface (like you have with phpMyAdmin where you can do lots of things with a remote database).

Just make sure that with any remote connections that you attempt, the MySQL server has been configured to accept remote connections.

If you want the back-up file to then reside on your local machine (not server), then that is a whole other story.

By Brent

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.