MySQL -- When mysqldump, mysql -- mysqldump

MySQL -- When mysqldump, mysql -- mysqldump

In the previous MySQL -- When mysqldump -- single-transaction encounters alter table test, we found that in MySQL 5.6, If you modify the table during mysqldump, an error may occur in mysqldump, this conclusion is inconsistent with the execution of colleagues. Therefore, we will test it in MySQL 5.5.

Test environment:

MySQL 5.5.14

Table tb1001 is available in the database testdb01. The current table contains two pieces of data:

##=================================================== ================================ ##

The returned result is an empty set and no error is reported.

This explains how my colleagues operate: During mysqldump, the table structure modification operation is not blocked, and the mysqldump operation is also "normal".

Because SELECT /*! 40001 SQL _NO_CACHE */* FROM 'tb1001' the operation does not return errors or data. The mysqldump process treats tb1001 as an empty table, then, export the following tables until all the tables are exported, and then return the successful execution status. However, the exported backup has missing data of tb1001. If this backup is used to restore data, it will inevitably lead to "data loss ".

Solution:

Before modifying a table in MySQL 5.5, check whether the current server is performing the mysqldump operation to avoid parallel execution of the two.

 

If you modify a table that has been exported by mysqldump, the modification will be blocked until mysqldump ends. This is consistent with MySQL 5.6.

##=================================================== ========================================================== ============== ##

Summary:

For MySQL 5.5, mysqldump and table modification are executed simultaneously:

If the table modification operation starts in the period before "mysqldump is enabled but the table data has not been exported", the table modification operation is successful, and mysqldump does not fail, however, data in the modified table cannot be exported normally;

If the modification operation starts in the period before "mysqldum has exported and modified table data, but mysqldump has not been completed", the modification operation is blocked and mysqldum can be successfully completed, after the mysqldump operation is complete, the table modification operation can be executed normally.

 

For MySQL 5.5, avoid mysqldump and table modification at the same time to avoid backup loss and data inconsistency!

##=================================================== ========================================================== ============== ##