3 ms·
Just my 2 cents. I run a small software business that involves a few moderately-sized databases. The day I moved from a fully managed hosting to a Linux VPS, I
by john_moscow 6y ago
Just my 2 cents. I run a small software business that involves a few moderately-sized databases.
The day I moved from a fully managed hosting to a Linux VPS, I have crontabbed a script like this to run several times a day:
for db in `mysql [...] | grep [...]`
do
mysqldump [...] > $db.sql
done
git commit -a -m "Automatic backup"
git push [backup server #1]
git push [backup server #2]
git push [backup server #3]
git gc
The remote git repos are configured with denyNonFastForwards and denyDeletes, so regardless of what happens to the server, I have a full history of what happened to the databases, and can reliably go back in time.
I also have a single-entry-point script that turns a blank Linux VM into a production/staging server. If your business is more than a hobby project and you're not doing something similar, you are sitting on a ticking time bomb.
- Ayesh 6y agoThis is what I do too. The mysqldump command is tweaked to use individual INSERT clauses as opposed to one bulk one, so the diff hunks are smaller. You can also sed and remove the mysqldump timestamp, so there will be no commits if there are no database changes, saving the git repo space.
- mgkimsal 6y agoAny issues with the privacy aspect of that data that's stored in multiple git repos? PII and such?
- john_moscow 6y agoThese are private repos on private machines communicating over SSL on non-standard ports with properly configured firewall. The risk is always there, but it's low.
- bufferoverflow 6y agoYou should really compress them instead of dumping them raw into Git. LZ4 or ZStandard are good.
- candiddevmike 6y agoAnyone reading the above: please don't do this. Git is not made for database backups, use a real backup solution like WAL archiving or dump it into restic/borg. Your git repo will balloon at an astronomical rate, and I can't imagine why anyone would diff database backups like this.
- john_moscow 6y agoIt really depends on your database size. This works just fine for ~300MB databases. Git gc takes pretty good care of the fluff and once every couple of years I reset the repository to prune the old snapshots. The big plus is that you can reuse your existing git infrastructure, so the marginal setup costs are minimal. You can always switch to a more specialized solution if the repository size starts bugging you, but don't fall into the trap of premature optimization.
- candiddevmike 6y agoGit GC won't do anything here unless you're deleting commits or resetting the repo constantly. Every commit will keep piling up, and you will never prune anything like you would a traditional backup tool. The day you do decide to start pruning things, expect your computer to burst into flames as it struggles to rewrite the commit history! Using a real database backup solution isn't a premature optimization, it's basic system administration.
- danmur 6y agoAgree. Nothing wrong with doing what works for most things, but with backups it's easier to use an appropriate process from the start. It's easy enough and saves you grief later.
- deleted 6y ago[deleted]
- john_moscow 6y agoI haven't dug into too much detail, but doing git gc did have a noticeable effect on size and subsequent update performance. I assume, some temporary artifacts got consolidated. Also resetting the repository once every 1-2 years, and keeping the old one for a while is fine for smaller setups. Depending on your business size and the amount of resources you want to allocate towards "basic system administration", accomplishing the same task with fewer tools could have its advantages.
- wolfgang000 6y agoI don't believe having a massive repo with backups would be the ideal solution. Couldn't you just upload the backup to an s3 bucket instead?