I'm working as an intern at a fund. I spent the last month building a website for internal use, and now I think it's a good time to set up a backup scheme for the MySQL database at its backend. Funny enough, my mentor is reluctant to get me another server because we are not allowed to use external IaaS like AWS and DigitalOcean for security reasons, and it takes weeks to get a usable server from our IT department. Thus, I'm planning to make the backup on the same server running my website. Yeah, I understand the data would be gone if the disk fails or the server brows up, but at least it would be a lifesaver in case of an accidental DROP DATABASE production;
. By the way, all "servers" assigned by IT appear to be VPS running on the same physical machine, so I guess backing up on another virtual server can't protect the data against a disk/server/power failure after all?
Anyway, here is my local backup plan: I will run mysqldump
and commit it to a local git repository every minute. More concretely, I have set up a cronjob to run the following script with */1 * * * *
. Essentially I'm using git as an incremental compression tool, rather than a VCS.
I have also included some tags for easy navigation.
#!/usr/bin/env bash
export BACKUP_DIR=/home/foo/Backups/bar
mysqldump \
--defaults-extra-file=/home/foo/Developer/MYSQL_ROOT.cnf \
--single-transaction \
--extended-insert=FALSE \
production | sed '$d' > $BACKUP_DIR/production.sql
git -C $BACKUP_DIR commit --all -m 'Auto backup via cron' > /dev/null
git -C $BACKUP_DIR tag -f `date +%F`
git -C $BACKUP_DIR tag -f `date +%F@%H`
git -C $BACKUP_DIR tag -f `date +%F@%H-%M`
Currently, the backup script takes about a second to complete, and the resulting production.sql
is around 3MiB in size. I estimate it would stay under 10MiB for years. The website in question has ~20 users, and I won't expect more than 1000 requests per day. I'm using MySQL Community Server 8.0.21 on RHEL 7, without any Enterprise subscription.
Can I make a reliable local backup this way? Is one lightweight tag per minute too much? Is there a better alternative?