MySQL -- When mysqldump, mysql -- mysqldump

MySQL -- When mysqldump, mysql -- mysqldump

Some production environments use mysqldump -- single-transaction to back up the database at night, while colleagues just execute the alter table operation during the backup. The operation partially succeeded and failed. Why?

In mysqldump, the single-transaction parameter is interpreted:

Creates a consistent snapshot by dumping all tables in
Single transaction. Works ONLY for tables stored in
Storage engines which support multiversioning (currently
Only InnoDB does); the dump is NOT guaranteed to be
Consistent for other storage engines. While
-- Single-transaction dump is in process, to ensure
Valid dump file (correct table contents and binary log
Position), no other connection shocould use the following
Statements: alter table, drop table, rename table,
Truncate table, as consistent snapshot is not isolated
From them. Option automatically turns off -- lock-tables.

The red font is the focus, but it seems a bit confusing.

According to the introduction of mysqldump, the command mysqldump -- single-transaction -- master-data is equivalent to executing the following code:

FLUSH TABLES;FLUSH TABLES WITH READ LOCK;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;START TRANSACTION WITH CONSISTENT SNAPSHOT;SHOW MASTER STATUS;UNLOCK TABLES;SHOW TABLES LIKE 'xxx'SET OPTION SQL_QUOTE_SHOW_CREATE=1SHWO CREATE TABLE 'xxx'SHOW FIELDS FROM 'xxx'SHOW TABLE STATUS LIKE 'xxx'SELECT /*!40001 SQL_NO_CACHE */ * FROM  xxxQUIT

 

 

Scenario 1: When mysqldump starts but has not been backed up to table tb001, in addition, the statement is used to alter table tb001, and then mysqldump exports table tb001.

The alter operation was successfully completed, but the mysqldump operation failed.

 

Scenario 2: mysqldump starts the backup and completes the export of tb001. During the export of other tables, other statements are used to alter the table.

The alter table operation is blocked until mysqldump is complete or fails to exit.

 

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

Summary:

The single-transaction parameter uses multiple versions of Innodb to obtain data consistency. The alter table, drop table, rename table, and truncate table operations may damage data consistency. The two operations cannot be executed concurrently.

If the modification operation starts in the period before "mysqldump is enabled but the table data has not been exported", the modification operation is successful, and mysqldump fails;

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.

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