2

I am new to mysql. Studying about logical backup I found that it contains insert/into statement. I am not able to find a logical explanation why insert/into statements should be included in backups provided that we have raw or physical backup.

mysql 8.0 reference manual on backups

enter image description here

Any reasonable explanation appreciated! Thanks in advance.

2 Answers 2

5

A logical backup was probably the simplest option when MySQL was first created. It allows for the migration of data from one platform to another -- even with a different cpu/disk layout, such a big-endian vs little-endian.

It also provides for moving data from one version of MySQL to another (even on the same platform). This avoids recreating internal structures that may have changed. This argument is going away. "mysql_upgrade" has gotten more and more sophisticated to handle internal change, thereby letting you do an "upgrade in place". Even with 8.0 (which has major internal changes relative to 5.7), the upgrade step has been well automated. On the other hand, you cannot migrate (in place) directly from 5.6 to 8.0. Meanwhile, you can use mysqldump to migrate from 5.6 (or before) to 8.0 in a single step.

Also could be useful to downgrading; see @dbdemon's comment.

A logical backup can provide partial dumps, such as Stored Routines, VIEW definitions, subsets of a table, etc.

1
  • 1
    Logical backups can also be crucial for downgrading. Let's say from 8.0.x to 8.0.x-1, in the event you encounter a serious bug in 8.0.x, but the on-disk format has changed so you can't restore a physical 8.0.x backup to 8.0.x-1.
    – dbdemon
    Commented Feb 9, 2023 at 18:42
3

Because its not a raw or encoded binary dump. It's made of SQL statements to recreate your database, your tables, and indexes and then insert the data. Which is why it says Suitable for smaller amount of data values

Logical backups save information represented as logical database structure (CREATE DATABASE, CREATE TABLE statements) and content (INSERT statements or delimited-text files). This type of backup is suitable for smaller amounts of data where you might edit the data values or table structure, or recreate the data on a different machine architecture.

1
  • Unfortunately that advice hasn't stopped enough people from making too many export/backups of terabyte-sized MySQL databases to .sql INSERT scripts, fml.
    – Dai
    Commented Feb 10, 2023 at 1:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.