Understanding MySQL Backup Consistency: A Practical Example

#webdev #sql #beginners #programming

This post documents the knowledge I’ve gained while learning how to perform data backups in MySQL. It serves both as a learning note and a reference for future use.

Objective

When performing a logical backup (for example, using mysqldump), ensuring data consistency is critical — especially in an active transactional environment.

This post explains how to use MySQL’s Repeatable Read isolation level, consistent snapshots, and savepoints to achieve that.

Example Code

Let’s look directly at the example SQL script:

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 */

Step-by-Step Explanation

Q1 — Set the Isolation Level

At the beginning of the backup process, explicitly set the Repeatable Read (RR) isolation level to ensure consistent reads across transactions.

Q2 — Start a Consistent Transaction

START TRANSACTION WITH CONSISTENT SNAPSHOT;

This statement ensures a consistent view (snapshot) of all tables at the moment it is executed.

Q3 — Create a Savepoint

Setting a savepoint (SAVEPOINT sp) allows rolling back to a specific point later in the transaction.

Q4 — Export Table Definition

Use SHOW CREATE TABLE t1; to capture the table structure.

Q5 — Export Table Data

Next, query all data from the table:

SELECT * FROM t1;

Q6 — Release Locks

Finally, ROLLBACK TO SAVEPOINT sp; rolls back to the savepoint, effectively releasing the MDL (Metadata Lock) on t1.

What Happens if a DDL Arrives Mid-Backup?

Let’s consider different timing scenarios if a DDL (for example, ALTER TABLE) is executed during the backup.

Time	Scenario	Outcome
Time 1 (before Q4)	DDL completes before the backup starts reading `t1`.	✅ No impact. The backup captures the structure after the DDL.
Time 2 (during Q4–Q5)	DDL occurs after `SHOW CREATE` but before data export.	❌ Error: `Table definition has changed, please retry transaction.` The backup (mysqldump) terminates.
Between Time 2 and Time 3	Backup holds an MDL read lock on `t1`.	⚠️ Binlog is blocked — replication delay until Q6 completes.
Time 4 and later	DDL executes after the MDL lock is released.	✅ No impact. Backup captures table structure before the DDL.

Summary

Use REPEATABLE READ + WITH CONSISTENT SNAPSHOT to maintain transactional consistency.
Use savepoints strategically to release locks without ending the transaction.
Be aware of DDL timing, which can affect replication and mysqldump behavior.