A MySQL Backup Primer
Feb 19, 2009 / By Sheeri Cabral
- Consistent backup:
A consistent backup is one that represents a snapshot of all data at a point in time. Consistent backups are used for disaster recovery. An inconsistent backup can be useful for retrieving partial data — for example, if a long-time customer accidentally deletes information from his profile, you can go back to an inconsistent backup and retrieve that information. It is not important that all the data be consistent with each other when retrieving a partial amount of point-in-time data.
- Logical backups:
Logical backups are backups that contain SQL statements to create and populate tables. In MySQL, logical backups can only be done while
mysqldis running, and are usually done with the mysqldump tool.
mysqldumpcan logically export (“dump”) multiple databases, tables, views, stored routines, triggers and events. It can also export partial tables using the –where option to filter out rows.
mysqldumpwill always output all the columns in a table.
SELECT ... INTO OUTFILE can also be used for logical backups, but that’s used less frequently.
SELECT ... INTO OUTFILE does not back up table structure, nor anything else that cannot be retrieved by a SELECT statement (ie, view schema, stored routine definitions, etc).
SELECT ... INTO OUTFILE is used for backing up specific columns in a table.
Logical backups can be used with a version control system to be able to audit approximately when changes occurred.
- Physical backups:
Physical backups (also called raw backups) are backups that contain the physical files. These are usually done by using a file copy utility (such as scp or rsync).
- Cold backups:
Cold backups are done when
mysqldis shut down. Currently only physical backups can be done as a cold backup (logical backups cannot be done as a cold backup).
- Hot backups:
Hot backups are done when
mysqldis running, with minimal impact. An inconsistent logical backup with
mysqldumpis an example of a hot backup.
- Warm backups:
Warm backups are done when
mysqldis running, but there may be major impact. For example, to get a consistent, warm backup it is necessary to ensure that tables are not written to, so for the duration of the backup a shared (read) lock will be put on all tables. Writes (including replication) will not be able to occur during the backup window, but reads can continue as usual.
mysqld is still running (so it’s not a cold backup) but there is impact to the database (so it’s not a hot backup).