Hot Backup

Cold backups require downtime. For a payment system processing thousands of transactions per second, downtime is not an option. The database must be backed up while it is running, while users are reading and writing, without dropping a single query.

Shopify publishes monthly throughput numbers: roughly 60,000 queries per second on their core MySQL clusters during Black Friday peaks. They take full backups every four hours. During those backup windows, application p99 latency moves by less than two milliseconds. Users never notice. PagerDuty never fires. The backup still produces a crash-consistent image of a database that was being modified 50,000 times per second during the copy.

This is a hot backup. The database stays online, the backup agent reads data files page by page while simultaneously tailing the redo log to capture in-flight changes. The result is a consistent point-in-time copy produced without a single moment of downtime. Every major SaaS company, Facebook, GitHub, Stripe, Netflix, uses hot backups as their default strategy.

The challenge: how do you copy a file that is being written to? If you read page 1 at 10:00:00 and page 1,000,000 at 10:45:00, the two pages are from different points in time. That is not a valid backup.

The mechanism has three phases. First, the agent reads data pages sequentially while the database continues writing. Second, the agent simultaneously tails the redo log, capturing every write that happens during the copy. Third, a "prepare" phase applies those redo log entries to the copied pages, rolling them forward to a single consistent LSN. The final result is identical to what you would get if the server had been cleanly shut down at that exact instant.