Version control for mysql via git hooks

[Samuel]

I found myself searching for an smooth workflow to collaboratively work on a process wire project and had the challenge of including the mysql data into version control to keep it in sync with the rest of process wire. 
Here is a solution with git hooks, that Ben Kubertis came up with: http://ben.kulbertis...-and-git-hooks/

Step 1:

When commiting you want to fetch the latest changes of the database.
Edit the "pre-commit.sample" hook located in /YourRepoDirectory/.git/hooks/ 

#!/bin/sh
mysqldump -u [db_user] -p[db_password] --skip-extended-insert [db_name] > /PathToYourRepo/database.sql
cd /PathToYourRepo/
git add database.sql

Enter your mysql login details and git repository paths and then save and rename it to "pre-commit"

That way each time you commit, any changes of the db will be downloaded and kept under version control

Please note: If you only have modified the database and didn't touch any other files, you will have to manually pull the database updates, since git commit needs "changed files" to successfully commit.

Step 2:

When you checkout or merge branches, then you always want to fire up the belonging database again.
Create another two hooks named "post-merge" and "post-checkout" with the following content:

mysql -u [db_user] -p[db_password] [db_name] < /PathToYourRepo/database.sql

NOTE:

!!! Please test this first on a dummy project, in order to not mess your database up !!!

When working with MAMP you probably have to add the path to mysql and mysqldump as well; Simply put /Applications/MAMP/Library/bin/ in front of it

Edited April 3, 2013 by Samuel

[pwired]

http://http//ben.kulbertis.org/2011/10/synchronizing-a-mysql-database-with-git-and-git-hooks/

Link is messed up, must be

http://ben.kulbertis.org/2011/10/synchronizing-a-mysql-database-with-git-and-git-hooks/

Now, besides mysql updating, if only one day we can find a solution for: to edit a local copy of a website on a laptop and then replicate the same edits on the on-line website on the server. Some script maybe that records the edits you do on the local copy, upload the script and execute it on the server ?

[Samuel]

You can use a git-based workflow and deploy to the live site with git-ftp: https://github.com/resmo/git-ftp
 

The only thing I didn't figured out yet is how to automatically load the updated db afterwards into the live mysql database.

For now I always update manually after pushing.

And another interesting part would be to find a way to git ignore the admin credentials. 

But keep in mind, this kind of workflow only works for simple setups, since when your processwire project is getting more complex and the database is updating itself, because of comments, blog_post, new accounts etc. you don't want to mess with the live data.
In that case better have an "pull" than "push" workflow. 

[pwired]

Interesting post, thanks. I didn´t know about git-ftp. This is one step closer to edit a local website on your laptop and deploy the same edits on the live server. Also didn´t think myself about the logical fact that a db is going to update itself when comments, blogs, new accounts, etc, are active. Updating the db from your laptop is going to destroy all that on the live server.

I just opened the zip and saw some .md files. Do I have to compile the files to an exe or is this a script ?

I just read that on windows you need cygwin. Is there maybe a similar program for native windows ?

[OrganizedFellow]

I just read that on windows you need cygwin. Is there maybe a similar program for native windows ?

Cygwin probably is not required, since Msysgit will replace it (much smaller alternative).

http://msysgit.github.io/

[pwired]

This topic could use more posts to know more about a pw workflow with a good local/on-line sync.

[diogo]

I just googled "git mysql database" and this thread was the forth result

Checked in incognito mode and it's still the forth, so it's not because of google private results.

[apeisa]

For me (incognito) it was fifth. 

[OrganizedFellow]

Down to 6th.

BAAAAAAAAAAACK on topic.

How has everyones workflow situation changed?

Has anyone implemented a reliable method for this?

I'm gonna try the method posted in Post#1