Re: [tlug] Non destructive update of MySQL database

[Brett Robson <b-robson@example.com>]

Dave M G wrote:
> All I need to do is have new tables and columns added to the existing 
> database structure. Columns or tables deleted in the "source" database 
> don't need to be deleted in the web databases.

Updating a live database is a serious task and would usually mean 
carefully hand coded scripts.


This will no work if tables or columns disappear from the live DB and 
will result in a small downtime.

1 create a temp DB using the structure from the dev DB
2 shutdown the live DB
3 populate the temp DB with the data from live database
4 copy the temp DB files into the live DB (after a backup!)
5 start the live DB

Step 3 is the key. The sqldump produces a series of insert statements.
I haven't done this but I'm pretty sure it will work.


Brett