There is three databases on a website that I’m working on. One is the live_db, one is the dev_deb and one is the test_db. There is also three versions of the site labeled the same (live.domain.com, dev.domain.com and www is the live). Right now when I want to update something I over write the test db and site with code from the dev site, if all is good, then I will copy the dev updates to the live site. So far this system is working pretty well and I’ve written some scripts which tell me which scripts are out of date and it’s really made updating a synch! However, I still have to manually alter the table structure and I’m starting to think there must be a way easier way to do this.
I know that I could drop tables altogether and then do an import from the updated database but the strange challenge here is that on the dev_db the structure is newer, but the data is older. So sometimes it’s not even possible to import either way. What I would really love to discover is some kind of handy mysql or php function that looks at the table structure of the dev_db and then removes or adds in missing columns on the test_db and then eventually the live_db. That would then pave the way for a simple data import if need be.
Right now I’m trying to launch some new inventory management features and I have about 30 columns of structure to add to various tables and I’m feeling a bit overwhelmed by it all and thinking there is probably a smarter way of looking at this problem. If you’ve got any ideas please let me know! Thanks a lot.
Drop the test_db, over write it with the dev_db, do any data imports on the dev_db from the live_db, and then when all is good drop the live_db and overwrite it with the test_db?
So what is it exactly you are suggesting then? I’m confused on the part of getting the dev_db structure onto the live_db with out affecting the live_db data.
if you make structural changes – new or different columns, new tables, etc. – you will eventually have to make those changes to the live database, yes?
this should not be done with drop-and-restore, not to the live database
it works between live and dev, and between dev and test, but not in the other direction, except in the case of test to dev, where it might be used for convenience, if needed
but eventually you will need to promote the stuctural changes to the live databasem and that should only be done with a script (series of SQL statements that performs the changes) with due regard for the live data in the live tables
i’m sorry if i glossed over this or gave you a wrong impression
Ahh, thank you for clarifying. So is there an easy way to compare two mysql tables together and then somehow generate the changes? I’m sure I could write a PHP Script that could pull that off.
