What is the physical backup and logical backup of mysql

In this issue, the editor will bring you about the physical backup and logical backup of mysql. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

1. Physical backup

(1) physical backup concept

Physical backup: copying data files

Physical backup is to back up data files, the more vivid point is the data files under cp, but the real backup is naturally not as simple as cp. (there are two types of physical backup: cold backup and hot backup.)

(2) advantages and disadvantages of physical backup

The recovery speed of physical backup is fast and takes up a lot of space.

(3) physical backup backup tool

Mysqlhotcopy physical backup tool, but only supports MyISAM engine, basically belongs to the category of cold backup, physical backup, faster.

two。 Logical backup

(1) logical backup concept

Logical backup: exporting data to a text file

Mysqldump uses a SQL-level backup mechanism, which exports datasheets into SQL script files, which is the most commonly used logical backup method. Logical backup is slow and takes up less space. The recovery cost of logical backup is high.

(2) advantages and disadvantages of logical backup

Advantages of logical backup:

In terms of backup speed, the two types of backup depend on different storage engines.

The restore speed of physical backups is very fast. But the minimum strength of physical backup can only be as strong as the table.

The structure saved by logical backups is usually pure ASCII, so we can use text processing tools to deal with

Logical backups are very compatible, while physical backups require very high versions.

Logical backup also ensures the security of data.

Disadvantages of logical backup:

Logical backups put additional pressure on RDBMS, while bare backups have no pressure

The result of a logical backup may be larger than the source file. So many people compress the contents of the backup.

Logical backups may lose precision information for floating-point numbers

(3) logical backup tool

Mysqldump logical backup tool, supports all engines, MyISAM engine is warm backup, InnoDB engine is hot backup, backup speed is medium speed, restore speed is very slow, but in the implementation of restore, there is a lot of room for operation. It has good elasticity.

This is what the physical backup and logical backup of mysql are like. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.