When I develop web based projects I often use a MySQL database to hold the data for my project. I also use git for source control and since I tend to work on my projects from several different computers I push and pull from a VPS of mine. This system works great for keeping all my source code in check and is easily accessible. However, I have had one issue. I was having to create a testing instance of my MySQL database on every computer that I pulled onto and worked from. This was a waste of time and also resulted in inconsistencies in my testing data which became confusing at times. I decided that I had had enough of this and set out to find a solution.
I found an article by David Eisinger entitled “Backup your Database in Git” in which he suggested using mysqldump and simply adding the dump to the database. While I had hoped for a more elegant solution than simply dumping your database every time, it would certainly work. He also suggested using cron to schedule dumps. David wrote this article describing a way to backup a production site, which cron would work fine for, but for me it would have to be a little more complicated as I wanted an updated database on every commit.
To automate the process of dumping and restoring my database dumps I decided that git hooks would be able to do the job. I had recently worked with git hooks as a way to deploy website updates directly from a git database. Using the pre-commit hook and the post-merge hook, we can create a system that will automatically dump and add your database to each commit and update your local database from each pull.
Let’s start with pre-commit. The pre-commit hook will run a script directly before a commit is executed. To edit your pre-commit hook:
[your editor] /path/to/your/repo/.git/hooks/pre-commit
Now, lets write the pre-commit script. We are going to tell the system to dump our MySQL database to our git repository and add it to be committed.
1
2
3
4
| #!/bin/sh
mysqldump -u [mysql user] -p[mysql password] --skip-extended-insert [database] > /path/to/your/repo/[database].sql
cd /path/to/your/repo
git add [database].sql |
Now, lets write the post-merge script. We are going to tell the system to restore the MySQL dump to the local database for the latest changes. Edit the post-merge hook with:
[your editor] /path/to/your/repo/.git/hooks/post-merge
And write:
1
2
| #!/bin/sh
mysql -u [mysql user] -p[mysql password] [database] < /path/to/your/repo/[database].sql |
Note that in both in the mysqldump and mysql commands, there is no space between the -p and the password.
That is it! Now your MySQL database will be pushed and pulled with the rest of the commit and the pre-commit and post-merge hooks will handle the importing and exporting of the dumps.
.