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.

As a high school student, I spend about half of the time that I am awake at school. Therefore, if I want to really work on a project, I have to do at least a little coding at school in one of my classes that makes use of computers. Unfortunately, setting up a portable development environment that can run off of my flash drive has been a bit of a trick, and thus far not completely successful. The system administrators don’t take very kindly to students who have knowledge of computers, they seem to believe that we are just smart enough to be a danger and therefore love to set up obnoxious settings that make my life that much more difficult. Despite their arrogance, I manage but it involves a lot of work just to get an environment running each time I want to work. Often the most tedious task is simply copying over my environment to my personal network drive from my flashdrive (as the running of programs directly from flash drives has been disabled). Transferring XAMPP over is a huge hassle, with over 4,000 files it takes at least 10 minutes.  Therefore, I think that a little love for the portable web developers is needed.

I propose something like an Eclipse style IDE. The IDE runs a  portable web server on the local computer and will test for you at the click of a button. Built in git support is also a must. That alone would save me the copying of Notepad++ Portable, Git Portable, and XAMPP portable. Instead it would be an easy to use and quick to get running. Another issue that would need to be addressed is the unnecessary networking restrictions often put in place by administrators. Currently XAMPP Portable has an issue running on it’s own and requires extensive modification to run. With a little bit of checking the IDE could figure out the required settings for the host computer and run without complaint.

I may attempt to develop this in the future because unfortunately I am far too busy with my senior year to be able to take the time out to do a project of this caliber any time soon. I am certain it would end up unfinished. Also, as I will be graduating and moving on to college next year, my need for this sort of thing will be gone soon. I am sure that I am not the only student out there with this problem however, so I decided it would be a good idea to outline the idea for someone else in the future or if I get really bored in college. No idea if that will happen or not, lots of things are set to change for me in the next 12 months.