Previous / Next / Up / Table of Contents

User Comments

Posted by dennis guba on March 24 2006 6:01pm

[Delete] [Edit]

You can also take very fast online or hot backups if you have linux volume management or LVM. You must have snapshots enabled. Basically follow the recipie given for veritas except use lvm. Of course your DB must live on a logical volume like /dev/vg01/mysql for example.

In a mysql shell (as root@unix and root@mysql):

mysql> flush tables with read lock;
mysql> flush logs;
mysql> system lvcreate --snapshot –-size=500M --name=backup /dev/vg01/mysql;
mysql> unlock tables;

Then back in shell land (as root@unix):

$ mount -o ro /dev/vg01/backup /mnt/tmp
$ cd /mnt/tmp/
$ tar czf backup-`date +%Y%m%d`.tgz mysql
$ umount /mnt/tmp
$ lvremove -f /dev/vg01/backup

Posted by Sunny Walia on May 14 2007 10:48pm

[Delete] [Edit]

You can use .my.cnf in your home dir as mentioned above by Dan Mahoney:

[client]
username=root
password=something

As far as Bryan Rentoul comment about mysqlhotcopy goes, I have never used it but I have used the .my.cnf with mysqldump which works great for me. If you would like to get the script which I use, its located at: http://crazytoon.com/2007/01/23/mysql-backups-using-mysqldump/

Recently i have changed the script to only do mysqldump and then i have another server logs in and grabs the files and zips it. I had to take that route since db dump file is over a gig now so it takes a long time to gzip/bzip2 on the db server which in turn lags the server quite a bit. I will have to look into master/slave setup soon so I can use slave server to run backups from. How do "you" deal with massive mysql db backups?

Posted by Jon Van Eyk on May 31 2007 2:18pm

[Delete] [Edit]

I created/used this script on windows 2003 (MySQL 5.0)
It uses mysqldump to create a *.sql backup then sends a notification e-mail.

It works online, and seems to take approximately 20 seconds for a 300Mb database.

Its Pre-Requisites are BLAT & GZIP, for e-mail and compression.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++

@ECHO OFF
CLS
Title MySQL Backup
ECHO ===============================================================================
ECHO MySQL Backup Script
ECHO Description: This scrip does the following:
ECHO - Backs up the specified database.
ECHO - Compresses the backup.
ECHO - Moves the file to d\:backups.
ECHO - Writes to a log file.
ECHO - Sends a notification e-mail.
ECHO Prerequisites:BLAT, GZIP, MYSQLDUMP
ECHO By: Jonathan Van Eyk
ECHO ===============================================================================

@REM Date Configuration
for /f "tokens=1-4 delims=/ " %%a in ('date/t') do (
set dw=%%a
set mm=%%b
set dd=%%c
set yy=%%d
)

@REM Backup Configuration
SET servername=Forum
SET database=forum
SET backupdir=D:\BACKUPS
SET workdir=c:\TEMP
SET mysqldir="C:\Program Files\MySQL\MySQL Server 5.0\bin"
SET utils=D:\UTILS
SET mysqlpassword=PASSWORD
SET mysqluser=USERNAME
SET logdir=D:\log\mysql
SET filename=%servername%.%database%.%yy%%mm%%dd%.sql

@REM Mail Configuration
SET mailsmtp="192.168.2.11"
SET mail=%utils%\Blat\full\blat.exe
SET mailto="Notification@email.com"
SET mailfrom="MySQL Backup Service on %servername% <norepy@email.com>"
SET mailsubject="MySQL Backup of %database%"
SET start=%date% - %time%: Database Backup of %database% Started

@REM Start Logging
ECHO %start%
ECHO %start% >> %logdir%\mysqlbackup.log

@REM dump database. This is all one line
mysqldump -u %mysqluser% -p%mysqlpassword% %database% > %workdir%\%filename%
if not exist %workdir%\%filename% goto FAIL_DUMP

@REM Zip up database
%utils%\gzip.exe -f %workdir%\%filename%
if not exist %workdir%\%filename%.gz goto FAIL_GZIP

@REM Move to random file name
move %workdir%\%filename%.gz %backupdir%\%filename%.gz
if not exist %backupdir%\%filename%.gz goto FAIL_MOVE

@REM All is well
GOTO SUCCESS

:FAIL_DUMP
SET message=%date% - %time%: Database Dump of %database% Failed
GOTO END
:FAIL_GZIP
SET message=%date% - %time%: Backup Compression of %database% Failed
GOTO END
:FAIL_MOVE
SET message=%date% - %time%: Backup Move of %database% Failed
GOTO END
:SUCCESS
SET message=%date% - %time%: Backup of %database% Completed Succesfully
GOTO END

:END
ECHO %message%
ECHO %message% >> %logdir%\mysqlbackup.log
ECHO ===============================================================================
ECHO Sending Notification E-Mail ...
ECHO To: %mailto%
ECHO From: %mailfrom%
ECHO Subject: %mailsubject%
%mail% -q -server %mailsmtp% -f %mailfrom% -to %mailto% -subject %mailsubject% -body "%message%"
ECHO ===============================================================================

Posted by Dave Walker on December 15 2007 4:12pm

[Delete] [Edit]

If your website is hosted on a shared server then you will most probably not be able to create or retrieve a MySQL dump.

You will be able to use PHP to export the database, but writing the script can be time consuming. Try this:
http://www.dwalker.co.uk/phpmysqlautobackup/
it will export your database and email to you. Its GPL open source.

Posted by David Bennett on October 28 2008 4:36am

[Delete] [Edit]

Script to restore commands from mysql-query.log that occured after last mysqldump backup.

http://office.bensoft.com/mysql_query_log_recover.zip

#!/usr/bin/perl -w

# this script will apply MySQL write instructions captured via
# the 'log' option into a mysql-query.log. This combined with
# a starting mysqldump backup will restore a database to it's
# current state.

# Example Usage: cat /var/log/mysql-query.log | recover.pl | mysql -u {user} {database} --password={password}

use strict;
use DateTime;

# Set the start and stop time to replay SQL commands. It is important
# to set both, especially if you are reading from a live mysql-query
# log. Without setting the stop time this script will cotinually re-apply
# database commands that were executed by this script.
#
my $startDT=DateTime->new(year=>2008,month=>10,day=>25,hour=>4,minute=>8);
my $stopDT=DateTime->new(year=>2008,month=>10,day=>27,hour=>12,minute=>27);

# Commands to apply back to the database, this skips SELECT, SHOW and DROP
# statements.
#
my @writeCmds=('COMMIT','DELETE','INSERT','LOCK','ROLLBACK','UNLOCK','UPDATE','SET');

# start of script
my $start=0;
while (<STDIN>) {
chomp;
if (m/^(08)(\d\d)(\d\d)\s+(\d+):(\d+):(\d+)\s.*/) {
my ($year,$month,$day,$hour,$min,$sec);
$year="20".$1;
$month=$2;
$day=$3;
$hour=$4;
$min=$5;
$sec=$6;
my $logDT=DateTime->new(year=>$year,month=>$month,day=>$day,hour=>$hour,minute=>$min);
#print $logDT->datetime(),"\n";
if (!$start && $logDT->epoch > $startDT->epoch) {
$start=1;
#print "STARTED!\n";
}
elsif ($start && $logDT->epoch > $stopDT->epoch) {
print STDERR "Stopping point reached: ".$logDT->datetime()."\n";
exit;
}
}
if ($start && m/[0-9] Query +([^ ].*)$/) {
my $larg=$1;
my ($sqlcmd)=split(' ',$larg);
#print $sqlcmd,"\n";
$sqlcmd =~ s/^\s+(.*)/$1/g;
$sqlcmd =~ s/(.*)\s+$/$1/g;
if (grep {$_ eq uc($sqlcmd)} @writeCmds) {
print $larg,";\n";
}
}
}

Posted by Tony A on March 20 2009 3:39pm

[Delete] [Edit]

<?php

// makes mysql backup files in sql and tgz


// begin configuration settings
// ############################


// just fill in each one.


// location of your temp directory
// this is where the backup files will be placed.
$tmpDir = "";


// username for MySQL
$user = "";


// password for MySQl
$password = "";


// database name to backup
$dbName = "";

// prefix for file name
// this is added onto the beginning of the backup file
$prefix = "";


// database server name
$server = "";


// finished message
// this is the message given when the script is finished.
// this does not mean the backup was successful.
// the only way to see if it was successful is to verify the file that was created.
$finishedmessage = "";



// end of configuration settings
// #############################

    
$sql = $tmpDir.$prefix.date('YmdHis').".sql";
$tgz = $tmpDir.$prefix.date('YmdHis').".tgz";
$creatBackup = "/usr/local/bin/mysqldump -u ".$user." --password=".$password." -h".$server." ".$dbName." > ".$sql;
$createZip = "tar cvzf $tgz $sql";
exec($creatBackup);
exec($createZip);
echo "<B><B><B><BIG><BIG><BIG>";
echo $finishedmessage;
echo "</B></B></B></BIG></BIG></BIG><BR><BR>";
echo "Note: Be sure to verify the backup files! An empty file means a configuration error.<br /><br />";
?>

Posted by Hassan Yousef on May 29 2009 4:38pm

[Delete] [Edit]

@ECHO OFF
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: backing up mysql databases , compressing , encrypting , logging , cleaning up , creating vloumes for large backups and sending them as an attachment to SMTP server
:: using blat
:: tools used
:: 7z http://www.7-zip.org/
:: winrar http://www.rarlab.com/
:: blat http://www.blat.net/
:: CMD md5 calc http://www.fourmilab.ch/md5/
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
set dbuser=username
set dbpass=password
set mysqldatadir="Mysql data dir"
set backupdir="Mysql Backup dir"
set encrtptionpassword=password
set path2winrar="path to winrar instllation"
set path27z="path to 7z instllation
set logsdir= "your path of logs"
set path2blat="your path to blat"
IF NOT EXIST %logsdir%\MySQLbackups.log echo. > %logsdir%\MySQLbackups.log
ECHO MySQLBackups on %DATE% at %TIME% >> C:\logs\MySQLbackups.log

pushd %mysqldatadir%
for /D %%f in (*) do (
echo %%f >> %logsdir%\MySQLbackups.log
mysqldump.exe --user=%dbuser% --password=%dbpass% --databases %%f --opt --quote-names --allow-keywords --complete-insert > %backupdir%\%%f.sql
)

dir %backupdir%\*.sql /B > %backupdir%\backup.txt
set b=%backupdir%\
for /F %%i in (%backupdir%\backup.txt) do echo %b%%%i >> %backupdir%\backup.lst

for /F "tokens=2-4 delims=/ " %%i in ('date /t') do (
set mm=%%i
set dd=%%j
set yy=%%k
)

for /F "tokens=5-8 delims=:. " %%i in ('echo.^| time ^| find "current" ') do (
set hh=%%i
set ii=%%j
set ss=%%k
)

pushd %backupdir%\
set fn=-%dd%-%mm%-%yy%-%hh%-%ii%-%ss%
rar a -ppassword -df %backupdir%\MySQLDataBaseBackup%fn% @backup.lst
:: 10MB 10485760 byte 10240 KB
for %%i in (%backupdir%\MySQLDataBaseBackup%fn%.rar) do set size=%%~zi
IF %size% GEQ 1048576 7z.exe a %backupdir%\MySQLDataBaseBackup%fn%.zip %backupdir%\MySQLDataBaseBackup%fn%.rar -ppassword -v2m & for %%i in (%backupdir%\MySQLDataBaseBackup%fn%.zip.???) do blat -to yourmail@domain.tld -subject "File:%%~ni Time/Date:%%~ti Size:%%~zi" -body "File:%%~ni Time/Date:%%~ti Size:%%~zi %%i" -attach %%i -u yourSMTPAcct -pw Password
for %%i in (%backupdir%\MySQLDataBaseBackup%fn%.zip.???) do md5.exe %%i >> md5Checksum.txt && echo File:%%i Time/Date:%%~ti Size:%%~zib >> md5Checksum.txt
DEL /F /Q backup.txt backup.lst %backupdir%\MySQLDataBaseBackup%fn%.zip.???
echo edning MySQLBackups on %DATE% at %TIME% >> C:\logs\MySQLbackups.log

set dbuser=
set dbpass=
set mysqldatadir=
set backupdir=

Add your own comment.

Top / Previous / Next / Up / Table of Contents