Using Git to track mysql schema - some questions

Question

If this is recommended ?

Can I ask some git command examples about how to track versions of mysql schema?

Should we use another repository other then the one we normally use on our application root ?

Should I use something called hook ?

Update:

1) We navigate onto our project root where .git database resides.

2) We create a sub folder called hooks.

3) We put something like this inside a file called db-commit:

   #!/bin/sh    mysqldump -u DBUSER -pDBPASSWORD  DATABASE --no-data=true> SQLVersionControl/vc.sql    git add SQLVersionControl/vc.sql    exit 0 

Now we can:

4) git commit -m

This commit will include a mysql schema dump that has been run just before the commit.

The source of the above is here: http://edmondscommerce.github.io/git/using-git-to-track-db-schema-changes-with-git-hook.html

If this is an acceptable way of doing it, can I please ask someone with patience to comment line by line and with as much detail as possible, what is happening here:

#!/bin/sh mysqldump -u DBUSER -pDBPASSWORD  DATABASE --no-data=true> SQLVersionControl/vc.sql git add SQLVersionControl/vc.sql exit 0 

Thanks a lot.

Answer 1

Assuming you have a git repo already, do the following in a shell script or whatever:

#!/bin/bash -e # -e means exit if any command fails DBHOST=dbhost.yourdomain.com DBUSER=dbuser DBPASS=dbpass # do this in a more secure fashion DBNAME=dbname GITREPO=/path/to/git/repo cd $GITREPO mysqldump -h $DBHOST -u $DBUSER -p$DBPASS -d $DBNAME > $GITREPO/schema.sql # the -d flag means "no data" git add schema.sql git commit -m "$DBNAME schema version $(`date`)" git push # assuming you have a remote to push to 

Then start this script on a daily basis from a cron job or what have you.

EDIT: By placing a script in $gitdir/hooks/pre-commit (the name is important), the script will be executed before every commit. This way the state of the DB schema is captured for each commit, which makes sense. If you automatically run this sql script every time you commit, you will blow away your database, which does not make sense.

#!/bin/sh 

This line specifies that it's a shell script.

mysqldump -u DBUSER -pDBPASSWORD  DATABASE --no-data=true> SQLVersionControl/vc.sql 

This is the same as in my answer above; taking the DDL only from the database and storing it in a file.

git add SQLVersionControl/vc.sql 

This adds the SQL file to every commit made to your repository.

exit 0 

This exits the script with success. This is possibly dangerous. If mysqldump or git add fails, you may blow away something you wanted to keep.