Scheduled backup of multiple MySQL databases

Scheduled backup of multiple MySQL databases

When you have one or more MySQL databases you probably want to create a backup at a regular basis. This article shows you how this can be done by using a simple DOS-script.

First thing to do is to create a text file with the names of your databases you want to backup in it. In this example I named the file ‘mysql_databases.txt‘:

[text]
database1
database2
database3
[/text]

Next thing to do is creating the DOS-script. The highlighted lines show which lines to change so it matches your own situation. The ‘d:\scripts‘ directory is where the script is stored and where other usefull tools are stored. More on that later on.

[text highlight=”4-9,11,25″]
@echo off
title Dump MySql databases

SET BUPDIR=d:\backup\mysql_dbexport
SET LOGFILE=dump_mysql_dbs.log
SET DBSLIST=mysql_databases.txt
SET USER=user
SET PWD=password
SET MAILTO=your@email.com

cd /d d:\scripts

echo ———————  > %LOGFILE%
echo MySql database backup >> %LOGFILE%
echo ——————— >> %LOGFILE%

REM Make backup of database given mysql_databases.txt file
for /F %%d IN (%DBSLIST%) DO (
echo %date% %time% >> %LOGFILE%
echo Dumping database %%d
echo Dumping database %%d >> %LOGFILE%
mysqldump –user=%USER% –password=%PWD% –result-file=%BUPDIR%\%%d.sql %%d >> %LOGFILE%
echo ——————— >> %LOGFILE%)

REM Insert code for further processing here

echo %date% %time% >> %LOGFILE%
echo ——————— >> %LOGFILE%
echo         D O N E       >> %LOGFILE%
echo ——————— >> %LOGFILE%

rem pause
[/text]

Now you have a script which can be scheduled so you will always have a backup of your database(s).

If you also want a copy of the backup being sent to you by e-mail, you can extent the script with the following code. Just put the following code in place of the ‘REM Insert code for further processing here’ line.

[text]
REM Zip sql-dumps
7-zip\7za a %BUPDIR%\mysql_dbexport.zip %BUPDIR%\*.sql

REM Mail the log- and zip-file
mailto -a %MAILTO% -s Backup done (databases) #dt -bf %LOGFILE% -f %BUPDIR%\mysql_dbexport.zip
[/text]

Remark: The above script is using 7-zip as a command-line compressing tool. The mailto command is a home made tool, but I’m sure the internet will provide you with a mailto tool that also does the trick.