HN Explorer

Ask HN: How do you backup your production databases?

6 points by Kovah ↗ HN
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,...?

5 comments

[ 2.9 ms ] story [ 21.3 ms ] thread
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.

toast0
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.

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.