Is mysqldump a "hot" backup kind?

I see in the documentation that the database backup tools are divided into four categories: hot backups, cold backups, physical backups and logical backups.

I understand the the most important difference between cold and hot backup is that the latter can be done while the database is operating and receiving read&write queries (and the result will be consistent and atomic).

But then, how do I know if mysqldump has this "hot" aspect or "cold" aspect? The documentation just places it in the logical backup category and is not 100% clear about this (yes, it seems to mention that it uses the same snapshot of the DB at the beginning of the mysqldump procedure, which suggests it's definitely a HOT backup tool, but just wanted to double check here).

The tables use InnoDB engine.

First of all, categorizing the backup into the four mentioned categories is not accurate. You can categorize into (hot, warm, cold), and into (physical, logical). In other words, the backup can be cold and physical, or, hot and physical, etc. It cannot be (physical and logical).

Notice please that physical backup is better called "Raw backup", which is a copy of the data files, and other related file, at filesystem level.

mysqldump is logical, i.e. it produces the statements that re-create the DB and/or the tables and/or the data, etc. Therefore it is not "physical"

"Hot Backup" means that the backup can happen without affecting the users, and without taking the application or DB server down, AND, the resulting backup is consistent. There are different strategies and tools to make hot backups. Sometimes, it is conditional to the storage engines you use.

To answer your question: mysqldump is not hot backup tool by default. This is especially true if you are using MyISAM among other engines, and/or tables are being locked during the backup.

But if your tables are pure InnoDB, and you use the --single-transaction option with mysqldump, then, yes, it would be considered hot.

HTH

StackExchange Database Administrators Q#142331, answer score: 6