MySQL Backup Cheat Sheet

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

  • 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

Brad Anderson is the founder and CEO of Fruition. Brad combined his passion for marketing, technology, innovation and data-based decision making into a successful national digital marketing agency when he created the Denver-based Fruition. Brad brings the unique perspective of an expert marketer, board member, agency owner and entrepreneur to his career and his thought-leadership writing.

Leave a Comment

Your email address will not be published. Required fields are marked *