mysql--when mysqldump--single-transaction encounters alter TABLE (2)

In the previous article "mysql--when mysqldump--single-transaction encountered alter TABLE" Test found that in the MySQL 5.6 version, if the table was modified during mysqldump, may result in mysqldump error, This conclusion is inconsistent with colleagues ' execution, so it is tested in MySQL version 5.5.

Test environment:

MySQL 5.5.14

There are table tb1001 under Database TESTDB01, and there are two data in the current table:

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

Found that unexpectedly returned an empty set, without any error.

This is also a reasonable explanation of my colleague Operation phenomenon: in the mysqldump process, modify the table structure, modify the operation is not blocked, mysqldump operation "normal completion."

Because the Select/*!40001 Sql_no_cache */* from ' tb1001 ' operation does not return an error and does not return data, the mysqldump process will treat tb1001 as an empty table, Then continue exporting the subsequent tables until you export all the tables and return to the status of successful execution. However, the exported backup has been missing tb1001 data, and if it happens to recover the data, it must eventually result in "data loss".

Workaround:

Before modifying the table for MySQL version 5.5, check that the current server is performing mysqldump operations and avoid parallel execution.

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

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

Summarize:

For MySQL version 5.5, the mysqldump is executed concurrently with the table modification operation:

If the Modify table operation starts in the time period after mysqldump is open but before the modified table data has been exported, the Modify table operation completes successfully, and mysqldump does not fail, but the data of the modified table cannot be exported properly;

If the Modify table operation starts within the time period when the Mysqldum has exported modified table data but before the mysqldump operation has been completed, the Modify table operation is blocked, Mysqldum can be completed successfully, and the table operator can be modified after the mysqldump operation is completed.

For MySQL version 5.5, you should avoid mysqldump and modify table operations at the same time, in order to avoid backup loss of modified table data, resulting in inconsistent data!

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

mysql--when mysqldump--single-transaction encounters alter TABLE (2)