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
- 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
- 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.