What You'll Need

  • One locally hosted server (e.g. MAMP)
  • One MySQL database on the aforementioned server, complete with tables of data
  • The ability to commit, push, pull, and be generally unkind with Git (I'll be using Tower for Mac but any Git software (command line or GUI) will work. This guide assumes you've already got your Git repository setup)

Step 1: Plan it

Our aim with this project is to make it easy, quick, and painless to keep MySQL databases in sync using our Git repo as a bridge. We're going to do this by automatically dumping the database into an SQL file and then committing that file whenever we see "#sql" in a commit's description.

We can do this by using Git hooks to check the commit message, and some 'simple' PHP run on our server to dump the MySQL database.

So, lets list out what we're going to do in the order we want to do it.

  1. Check if "#sql" is anywhere in the current commit's message
  2. If found, use cURL to access a PHP file on our local server and save the contents of that file to a specified location as an SQL file (we'll discuss this later)
  3. Once our users commit has finished, stage and commit our SQL file.

Step 2: Do it

The first thing we want to do is write our hooks. "But what are hooks" I hear you cry in anguish. Git hooks are simply a "way to fire off custom scripts when certain important actions occur" (see 7.3 Customizing Git - Git Hooks).

Git hooks are repository specific, and can be found in a hidden folder in the root of your repository called .git. We'll need the folder .git/hooks/, in which you should find some .sample files created by Git. It's worth poking through these files to learn a bit more about what Git hooks can do and how they're coded.

For our project, we'll be using the following two hooks: commit-msg and post-commit.

Step 3: commit-msg

This hook is fired just after the commit's message has been created. We'll use this hook to check if the message contains "#sql" and then save our dumped database to file if it does.

I'll first write the entire file, then talk you through it line by line below. The code is saved as commit-msg in [repository-root]/.git/hooks/. There is no file extension on the file

Okay, let's look at this scary block of code in some even more terrifying detail.

This is what's called the Shebang line. It's purpose is to tell the shell which language to use. In this case we're telling it to use the bash language which is safe for most operating systems (for a long time this was essentially the 'default' language), but some Linux systems are starting to peel away from the current norm. This is becoming a whole separate conversation, but if you're interested I recommend reading this answer on StackOverflow.

Next we set our variables that we'll use within the if statement.

This variable points to the PHP file on our local server that will do the dumping of our SQL database (we'll write this later).

Here we're getting the first 10 lines of our commit message. Note that we are using backticks not single quote marks! Please keep in mind that if the commit message is longer than 10 lines, then anything after the 10th line will not be included in our check for "#sql".

The variable above uses the UNIX date command (date) to get the current timestamp (+%s).

Finally, we're setting the filename of the sql file where our database will be dumped. In this case, our file will be saved in [repository-root]/sql-dump/sql-dump_[current-timestamp].sql. Note we include the path to the file as well as the file itself. Here, we are using the UNIX timestamp to make a new file each time we push our database. This is optional, and could be replaced by updating a single file and letting Git do the versioning.

Right, that's our variables out the way, now on to the real code!

We start our if statement by looking for any instances of "#sql" (without speech marks) within our message string.

If any instances were found, then the first thing we'll do is let the user know by echoing out a message to the console (the variables in the message will be replaced by their respective values).

Next we'll create the empty sql file. We can do this using the redirection operator (>) which in this case tells the shell to create the file using the filename variable.

Now the file is created, we'll use curl to open the dumpurl file and save it's contents to the newly created file.

Finally, we echo out a message to the console if we couldn't find "#sql" and end the if statement.

There, that wasn't so hard!

Step 4: sql-dump.php

This is the file that will be on our local server and will echo the contents of our database so we can save it to a file (above). The code below is modified from some code I found online, but I've forgotten the source (my bad)! If you recognise the code, be sure to give it's author a hug.

I'd love to talk you through this code line by line, but that would take far too long. Instead I'll break it up into blocks and talk you through what each block is doing. It's relatively basic PHP code, so shouldn't be too hard to understand.

Okay, first off we're opening our PHP tag and echoing out the returned contents of the function dump_tables. This function requires the host name, database username, database password, and the database name. It also accepts the table name (as an array or comma separated string of table names), but this is optional and defaults to all tables.

Next we create an array of the tables we will be dumping. If $tables is set to * (all), then we will query the database for 'SHOW TABLES' which will list all the non-temporary tables in our database. From there we loop through each returned result as $row and add the table name ($row[0]) to our $tables array. If the $tables variable has been set by the user, we'll do a quick check to see if it's an array. If it is an array, then set $tables to that array, if not we assume it's a comma separated list string (e.g. 'table1,table2,table3') and we turn that into an array and set $tables to be that new array. Finally, we create our $return variable and set it to an empty string.

won't go into detail here but put simply this huge block of code loops through each table, get's it's contents, and adds everything into our $return variable formatted as to be importable to a database. Finally the $return variable is returned for us to echo out.

Step 5: post-commit

As I'm sure you can guess by the name, our post-commit hook is called after the commit has been committed. We'll use this hook to automatically commit our dumped SQL file. Since the file was created after the users commit was started, it was not included so we have to do it here.

Again, I'll talk you through everything line by line.

First off we tell the shell what language to use. Here we're telling it to use the default shell language (sh), as opposed to bash. This is because we don't need any of the extra functionality added by bash (although in some cases the default language will be bash, but again this is a separate conversation - see above).

Here we're setting our lastcommit variable to be the SHA1 hash of the latest commit (this would be the commit the user just made). We're doing this to make our automated commit identifiable with the commit that triggered it.

Now we're telling Git to stage all changes since the last commit, ready for us to commit. The only changes will be our generated SQL file. If the SQL file wasn't generated (because the user didn't use #sql in their commits message) then there wouldn't be any new changes so there would be no automated commit.

Finally, we commit our changes (if there are any) with the message "AUTO: Added Latest sql-dump for [previous-commit-SHA1-hash]".

Final Notes

This method uses Git directly. It doesn't matter whether you're using command line Git or a GUI for Git, the result should be the same.

The only other step needed to keep your databases in sync is for everyone on the receiving end to import the latest SQL file into their database.

Remember: Git hooks are unique to each repository. As such, you'll have to add the hooks to each repository you want to use the method with.