How to "sync"/revision-control an entire mysql database with all git commits and checkouts?

Question

I want to "synchronize" a mysql database (and not just the schema) with all git commits and checkouts (probably via hooks) in Linux/BSD/Unix enviornment.

[Useful when controlling the entire presentation + content of a mysql-based web site/application, and other mysql applications, as the code for said sites/applications are under development, and/or to "rev control" the entire website. Presumably useful for pushing "copies" (exclusively via git) of small, relatively-static websites to various test/production servers that each have separate, independent mysql servers.]

Potential design/workflow: a git commit dumps a mysql database and adds said database (in a .sql file) to the commit (probably via a pre-commit hook). A subsequent git checkout imports said .sql file (from the current git branch) to the database (probably via a post-commit hook), hence "synchronized" with the git commit/branch. This basically rev-controls the entire database, synchronizing each database rev to a git commit.

How to implement this?

Answer 1

My WordPress-database-control example follows.

• WordPress-specific parts. ./dbase-baseurl-set.sh $baseurl needed to tell WordPress which host it's on--WP unfortunately not that modular. ./dbase-baseurl-set.sh runs from the top level of the working tree.
• git hooks env vars. Far-and-away best doc I've found to answer "what's the working directory" and "which environment variables are set" in git hooks: Mark's Blog: "Missing git hooks documentation."
• Bybass pre-commit. Employ git commit -n [...] to avoid executing the pre-commit hook. (Potentially useful when first-time adding/committing dabase-dump.sql.gz.)
• Optional. git-control the hooks.

Example Ubuntu session:

root@host /var/www/.git/hooks# ls -lF
total 12
-rwxr-xr-x 1 root webmasters  218 2012-02-23 22:06 post-checkout*
lrwxrwxrwx 1 root webmasters   13 2011-12-13 21:45 post-merge -> post-checkout*
-rwxr-xr-x 1 root webmasters  357 2012-02-23 22:06 pre-commit*
drwxr-sr-x 2 root webmasters 4096 2011-12-13 10:19 samples/
root@host /var/www/.git/hooks# 
root@host /var/www/.git/hooks# cat pre-commit 
#!/bin/bash
set -e
# run with '--no-data=true' to capture only schema
# mysqldump -umysqluser -pmysqlpasswd wordpressdbase --no-data=true | gzip > wordpressdbase-schema.sql.gz
echo -n 'dumping and compressing wordpress database... '
mysqldump -umysqluser -pmysqlpasswd wordpressdbase | gzip > wordpressdbase.sql.gz
git add wordpressdbase.sql.gz
echo 'done'
root@host /var/www/.git/hooks#
root@host /var/www/.git/hooks# cat post-checkout 
#!/bin/bash
set -e
baseurl="http://host.domain.com"
echo -n 'loading wordpress database... '
gunzip -c wordpressdbase.sql.gz | mysql -umysqluser -pmysqlpasswd wordpressdbase
echo 'done'
./dbase-baseurl-set.sh $baseurl
root@host /var/www/.git/hooks#
root@host /var/www/.git/hooks# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 10.04.3 LTS
Release:    10.04
Codename:   lucid
root@host /var/www/.git/hooks# 
root@host /var/www/.git/hooks# git --version
git version 1.7.0.4
root@host /var/www/.git/hooks#

Contents of dbase-baseurl-set.sh (note said file was edited from its original for best presentation, unlikely it introduced bugs, but still possible):

#!/bin/bash
# Reset the WordPress "base" URL.  Useful in a git hook
set -e
echo -n "setting WordPress baseurl to '$1'... "printf -v sqlstr 'SELECT * FROM wp_options WHERE option_name = "home" OR option_name = "siteurl"; UPDATE wp_options SET option_v
alue = "%s" WHERE option_name = "home" OR option_name = "siteurl"' "$1"
echo "$sqlstr" | mysql -umysqluser -pmysqlpasswd wordpressdbase > /dev/null
echo "done"