any suggestions on how to update production database when dev db has had tables added etc

[Nick Way]

hi guys, i've been really struggling with this problem and i wondered if anyone has any clever solutions.

we have a production db (mysql) and a development db (also mysql).

the production db gets lots of rows added to it throughout the course of the day

i am working on the dev db and from time to time need to introduce new columns and new tables on the dev db.

the problems come when it's time to update the production db (by adding the new columns / tables etc) - although i backup the production db and download it and work from that copy. while im working on it new rows are being added to the production db so i cant just upload my amended copy from dev db to prodution (as i would loose those rows).

what i tend to do is to use php myadmin to 're build' the new tables etc on the production db - but it's a real pain.

i wonder if there is a clever 'compare the structure of these 2 databases and update 1 in terms of tables/colum structure whilst keeping the data intact' facility in mysql?

or perhaps i can ask mysql to generate the necessary code that i could then run on the production db to have it amend itself accordingly?

hope this makes sense! what do you guys do?

[Dan_Bracuk]

I would simply run the necessary sql (alter table, create table, etc) against the production db.

[Nick Way]

hi Dan, thanks for that

i suspect this is the way forward, i wish i'd copied and pasted the sql into a word doc or something whenever i'd changed the db on the dev server now that would then make life very easy..

[Stressed_Simon]

For SQL Server there is a commercial product called SQL Compare which is awesome, I do not use MySQL anymore so I am a little out of touch on that front, but there may be a similar product.

What suggest you do is restore your production database in your dev environment and then write all the alter scripts you need and test them on that copy and then test your dev application against it. Then you can be confident that you can ally them to live.

In future you should try and do code releases that include database schema changes coupled with code releases, this makes life much easier. As always I would suggest using svn (subversion) to help with this as it is free and basically awesome.

[Guest]

sqlyog?

http://www.databasejournal.com/features/mysql/article.php/1584401