TechPrufer
Get started
Shopify
Shopify logoShopify · Feature trail

MySQL Backup and Restore Infrastructure

1 post · Sep 2019 – Dec 2021

What this trail covers

Shopify has redesigned its MySQL backup and restore tooling to leverage GCP's Persistent Disk (PD) snapshot feature, significantly reducing the Recovery Time Objective (RTO) to under 30 minutes. The new system uses Kubernetes CronJobs for frequent, automated PD snapshots across all regions, minimizing data loss and enabling faster replica rebuilds. A robust retention policy manages snapshot storage costs. A verification process ensures data integrity by restoring and checking daily snaps. Offsite backups are also managed by exporting snapshots and archiving them.

All Shopify trails
  1. Upgrading MySQL at Shopify - ShopifyThis post details the process of upgrading Percona Server from 5.7.21 to 5.7.32, including challenges with rollback testing due to changes in `innodb_index_stats` and `innodb_table_stats` column lengths and unexpected table analyze processes. The team developed internal tooling, extending the Mason self-healing service into a Declarative Database Topologies project, to automate provisioning of upgraded VMs and scale-up provisioning. They also identified and fixed a bug related to `ALTER TABLE AUTO_INCREMENT` commands causing full table rebuilds during downgrades.
  2. How Shopify Manages Petabyte Scale MySQL Backup and Restore - ShopifyThis post details the migration from Percona Xtrabackup to GCP's Persistent Disk (PD) snapshot feature for MySQL backups. It describes the implementation of a new backup tooling using Kubernetes CronJobs for frequent snapshots (every 15 minutes), a retention policy framework for managing snapshot storage costs, and a verification process to ensure data integrity by restoring and checking daily snapshots. It also outlines the process for offsite backup storage by exporting snapshots and archiving them. The new system reduces RTO to under 30 minutes.