“This blog post is intended to document the new knowledge I’ve acquired during the learning process, specifically about how to perform data backup in MySQL. If needed in the future, it can serve as a reference.”

Take a look at the code directly.

1
2
3
4
5
6
7
8
9
10
11
12
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* Time 1 */
Q4:show create table `t1`;
/* Time 2 */
Q5:SELECT * FROM `t1`;
/* Time 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* Time 4 */
/* other tables */

At the beginning of the backup process, to ensure the Repeatable Read (RR) isolation level, set the RR isolation level once more (Q1).

Start a transaction, using ‘WITH CONSISTENT SNAPSHOT‘ to ensure that a consistent view is obtained as soon as this statement is executed (Q2).

Set a savepoint (Q3).

Use ‘SHOW CREATE‘ to retrieve the table structure (Q4), and then proceed to export the data (Q5). After that, roll back to the savepoint ‘sp,’ where its purpose is to release the MDL lock on ‘t1’ (Q6).

Please note, if at this point, a DDL statement is received, assuming it is for a small table and if it starts executing shortly after arrival and can complete quickly, what would be the scenario?

  • If it arrives before the execution of the Q4 statement, i.e., at time 1, the scenario is: No impact; the backup will capture the table structure after the DDL statement.
  • If it arrives at “time 2,” the table structure has been altered. When Q5 is executed, it will result in an error: “Table definition has changed, please retry transaction.” Scenario: mysqldump terminates.
  • If it arrives between “time 2“ and “time 3,” mysqldump holds the MDL read lock on t1, and the binlog is blocked. Scenario: There is master-slave replication delay until Q6 is completed.
  • Starting from “time 4,” mysqldump releases the MDL read lock. Scenario: No impact; the backup captures the table structure before the DDL statement.

This blog post references content from ‘MySQL in Action: 45 Lectures’ ^(1)^.

Reference

(1) https://time.geekbang.org/column/article/70215