Skip navigation links

Additional languages
6.2 Example Backup and Recovery Strategy »
Section Navigation      [Toggle]

6.1. Database Backups

This section summarizes some general methods for making backups.

Making Backups by Copying Files

MyISAM tables are stored as files, so it is easy to do a backup by copying files. To get a consistent backup, do a LOCK TABLES on the relevant tables, followed by FLUSH TABLES for the tables. See Section 12.4.5, “LOCK TABLES and UNLOCK TABLES Syntax”, and Section 12.5.6.3, “FLUSH Syntax”. You need only a read lock; this allows other clients to continue to query the tables while you are making a copy of the files in the database directory. The FLUSH TABLES statement is needed to ensure that the all active index pages are written to disk before you start the backup.

Making Delimited-Text File Backups

To create a text file containing a table's data, you can use SELECT * INTO OUTFILE 'file_name' FROM tbl_name. The file is created on the MySQL server host, not the client host. For this statement, the output file cannot already exist because allowing files to be overwritten would constitute a security risk. See Section 12.2.8, “SELECT Syntax”. This method works for any kind of data file, but saves only table data, not the table structure.

To reload the output file, use LOAD DATA INFILE or mysqlimport.

Making Backups with mysqldump or mysqlhotcopy

Another technique for backing up a database is to use the mysqldump program or the mysqlhotcopy script. mysqldump is more general because it can back up all kinds of tables. mysqlhotcopy works only with some storage engines. (See Section 4.5.4, “mysqldump — A Database Backup Program”, and Section 4.6.8, “mysqlhotcopy — A Database Backup Program”.)

Create a full backup of your database: 

shell> mysqldump --tab=/path/to/some/dir --opt db_name

Or: 

shell> mysqlhotcopy db_name /path/to/some/dir

You can also create a binary backup simply by copying all table files (*.frm, *.MYD, and *.MYI files), as long as the server isn't updating anything. The mysqlhotcopy script uses this method. (But note that these methods do not work if your database contains InnoDB tables. InnoDB does not necessarily store table contents in database directories, and mysqlhotcopy works only for MyISAM and ISAM tables.)

For InnoDB tables, it is possible to perform an online backup that takes no locks on tables; see Section 4.5.4, “mysqldump — A Database Backup Program”.

Using the Binary Log to Enable Incremental Backups

MySQL supports incremental backups: You must start the server with the --log-bin option to enable binary logging; see Section 5.2.4, “The Binary Log”. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you performed a backup. At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained in Section 6.3, “Point-in-Time Recovery”. The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS, mysqldump --flush-logs, or mysqlhotcopy --flushlog. See Section 4.5.4, “mysqldump — A Database Backup Program”, and Section 4.6.8, “mysqlhotcopy — A Database Backup Program”.

Backing Up Replication Slaves

If your MySQL server is a slave replication server, then regardless of the backup method you choose, you should also back up the master.info and relay-log.info files when you back up your slave's data. These files are always needed to resume replication after you restore the slave's data. If your slave is subject to replicating LOAD DATA INFILE commands, you should also back up any SQL_LOAD-* files that may exist in the directory specified by the --slave-load-tmpdir option. (This location defaults to the value of the tmpdir system variable if not specified.) The slave needs these files to resume replication of any interrupted LOAD DATA INFILE operations.

MySQL Enterprise.  The MySQL Enterprise Monitor provides numerous advisors that issue immediate warnings should replication issues arise. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

If you have performance problems with your master server while making backups, one strategy that can help is to set up replication and perform backups on the slave rather than on the master. See Chapter 15, Replication.

Recovering Corrupt Tables

If you have to restore MyISAM tables that have become corrupt, try to recover them using REPAIR TABLE or myisamchk -r first. That should work in 99.9% of all cases. If myisamchk fails, try the following procedure. It is assumed that you have enabled binary logging by starting MySQL with the --log-bin option.

  1. Restore the original mysqldump backup, or binary backup.

  2. Execute the following command to re-run the updates in the binary logs: 

    shell> mysqlbinlog binlog.[0-9]* | mysql

    In some cases, you may want to re-run only certain binary logs, from certain positions (usually you want to re-run all binary logs from the date of the restored backup, excepting possibly some incorrect statements). See Section 6.3, “Point-in-Time Recovery”.

Making Backups Using a Filesystem Snapshot

If you are using a Veritas filesystem, you can make a backup like this:

  1. From a client program, execute FLUSH TABLES WITH READ LOCK.

  2. From another shell, execute mount vxfs snapshot.

  3. From the first client, execute UNLOCK TABLES.

  4. Copy files from the snapshot.

  5. Unmount the snapshot.


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";
}
}
}

Add your own comment.