Pre-General Availability Draft: 2017-09-21
This section summarizes some general methods for making backups.
Customers of MySQL Enterprise Edition can use the
Backup product to do
physical backups of entire
instances or selected databases, tables, or both. This product
includes features for
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.
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 29.2, “MySQL Enterprise Backup Overview”.
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.10, “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
--log-bin option to enable
binary logging; see Section 5.4.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
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 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 mysqldump
--flush-logs. See Section 4.5.4, “mysqldump — 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 18.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 18.2.4, “Replication Relay and Status Logs”) when you back up the slave's databases, regardless of the backup method you choose. This information is always needed to resume replication after you restore the slave's data. If your slave is replicating
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 the
--slave-load-tmpdir option. If the server was not started with that option, the directory location is the value of the
tmpdir system variable.
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, see
Section 7.6, “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.