What should I do when mysqldump --single-transaction encounters alter table?

Test environment:

MySQL 5.5.14

There is table tb1001 under database testdb01, and there are two pieces of data in the current table:

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

##It was found that an empty set was returned without any error.

This also reasonably explains the phenomenon of my colleague's operation: during the mysqldump process, the table structure was modified, the modification operation was not blocked, and the mysqldump operation was "completed normally".

Since the SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb1001` operation does not return an error or data, the mysqldump process will treat tb1001 as an empty table, and then continue to export subsequent tables until all are exported. The table then returns a status of successful execution. However, the exported backup already lacks the data of tb1001. If you happen to use this backup to restore data, it will inevitably lead to "data loss".

Solution:

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

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

Summary:

For MySQL version 5.5, mysqldump is executed at the same time as the table modification operation:

If the table modification operation is after "mysqldump is turned on but the modification has not been exported" Starting within the time period " before the table data, the modifying table operation is completed successfully, and mysqldump will not fail to execute, but the data of the modified table cannot be exported normally;

If the modifying table operation is performed in "mysqldum has exported the modified table data but not yet completed the mysqldump operation" time period, the table modification operation is blocked, mysqldum can be completed successfully, and the table modification operation can be executed normally after the mysqldump operation is completed.

For MySQL version 5.5, you should avoid performing mysqldump and modify table operations at the same time to avoid losing the data of the modified table in the backup and causing data inconsistency!

The above is the detailed content of What should I do when mysqldump --single-transaction encounters alter table?. For more information, please follow other related articles on the PHP Chinese website!