Synchronizing A MySQL Database with Git and Git Hooks

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’d 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.

#!/bin/bash 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

And mark the script executable.

chmod +x /path/to/your/repo/.git/hooks/pre-commit

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:

#!/bin/bash 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.

And let’s mark this one executable too.

chmod +x /path/to/your/repo/.git/hooks/post-merge

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.