PHP Mysql database schema compare and update

[Danny620]

Has anyone got any script or refence to a tut where i can find a script that compares two mysql database (current) and outdated db and then takes the current db and updates the outdated one to match accordingly. Thanks

[xyph]

Does the script have to determine which has the most recent updates?

 

Why are you storing the same data in two different databases?

 

Are you looking to do this as efficiently as possible? Or can you just dump the entire contents of the "current" database, empty the old database, and repopulate it with the "current" one?

[Danny620]

I have deployed 10 blogging database platforms now when i change the devlopment one i want the 10 blogging database to mirror them changes so i would like a script that would connect to the main devlopment one then connect to an outdated one and update it to match current one.

[xyph]

Are you talking database structure, database data or both?

 

You should be very careful modifying the structure of live databases.

[Danny620]

just structure and it wont affect the application as all 10 is updated automically with new code

[xyph]

So you want to maintain the data on the 10 remote databases?

 

That's going to be tricky.

 

To automate it completely, you first have to grab the details of both tables, sort out the differences, then generate and execute a custom ALTER TABLE query.

 

I'd instead suggest making a script that allows you to perform a single query on all of your remote databases. You then manually create your alter queries based on the changes you've made, and input that into the script.

 

Personally, I'd have a single database. Add a new table, called blogs. That table will have a row for each of the 10 blogs you have, along with any configuration needed specific the that blog. Then, each other table gets an extra row, called blogID, or something similar. Rows in those tables can be specific to a blog by setting blogID to the blog's unique id.

You now have a single database you need to update, rather than trying to update 10 each time a change is made.

[Danny620]

ive made 10 dbs to handle the loads would a mysql be able to handle which could grow to 1000 blogs would it handle ok?

[xyph]

Databases like MySQL are designed to handle millions of rows per table. It's scalable over multiple computers to handle the heaviest of loads.

 

If you get to the point where you're handling tables at those sizes, you'd probably want to hire a database specialist any ways.

 

Generally though, if what you're searching for is properly indexed and the hardware can handle it, you should be safe.

 

Regardless, it'll be WAY less of a headache than trying to manage 1000+ different databases/servers