Hot, Warm and Cold Backups in MySQL

When dealing with backups in MySQL, you have probably heard the terms “hot backup”, “warm backup” and “cold backup”. In this blog post we will try to figure out the similarities and differences between them.

Hot Backups in MySQL

Hot backups are backups that are performed on data while the database is online and accessible to users. Hot backups are often a very convenient solution because they do not require downtime and the disruption of operations is very minimal, but they also have certain downsides: if the data that is being backed up is altered in any way while a backup is in progress, the backup might not be the same as the final version of the data in the database.

In MySQL, hot backups can be performed using the mysqlbackup command that is part of the MySQL Enterprise Backup component or XtraBackup. mysqldump can also be used:

• The mysqlbackup command lets you back up a running MySQL instance (including InnoDB tables) with minimal disruption – when mysqlbackup is backing up InnoDB tables, reads and writes to InnoDB tables can continue.
• Percona’s XtraBackup does not lock your database during the backup either – it’s also one of the options in the open-source hot backup world. The utility can back up data from InnoDB, XtraDB and MyISAM tables on various MySQL versions (5.1, 5.5, 5.6 and 5.7) and Percona Server for MySQL with XtraDB.
• mysqldump is also an option – note that mysqldump is not a hot backup tool by default (especially if your tables are using multiple storage engines or the tables are being locked during the backup), but if your tables are only using the InnoDB storage engine and you use the --single-transaction option when using mysqldump, your backup would be considered hot.

Warm Backups in MySQL

Warm backups are backups that are usually used for mirroring or replication – in a warm backup scenario the server is powered on, but it’s not performing any work. The server might also be turned on only from time to time to get updates from the server being backed up. Bear in mind that when warm backups are running, some database operations may be restricted.

Cold Backups in MySQL

Cold backups are backups that are performed on data while the database is offline and not accessible to users. Since the backups are performed while the database is offline, cold backups are also often called offline backups. Cold backups often consume fewer resources and since no new data can be added (the database is offline), the backup process can back up all of the data in one go. Cold backups, of course, have a limitation in the fact that the database cannot be accessed when the backup operation is in progress. In order to take a cold backup in MySQL:

1. Shut down your MySQL server.
2. Take a backup of all of the data related to InnoDB (the ibdata1 file, also the .ibd, .frm and ib_logfile* files – the ibdata1 file holds the most vital information for InnoDB to function correctly, .ibd files store InnoDB table information, .frm files represent the format of the table).
3. Take a backup of the contents of your /etc/my.cnf file or the file itself.

Summary

Hot, warm and cold backups have their upsides and downsides. When choosing a backup solution for MySQL, evaluate your needs upfront and choose the option that best suits your requirements – take a look at Backup Ninja’s documentation to learn more about how Backup Ninja can help achieve your backup goals. For more insight regarding cold and hot backups and backups in general you might also want to take a look at MySQLBackups.