Setup for automatic Magento database backup in Git

Well, any version control system is great, at least in compare with none at all. But what’s happening with your database? Today I’ll show you how to create automated solution for backup and insertion of MySQL database into Git repository.

Explanation and Prerequisites

Explanation

What I did was clone of existing Magento Git repository on development server, and then created cron task that dumps its current development database into local repository, commits changes, and then pushes them back to remote repository.
Prerequisites

First of all, I’ll explain this using our development server that runs on Linux. If you wish to follow this article, you’ll need Git, MySQL,SSH,Remote git repository (ours is at beanstalk) and crontab access on it.

Setup

First, you need to setup your server to gain access to Git, so if you’re not sure how, just follow this link.

Notes:

– Since our repository works with SSH keys, I explained how to do this in that way. If your works with password authentication, your cron commands will differ from this.

– I’ve setup SSH keys without password, for simplicity

Depending on your repository, Git URL might differ, but it should look like this:

git@you.beanstalkapp.com:/your_repository.git

Next, you need to create clone your remote repository, like this:

cd path/to/your/local/repository/
//first, navigate to folder in which you wish to clone repository
git clone git@you.beanstalkapp.com:/your_repository.git
//cloning itself

Next thing you need to do is to create and add your database dump to the repository. You’ll do that with this command:

cd path/to/your/local/repository/your_repository/
//first, navigate to folder with repository clone if you haven't already
mysqldump -u [mysql_username] -p[mysql_password] --skip-extended-insert [mysql_database] > [name_of_the_dump].sql
//initial database dump
git add [name_of_the_dump].sql
//adding the dump to the repository
git push origin master
//pushing initial database dump to remote repository, on master branch

And next thing is the creation of cron task that will dump the database, and that is the final step of this HOW-TO post.

crontab -e
//start the editor that will allow you to edit cron list for your user on the server
//and then enter the following cron:
*/30 * * * * cd path/to/your/local/repository/your_repository/ && mysqldump -u [mysql_username] -p[mysql_password] --skip-extended-insert [mysql_database] > [name_of_the_dump].sql && git pull origin master && git commit -am 'Updating DB backup' && git push origin master > crontab-[name_of_the_dum]p.log 2>&1
//note that this above cron must be a "single line", without line break characters and that it contains more that 1 command to execute

A small explanation of cron itself is in order so, to break it apart:
*/30 * * * * – cron execution time setup, you can read more about it here.
cd path/to/your/local/repository/your_repository/ – relative path from your SSH login directory (usually user’s “home”) to your local Git repository on development server
mysqldump -u [mysql_username] -p[mysql_password] –skip-extended-insert [mysql_database] > [name_of_the_dump].sql – dump the database with –skip-extended-insert parameter, that will increase readability on each commit / push.
git pull origin master – pull changes on remote Git repository, just in case there won’t be any conflicts
git commit -am ‘Updating DB backup’ – make a commit with new database dump
git push origin master crontab-[name_of_the_dum]p.log 2>&1 – push changes to remote Git repository, and create a log file

And that’s all about the setup.

Conclusion

All of this shouldn’t take more than 10-15 minutes, when you get the hang of it. And in return, you have automated file / database backup of your Magento project on remote development server. I think it’s worth the time, and you?