This section summarizes some general methods for making backups.
Customers of MySQL Enterprise Edition can use the MySQL Enterprise Backup
product to do physical backups of entire
instances or selected databases, tables, or both. This product includes features for incremental
backups. Backing up the physical database files makes restore much faster than logical techniques such as the
InnoDB tables are copied using a hot backup mechanism. (Ideally,
InnoDB tables should represent a substantial majority of the data.) Tables from
other storage engines are copied using a warm backup mechanism. For an overview of
the MySQL Enterprise Backup product, see Section 23.2, "MySQL
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 7.4, "Using mysqldump for Backups", and Section 4.6.10, "mysqlhotcopy — A Database Backup Program".)
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 (
*.MYI files). To get a consistent backup, stop the server or lock and flush the
tbl_listWITH READ LOCK;
You need only a read lock; this enables other clients to continue to query the tables while you are making a
copy of the files in the database directory. The flush is needed to ensure that the all active index pages are
written to disk before you start the backup. See Section
LOCK TABLES and
UNLOCK TABLES Syntax",
and Section 184.108.40.206, "
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 contains
InnoDB tables. mysqlhotcopy does not work for
InnoDB tables because
InnoDB 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.)
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 permitting files to be
overwritten constitutes a security risk. See Section 13.2.9, "
SELECT Syntax". This method works for any kind of data file, but saves only
table data, not the table structure.
Another way to create text data files (along with files containing
CREATE TABLE statements for the backed up tables) is to use mysqldump with the
--tab option. See Section
7.4.3, "Dumping Data in Delimited-Text Format with mysqldump".
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
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
7.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 using
--flush-logs, or mysqlhotcopy --flushlog. See Section
4.5.4, "mysqldump — A Database Backup Program", and Section
4.6.10, "mysqlhotcopy — A Database Backup Program".
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 Section 16.3.1, "Using Replication for Backups".
If you are backing up a slave replication server, you should back up its master info and relay log info
repositories (see Section 16.2.2, "Replication Relay and
Status Logs") 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
LOAD DATA INFILE statements, you should also back up any
SQL_LOAD-* files that exist in the directory that the slave uses for this
purpose. The slave needs these files to resume replication of any interrupted
LOAD DATA INFILE operations. The location of this directory is the value of
option. If the server was not started with that option, the directory location is the value of the
If you have to restore
MyISAM tables that have become corrupt, try to recover them
REPAIR TABLE or myisamchk -r first. That should work in 99.9% of all cases.
If myisamchk fails, see Section
MyISAM Table Maintenance and Crash Recovery".
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.