Snapshot Strategy

A snapshot is a full copy of your tables at one moment, as Parquet. DBTrail takes the first one by reading your database, and keeps every row change after it. Together they rebuild a table as it was at any second after your oldest snapshot. They do not replace your own backups.

Taking snapshots

• Once: Snapshots → Read database now.
• On a schedule: Snapshots → Settings → Update the copy, from five minutes to a day. Later snapshots are built from the previous one plus the changes, without reading your database: how the Parquet copy works.
• Where they go: a Local folder, an S3 location, or both (Snapshots settings). S3 alone also stays current without reading your database (since 0.101.0; before that, set both).

How a snapshot stays point-in-time

Your application keeps writing while a snapshot is taken. A snapshot that reads your database (the first one, Read database now, or an update that has to read in full) makes writes wait for an instant at the start. In that instant every copying thread marks the same point-in-time, then the wait ends and the copy runs, for minutes or hours, with writes going on as usual. Without that instant each table would be copied at a different point-in-time, and the snapshot would show a state your database was never in. Updates built from the previous snapshot plus the changes do not read your database and never make writes wait.

How long the wait is. The instant itself is short: a few hundredths of a second (13 to 73 ms in our measurements, never more than 141 ms). What can stretch is the wait before that instant, when something is already running on your database. What waits, and for how long, depends on the server:

• MySQL on a server you run yourself (the ftwrl mode): a long query reading a copied table. The snapshot waits until the query ends, and in the meantime writes to that table stop. If your application's transactions touch that table, the application stops too (13 seconds with no writes in our test). A schema change (ALTER TABLE) that is running delays the snapshot until it finishes, but writes go on. Once the snapshot starts, no schema change can run until it ends.
• MariaDB on a server you run yourself (the ftwrl mode): long queries do not make it wait. A schema change (ALTER TABLE) that is running does, and while it waits every write on the whole server stops until the schema change ends. If that takes more than 60 seconds, the snapshot gives up and fails. In our test: 61 seconds with no writes anywhere on the server.
• RDS and Aurora (the lock-all mode): an open transaction that wrote to a copied table and has not committed yet. The snapshot waits until it commits, and on MySQL writes to some of the other copied tables stop in the meantime (13 seconds in our test). Long queries and schema changes add at most a couple of seconds.

So take snapshots that read your database when no long reports, long transactions or schema changes are running, for example outside business hours, and on MariaDB never during a schema change. To see what is running right now: SHOW FULL PROCESSLIST. All the numbers, and how they were measured: How long a snapshot blocks writes.

Encryption and retention

• Encryption: SSE-KMS with your key on the bucket.
• How long: an S3 lifecycle rule on the snapshots prefix, which Snapshots settings generates per server. Without S3, keep the newest N locally. You can go back as far as the oldest snapshot kept.

Going back

A few rows: Recovery. Whole tables at a moment: Restore Tables to a Moment. DBTrail never writes to your MySQL: you review and apply.

Compared to mysqldump + cron

mysqldump still fits one-off exports. Scripted snapshots from cron, without the web interface: dump and baseline reference.