This section summarizes some general methods for making backups.
Making a Hot Backup with MySQL Enterprise Backup
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 and compressed backups. Backing up the physical database files makes restore much faster than logical techniques such as the 'mysqldump' command. 'InnoDB' tables are copied using a hot backup mechanism. (Ideally, the '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 *note mysql-enterprise-backup::.
Making Backups with mysqldump
The note 'mysqldump': mysqldump. program can make backups. It can back up all kinds of tables. (See note using-mysqldump::.)
For 'InnoDB' tables, it is possible to perform an online backup that takes no locks on tables using the '--single-transaction' option to note 'mysqldump': mysqldump. See note backup-policy::.
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 lock and flush the relevant tables:
FLUSH TABLES TBL_LIST WITH 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 note lock-tables::, and note flush::.
You can also create a binary backup simply by copying all table files, as long as the server is not updating anything. (But note that table file copying methods do not work if your database contains 'InnoDB' tables. Also, even if the server is not actively updating data, 'InnoDB' may still have modified data cached in memory and not flushed to disk.)
Making Delimited-Text File Backups
To create a text file containing a table's data, you can use note 'SELECT INTO OUTFILE 'FILE_NAME' FROM TBL_NAME': select-into. 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 *note select::. 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 note 'CREATE TABLE': create-table. statements for the backed up tables) is to use note 'mysqldump': mysqldump. with the '--tab' option. See *note mysqldump-delimited-text::.
To reload a delimited-text data file, use note 'LOAD DATA': load-data. or note 'mysqlimport': mysqlimport.
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 note 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 note point-in-time-recovery::. The next time you do a full backup, you should also rotate the binary log using 'FLUSH LOGS' or note 'mysqldump --flush-logs': mysqldump. See note mysqldump::.
Making Backups Using Replicas
If you have performance problems with your source server while making backups, one strategy that can help is to set up replication and perform backups on the replica rather than on the source. See *note replication-solutions-backups::.
If you are backing up a replica server, you should back up its source info and relay log info repositories (see note replica-logs::) when you back up the replica's databases, regardless of the backup method you choose. These information files are always needed to resume replication after you restore the replica's data. If your replica is replicating note 'LOAD DATA': load-data. statements, you should also back up any 'SQL_LOAD-' files that exist in the directory that the replica uses for this purpose. The replica needs these files to resume replication of any interrupted note 'LOAD DATA': load-data. operations. The location of this directory is the value of the 'slave_load_tmpdir' system variable. If the server was not started with that variable set, the directory location is the value of the 'tmpdir' system variable.
Recovering Corrupt Tables
If you have to restore 'MyISAM' tables that have become corrupt, try to recover them using note 'REPAIR TABLE': repair-table. or note 'myisamchk -r': myisamchk. first. That should work in 99.9% of all cases. If note 'myisamchk': myisamchk. fails, see note myisam-table-maintenance::.
Making Backups Using a File System Snapshot
If you are using a Veritas file system, 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 *note 'UNLOCK TABLES': lock-tables.
Copy files from the snapshot.
Unmount the snapshot.
Similar snapshot capabilities may be available in other file systems, such as LVM or ZFS.
File: manual.info.tmp, Node: backup-strategy-example, Next: using-mysqldump, Prev: backup-methods, Up: backup-and-recovery