Summary

Backups are a vital part of any MySQL administrator's toolbox. There are a number of methods to use:

• The BACKUP statement creates a copy of the definition and data files of a MyISAM table. The RESTORE statement restores the data.

• Directly copying the files. You need to do your own locking. Returning the data files to the data directory restores the data.

• mysqldump, which creates a text file containing the SQL statements needed to regenerate the tables. Using the file as input to the MySQL daemon restores the data.

• Using SELECT INTO statements create a text file that can be used to restore the data with the LOAD DATA command or the mysqlimport utility.

• Using the mysqlhotcopy utility. This is a Perl script that copies the data files to another directory. Returning the data files to the data directory restores the data.

• Replication, which backs up onto another machine, but also replicates data loss across machines if it's caused by SQL statements.

The binary update log, if enabled, keeps a record of all changes to the database tables. The mysqlbinlog utility can be used to view the contents of the log or be used to restore updates to the database made because of a backup. InnoDB tables are not in stored in files, like MyISAM tables, and so they require extra care. They also have their own logging mechanism.