CVS for Databases?

[Nimit]

Hello! I have developing a PHP/MySql web application with a team. We
are all located in different locations, so we cannot have anything
running on one local server.

We have a CVS setup on a virtual host for the php files, and that has
been working out great. Everyone has a local copy of the site, that
they can work on without being connected to the net and when they are
done working on something, they commit it to the cvs and then everyone
can get it from there.

The problem we have been having is keeping all of our local mysql
databases in sync. We each have a local php/mysql setup running (with
something like wamp). And once in a while while coding, we update the
local database...and after a week or two, we all end up with different
databases.

Is there some way that we can keep our database in sync (other than
hosting the database on the virtual host, because we want to be able to
work on this site when not on the internet (like in an airplane))? Is
there some way to "CVS" a database?

BTW, We have tried using phpmyadmin to dump the entire database to a
sql file and sending that to everyone, but it is a real pain updating
databases in that way because when you try to import that sql file back
on another database, phpmyadmin will timeout, so we have to break down
the file into smaller files etc.

If someone could point me to a good free/open source solution to this
problem, I would be grateful. If you need me to clarify the problem
further, please do not hesitate to ask. Thank you!

- Nimit

[Malcolm Dew-Jones]

Nimit (ni********@gmail.com) wrote:
: Hello! I have developing a PHP/MySql web application with a team. We
: are all located in different locations, so we cannot have anything
: running on one local server.

: We have a CVS setup on a virtual host for the php files, and that has
: been working out great. Everyone has a local copy of the site, that
: they can work on without being connected to the net and when they are
: done working on something, they commit it to the cvs and then everyone
: can get it from there.

: The problem we have been having is keeping all of our local mysql
: databases in sync. We each have a local php/mysql setup running (with
: something like wamp). And once in a while while coding, we update the
: local database...and after a week or two, we all end up with different
: databases.

: Is there some way that we can keep our database in sync (other than
: hosting the database on the virtual host, because we want to be able to
: work on this site when not on the internet (like in an airplane))? Is
: there some way to "CVS" a database?

: BTW, We have tried using phpmyadmin to dump the entire database to a
: sql file and sending that to everyone, but it is a real pain updating
: databases in that way because when you try to import that sql file back
: on another database, phpmyadmin will timeout, so we have to break down
: the file into smaller files etc.

: If someone could point me to a good free/open source solution to this
: problem, I would be grateful. If you need me to clarify the problem
: further, please do not hesitate to ask. Thank you!

: - Nimit

Are you referring to the data within the database, or the database
structures (table definitions etc)?
--

This space not for rent.

[macbri]

After dumping out the database, why rely on phpMyAdmin to re-import it?
Why not just do:

mysqladmin -u root create somedatabase
mysql -u root somedatabase < somedatabase.dump
--
macbri
------------------------------------------------------------------------
macbri's Profile: http://www.macosx.com/forums/member.php?userid=34415
View this thread: http://www.macosx.com/forums/showthread.php?t=237146
macosx.com - The Answer to Mac Support - http://www.macosx.com

[ZeldorBlat]

Here's how we handle this:

First of all, we have our production database. Presumably, any changes
you make to the development database will ultimately need to be made in
production.

For each release, we have a "fix" directory that contains all the
scripts necessary to "upgrade" the production database to the next
release. In our case, we have individual scripts for each
table/view/stored procedure/etc. that needs to change. There is also a
release script, which basically just calls all the individual scripts
in the right order. This whole directory is stored in CVS. As people
need to change stuff in the DB, they either create a new script or
update an existing script -- then make sure it's called in the release
script.

So, at any point, you can restore a backup of production, then run your
release script. This brings the whole database up to the next release.
It also allows you to catch errors in the script(s), which is good
since they will ultimately run against a production database.

Some of us also run instances of the database server on our local
machine. If you do this in conjunction with a webserver, everyone can
have their own, local mirror of production on which to do development.

To synchronize your databases, simply load up a recent production
backup onto your local box, update your CVS directory containing all
the fixes, then run the fix script. Voila!

[matt]

I use these bash scripts to commit DB changes to CVS:
//----Dump DB--------------------------------------
#!/bin/sh

clear

db[0]="db_bplot"
db[1]="db_jobs"
db[2]="db_print_supplies"
db[3]="db_tracker"
db[4]="db_intranet"

for x in "${db[@]}"
do
echo -n "Dumping $x..."
mysqldump -u XXX -pXXX --add-drop-table -B $x > $x.sql;
echo " Done."
done

echo -n "Creating dbs.tar.bz2..."
tar cjpf dbs.tar.bz2 db_*;
echo " Done."

for x in "${db[@]}"
do
rm $x.sql;
done
//-------------Import DB--------------------------------------------
#!/bin/sh

clear

db[0]="db_bplot"
db[1]="db_jobs"
db[2]="db_print_supplies"
db[3]="db_tracker"
db[4]="db_intranet"

echo -n "Extracting dbs.tar.bz2..."
tar jxf dbs.tar.bz2
echo " Done."

for x in "${db[@]}"
do
echo -n "Updating $x..."

mysql -u XXX -pXXX --execute="drop database $x"
mysql -u XXX -pXXX --execute="create database $x"
mysql -u XXX -pXXX < $x.sql
rm $x.sql

echo " Done."
done
Hope this helps!