Table of Contents
It is important to back up your databases in case problems occur so that you can recover your data and be up and running again. MySQL offers a variety of backup strategies that you can choose from to select whatever methods best suit the requirements for your installation.
Briefly summarized, backup concepts with which you should be familiar include the following:
Logical versus physical backups
Online versus offline backups
Local versus remote backups
Snapshot backups
Full versus incremental backups
Point-in-time recovery
Backup scheduling, compression, and encryption
Table maintenance
More generally, the following discussion amplifies on the properties of different backup methods.
Logical versus physical (raw)
backups. Logical backups save information represented
as logical database structure (CREATE
DATABASE, CREATE TABLE statements)
and content (INSERT statements or
delimited-text files). Physical backups consist of raw copies of
the directories and files that store database contents.
Logical backup methods have these characteristics:
The backup is done by going through the MySQL server to obtain database structure and content information.
Backup is slower than physical methods because the server must access database information, convert it to logical format, and send it to the backup program.
Output is larger than for physical backup, paticularly when saved in text format.
Backup and restore granularity is available at the server level (all databases), database level (all tables in a particular database), or table level. This is true regardless of storage engine.
The backup does not include log or configuration files, or other database-related files that are not part of databases.
Backups stored in logical format are machine independent and highly portable.
Logical backups are performed with the MySQL server running (the server is not taken offline).
Logical backup tools include the
mysqldump program and the SELECT
... INTO OUTFILE statement. These work for any
storage engine, even MEMORY.
For restore, SQL-format dump files can be processed using
the mysql client. To load delimited-text
files, use the LOAD DATA INFILE statement
or the mysqlimport client.
Physical backup methods have these characteristics:
The backup consists of exact copies of database directories
and files. Typically this is a copy of all or part of the
MySQL data directory. Data from MEMORY
tables cannot be backed up this way because their contents
are not stored on disk.
Physical backup methods are faster than logical because they involve only file copying without conversion.
Output is more compact than for logical backup.
Backup and restore granularity extends from the level of the
entire data directory down to the level of individual files.
This may or may not provide for table-level granularity,
depending on storage engine. (Each MyISAM
table corresponds uniquely to a set of files, but an
InnoDB table shares file storage with
other InnoDB tables.)
In addition to databases, the backup can include any related files such as log or configuration files.
Backups are portable only to other machines that have identical or similar hardware characteristics.
Backups can be performed while the MySQL server is not running. If the server is running, it is necessary to perform appropriate locking so that the server does not change database contents during the backup.
Physical backup tools include filesystem-level commands
(such as cp, scp,
tar, rsync),
mysqlhotcopy for
MyISAM tables,
ibbackup for InnoDB
tables, or START BACKUP for
NDB tables.
For restore, files copied at the filesystem level or with
mysqlhotcopy can be copied back to their
original locations with filesystem commands;
ibback restores InnoDB
tables, and ndb_restore restores
NDB tables.
Online versus offline backups. Online backups take place while the MySQL server is running so that the database information can be obtained from the server. Offline backups take place while the server is stopped. (This distinction can also be described as “hot” versus “cold” backups; a “warm” backup is one where the server remains running but locked against modifying data while you access database files externally.)
Online backup methods have these characteristics:
Less intrusive to other clients, which can connect to the MySQL server during the backup and may be able to access data depending on what operations they need to perform.
Care must be taken to impose appropriate locking so that data modifications do not take place that compromise backup integrity.
Offline backup methods have these characteristics:
Affects clients adversely because the server is unavailable during backup.
Simpler backup procedure because there is no possibility of interference from client activity.
Local versus remote backups. A local backup is performed on the same host where the MySQL server runs, whereas a remote backup is initiated from a different host.
mysqldump can connect to local or remote
servers. For SQL output (CREATE and
INSERT statements), local or remote dumps
can be done and generate output on the client. For
delimited-text output (with the --tab
option), data files are created on the server host.
mysqlhotcopy performs only local backups: It connects to the server to lock it against data modifications and then copies local table files.
SELECT ... INTO OUTFILE can be initiated
from a remote client host, but the output file is created on
the server host.
Physical backup methods typically are initiated locally on the MySQL server host so that the server can be taken offline, although the destination for file copies might be remote.
Snapshot backups. Some filesystem implementations enable “snapshots” to be taken. These provide logical copies of the filesystem at a given point in time, without having to physically copy the entire filesystem. (For example, the implementation may use copy-on-write techniques so that only parts of the filesystem modified after the snapshot time need be copied.) MySQL itself does not provide the capability for taking filesystem snapshots. It is available through third-party solutions such as Veritas or LVM.
Full versus incremental backups. A full backup includes all data managed by a MySQL server at a given point in time. An incremental backup consists of the changes made to the data since the full backup. MySQL has different ways to perform full backups, such as those described in previous items. Incremental backups are made possible by enabling the server's binary log, which the server uses to record data changes.
Point-in-time recovery. One use for the binary log is to achieve point-in-time recovery. This is done by recovering first from the backup files to restore the server to its state when the backup was made, and then by re-executing changes in subsequently written binary log files to redo data modifications up to the desired point in time.
Backup scheduling, compression, and
encryption. Backup scheduling is valuable for
automating backup procedures. Compression of backup output
reduces space requirements, and encryption of the output
provides better security against unauthorized access of
backed-up data. MySQL itself does not provide these
capabilities. ibbackup can compress
InnoDB backups, and compression or encryption
of backup output can be achieved using filesystem utilities.
Other third-party solutions may be available.
Table maintenance. Data
integrity can be compromised if tables become corrupt. MySQL
provides programs for checking tables and repairing them should
problems be found. These programs apply primarily to
MyISAM tables. See
Section 6.4, “Table Maintenance and Crash Recovery”.
Additional resources
Resources related to backup or to maintaining data availability include the following:
A forum dedicated to backup issues is available at http://forums.mysql.com/list.php?93.
The syntax of the SQL statements described here is given in Chapter 12, SQL Statement Syntax.
Details for mysqldump, mysqlhotcopy, and other MySQL backup programs can be found in Chapter 4, MySQL Programs.
For additional information about InnoDB
backup procedures, see Section 13.2.8, “Backing Up and Recovering an InnoDB Database”.
Replication enables you to maintain identical data on multiple servers. This has several benefits, such as allowing client load to be distributed over servers, availability of data even if a given server is taken offline or fails, and the ability to make backups using a slave server without affecting the master. See Chapter 15, Replication.
MySQL Cluster provides a high-availability, high-redundancy version of MySQL adapted for the distributed computing environment. See Chapter 16, MySQL Cluster. For information specifically about MySQL Cluster backup, see Section 16.9, “On-line Backup of MySQL Cluster”.
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.5.2, “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
'. 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.7, “file_name' FROM
tbl_nameSELECT 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.3, “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.
Restore the original mysqldump backup, or binary backup.
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:
From a client program, execute FLUSH TABLES WITH READ
LOCK.
From another shell, execute mount vxfs
snapshot.
From the first client, execute UNLOCK
TABLES.
Copy files from the snapshot.
Unmount the snapshot.