15.5 InnoDB Backup and Recovery
You can back up InnoDB tables in any of the following ways:
Use mysqldump.
This method generates backups as text files and can back up individual tables. mysqldump is covered in the "Core Study Guide," with additional discussion in section 14.4.1, "Using mysqldump." Use the SELECT … INTO OUTFILE statement to save the contents of a table into a file on the server host. More information about this statement is available in the "Core Study Guide."
Use InnoDB Hot Backup (ibbackup).
This is a commercial product available from Innobase Oy that can back up InnoDB tables while the server is running without disturbing normal database activity. Perform a binary backup operation that makes a complete InnoDB backup (a backup of all InnoDB tables in the tablespace). This method is based on making exact copies of all files that InnoDB uses to manage the tablespace. For a binary backup to be successful, the conditions for binary portability that are noted in section 15.1.1, "InnoDB Tablespace Portability," must be satisfied.
To make an InnoDB binary backup, use the following procedure:
Shut down the server for the duration of the copy operation. The tablespace must not be in use when copying the tablespace files.
Make sure that the server shut down without error. Binary InnoDB backups require a clean shutdown to be certain that the server has completed any pending transactions.
Make a copy of each of the following components:
The .frm file for each InnoDB table. The tablespace files. The InnoDB log files. Any InnoDB configuration options, such as those stored in option files. The configuration options are required in case you need to restore the backup from scratch. In that case, you'll need to know how the tablespace and log files were created originally.
To recover an InnoDB tablespace using a binary backup, stop the server, replace all the components that you made copies of during the backup procedure, and restart the server.
If you use a binary InnoDB backup to copy the InnoDB tablespace to another server, note that the necessity of copying the tablespace as a unit means you'll need to replace any existing tablespace on the destination server. You cannot add one tablespace to another using a binary backup.
An alternative to making a binary backup is to use mysqldump to dump table contents in text format. This technique can be useful for copying individual InnoDB tables from one server to another or if the conditions for binary portability are not satisfied. It can also be used to add tables from one tablespace to another: Run mysqldump to dump the tables into a text file, and then load the file into the destination server using mysql.
|
