Blog » MySQL Backup Cheat Sheet 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 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.