Mysql – Few questions related to MySQL Shell

MySQL

In the MY SQL documentation Instance Dump Utility, Schema Dump Utility, and Table Dump Utility

  1. The compatibility option – strip_tablespaces
    Does it remove all references to tablespaces or only in the GRANT statement. Can you please provide an example for how this option makes change in the dump statement?

  2. consistent

The documentation mentions that backup locks are released before the backup then how are consistent backups taken? START TRANSACTION WITH CONSISTENT SNAPSHOT is mentioned. Does it take snapshots?
If yes, what is the memory requirement?

Thank you!

Best Answer

  1. It removes them from the GRANT / CREATE statements as per the doc you linked and also per this documentation: Importing and Exporting Databases

  2. Only the global read lock is released when the backups are started per the documentation you linked:

When all threads have started their transactions, the instance is locked for backup and the global read lock is released.

In other words, the data is read safe but likely still holds other locks preventing the data or schema from being changed during the backup process (aka blocking transactions).

This website goes into good detail on global locks and specifically read locks in MySQL which is relevant to this question: Deep Understanding of MySQL Global Lock and Table Lock

Specifically this part mentions what type of statements are locked:

A global lock is to lock the entire database instance. MySQL provides a way to add a global read lock. The command isFlush tables with read lock (FTWRL)。

When you need to make the whole library read-only, you can use this command, and then the following statements of other threads will be blocked: data update statements (data addition and deletion), data definition statements (including table building, table structure modification, etc.) and submission statements for updating class transactions.

So yes, technically the data is a snapshot in time because no other transactions are allowed to change the data or schema during the backups. The amount of memory needed is going to be dependent on the amount of data being backed up.

Related Question