So you built something, its running, and now you need to have some kind of automated database backup running.
Wouldn’t it be nice to just be able to implement this with a few lines of code and a git repository ? Rightâ€Š–â€Šthat’s what I thought too.
Lately I’ve been working a lot with sites, servers and users in low-bandwidth areas , and I’ve had to come up with a database backup solution that would run on almost any system, and that would not require much bandwidth.
One solution I’ve found that have worked particularly well for me in these circumstances is a small shell script which dumps the database to a sql file, and then the changes are pushed to a git repository.
This approach has a few benefits:
- Easy to set upâ€Š–â€Šyou only need git repository, a tiny shell script and a cron job
- Low Bandwidthâ€Š–â€ŠBy using git, you will only ever push the changed lines in your sql backupâ€Š–â€Šnot the entire file.
- Easy to track changesâ€Š–â€ŠBy using gitâ€Š–â€Šyou can easily browse the commit’s using your favourite UI to see what changes has actually been made to the database
How to do it
- Create a git repository that will hold your database backup ( Bitbucket allows free private repositories )
On your server, clone the git repository into a folder.
git clone firstname.lastname@example.org:yourusername/db-backup.git .
Create a shell script, lets call it run-db-backup.sh, that will dump your database into this folder, and then add the changes, commit and push to git
#!/bin/bash localPATH=’pwd’ echo “Dump Database” cd /home/ec2-user/db-backup mysqldump -u dbusername -p”yourdbpasswordhere db_name > db_backup.sql echo “Commit changes to git and push” git add .;git commit -m “Latest Database Snapshot”; git push origin master echo “Done”
- Create a cron job to execute your new shell script at your desired interval
0 */6 * * * /home/ec2-user/run_db_backup.sh
Now you can log into your git repository and watch your automated database backups flow in. :)
To restore a specific commit from the repository, just reference your commit ID/hash when doing a git checkout:
git checkout 0d1d7fc32 (where 0d1d7fc32 would be your commit hash)
Now you can import your db-backup.sql file into your database and have a well deserved cup of coffee.
This is a cross-post from the original Medium article
Top comments (1)
If performance is an issue, I've found mydumper/myloader to be about 4 times faster than a mysqldump workflow.