Some production environments use mysqldump --single-transaction to perform database backup at night, and a colleague happened to perform an alter table operation during the backup period. Part of the operation succeeded and part failed. Why?
The test was executed on MySQL 5.6.36. There is a version difference in this problem!
##========================================== =================================
##Explanation of single-transaction parameter in mysqldump For:
Creates a consistent snapshot by dumping all tables in a 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 a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should 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 part is the key point, but it’s a little confusing, so I’d better try it out.
According to the introduction of "Exploration of Several Main Options of Mysqldump", the command mysqldump --single-transaction --master-data that we execute during backup 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 xxx QUIT
Scenario 1: When mysqldump starts but has not yet been backed up to table tb001, another session performs an alter operation on table tb001, and then mysqldump exports table tb001
-- -- Dumping data for table `tb1002`--LOCK TABLES `tb1002` WRITE;/*!40000 ALTER TABLE `tb1002` DISABLE KEYS */;Summary:
The above is the detailed content of Detailed explanation of examples of mysqldump. For more information, please follow other related articles on the PHP Chinese website!