This section summarizes some general methods for making backups.
Making Backups by Copying Table Files
For storage engines that represent each table using its own files,
tables can be backed up by copying those files. For example,
MyISAM tables are stored as files, so it is
easy to do a backup by copying files (
To get a consistent backup, stop the server or do a
LOCK TABLES on the relevant tables
TABLES for the tables. See
Section 12.3.5, “
LOCK TABLES and
TABLES Syntax”, and Section 220.127.116.11, “
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
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
'. 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 constitutes a security risk. See
Section 12.2.7, “
SELECT Syntax”. This method works for any kind of data
file, but saves only table data, not the table structure.
The mysqldump program and the mysqlhotcopy script can make backups. 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 using mysqldump:
The first command dumps the database to the named file as
CREATE TABLE and
INSERT statements. The second
command creates two files per table in the named output directory.
One file contains the table contents as tab-delimited text. Other
other contains a
statement for the table.
Create a full backup of your database using mysqlhotcopy:
You can also create a binary backup simply by copying all table
files, as long as the server isn't updating anything. The
mysqlhotcopy script uses this method. (But note
that table file copying methods do not work if your database
mysqlhotcopy does not work for
InnoDB tables because
does not necessarily store table contents in database directories.
Also, even if the server is not actively updating data,
InnoDB may still have modified data cached in
memory and not flushed to disk.
InnoDB tables, it is possible to perform an
online backup that takes no locks on tables using the
--single-transaction option to
mysqldump. See Section 4.5.4, “mysqldump — A Database Backup Program”.
Making Incremental Backups by Enabling the Binary Log
MySQL supports incremental backups: You must start the server with
--log-bin option to enable
binary logging; see Section 5.3.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
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.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”. The next
time you do a full backup, you should also rotate the binary log
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”.
Making Backups Using Replication Slaves
If you are backing up a slave replication server, you should back
relay-log.info files when you back up the
slave's databases, regardless of the backup method you choose.
These information files are always needed to resume replication
after you restore the slave's data. If your slave is replicating
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
DATA INFILE operations.
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 Using Replication for Backups.
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.
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
Restore the table from a mysqldump backup or binary backup.
Execute the following command to re-run the updates in the binary logs:
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.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
If you are using the update logs instead, you can process their contents like this:
ls -1 -t -r hostname.[0-9]* | xargs cat | mysql
ls is used to sort the update log file names into the right order.
Making Backups Using a File System Snapshot
If you are using a Veritas file system, you can make a backup like this:
Similar snapshot capabilities may be available in other file systems, such as LVM or ZFS.