Skip to main content
N
Nick Way
Inspiring
July 7, 2008
Question

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

  • July 7, 2008
  • 3 replies
  • 648 views
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?
This topic has been closed for replies.

3 replies

A
Anonymous
July 9, 2008
sqlyog?

http://www.databasejournal.com/features/mysql/article.php/1584401
N
Nick WayAuthor
Inspiring
July 7, 2008
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..
S
Stressed_Simon
Inspiring
July 7, 2008
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.
D
Dan_Bracuk
Inspiring
July 7, 2008
I would simply run the necessary sql (alter table, create table, etc) against the production db.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices