Backup production mysql in Git
Truth be told this is not a good Idea. Here is our experience.
Our server runs on a machine with a mysql as a database. Since we are a number of people on the team we decided to do this marvelous, genius idea. We would backup our production database every day with a sql dump and commit this dump it into git. Then when each of us does his normal job and checkouts other code changes he would also checkout the database. And the best part is that Git can manage differences between files so we are not actually storing the whole file. We store only the differences and this difference most of the time is in the users table – who has logged in, when, what has he done.
As a result we would have the database and all of it history distributed on several machines and we would have the whole history of each and every day. Just genius.
Turns out this is not a good idea. It took git only 315 commits of our not that large 50 MB database to stop working. This “fast”, “supppper speed”, “one of a kind” version control system just stopped working after the 315 build . When we were trying to pull and this was the error occurring:
remote: Counting objects: 677, done.
error: pack-objects died of signal 9/461)
error: git upload-pack: git-pack-objects died with error.
fatal: git upload-pack: aborting due to possible repository corruption on the remote side.
remote: aborting due to possible repository corruption on the remote side.
fatal: protocol error: bad pack header
So we took another approach
- Delete the whole backup branch locally and remotely
git branch -D production_backup
git push origin –delete production_backup
(locally) – git gc
(remotely) – git gc
- Setup a new production backup process where you have daily, weekly and monthly backup
- Daily – runs every day and only the last 8 backups are saved
mysqldump –user=root –password=ThePassword –databases production | gzip > production_backup$(date +”%Y_%m_%d”).sql.gz
- Weekly – runs every week and only the last 5 backups are saved
mysqldump –user=root –password=ThePassword –databases production | gzip > production_backup$(date +”%Y_%m_%d”).sql.gz
- Monthly – runs every month and only the last 13 are saved.
mysqldump –user=root –password=ThePassword –databases production | gzip > production_backup$(date +”%Y_%m_%d”).sql.gz
Take a look at the gzip part. Since sql dumps more often than not, contain text information you should always gzip them. And the $(date +”%Y_%m_%d”) just puts the current date in the file name.
Now we just have to figure our when an how to copy this backups to another machine