MySQL database backup

From: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

***** Posted by Mathieu van Loon on June 29 2006 7:02pm

Here's a DOS script that will backup all your databases to a seperate file in a new folder, zip the folder, encrypt the zip and email the encrypted zip to one or many adresses. If the backup is larger than a specified limit only the logfile is emailed. The unencrypted zipfile is left on your local machine.

The script is also available at http://www.jijenik.com/projects/mysqlbackup/

Many thanks to Wade Hedgren whose script formed the basis for this version.


//--- Begin Batch File ---//
::
:: Creates a backup of all databases in MySQL.
:: Zip, encrypts and emails the backup file.
::
:: Each database is saved to a seperate file in a new folder.
:: The folder is zipped and then deleted.
:: the zipped backup is encrypted and then emailed, unless the file exceeds the maximum filesize
:: In all cases the logfile is emailed.
:: The encrypted backup is deleted, leaving the unencrypted zipfile on your local machine.
::
:: Version 1.1
::
:: Changes in version 1.1 (released June 29th, 2006)
:: - backups are now sent to the address specified by the mailto variable
::
:: The initial version 1.0 was released on May 27th, 2006
::
::
:: This version of the script was written by Mathieu van Loon (mathieu-public@jijenik.com)
:: It is based heavily on the script by Wade Hedgren (see comments at http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html)
::
:: This script requires several freeware libraries:
:: - zipgenius (a compression tool), www.zipgenius.it
:: - blat (an emailer tool), www.blat.net
:: - doff (extracts datetime, ignores regional formatting), www.jfitz.com/dos/index.html
::
:: Some areas where this script could be improved:
:: - include error trapping and handling
:: - make steps such as encryption and email optional
:: - allow the user to specify a single database on the command line
::
@echo off

::
:: Configuration options
::

:: The threshold for emailing the backup file. If the backup is larger
:: it will not be emailed (the logfile is always sent).
set maxmailsize=10000000

:: The passphrase used to encrypt the zipfile. Longer is more secure.
set passphrase=secret

:: Name of the database user
set dbuser=root

:: Password for the database user
set dbpass=password

:: Recipients of database backup, comma seperated, enclosed in quotes
set mailto="backups@example.com,backups2@example.com"

:: From address for email
set mailfrom="MySQL Backup Service "

:: Email server
set mailsmtp=localhost

:: Email subject
set mailsubject="MySQL Backup"

:: directory where logfiles are stored
set logdir=C:\DatabaseBackups\logs

:: directory where backup files are stored
set bkupdir=C:\DatabaseBackups

:: Install folder of MySQL
set mysqldir=C:\Program Files (x86)\MySQL\MySQL Server 4.1

:: Data directory of MySQL (only used to enumerate databases, we use mysqldump for backup)
set datadir=C:\Program Files (x86)\MySQL\MySQL Server 4.1\data

:: Path of zipgenius compression tool
set zip=C:\Program Files (x86)\ZipGenius 6\zg.exe

:: Path of blat mail tool
set mail=C:\DatabaseBackups\Backupscript\libraries\Blat250\full\blat.exe

:: Path of doff date tool (specify only the folder not the exe)
set doff=C:\DatabaseBackups\Backupscript\libraries\doff10

::
::
:: NO NEED TO CHANGE ANYTHING BELOW
::
::

:: get the date and then parse it into variables
pushd %doff%
for /f %%i in ('doff.exe yyyymmdd_hhmiss') do set fn=%%i
for /f %%i in ('doff.exe dd-mm-yyyy hh:mi:ss') do set nicedate=%%i
popd

set logfile="%logdir%\%fn%_Backuplog.txt"

:: Switch to the data directory to enumerate the folders
pushd "%datadir%"

:: Write to the log file
echo Beginning MySQLDump Process > %logfile%
echo Start Time = %nicedate% >> %logfile%
echo --------------------------- >> %logfile%
echo. >> %logfile%


:: Create the backup folder
if not exist "%bkupdir%\%fn%\" (
echo Making Directory %fn%
echo Making Directory %fn% >> %logfile%

mkdir "%bkupdir%\%fn%"
)

:: Loop through the data structure in the data dir to get the database names
for /d %%f in (*) do (

:: Run mysqldump on each database and compress the data by piping through gZip
echo Backing up database %fn%_%%f.sql
echo Backing up database %fn%_%%f.sql >> %logfile%
"%mysqldir%\bin\mysqldump" --user=%dbuser% --password=%dbpass% --databases %%f --opt --quote-names --allow-keywords --complete-insert > "%bkupdir%\%fn%\%fn%_%%f.sql"
echo Done... >> %logfile%
)

:: return from data dir
popd

pushd %bkupdir%

echo Zipping databases
echo Zipping databases >> %logfile%
REM C9 : maximum compression
REM AM : Delete source files
REM F1 : Store relative path
REM R1 : include subfolders
REM K0 : Do not display progress

"%zip%" -add "%fn%_MySQLBackup.zip" C9 AM F1 R1 K0 +"%bkupdir%\%fn%"

echo Crypting zipfile
echo Crypting zipfile >> %logfile%

REM C : Create non-executable zip
REM S : Do not delete after x tries
REM 3 : Use AES encryption
"%zip%" -encrypt "%fn%_MySQLBackup.zip" C S 3 "%passphrase%" %mailfrom%

echo Deleting directory %fn%
echo Deleting directory %fn% >> %logfile%

rmdir /s /q "%bkupdir%\%fn%"

:: Go back and get the end time for the script
set endtime=1

:: return from backup dir
popd


:: update the nicedate for the log
pushd %doff%
for /f %%i in ('doff.exe dd-mm-yyyy hh:mi:ss') do set nicedate=%%i
popd


:: Write to the log file
echo. >> %logfile%
echo --------------------------- >> %logfile%
echo MySQLDump Process Finished >> %logfile%
echo End Time = %nicedate% >> %logfile%
echo. >> %logfile%


:: Send the log file in an e-mail, include the backup file if it is not too large
:: We use the CALL Trick to enable determination of the filesize (type CALL /? at prompt for info)
:: note that you _must_ specify the full filename as the argument

pushd %bkupdir%
Call :MAILFILE "%bkupdir%\%fn%_MySQLBackup.czip"
echo Backup completed
goto :EOF

:MAILFILE

if /i %~z1 LSS %maxmailsize% (
echo Emailing backup file
"%mail%" %logfile% -q -attach %1 -serverSMTP %mailsmtp% -f %mailfrom% -to %mailto% -subject %mailsubject%
) ELSE (
echo Size of backup file %~z1 B exceeds configured email size %maxmailsize% B.
echo Emailing logfile only
echo Size of backup file %~z1 B exceeds configured email size %maxmailsize% B. only emailing logfile. >> %logfile%

"%mail%" %logfile% -q -serverSMTP %mailsmtp% -f %mailfrom% -to %mailto% -subject %mailsubject%
)

echo Deleting encrypted backup file
del %1

popd
//--- End Batch File ---//

Posted by Bradford Mitchell on July 18 2008 7:11pm

This is an example of a Windows batch script that implements a rotating archive of backups on a daily, weekly, and monthly basis. It also provides an installation option for the creation of the backup directories and an option to add a scheduled task to the system to run the batch file.

I started with what Lon B posted and many editions/revisions later this was produced. I hope you find it as useful as we have.

~~~ BEGIN FILE ~~~

@ECHO OFF
SET VERSIONMAJOR=10
SET VERSIONMINOR=6

FOR /f "tokens=1-4 delims=/ " %%a IN ('date/t') DO (
SET dw=%%a
SET mm=%%b
SET dd=%%c
SET yy=%%d
)

REM *** VERIFY AND UPDATE THESE SETTINGS BEFORE INITIAL RUN ***
REM *** mysqldir must point to the \bin directory! ***
SET bkupdir=C:\MySQL-Backups
SET mysqldir=C:\wamp\bin\mysql\mysql5.0.51b\bin
SET dbhost=localhost
SET dbuser=
SET dbpass=
REM *** END USER CONFIGURABLE SETTINGS ***

IF /i "%1" == "--INSTALL" GOTO INSTALLER
IF /i "%1" == "--CREATEDIRS" GOTO CREATEDIRS
IF /i "%1" == "--ADDSCHEDULEDTASK" GOTO TASKSCHED
IF ""%1"" == """" GOTO ALLDB
IF /i "%1" == "--ALL" GOTO ALLDB
IF /i "%1:~0,2%" == "--" GOTO PARAMERROR

SET ALLDBS=0
SET dbnames=%1
SET dbnamesf=%1
SHIFT
:setArgs
IF ""%1""=="""" GOTO BKUP
SET dbnames=%dbnames% %1
SET dbnamesf=%dbnamesf%_%1
SHIFT
GOTO setArgs

:ALLDB
SET ALLDBS=1
SET dbnames=ALL DATABASES
SET dbnamesf=ALL_DATABASES

:BKUP
@ECHO MySQLdump script for Windows v%VERSIONMAJOR%.%VERSIONMINOR% > %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.log
@ECHO. >> %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.log
@ECHO MySQLdump script for Windows v%VERSIONMAJOR%.%VERSIONMINOR%
@ECHO.

IF NOT EXIST %bkupdir%\INSTALLED.OK (
@ECHO DIRECTORY STRUCTURE NOT IN PLACE. >> %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.log
@ECHO PLEASE RUN %0 --INSTALL OR %0 --CREATEDIRS >> %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.log
@ECHO FAILED TO BACKUP DATABASES. >> %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.log
@ECHO DIRECTORY STRUCTURE NOT IN PLACE.
@ECHO PLEASE RUN %0 --INSTALL OR %0 --CREATEDIRS
@ECHO FAILED TO BACKUP DATABASES.
GOTO BOTTOM
)

@ECHO Beginning backup of %dbnames%... >> %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.log
@ECHO Beginning backup of %dbnames%...

IF %ALLDBS% == 1 (
SET dumpparams=--host=%dbhost% -u %dbuser% -p%dbpass% -A -f -x -q --create-options --flush-privileges -r %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.sql
) ELSE (
SET dumpparams=--host=%dbhost% -u %dbuser% -p%dbpass% -f -x -q --create-options --flush-privileges -r %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.sql --databases %dbnames%
)

%mysqldir%\mysqldump %dumpparams% >> %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.log

@ECHO Done! New File: dbBkup_%dbnamesf%_%yy%%mm%%dd%.sql >> %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.log
@ECHO Done! New File: dbBkup_%dbnamesf%_%yy%%mm%%dd%.sql

COPY /Y %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.sql /A %bkupdir%\Daily\dbBkup_%dbnamesf%_%dw%.sql /A > NUL
@ECHO Created Daily Backup: Daily\dbBkup_%dbnamesf%_%dw%.sql >> %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.log
@ECHO Created Daily Backup: Daily\dbBkup_%dbnamesf%_%dw%.sql

REM Check to see if it's time for the Weekend backup
IF /i "%dw%" NEQ "Sat" GOTO SKIPWKBK
IF EXIST %bkupdir%\Weekly\safety_%dbnamesf%_%yy%%mm%%dd%.txt GOTO WKCUR
IF NOT EXIST %bkupdir%\Weekly\dbBkup_%dbnamesf%_Current.sql GOTO WKCUR
IF NOT EXIST %bkupdir%\Weekly\dbBkup_%dbnamesf%_Previous.sql GOTO WKPRE
IF NOT EXIST %bkupdir%\Weekly\dbBkup_%dbnamesf%_Previous_2.sql GOTO WKPR2
MOVE /Y %bkupdir%\Weekly\dbBkup_%dbnamesf%_Previous_2.sql %bkupdir%\Weekly\dbBkup_%dbnamesf%_Previous_3.sql > NUL
:WKPR2
MOVE /Y %bkupdir%\Weekly\dbBkup_%dbnamesf%_Previous.sql %bkupdir%\Weekly\dbBkup_%dbnamesf%_Previous_2.sql > NUL
:WKPRE
MOVE /Y %bkupdir%\Weekly\dbBkup_%dbnamesf%_Current.sql %bkupdir%\Weekly\dbBkup_%dbnamesf%_Previous.sql > NUL
:WKCUR
COPY /Y %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.sql /A %bkupdir%\Weekly\dbBkup_%dbnamesf%_Current.sql /A > NUL
@ECHO. > %bkupdir%\Weekly\safety_%dbnamesf%_%yy%%mm%%dd%.txt
@ECHO Created Weekly Backup: Weekly\dbBkup_%dbnamesf%_Current.sql >> %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.log
@ECHO Created Weekly Backup: Weekly\dbBkup_%dbnamesf%_Current.sql

:SKIPWKBK
REM if (day >= 28) write EoM backup
IF %dd% GEQ 28 (
COPY /Y %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.sql /A %bkupdir%\Monthly\dbBkup_%dbnamesf%_%mm%.sql /A > NUL
@ECHO Created End of Month Backup: Monthly\dbBkup_%dbnamesf%_%mm%.sql >> %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.log
@ECHO Created End of Month Backup: Monthly\dbBkup_%dbnamesf%_%mm%.sql
)

DEL /q /f %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.sql

@ECHO Backup stored in rotating archives. >> %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.log
@ECHO. >> %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.log
@ECHO End MySQLdump Script >> %bkupdir%\dbBkup_%dbnamesf%_%yy%%mm%%dd%.log
@ECHO Backup stored in rotating archives.
@ECHO.
@ECHO End MySQLdump Script
GOTO BOTTOM

:INSTALLER
@ECHO VERIFY: Path to mysqldump: %mysqldir%
@ECHO VERIFY: Path to backups: %bkupdir%
@ECHO VERIFY: MySQL User: %dbuser%
@ECHO VERIFY: MySQL Pass: %dbpass%
@ECHO VERIFY: MySQL Host: %dbhost%
IF NOT EXIST %bkupdir%\INSTALLED.OK (
@ECHO ALERT: Backup directory does not exist. Create base directory and subdirectories?
SET /p domkdir=[Y/N]:
IF /i "%domkdir%" == "N" (
@ECHO ALERT: CANNOT CONTINUE WITHOUT DIRECTORIES IN PLACE.
GOTO BOTTOM
)
)
:CREATEDIRS
IF NOT EXIST %bkupdir%\INSTALLED.OK (
MD "%bkupdir%" > NUL
MD "%bkupdir%\Daily" > NUL
MD "%bkupdir%\Weekly" > NUL
MD "%bkupdir%\Monthly" > NUL
@ECHO INSTALLED CORRECTLY > %bkupdir%\INSTALLED.OK
)
GOTO BOTTOM

:TASKSCHED
@ECHO Preparing add Scheduled Task...
:STUPIDUSER1
SET /p taskuser=Domain\User to run task:
IF /i ""%taskuser%"" == """" GOTO STUPIDUSER1
:STUPIDUSER2
SET /p taskpwd1=Password:
SET /p taskpwd2=Confirm Password:
IF %taskpwd1% NEQ %taskpwd2% GOTO STUPIDUSER2
:STUPIDUSER3
SET /p taskname=Task name:
IF /i ""%taskname%"" == """" GOTO STUPIDUSER3
SET /p taskparam=Parameters to pass to batch file:
SCHTASKS /Create /SC DAILY /ST 04:00:00 /TN "%taskname%" /TR "%~f0 %taskparam%" /RU "%taskuser%" /RP %taskpwd1%
GOTO BOTTOM

:PARAMERROR
@ECHO ERROR: Unknown Parameter Passed.
@ECHO Current supported parameters:
@ECHO --ALL - Backup all databases, same as passing nothing to batch file
@ECHO --ADDSCHEDULEDTASK - Adds a scheduled task for this process
@ECHO --CREATEDIRS - Creates Directory Structure
@ECHO --INSTALL - Creates directory structure and outputs configuration settings that need verification

:BOTTOM



Posted by Steve Hand on March 7 2008 7:47am


In case this helps anyone. This backs up all databases and tables and keeps all files for a week, placing them in a directory structure of the format:

/backup_dir/db_name/day/table.sql.gz

Useful if you want to restore to a particular days data.

It checks new backups are different to last before overwriting files. This helps if you are rsyncing your filesystems as normally mysql writes a date into the dump so the files always appear to differ even if the data is the same.

Also saves a directory with just your schema in, checks and repairs tables where necessary and defrags tables on a Sunday.

// Change Me

$username = "root";
$password = "password";
$backup_dir = "/mnt/backup";
$dump = "/usr/bin/mysqldump";
$grep = "/bin/grep";
$gzip = "/bin/gzip";

// This should not need changing from here

function sql_dict($sql){
$x = mysql_query($sql);
if ($x) return mysql_fetch_assoc($x);
}

function cleanup($dir){
$d = dir($dir);
echo "Path: " . $d->path . "\n";
while (false !== ($entry = $d->read())) {
if ($entry=="." or $entry=="..") continue;
$e = str_replace(".sql.gz","",$entry);
$x = sql_dict("describe $e");
if (!$x) {
print "Removing old backup file [$entry]\n";
unlink("$dir/$entry");
}
}
$d->close();
}

function crc32_file($filename)
{
global $gzip;
$x = exec("$gzip --list --verbose $filename");
$x = explode(" ",$x);
return $x[1];
}

if (mysql_connect("localhost",$username,$password)) print "Connected.\n";
else die("Failed to connect to database.");
$dbs = mysql_query("show databases");
if ($dbs) while ($db = mysql_fetch_array($dbs, MYSQL_ASSOC)) {
$db = $db['Database'];
if ($db=="information_schema") continue;
if (mysql_select_db($db)) print "Selected [$db]\n";
else die("Failed to select db [$db]");
foreach (array("schema","data") as $pass){
$sql = mysql_query("show tables");
$day = date("l");
if ($pass=="schema") $dir = "/$backup_dir/$db/schema";
else $dir = "/$backup_dir/$db/$day";
if (!file_exists($dir)) system("mkdir -p $dir");
if (!file_exists($dir)) die("Couldn't Create $dir");
if ($pass=="data"){
$latest = "/$backup_dir/$db/latest";
unlink($latest);
system("/bin/ln -s \"$dir\" \"$latest\"");
}
cleanup($dir);
if ($sql) while ($s = mysql_fetch_assoc($sql)) {
if (!isset($s["Tables_in_{$db}"])) {
print "no result";
print_r($sql);
die();
}
$t = $s["Tables_in_{$db}"];
if (
$pass=="schema" ) $data = "--no-data";
else $data = "--lock-tables";
$tab = $t;
$lim = 30;
if (strlen($tab)>$lim) $tab = substr($tab,0,$lim-3)."...";
while (strlen($tab)<30) $tab .= " ";
print "BACKUP: $pass : $day : $db : $tab : ";
if ($pass=="data"){
print "Check : ";
$check = sql_dict("check table $t");
$check = $check['Msg_text'];
print "$check : ";
if ($check != "OK") {
print "Repair";
$repair = sql_dict("repair table $t");
$repair = $repair['Msg_text'];
print " : $repair : ";
}
if ($day=="Sunday"){
// optimize
print "Optimize : ";
$type = sql_dict("show table status like '$t'");
$type = $type['Engine'];
if ($type=="MyISAM") sql("optimize table $t");
if ($type=="InnoDB") sql("alter table $t engine='InnoDB'");
}
}
if (isset($argv[1])){
print "Skipping dump\n";
} else {
$temp = "/tmp/backup.$t.sql.gz";
$out = "$dir/$t.sql.gz";
print "Dump : ";
$cmd = "$dump -u$username -p$password $data --quick --add-drop-table $db $t | $grep -v 'Dump completed' | $gzip -n > $temp";
system($cmd);
print "CRC32 : ";
if (!file_exists($out)){
print "Saving : ";
$cmd = "/bin/mv $temp $out";
system($cmd);
} else {
$md5 = crc32_file($temp);
$nmd5 = crc32_file($out);
if ($md5!=$nmd5) {
print "Saving : ";
$cmd = "/bin/mv $temp $out";
system($cmd);
} else {
print "Skipped : ";
unlink($temp);
}
}
$size = filesize($out);
print "[$size]\n";
}
}
}
}
?>