Re: how to apply new database structure to an existing database, without overwriting data?
Posted by:
James King
Date: August 15, 2005 03:02PM
Brian Culler wrote:
> I am writing a simple blog software application.
> We released a version of this software a while
> ago.
>
> It comes with a sql-tables.sql file, that you can
> just import into your mysql database and it will
> setup all the tables for you, for a fresh
> installation. We made the sql file by just doing
> an export I believe, without exporting the data
> that was in OUR database.
>
> Well, since then several people have downloaded
> and installed our software, and are using it.
>
> We are about to release a new version soon,
> however. Since it is all still under development,
> many things have changed in our database
> structure. We've added new tables, and modified
> or even removed fields from existing tables.
>
> So for the new release, we're going to include a
> new sql-tables.sql file obviously, so that for
> fresh installations they can still install it just
> like normal.
>
> However, the question is... how do you "upgrade"
> all the previous version's users's databases,
> without losing any of their data?
>
> Is there anyway to export just the structure of
> our current database... then somehow apply it so
> that it just goes through and changes another
> database to match the new structure?
>
> Or any ideas at all how to do this?
I'd really like to know how to do this as well --
The technical way I was thinking of going about it is to take a dump of my production database and dump of my development database, diffing the two .sql files and merging them. Then lock, drop all tables, and repopulate the database with the new merged .sql file -- basically so I can "patch" the table structure without losing my production servers' database.
I don't know how I'd do it from a productized "one click" POV.
I'd really like to hear more about other people's experience with this problem before I try out my solution. Backups will protect me, but I'd rather not spend too much time after-hours doing this stuff. ;)