HACKER Q&A
📣 Kovah

How do you backup your production databases?


For me, backups of the database are probably the most crucial asset any web service can have. And there are like 1000 ways to make backups of that data. I would like to know how others handle database backups, especially in Docker-based environments.

- Which tools do you use? Which ones should be avoided?

- Where do you store the data and how do you handle the connection between your servers and that other location?

- What about the schedule, and do you use multiple backup locations?

- How does one make sure the backups are monitored and, most important, can be actually used to restore a production database?

- Are there any pitfalls everyone should know when doing backups for MySQL, PostgreSQL, SQLite,...?

  👤 toast0 Accepted Answer ✓
When I ran MySQL servers (which was many years ago), I ran a replication slave (or two) that didn't get query traffic; every so often, mysql would be shut down, the whole data directory would be tared and compressed, and then mysql would be started back up. IIRC, the interval was every 4 hours.

Normal production monitoring kept track that replication was working, but had to have an exception to allow more time between detection and alerting for the archiving.

These archives were used to start new production slaves, so the backups were tested from time to time. Older archives and careful use of binary logs could allow restoration to any particular point (although I never had to do that); at least within the retention of binary logs.

I did a rotation of backups to have the most recent X frequent backups, Y backups no more than 1 a day, Z weeklies, etc monthlys, etc, etc.

👤 PeterZaitsev
Percona Monitoring and Management - Open Source tool has backups included

https://www.percona.com/software/database-tools/percona-moni...

(Percona Founder so kind of biased)

👤 kmos17
Using MySQL, Percona xtrabackup is very helpful. We do daily backups that are exported to multiple cloud and local storage via rsync and a commercial backup application. We have a fully automated script that can recreate the db from one of these backups in a single command so it can be tested on a regular basis.
👤 konha
Managed database service with automated backups and point in time recovery by our cloud provider of choice.

Additionally daily backups to a storage server hosted by a different cloud provider with a simple cronjob.

👤 darrenwestall
Snapshooter.