Mysql and proper output

[itisnot_me]

first off hi again.

Now i dont know where to start this one script or let alone how to execute it. This is what i would like to learn how to do.

I would like to output the current DB structure then take that and update another db with the same structure without harming the data.

Sounds simple yes?

But there is more. I would like to output in a certain way that when i run the script for another DB that it changes what the field is and the default value.

For example on S1 (server1) i have a field that i changed from TEXT to Varchar (32). So on S2 i need to check to see if the field exist and make sure it has the proper layout. meaning i need to change TEXT to Varchar (32). Also if i add fields to the table i need to check to see if exist. If not then create.

Please help me. I have been racking my brain thinking of how to do this. Or can i even do this?

[|-|x]

Are you intending to trigger this process manually? If so I recommend using a database comparison utility to generate the difference scripts for you. This will also give you a chance to visually verify the updates that will occur before executing the script.

There is a discussion on this topic here which lists a number of tools for this purpose. The redgate comparison toolkit is probably the best, though it is commercial, so if you want to use it beyond their 30 day trial you need to buy it. Otherwise Toad is probably the best free tool I have used which does good DB comparisons.

[itisnot_me]

I was looking to write my own. As it would be nice use other scripts but where is the fun in that.

Im thinking about having it as an update feature to something that i am building. Like I would send the commands to the new place and it would execute them. But inorder to to that I need to learn how to output it to a script.

[|-|x]

OK in that case, I suggest you download Toad, and generate some changes in your 2 databases. Then run Toads comparison function and output the script so you can see what its doing. You will see how it creates temporary tables to copy data while updating the schema, drops and recreates foreign keys, and does the schema changes itself.

The best way to learn is to study what others have done, then you can always add your own features as fits your needs. :)