BiteSizedChunks.comLearn one small thing at a time.
Course contentsShow
← Databases & SQL
Lesson 1031 of 1,9539. MySQL and MariaDB EcosystemsFree lesson

Snapshot Backup Strategies

Execute physical backups using snapshot-consistent methods for minimal downtime.

In this lesson, you will learn how to leverage storage-level snapshots to perform physical backups in MySQL and MariaDB with minimal impact on database performance and availability.

Why Snapshot Backups?

Traditional logical backups (like mysqldump) read data row-by-row, which is slow and resource-intensive for large datasets. In contrast, a snapshot backup operates at the file-system or block-storage level. By utilizing the underlying storage infrastructure (like LVM, AWS EBS, or ZFS), you can capture a "frozen" image of your data files in seconds, regardless of the database size.

Achieving Consistency

The challenge with snapshots is that they are "crash-consistent"—like pulling the power cord on a server. To make them application-consistent (safe to restore), we must ensure the database is in a quiet state where all pending transactions are flushed to disk.

For MySQL/MariaDB with InnoDB, the gold standard for snapshot-consistent backups is to use the FLUSH TABLES WITH READ LOCK (FTWRL) command. This forces the storage engine to synchronize the memory buffers to disk and prevents new writes, ensuring that your snapshot represents a clean, stable point-in-time.

The Workflow

To perform a snapshot backup safely, follow this high-level sequence:

  1. Enter Read-Only Mode: Execute FLUSH TABLES WITH READ LOCK; in your SQL client. This blocks incoming writes, ensuring the data on disk is fully consistent.
  2. Trigger the Snapshot: While the lock is active, trigger the volume-level snapshot through your storage API (e.g., lvcreate --snapshot or AWS CLI).
  3. Release Locks: Execute UNLOCK TABLES; immediately after the snapshot command finishes.
-- Step 1: Prepare the instance
FLUSH TABLES WITH READ LOCK;

-- Step 2: (External) Run storage-level snapshot command
-- e.g., lvcreate -L 10G -s -n mysql_snap /dev/vg0/mysql

-- Step 3: Resume normal operations
UNLOCK TABLES;

This method is highly efficient because the "stop-the-world" time is reduced to only the duration of the storage snapshot initiation, which is typically sub-second. This allows you to maintain high availability while ensuring a valid, bit-perfect copy of your database.

Key Takeaway: Snapshot backups provide near-instant physical backups by freezing the database state, taking a block-level copy, and resuming operations, effectively minimizing downtime during large-scale data protection tasks.