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 (*.frm, *.MYD, and *.MYI files). To get a consistent backup, stop the server or do a LOCK TABLES on the relevant tables followed by FLUSH TABLES for the tables. See LOCK TABLES and UNLOCK TABLES Syntax, and 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 constitutes a security risk. See 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.

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

Making Backups with mysqldump or mysqlhotcopy

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 mysqldump, and mysqlhotcopy.)

Create a full backup of your database using mysqldump:

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

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 CREATE TABLE statement for the table.

Create a full backup of your database using mysqlhotcopy:

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

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.

For InnoDB tables, it is possible to perform an online backup that takes no locks on tables using the --single-transaction option to mysqldump. See mysqldump.

Making Incremental Backups by Enabling the Binary Log

MySQL supports incremental backups: You must start the server with the --log-bin option to enable binary logging; see 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 1.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, mysqldump --flush-logs, or mysqlhotcopy --flushlog. See mysqldump, and mysqlhotcopy.

Making Backups Using Replication Slaves

If you are backing up a slave replication server, you should back up its master.info and 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 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.

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 2, 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 --log-bin option.

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.