Php Db synchronization

Question

Hello,

I have a local server where I do all my testing and work. Once I am done, I simply upload the db schema along with the relevant code. However the problem arises when I make some changes to the schema. I have to manually type the "alter table" query on my live server.

Is there a way to get the incremental changes that took place in the db schema so that I can just apply the new ones? Or do I have to manually keep track?

If I use mysqldump, then it produces the schema of the final db (not with alter table etc).

Thank you very much.

Answer 1

I keep the delta sql scripts which go to the production server together with the new code. I script everything so the change of the application from version X to version Y is "automatic". Some people also have undo scripts in case something goes wrong.

Answer 2

How about storing off the changes that you did in development, test those changes as a group to live?

Answer 3

Check out Liquibase. It's Java, but it works fairly well.

Answer 4

MySQL Workbench lets you export ALTER scripts (File -> Export -> Forward Engineer SQL ALTER Script). You'll have first to import your existing database and apply your modifications to it. The commercial version lets you reverse engineer a live database, the community one does it importing a CREATE script instead (this would be a simple dump of the existing structure). Alternatively you can reverse engineer a live database with DBDesigner 4, save all to an XML Model file end import this, but you're gonna lose stuff like the default charset and such...