A MySQL Backup Primer

Feb 19, 2009 / By Sheeri Cabral

Tags:

 

  • 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 mysqld is running, and are usually done with the mysqldump tool. mysqldump can 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. mysqldump will 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 mysqld is 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 mysqld is running, with minimal impact. An inconsistent logical backup with mysqldump is an example of a hot backup.

 

 

  • Warm backups:
    Warm backups are done when mysqld is 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).

2 Responses to “A MySQL Backup Primer”

  • Shlomi Noach says:

    Hi,

    Which is why mysqlhotbackup is wrongly named, and should have been called “mysqlwarmbackup”.

    While using `mysqldump –single-transactions` on InnoDB only database is indeed a hot backup (it’s even consistent),
    InnoDB-Hot-Backup, is even hotter (“Searing backup?”), since it almost does not impacts the running server (load average is almost unaffected). With mysqldump, a performance drop is noticeable.

  • Sheeri Cabral says:

    Shlomi — I completely agree about mysqlhotbackup!

    However, we’ve seen InnoDB Hot Backup cause cpu usage to spike, though to be fair, it spikes more after the backup is done, during scp :) if there are problems we just “nice” the process and it’s all set.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>