How do you manage changes from development server to production server?

[zacware]

Hi!

What started out as a simple test of mysql has turned into what is
quickly becoming a replacement for our entire business system and web
site

The bad news is that we are now wrestling with how to we continue
working on development the system without risking down time for users.
If we work on a development server, how can we log the changes to table
structure and then apply them to the live database on the production
server when we are done testing?

Is there some sort of tool or log that would just let us apply table
structure changes to another copy of the database?

[Aggro]

zacware wrote:

Is there some sort of tool or log that would just let us apply table
structure changes to another copy of the database?

I use mysql-console for small changes by giving direct sql-commands like:
alter table user add column age int unsigned default 0;

But if there are many changes I usually create .sql file with similar
commands as above, but in one while. And then I use the same
mysql-console either by copy-pasting the queries to there, or if there
are too many, I call from commandline:
$ mysql -u user -p databasename < queries.sql

Which is basicly same as copy-pasting queries from a file into the
database, except without force-flag execution of queries is stopped on
errors.
I usually do this so that I write the query down. Then I copy-paste it
to development release to see that it actually works. After testing I
copy-paste the same query to release-database.

If you want to minimize the risk of making errors. Create one single
file where you will put all the update queries. Then, under or above of
each query write down when and where you have executed it. This way you
should see right away if some query is not executed for example in
release version.

[jerry gitomer]

zacware wrote:

Hi!

What started out as a simple test of mysql has turned into what is
quickly becoming a replacement for our entire business system and web
site

The bad news is that we are now wrestling with how to we continue
working on development the system without risking down time for users.
If we work on a development server, how can we log the changes to table
structure and then apply them to the live database on the production
server when we are done testing?

Is there some sort of tool or log that would just let us apply table
structure changes to another copy of the database?

Since you are dealing with a business and can't afford
interruptions to service you need two things: 1 - a test server
and 2 - a release control system.

A release control system can be as simple as once a week copying
the development database and programs to the test server and
then running an exhaustive set of tests and once satisfied
copying the programs and modifying the database on the
production server.

If you find that you consistently need more than one release per
week you have some serious development problems that you had
better fix -- quickly.

Once your system matures you should be able to increase to semi
annual or annual major releases with minor releases as needed to
cope with external changes that force changes to your applications.

HTH
Jerry

[paul]

zacware wrote:

Hi!

What started out as a simple test of mysql has turned into what is
quickly becoming a replacement for our entire business system and web
site

The bad news is that we are now wrestling with how to we continue
working on development the system without risking down time for users.
If we work on a development server, how can we log the changes to table
structure and then apply them to the live database on the production
server when we are done testing?

Is there some sort of tool or log that would just let us apply table
structure changes to another copy of the database?

I use phpMyAdmin which lets me make those changes manually real easy and
lets me export/import SQL format files that build/rebuild/populate the
tables.

Maybe that doesn't really answer your question though. For incremental
changes once it's up, I simply email myself the various update queries
to keep them together. phpMyAdmin does help visually comparing the two
in case I miss something.

[Malcolm Dew-Jones]

zacware (za*****@comcast.net) wrote:
: Hi!

: What started out as a simple test of mysql has turned into what is
: quickly becoming a replacement for our entire business system and web
: site

: The bad news is that we are now wrestling with how to we continue
: working on development the system without risking down time for users.
: If we work on a development server, how can we log the changes to table
: structure and then apply them to the live database on the production
: server when we are done testing?

: Is there some sort of tool or log that would just let us apply table
: structure changes to another copy of the database?

Often CVS is used as the underlying tool to help control this.

Table definitions and changes (and etc) should always be done via a
script. The person that creates the script should check it into CVS.
After testing, all the scripts that are ready for the next version should
be applied, by someone other than the developer, by checking the code out
of CVS and applying it.

Naturally this should be done several times before being done on the
production server. First the process is done in development, then it's
done to the test server, and finally, it's done to production.

The above just skims the surface, but that's basicly what you need to do.