MySQL Backup Cheat Sheet

MySQL Backup Cheat Sheet

Posted on October 13, 2011. • Written by Brad Anderson

There are a few options for MySQL backup. Hot backup (or on-line) is the most preferred
method for MySQL database backup as it does not compromise data integrity and availability
with some hard requirements. Other options do exist but they are less preferred with
compromising of either availability or data integrity.
II. MySQL Hot Backup

  • It comes with MySQL Enterprise Edition from Oracle

  • Or it comes from Percona
  • It only does hot backup for Innodb tables

III.Logical Backup

  • It is supported by MySQL community edition Backup tables through SQL statements in sequence and restore is just opposite of backup by creating tables and load data into tables in a new MySQL databases.
  • It takes time to backup a large database.

IV. Off-line Physical Backup

  • It’s basically file system backup and maintain data integrity

  • Shutdown database for backup.
  • Make file copy when MySQL is off-line
  • It’s much faster
  • MySQL is not available during the backup

V.Snapshot Backup

  • It’s also file system backup and maintain data integrity

  • Shutdown database for snapshot taking in a few seconds, which is a major advantage
  • Bring MySQL database up when snapshot is taken successfully
  • Make file copy from snapshot to a backup repository.
  • It requires extra efforts to automate the process

VI. MySQL Replication

  • It’s not really a backup but have some major effects of a backup

  • Set another server as MySQL slave to create a duplicated copy of databases
  • The slave can serve as a read only role for off-loading the system
  • If something goes wrong with the master (e.g. failure of disk, OS and so on), the slave can
    be promoted to the master (failover effect) in addition to have a good copy of data (backup
    effect)

  • It’s not really a backup because if someone accidentally deleted/updated data, or even worse,
    dropped tables, the same change will be replicated to the slave. However, with the latest
    version of MySQL (5.6), replication can be configured with a time duration of delay from the
    master. This is a feature that can be used to protect data from being accidentally dropped if
    the delay is setup long enough.

VII. Combination of MySQL Replication and Snapshot Backup

  • This is a common excise prior to availability of hot backup. I used this method for 3 years
    before moving on with Percona.

  • You get high availability and load distribution.
  • You also have a good backup that takes place on the slave, which will not compromise your
    high availability as your master will be on-line all the time unless it needs to failover.
Brad Anderson

Written by Brad Anderson

Related Articles:

View All Blog Articles

From our team & partners